SQL Query for Interface Utilization

I am trying to get historic utilization for a specific interface. Specifically I would like to see the Average and Max Utilization by day for the last month for InterfaceA.

Example ( InterfaceA 09/01/2013 30% 67%)

This data is not stored in the DB it is calculated on the fly by solarwinds.

First - Why doesnt solarwinds calculate this percentage then store that number in the DB?

Second - Why Is there no report to look at this data historically? Only the top 25 current.

Third - Has anyone developed a custom SQL query that can calculate this data for a specific interface. I am having trouble with the calculations

Also I have opened a ticket with support and they were somewhat helpful in providing the calculation that happens behind the scenes see the attached excel doc for the information they provided about how utilization % is calculated

Interface utilization calculation.xls

Find more posts tagged with

Reports

interface_utilization

historical_report

Accepted answers

mezdem

sorry was a bit rushed earlier.

Ok the below should give you what you need, if you want per interface just add interfaceid, i have limited this to one for testing.

This gives the same (within a very small difference) value as if you ran the interface utilization chart in NPM, but put the 'SampleInterval' to 5mins. If you export this data in xls then AVG the Util columns you get the results very very similar to the below script... However if you use 'Daily' sample on the chart in NPM for the interafce and export - it provides data which looks to be 1 day out - but maybe it does its averages based on the hour you are running the report? maybe SW can fathom that one, as i cannot. Both extracts attached for perusal.

This kind of reporting should be standard fucntionality and i agree should not have to be done via sql all the time.

SELECT
TOP (100) PERCENT
CONVERT(DateTime, FLOOR(CAST(dbo.InterfaceTraffic.DateTime AS Float)), 0) AS DayRound,
dbo.InterfaceTraffic.NodeID,
dbo.InterfaceTraffic.InterfaceID,
dbo.Interfaces.Caption,
AVG(dbo.InterfaceTraffic.In_Averagebps) AS In_Averagebps,
MIN(dbo.InterfaceTraffic.In_Minbps) AS In_Minbps,
MAX(dbo.InterfaceTraffic.In_Maxbps) AS In_Maxbps,
AVG(dbo.InterfaceTraffic.Out_Averagebps) AS Out_Averagebps,
MIN(dbo.InterfaceTraffic.Out_Minbps) AS Out_Minbps,
MAX(dbo.InterfaceTraffic.Out_Maxbps) AS Out_Maxbps,
AVG(dbo.Interfaces.InBandwidth) AS InBandwidth,
AVG(dbo.Interfaces.OutBandwidth) AS OutBandwidth,
MAX(CASE WHEN InBandwidth + OutBandwidth = 0 THEN 0 WHEN InBandwidth = 0
THEN (Out_Averagebps / OutBandwidth) * 100 WHEN OutBandwidth = 0
THEN (In_averagebps / InBandwidth) * 100
ELSE ((Out_averageBps / OutBandwidth) + (In_AverageBps / InBandwidth)) * 50 END) AS Util,
AVG(dbo.Interfaces.InterfaceSpeed) AS InterfaceSpeed, AVG(CASE WHEN InBandwidth = 0 THEN 0 ELSE (In_Averagebps / InBandwidth) * 100 END) AS [InUTil%],
AVG(CASE WHEN OutBandwidth = 0 THEN 0 ELSE (Out_AverageBps / OutBandwidth) * 100 END) AS [OutUtil%]
FROM
dbo.InterfaceTraffic INNER JOIN
dbo.Interfaces ON
dbo.InterfaceTraffic.NodeID = dbo.Interfaces.NodeID
AND dbo.InterfaceTraffic.InterfaceID = dbo.Interfaces.InterfaceID
GROUP BY
dbo.InterfaceTraffic.NodeID,
CONVERT(DateTime, FLOOR(CAST(dbo.InterfaceTraffic.DateTime AS Float)), 0),
dbo.InterfaceTraffic.InterfaceID,
dbo.Interfaces.Caption
HAVING
(CONVERT(DateTime, FLOOR(CAST(dbo.InterfaceTraffic.DateTime AS Float)), 0) > GETUTCDATE() - 7)
AND (dbo.InterfaceTraffic.InterfaceID = 3982)
ORDER BY
DayRound,
dbo.InterfaceTraffic.NodeID,
dbo.InterfaceTraffic.InterfaceID

