SQL Alert Help

So this query is pretty likely to time out just do to the sheer amount of data it would have to crunch unless you environment is very small.  The interface in/out total bytes value is the total byes since the last time we polled.  So to get to your situation we are going to have to add them all up for each interface from the beginning of the month until now.

I'm going to need some clarification here, is that 25 MB per month a limit for each interface individually, or is it 25 MB total across them all at once?

The InterfaceTrafficByDays holds the daily total for the interface... so yea, i would need the add up the days to determine if the interface went over 25Mb of usage.  There is one value per day in the table... so the query would only have to add up 31 values (days) at the most per Column in the table (2 columns, In_TotalBytes and Out_TotalBytess), per interface... total of about 7 interfaces. 

Yes, 25Mb per month limit for EACH interface... .  If any one of those interfaces goes over the usage threshold, we need to trigger an alert.

Like I said, my SQL is limited, and what I have above is patched together via some "googling".... so I'm open to any and all options

Thanks

Interesting question - Could you expand on the problem? Not sure I understand the issue yet.

UNION can add another query, not sure what "inside the join" means in context, also not entirely sure how Orion handles UNION here. It doesn't seem like that would be required though.

You were definitely over complicating this, but if you don't know SQL you wouldn't know what you need and what's irrelevant.

First of all you don't want this to be a node alert, it should be an interface alert on the interface that breached the threshold.

Like I mentioned initially, this query might time out in a big environment because the interface traffic history is often one of the biggest tables for many customers, but this is about as good as you'll get.

The -- is a comment indicator, i included a lot more info than the query needs in case you want to reverse engineer how it works

SELECT Interfaces.Uri, Interfaces.DisplayName FROM Orion.NPM.Interfaces AS Interfaces

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

where interfaceid in

(

SELECT distinct i.interfaceid

--, min(tolocal(i.DateTime)) as [Earliest Timestamp]

--, sum(i.InTotalBytes) as [InBytesTotal]

--, sum(i.OutTotalBytes) as [OutBytesTotal]

from orion.npm.InterfaceTraffic i

where tolocal(i.DateTime) >= DATETRUNC('month',GETDATE()) --datetrunc gets us the start of the current calendar month

and i.interfaceid in (96662, 85498, 96664, 88157, 99464, 99470, 96943) --give me all the interface id's

group by i.interfaceid

having sum(i.InTotalBytes) > 25000000 --could be 26214400 if counting using binary

or sum(i.OutTotalBytes) > 25000000 --could be 26214400 if counting using binary

)

It should look like this

-Marc Netterfield

    Loop1 Systems: SolarWinds Training and Professional Services

•                 LinkedIN: Loop1 Systems
•                 Facebook: Loop1 Systems
•                 Twitter: @Loop1Systems

Awesome... this seems to have done the trick.   My SQL is "ehh" and my SWQL is worse... so this was just what I needed.

Quick question... if i wanted to just run this query normally (say via a custom SWQL report), and show the values for In Total Bytes and Out Total Bytes, and the combined In+Out, so i could verify what values are getting returned, how would I do that... modify the select statement or something else... and what should I put? 

Thanks again for all your help.

This should work as a custom swql query

SELECT distinct i.interfaceid

, min(tolocal(i.DateTime)) as [Earliest Timestamp]

, sum(i.InTotalBytes) as [InBytesTotal]

, sum(i.OutTotalBytes) as [OutBytesTotal]

from orion.npm.InterfaceTraffic i

where tolocal(i.DateTime) >= DATETRUNC('month',GETDATE()) --datetrunc gets us the start of the current calendar month

and i.interfaceid in (96662, 85498, 96664, 88157, 99464, 99470, 96943) --give me all the interface id's

group by i.interfaceid

--having sum(i.InTotalBytes) > 25000000 --could be 26214400 if counting using binary

--or sum(i.OutTotalBytes) > 25000000 --could be 26214400 if counting using binary

Order by i.interfaceid

Awesome!! Thanks so much for your help.

One more quick thing... for the report... what would i need to do if i wanted to join another table like ("Interfaces"... for example) so I could add additional data to the report.... Like adding Interfaces.FullName or some specific Interface Custom Properties?

add something like

join orion.npm.interfaces int on int.interfaceid=i.interfaceid

for all the columns you want to pull from orion.interfaces just put them in the select areas as int.columnname, for custom properties it would be int.customproperties.propertyname

Have you installed SWQL studio on your orion server yet?  its nearly impossible to get anywhere doing this without it

Home · solarwinds/OrionSDK Wiki · GitHub

Releases · solarwinds/OrionSDK · GitHub

So, as the join seems to work fine, but when i add the column name to the select (in this case its int.FullName), the query fails... not sure what im doing wrong.  :

SELECT distinct i.interfaceid, int.FullName

, min(tolocal(i.DateTime)) as [Earliest Timestamp]

, sum(i.InTotalBytes) as [InBytesTotal]

, sum(i.OutTotalBytes) as [OutBytesTotal]

from orion.npm.InterfaceTraffic i

join orion.npm.interfaces int on int.interfaceid=i.interfaceid

where tolocal(i.DateTime) >= DATETRUNC('month',GETDATE()) --datetrunc gets us the start of the current calendar month

and i.interfaceid in (96662, 85498, 96664, 88157, 99464, 99470, 96943) --give me all the interface id's

group by i.interfaceid

--having sum(i.InTotalBytes) > 25000000 --could be 26214400 if counting using binary

--or sum(i.OutTotalBytes) > 25000000 --could be 26214400 if counting using binary

Order by i.interfaceid

Ah yeah, because we are doing the sum/min aggregations you have to also add those additional columns to the group by section.

group by i.interfaceid, int.FullName