Tweaking SQL server 2008 code to capture historical received percentage utilization of an interface

Hi Everyone,

Actually, I'm new to SolarWinds and SQL server 2008 commands. Currently, I use NPM 10.6. And, in my work environment, SolarWinds runs on Microsoft SQL server 2008.

Although, I stand corrected but I observed that the Orion Report Writer cannot capture historical percentage utilization of an interface, whether transmit or receive; instead, it can only capture current utilization. Please, I need help if capturing the historical one is possible.

Also, I have another challenge. I want to be able to generate reports concerning node and/or interface availability between, say 6:00am to 8:00pm each weekday only, that is, from Mondays to Fridays.

I tried doing it using the Orion Report Writer but all I can get under the Time Frame box is Last 7 days, Last Month, etc. Please how do I go about it? Find below the code I wrote while trying to generate a new report and using the Advanced SQL report type.

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime

SET @StartDate = '2014-01-20'
SET @EndDate = '2014-01-24'

WHILE (@StartDate < @EndDate)

--- CASE (DATEPART(dw, @StartDate))

SELECT TOP 10000
Nodes.NodeID AS NodeID, Nodes.Caption AS NodeName, Nodes.Location AS Location, Interfaces.InterfaceName AS Interface_Name, AVG(Interfaces.InPercentUtil) AS Recv_Percent_Utilization, AVG(Interfaces.Inbps) AS Recv_bps, AVG(ResponseTime.Availability) AS AVERAGE_of_Availability, MIN(ResponseTime.MinResponseTime) AS MIN_of_Minimum_Response_Time, AVG(ResponseTime.AvgResponseTime) AS AVERAGE_of_Average_Response_Time, MAX(ResponseTime.MaxResponseTime) AS MAX_of_Peak_Response_Time

FROM
Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID) INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)

WHERE
(
(DateTime BETWEEN @StartDate AND @EndDate) AND (Nodes.Caption LIKE '%AIDOS%') AND ((Interfaces.InterfaceName LIKE '%Tunnel%%') OR
(Interfaces.InterfaceName LIKE '%Gigabitethernet%0/2%%'))

)

GROUP BY Nodes.NodeID, Nodes.Caption, Nodes.Location, Interfaces.InterfaceName

ORDER BY Nodes.NodeID, DateTime ASC

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Honestly, the code ran but I do not think it's actually correct. Please I need help. Many thanks as you contribute to help me work better.

Find more posts tagged with

network_monitoring

network_management

Accepted answers

All comments

rob.hock

Perhaps a custom table (from web-based reports) would work for you:

1-31-2014 1-13-28 PM.jpg

sgenius

Hi Rob,

Thanks for responding.

Actually, I do not seem to understand what you did in the web console report. Under what Orion Object Type can I get the Database columns for Transmit Pecernt Utilization and Receive Percent Utilization?

I would really appreciate a reply. Thanks.

Just to emphasize what I want from the report; All I want is to get statistics from 6:00am to 8:00pm of each week day (excluding weekends), and not 24 hours or should it calculate based on, for example, data over the last xx hours.

For example, today is Monday, and I want to generate weekly reports (6:00am to 8:00pm of Monday through Friday) of last week (January 27, 2014 to January 31, 2014). This is actually my challenge here.

Regards.

LadaVarga

Hello,

Could you please try prepared report here:

Example Business Hours Report for Interface Percent Utilization

I think it will be fit your needs.

Thanks

sgenius

Hi LadaVarga,

Honestly, my words are not enough to express how grateful I am for your help. It was really an eye-opener. Many thanks. Please find below, the SQL 2008 server code on what I did to achieve mine.

SELECT TOP 10000 Convert(DateTime,Floor(Cast((DateTime) as Float)),0) AS SummaryDate,
Nodes.NodeID AS NodeID,
Interfaces.InterfaceID AS InterfaceID,
Nodes.Caption AS NodeName,
Nodes.Location AS Location,
Interfaces.Caption AS Interface_Caption,
AVG(Case InBandwidth
            When 0 Then 0
            Else (In_Averagebps/InBandwidth) * 100
            End) AS AVERAGE_of_Recv_Percent_Utilization

FROM
(Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)) INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID AND InterfaceTraffic.NodeID = Nodes.NodeID)

WHERE
( DateTime BETWEEN 41666 AND 41673.7916666667 )
AND
(
(Interfaces.Caption LIKE '%%Tunnel%%') AND
(
   (DatePart(Hour,DateTime) >= 8)) AND
(DatePart(Hour,DateTime) <= 18) AND
(
   (DATEPART(weekday, DateTime) = 2) OR
   (DATEPART(weekday, DateTime) = 3) OR
   (DATEPART(weekday, DateTime) = 4) OR
   (DATEPART(weekday, DateTime) = 5) OR
   (DATEPART(weekday, DateTime) = 6))
)

GROUP BY Convert(DateTime,Floor(Cast((DateTime) as Float)),0),
Nodes.NodeID, Interfaces.InterfaceID, Nodes.Caption, Nodes.Location, Interfaces.Caption

ORDER BY SummaryDate ASC, 4 ASC, 6 ASC

Actually, there exists a preconfigured report on Orion Report Writer named "Average and Peak Traffic Rates - WAN Interfaces Last 7 Days." What I did was to tweak it to fit into what I want to achieve. You did it!!!!!

However, please I like to add: is it possible to get historical response times (minimum, average, and maximum), and historical packet losses for an interface? Kindly assist me.

I want to add it to the code aforewritten above and generate one report containing all these pieces of information. I must confess that I'm new to this.

Regards.

LadaVarga

Hello sgenius,

Interface doesn't give such info (response time and packet loss). If you need this info I recommended create similar report for nodes (SELECT TOP 1000 * FROM [dbo].[ResponseTime]).

For interfaces you can get SELECT TOP 1000 * FROM [dbo].[InterfaceErrors] which can you provide info about errors or dropped packets.

Lada

sgenius

Hi Lada,

I did it. Find below, Business hours availability for nodes.

SELECT TOP 10000 Convert(DateTime,Floor(Cast((DateTime) as Float)),0) AS SummaryDate,
Nodes.Caption AS NodeName,
Nodes.Location AS Location,
MIN(ResponseTime.MinResponseTime) AS MIN_of_Minimum_Response_Time,
AVG(ResponseTime.AvgResponseTime) AS AVERAGE_of_Average_Response_Time,
MAX(ResponseTime.MaxResponseTime) AS MAX_of_Peak_Response_Time,
AVG(ResponseTime.PercentLoss) AS AVERAGE_of_Percent_Loss,
AVG(ResponseTime.Availability) AS AVERAGE_of_Availability

FROM
Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)

WHERE
( DateTime BETWEEN 41664.2916666667 AND 41667.7916666667 )
AND
(
(
   (DatePart(Hour,DateTime) >= 8) AND
   (DatePart(Hour,DateTime) <= 18) AND
   (
    (DATEPART(weekday, DateTime) = 2) OR
    (DATEPART(weekday, DateTime) = 3) OR
    (DATEPART(weekday, DateTime) = 4) OR
    (DATEPART(weekday, DateTime) = 5) OR
    (DATEPART(weekday, DateTime) = 6)))
)

GROUP BY Convert(DateTime,Floor(Cast((DateTime) as Float)),0),
Nodes.Caption, Nodes.Location

ORDER BY SummaryDate ASC

----------------------------------------------------------------------------------------------------------

Regards.