All comments

ram.esakky

Hi,

Can you please post the ticket number so we can track it and the get issue resolved. Thank you

-Ram Esakky

PavelSuchy

Hi mivy00,

can you provide information about NPM version you are running on?

Thanks,

Pavel

mezdem

i guess when i first started it was strange no util wass stored. but raw data is there.

there is a view called InterfaceTraffic, which combines detailed, hourly, daily data. this is what is shown below.

i think sw report writer does allow u to easily select historic traffic data. will check.

the calculation, or sql you will need is this, as in sql query. There are min/max/avg values.

CASE WHEN InBandwidth = 0 THEN 0 ELSE (In_maxbps / InBandwidth) * 100 END AS [InUTil%],

CASE WHEN OutBandwidth = 0 THEN 0 ELSE (Out_maxBps / OutBandwidth) * 100 END AS [OutUtil%]

SELECT TOP (100) PERCENT dbo.InterfaceTraffic.NodeID, dbo.InterfaceTraffic.InterfaceID, dbo.InterfaceTraffic.DateTime, dbo.Interfaces.Caption,

.InterfaceTraffic.In_Averagebps, dbo.InterfaceTraffic.In_Minbps, dbo.InterfaceTraffic.In_Maxbps, dbo.InterfaceTraffic.In_TotalBytes,

.InterfaceTraffic.In_TotalPkts, dbo.InterfaceTraffic.In_AvgUniCastPkts, dbo.InterfaceTraffic.In_MinUniCastPkts, dbo.InterfaceTraffic.In_MaxUniCastPkts,

.InterfaceTraffic.In_AvgMultiCastPkts, dbo.InterfaceTraffic.In_MinMultiCastPkts, dbo.InterfaceTraffic.In_MaxMultiCastPkts, dbo.InterfaceTraffic.Out_Averagebps,

.InterfaceTraffic.Out_Minbps, dbo.InterfaceTraffic.Out_Maxbps, dbo.InterfaceTraffic.Out_TotalBytes, dbo.InterfaceTraffic.Out_TotalPkts,

.InterfaceTraffic.Out_AvgUniCastPkts, dbo.InterfaceTraffic.Out_MaxUniCastPkts, dbo.InterfaceTraffic.Out_MinUniCastPkts,

.InterfaceTraffic.Out_AvgMultiCastPkts, dbo.InterfaceTraffic.Out_MinMultiCastPkts, dbo.InterfaceTraffic.Out_MaxMultiCastPkts, dbo.InterfaceTraffic.Archive,

.Interfaces.InBandwidth, dbo.Interfaces.OutBandwidth,

CASE WHEN InBandwidth + OutBandwidth = 0 THEN 0 WHEN InBandwidth = 0 THEN (Out_maxbps / OutBandwidth)

* 100 WHEN OutBandwidth = 0 THEN (In_maxbps / InBandwidth) * 100 ELSE ((Out_maxBps / OutBandwidth) + (In_maxBps / InBandwidth)) * 50 END AS Util,

.Interfaces.InterfaceSpeed, dbo.InterfaceTraffic.In_Maxbps + dbo.InterfaceTraffic.Out_Maxbps AS avgbps, dbo.Interfaces.CircuitID, 1 AS sample, DATEPART(day,

.InterfaceTraffic.DateTime) AS Day, CASE WHEN InBandwidth = 0 THEN 0 ELSE (In_maxbps / InBandwidth) * 100 END AS [InUTil%],

CASE WHEN OutBandwidth = 0 THEN 0 ELSE (Out_maxBps / OutBandwidth) * 100 END AS [OutUtil%]

