Availability SQL data in alerts

Hello,

I'm working on making our NPM alert emails more informative.

In the email I want to include Availability stats for 'Today', 'Yesterday', 'Last 7 Days' and so on, as the default Node page does in Orion.

I'm getting there but could use some help with the SQL syntax. In the email I have

Availability Last Month: ${SQL:Select AVG(ResponseTime.Availability) AS AVERAGE_of_Availability FROM Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID) WHERE ( DateTime BETWEEN DATEADD(MONTH, -2, GETDATE()) AND DATEADD(MONTH, -1, GETDATE()) ) AND ((Nodes.NodeID = ${NodeID}))}%

Which gives me

Average Availability Last Month: 100%

Which doesn't match the screenshot below. I've confirmed the SQL gives the same result using the Solarwinds Database Manager and it comes back with 100%

Does anyone have this SQL recipe already written out for the data in the screenshot below, or any SQL wizards who can help?

Thanks

~sm

Find more posts tagged with

Alert

Availability

Accepted answers

All comments

SimonMorris

Oh dear - not getting any love from the forum on this one.

Can I change the question to "Are you doing anything creative and information rich with your alert emails... if so would you share with me?"

Thanks

~sm

sedmo

I think your problem is related to the way you are specifying the dates to be selected in your query. Your current code selects records from the availability table that have dates between two months prior to the current date and one month prior to the current date. This means if you run the query today it will select records with dates between 5/8/2010 and 6/8/2010. If you run it tomorrow you will get records between 5/9/2010 and 6/9/2010. Try modifying your query to match the one below to get records from last month.

Availability Last Month: ${SQL:Select AVG(ResponseTime.Availability) AS AVERAGE_of_Availability FROM Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)
WHERE ( DateTime BETWEEN dateadd(mm,-1,dateadd(mm,datediff(mm,0,getdate()),0)) AND dateadd(ms,-3,dateadd(mm,0,dateadd(mm,datediff(mm,0,getdate()),0))) ) AND ((Nodes.NodeID = ${NodeID}))}%