Reporting and Filtering by Time of Day

Just as a preface to this post, I just want to say that I work for an entity that supports a large group of schools and that I do not know SQL all that well. I have used a lot from this community so I figured that I would give something (hopefully useful) back.

I know that the Report Writer is able to do some complex filtering when it comes to reporting. The web version has been great and has allowed my team and I to share our reports with our school districts but one thing that it cannot do is report on activity just during the school day. While this is not a major issue, it does throw off some numbers like the bandwidth utilization of links. Network traffic during off hours for our schools is generally non existent so the average is thrown off quite a bit when you consider that they are only using the links for ~9 hours a day and the other 15 are negligible. They also do Windows updates, backups and things in the evenings as well, which can throw off peak usage, and make it look like they may need a larger connection. So after looking around quickly on thwack, and not finding a way to only report on a specific time frame, I decided that I would try and write something in SQL to do this.

Here is what I came up with. It loses something when its not colored from the Database Manager built into Solarwinds. I tried to comment everything (comments are anything after a ''--'') with what it is/does but again I am not an SQL guru so I could be wrong about it. I believe that it works correctly though.

--Select displays everything within it (Each thing is separated with a comma) as a column. This is what is going to be output.

SELECT Nodes.Caption AS NodeName, --Outputs Node.Caption as the Broadband Device Name as a column.

  CAST(DateTime AS Date) AS Date, --Outputs the Date of the DateTime Column as a column

  CAST(ROUND(AVG((In_Averagebps)/1024/1024),2) AS varchar) + ' Mbps' AS 'Average Receive', --Outputs the Rounded Average Receive from "In_Averagebps" in Mbps

  CAST(ROUND(MAX((In_Maxbps/1024/1024)),2) AS varchar) + ' Mbps' AS 'Peak Receive', --Outputs the Rounded Peak Receive from "In_Maxbps" in Mbps

  CAST(ROUND(AVG((Out_Averagebps/1024/1024)),2) AS varchar) + ' Mbps' AS 'Average Transmit', -- Outputs the Rounded Average Transmit from "Out_Averagebps" in Mbps

  CAST(ROUND(MAX((Out_Maxbps/1024/1024)),2) AS varchar) + ' Mbps' AS 'Peak Transmit' -- Outputs the Rounded Peak Transmit from "Out_Maxbps" in Mbps

--From is where the data is going to be pulled from, it is also were you join tables together.

FROM [InterfaceTraffic] -- Use data from "InterfaceTraffic" Table

  JOIN Interfaces ON InterfaceTraffic.InterfaceID = Interfaces.InterfaceID -- Combines "Interfaces" and "InterfaceTraffic"

  JOIN Nodes ON Interfaces.NodeID = Nodes.NodeID -- Combines "Interfaces" and "Nodes"

--Where is like a filter, it specifies what you are using to get the data that you want.

WHERE DATEDIFF(day, DateTime, GetDate()) <= 7 -- Last 7 Days

  AND CONVERT(varchar, DateTime,114) BETWEEN '07:00:00:00' AND '16:00:00:00' --Converts the DateTime column to provide School Hours in Military as "16:00:00:00" and filters results to just between the 2 listed times.

  AND   --Interfaces.InterfaceID = 4469 --What is being reported on. Used as a test in the Database Manger because it times out on the whole query

  Interfaces.InterfaceAlias = 'LEGACY_BROADBAND_CONNECTION' -- Name of the interface that I want to monitor. It is the same on every device.

  AND DATEPART(weekday,DateTime) <> 1 -- Excludes Sunday

  AND DATEPART(weekday,DateTime) <> 7 -- Excludes Saturday

--Group By combines the data together.

GROUP BY CAST(DateTime as date), --groups data into a single row based on date.

  Nodes.Caption -- groups dates for each device together by Node Name

--How the rows are ordered.

ORDER BY CAST(DateTime as date), --Ordered by Date.

  Nodes.Caption -- Adds sorting alphabetically by Node Caption Name

The output looks something like this although this is missing its headers:

If anyone has a better way of doing this or feedback, it would be appreciated. In addition, if anyone has any questions, I would be happy try and help. Again, I hope someone will find this helpful and save them some time.

- Brandon