FROM dbo.InterfaceTraffic INNER JOIN

.Interfaces ON dbo.InterfaceTraffic.NodeID = dbo.Interfaces.NodeID AND dbo.InterfaceTraffic.InterfaceID = dbo.Interfaces.InterfaceID

WHERE (DATEPART(weekday, dbo.InterfaceTraffic.DateTime) = 2 OR

DATEPART(weekday, dbo.InterfaceTraffic.DateTime) = 3 OR

DATEPART(weekday, dbo.InterfaceTraffic.DateTime) = 4 OR

DATEPART(weekday, dbo.InterfaceTraffic.DateTime) = 5 OR

DATEPART(weekday, dbo.InterfaceTraffic.DateTime) = 6) AND (CONVERT(Char, dbo.InterfaceTraffic.DateTime, 108) >= '09:00') AND (CONVERT(Char,

.InterfaceTraffic.DateTime, 108) <= '17:00') AND (dbo.InterfaceTraffic.DateTime > GETDATE() - 30) AND (dbo.Interfaces.CircuitID IS NOT NULL)

Note. i have left in my filters, circuitid being not null and working time 9am to 5 pm, mon to fri bit clunky was a quick report.

mivy00

I am on version 10.5

Thanks Maxdem for the above......after I made some modifications I was able to get the above SQL to run. I am still not sure that is what i am looking for..... It doesnt appear to group the utilization by day the output provides 427 lines and Im not sure how to group it to what I need and oviously there are a number of fields there i dont need to see. Thank you though. If no one else has any better options i may have to play with that until i get what i need.

mezdem

sorry was a bit rushed earlier.

Ok the below should give you what you need, if you want per interface just add interfaceid, i have limited this to one for testing.

This kind of reporting should be standard fucntionality and i agree should not have to be done via sql all the time.

SELECT
TOP (100) PERCENT
CONVERT(DateTime, FLOOR(CAST(dbo.InterfaceTraffic.DateTime AS Float)), 0) AS DayRound,
dbo.InterfaceTraffic.NodeID,
dbo.InterfaceTraffic.InterfaceID,
dbo.Interfaces.Caption,
AVG(dbo.InterfaceTraffic.In_Averagebps) AS In_Averagebps,
MIN(dbo.InterfaceTraffic.In_Minbps) AS In_Minbps,
MAX(dbo.InterfaceTraffic.In_Maxbps) AS In_Maxbps,
AVG(dbo.InterfaceTraffic.Out_Averagebps) AS Out_Averagebps,
MIN(dbo.InterfaceTraffic.Out_Minbps) AS Out_Minbps,
MAX(dbo.InterfaceTraffic.Out_Maxbps) AS Out_Maxbps,
AVG(dbo.Interfaces.InBandwidth) AS InBandwidth,
AVG(dbo.Interfaces.OutBandwidth) AS OutBandwidth,
MAX(CASE WHEN InBandwidth + OutBandwidth = 0 THEN 0 WHEN InBandwidth = 0
THEN (Out_Averagebps / OutBandwidth) * 100 WHEN OutBandwidth = 0
THEN (In_averagebps / InBandwidth) * 100
ELSE ((Out_averageBps / OutBandwidth) + (In_AverageBps / InBandwidth)) * 50 END) AS Util,
AVG(dbo.Interfaces.InterfaceSpeed) AS InterfaceSpeed, AVG(CASE WHEN InBandwidth = 0 THEN 0 ELSE (In_Averagebps / InBandwidth) * 100 END) AS [InUTil%],
AVG(CASE WHEN OutBandwidth = 0 THEN 0 ELSE (Out_AverageBps / OutBandwidth) * 100 END) AS [OutUtil%]
FROM
dbo.InterfaceTraffic INNER JOIN
dbo.Interfaces ON
dbo.InterfaceTraffic.NodeID = dbo.Interfaces.NodeID
AND dbo.InterfaceTraffic.InterfaceID = dbo.Interfaces.InterfaceID
GROUP BY
dbo.InterfaceTraffic.NodeID,
CONVERT(DateTime, FLOOR(CAST(dbo.InterfaceTraffic.DateTime AS Float)), 0),
dbo.InterfaceTraffic.InterfaceID,
dbo.Interfaces.Caption
HAVING
(CONVERT(DateTime, FLOOR(CAST(dbo.InterfaceTraffic.DateTime AS Float)), 0) > GETUTCDATE() - 7)
AND (dbo.InterfaceTraffic.InterfaceID = 3982)
ORDER BY
DayRound,
dbo.InterfaceTraffic.NodeID,
dbo.InterfaceTraffic.InterfaceID

