Interface utilization for last month on dashboard

hiello everyone,

first of all thank you in advance for reading this post.

i need your support guys. can anyone from this group help me with SWQL query to fetch last month interfaces utilization and display it on modern dashboard in table ? i need one average value for each interface for the last month.

actually i need this to evaluate and analyze the utilization of each interface which may help me in decrease the speed for under utilized interefaces.

your support is highly appreciated. thank you.

Find more posts tagged with

SWQL

utilization

moderndashboard

interface

report

Accepted answers

pattpall

You can try the below query. I had set up a interface custom property to identify WAN interfaces so the below query only pulls those . I also used Business Hours and no weekends so they didn't skew the numbers since they have much lower activity. You can remove those statements from the query. they are the first 5 lines for the query. For testing purposes, I set the date to the last 2 days, but you can change that value pretty easily in the ADDDay line.

SELECT N.Caption AS [NodeName], I.InterfaceCaption AS [InterfaceName],I.InBandwidth,
I.OutBandwidth,
AVG(T.InAveragebps) AS AvgRecvBPS,
AVG(T.OutAveragebps) AS AvgXmitBPS,
MAX(T.InMaxbps) AS MaxRecvBPS,
MAX(T.OutMaxbps) AS MaxXmitBPS,
AVG(T.InPercentUtil) AS RecvUtil,
AVG(T.OutPercentUtil) AS XmitUtil
FROM orion.npm.interfaces AS I
INNER JOIN Orion.Nodes N ON N.NodeID=I.NodeID
INNER JOIN Orion.NPM.InterfaceTraffic T ON T.InterfaceID=I.InterfaceID
AND N.NodeID=T.NodeID
INNER JOIN Orion.NodesCustomProperties P ON P.NodeID=N.NodeID
WHERE ((([I].[CustomProperties].[WAN_Link]) = (True)))
AND Hour(tolocal(T.ObservationTimestamp)) > 8
and Hour(tolocal(T.ObservationTimestamp)) < 17
AND Weekday(tolocal(T.observationTimestamp)) > 0
AND Weekday(Tolocal(T.observationTimestamp)) < 6
AND toLocal(T.observationtimestamp) > ADDDay(-2, TOLocal(GETDATE()))
GROUP BY I.InterfaceCaption, N.Caption, I.InBandwidth , I.OutBandwidth

All comments

amer10286

@KMSigma.SWI @chris.tian @mark.gover @Seashore could you please help me to get this SWQL query ? thank you.

Sriram_1

I have came across below query..SELECT n.Caption,n.DetailsUrl,n.StatusLED,n.InPercentUtil,n.OutPercentUtil
FROM Orion.NPM.Interfaces n where getutcdate()> getutcdate()-30

you can add joins and date functions to get other details into query

amer10286

hi sriram, its not working. i mean its not giving me the average or peak utilization of last month.

im new to SWQL, i was trying hard with graphical query builder in modern dashboard but failing to fetch data. i could create report but i want same data to be displayed on dashboard. anyways, thanks for the support.

pattpall

amer10286

@pattpall thank you very much for the query, so far it didnt fail and working, im just validating and verifying the results it is returning. i will reach you out if i need further help in this query. once again, thanks a lot.

pattpall

Sounds good amer10286

pattpall

let me know if that worked for you.

amer10286

Dear Patt,

i tried with your SWQL query, but i notice something is not right or missing. because im not getting the correct data. to validate the output, i created a report and was comparing it with the results of your query, its not the same.

in report, i have an option of "last 30 days" or "last month" these both filters have different value. i need result for "Last Month" could you please let me know how to add WHERE condition for last month ? i was trying this condition from your query "AND toLocal(T.observationtimestamp) > ADDDay(-30, TOLocal(GETDATE()))" im not sure if it is correct, but its not giving me the desired output.