Need Help in KPI SWQL query

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)

Parents
  • 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

  • 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'

Reply
  • 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'

Children
No Data