mivy00

Sorry Ram, I just noticed your request the ticket number was 525993.

mivy00

Thanks, this worked great!! My only question. Is the Util field that is shown the average utilization for that day or the max Utilization ?

josephwdowling

I tried running this in my query builder and got "Query is not valid"

harpreet.singh

Hi all, the post has been extremely useful to get what we needed. Thinking that it might help someone out there I've adjusted the original query and here's the version:

--Filters only TenGig interfaces

SELECT   
     TOP (100) PERCENT
     CONVERT(DateTime, FLOOR(CAST(dbo.InterfaceTraffic.DateTime AS Float)), 0) AS DayRound,
     dbo.Interfaces.FullName,
     dbo.Interfaces.IfName,
     CAST(MAX(dbo.InterfaceTraffic.In_Maxbps)/1000000 AS DECIMAL(10,3)) AS PeakReceiveMbps,
     CAST(MAX(dbo.InterfaceTraffic.Out_Maxbps)/1000000 AS DECIMAL(10,3)) AS PeakTransmitMbps,
     CAST(AVG(dbo.InterfaceTraffic.In_Averagebps)/1000000 AS DECIMAL(10,3)) AS In_AverageMbps,
     CAST(AVG(dbo.InterfaceTraffic.Out_Averagebps)/1000000 AS DECIMAL(10,3)) AS Out_AverageMbps,
     CAST(MAX(CASE WHEN InBandwidth = 0 THEN 0 ELSE (In_Maxbps / InBandwidth) * 100 END) AS DECIMAL(10,0)) AS [PeakInUTil%],
     CAST(MAX(CASE WHEN OutBandwidth = 0 THEN 0 ELSE (Out_Maxbps / OutBandwidth) * 100 END) AS DECIMAL(10,0)) AS [PeakOutUtil%]
FROM       
     dbo.InterfaceTraffic
     INNER JOIN dbo.Interfaces
          ON dbo.InterfaceTraffic.NodeID = dbo.Interfaces.NodeID
          AND dbo.InterfaceTraffic.InterfaceID = dbo.Interfaces.InterfaceID
WHERE dbo.InterfaceTraffic.DateTime BETWEEN (DATEADD(dd, DATEDIFF(dd,0, getdate()) -1, 0)) AND (DATEADD(dd, DATEDIFF(dd,0, getdate()) , 0))
     AND dbo.Interfaces.InterfaceSpeed = '10000000000'
     AND dbo.Interfaces.InterfaceTypeName = 'ethernetCsmacd'
GROUP BY
GROUP BYdbo.Interfaces.FullName,CONVERT(DateTime, FLOOR(CAST(dbo.InterfaceTraffic.DateTime AS Float)), 0),dbo.Interfaces.ifNameHAVING     (CONVERT(DateTime, FLOOR(CAST(dbo.InterfaceTraffic.DateTime AS Float)), 0) > GETUTCDATE() - 7)ORDER BYDayRound,dbo.Interfaces.FullName

vineetjalali

Hello Harpreet,

I tried the above SWQL querry for my stuff, looks like its not working from querry perspective. Can you please elaborate more how its working? Did you test it on your own solarwinds ?