Need Help in KPI SWQL query

Question

Hi 
 
 can any one generate SQL query to get % Availability of 7 Days , Last month. Mention query is of Today's Availability. 
 
 SELECT Availability, Status, Color FROM ( SELECT ROUND(AVG( rt.Availability),2)as Availability, 'UP - ' + SubString(tostring(DATETRUNC('day',GETDATE())) ,0,12 ) as Status, '#30b230' As Color FROM Orion.ResponseTime rt WHERE rt.node.customproperties.SP_Media='fiber' AND DATETIME>=ADDDAY(-1,getdate()) AND DATETIME<=getdate() GROUP BY rt.node.CustomProperties.SP_Media, month(DATETIME)

marlief22 · Answer

Hello, This is the line in the script that changes how many days the query looks back: AND DATETIME>=ADDDAY(-1,getdate()) currently it says '-1' for yesterday, but changing that to '-7' would be for the past week and '-30' would do last month. The other option would be to change ADDDAY to ADDWEEK or ADDMONTH and keep the '-1' the same. Last week Last Month SELECT Availability, Status, Color FROM ( SELECT ROUND(AVG( rt.Availability),2)as Availability, 'UP - ' + SubString(tostring(DATETRUNC('day',GETDATE())) ,0,12 ) as Status, '#30b230' As Color FROM Orion.ResponseTime rt WHERE rt.node.customproperties.SP_Media='fiber' AND DATETIME>=ADDDAY(-7,getdate()) AND DATETIME<=getdate() GROUP BY rt.node.CustomProperties.SP_Media, month(DATETIME)) SELECT Availability, Status, Color FROM ( SELECT ROUND(AVG( rt.Availability),2)as Availability, 'UP - ' + SubString(tostring(DATETRUNC('day',GETDATE())) ,0,12 ) as Status, '#30b230' As Color FROM Orion.ResponseTime rt WHERE rt.node.customproperties.SP_Media='fiber' AND DATETIME>=ADDDAY(-30,getdate()) AND DATETIME<=getdate() GROUP BY rt.node.CustomProperties.SP_Media, month(DATETIME)) SELECT Availability, Status, Color FROM ( SELECT ROUND(AVG( rt.Availability),2)as Availability, 'UP - ' + SubString(tostring(DATETRUNC('day',GETDATE())) ,0,12 ) as Status, '#30b230' As Color FROM Orion.ResponseTime rt WHERE rt.node.customproperties.SP_Media='fiber' AND DATETIME>=ADDWEEK(-1,getdate()) AND DATETIME<=getdate() GROUP BY rt.node.CustomProperties.SP_Media, month(DATETIME)) SELECT Availability, Status, Color FROM ( SELECT ROUND(AVG( rt.Availability),2)as Availability, 'UP - ' + SubString(tostring(DATETRUNC('day',GETDATE())) ,0,12 ) as Status, '#30b230' As Color FROM Orion.ResponseTime rt WHERE rt.node.customproperties.SP_Media='fiber' AND DATETIME>=ADDMONTH(-1,getdate()) AND DATETIME<=getdate() GROUP BY rt.node.CustomProperties.SP_Media, month(DATETIME)) If this helps answer your question please mark my answer as confirmed to help other users, thank you! Marlie Fancourt | SolarWinds Pre-Sales Manager Prosperon Networks | SolarWinds Partner since 2006

marlief22 · Answer

Availability for yesterday (NOT last 24 hours): 
 SELECT round(avg(Availability),2) as [Availability]
FROM Orion.ResponseTime
where day(DateTime) = day(ADDDAY(-1, GETDATE())) 
 Availability last 7 days (Not last week): 
 SELECT round(avg(Availability),2) as [Availability]
FROM Orion.ResponseTime
where day(DateTime) >= day(ADDDAY(-7, GETDATE())) 
 If this helps answer your question please mark my answer as confirmed to help other users, thank you! 
 Marlie Fancourt | SolarWinds Pre-Sales Manager 
 Prosperon Networks | SolarWinds Partner since 2006

Need Help in KPI SWQL query

Top Replies