Need Help in KPI SWQL query

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. 

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))

Thanks for detail reply.

-1 is not giving correct % Availability = 96.75 

because by creating report of last 1 day is giving = 94.45 %

Thanks for detail reply.

-1 is not giving correct % Availability = 96.75 

because by creating report of last 1 day is giving = 94.45 %

Hi there, 

Is the report using 'Yesterday' or 'Last 24 hours' as the time period?

If you ran the report right now, 'Yesterday' would be 00:00 to 23:59 on the 11th October, whereas 'Last 24 hours' would be 17:06 on October 11th to 17:06 on October 12th - This would definitely give you different results. 

i select time period = yesterday

SWQL and Report writer giving difference in % Availability.

- 1 means yesterday  00:00 to 23:59 on the 11th October ?

-1 day = -24 hours

For yesterday you could try this: 

where day(DATETIME) = day(ADDDAY(-1,getdate()))

Hi

can you re-generate swql script for yesterday and last 7 days ( as simple script as possible) , i just want add this scrpit in Modern Dashboard KPI to show SLA of Service Provider.

like

Avg % Availability  / Date

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

dont know why but still there is differance between SWQL script and Report from WEB report for % Availability of yestaerday.

SWQL SCRIPT = 96.06

Report Writer Yesterday report of same custom Property ( SP_Media='ptcl fiber') = 96.56

SELECT round(avg(Availability),2) as [Availability]
FROM Orion.ResponseTime rt
where day(DateTime) = day(ADDDAY(-1, GETDATE()))

and rt.node.customproperties.SP_Media='ptcl fiber'

marlief22 How could you take that script and filter the results to only include device's that are in a specific location... we have a customProperty that is device_location?