Adjust SWQL report to show last 12 months of Historical Status of each Group

nkunstmann

I'm trying to tweak this alert to show the last 12 months of availability instead of the last 1 month. I would also like to filter the data source to only show specific group names.

The formatting of the report is good as is. I needed to be able to see the percentage of time the group is completely down so this report is great. I just need the data to go further back and to see only specific groups.

Any help would be appreciated, thank you!

I don't know when this report was imported or who originally made it, otherwise I would give credit. @KMSigma.SWI  helped me out by creating a variation of a report he had already made but this report was already in our web console from what I can tell.

SELECT c.ContainerID AS GroupID
     , c.Name AS GroupName
     , csc.DATE AS DATE
     , si.ShortDescription + ' (' + toString(ROUND(csc.[Count] * 100 / csc.[Total], 2)) + '%)' AS GroupStatusInfo
     , si.Ranking
     , c.DetailsUrl
FROM Orion.Groups AS c
JOIN (
     SELECT hcs.ContainerID
          , hcs.STATUS
          , DateTrunc('DAY', ToLocal(hcs.ObservationTimestamp)) AS DATE
          , COUNT(hcs.STATUS) AS [Count]
          , cst.[Total]
     FROM Orion.ContainerStatus AS hcs
     JOIN (
          SELECT ContainerID
               , DateTrunc('DAY', ToLocal(ObservationTimestamp)) AS DATE
               , COUNT(STATUS) AS Total
          FROM Orion.ContainerStatus
          WHERE ToLocal(ObservationTimestamp) BETWEEN AddMonth(- 1, DateTrunc('MONTH', GetDate()))
                    AND DateTrunc('MONTH', GetDate())
          GROUP BY ContainerID
               , DateTrunc('DAY', ToLocal(ObservationTimestamp))
          ) AS cst
          ON cst.ContainerID = hcs.ContainerID
               AND cst.DATE = DateTrunc('DAY', ToLocal(hcs.ObservationTimestamp))
     WHERE ToLocal(hcs.ObservationTimestamp) BETWEEN AddMonth(- 1, DateTrunc('MONTH', GetDate()))
               AND DateTrunc('MONTH', GetDate())
     GROUP BY hcs.ContainerID
          , DateTrunc('DAY', ToLocal(hcs.ObservationTimestamp))
          , hcs.Status
          , cst.Total
     ) AS csc
     ON csc.ContainerID = c.ContainerID
JOIN Orion.StatusInfo AS si
     ON si.StatusId = csc.STATUS
ORDER BY c.ContainerID
     , csc.DATE
     , si.Ranking

mesverrum

Try changing line 20 and line 27  so that instead of addmonth( -1 etc  so they say addmonth( -12

Worth mentioning that doing reports that go back a year very often causes time outs, and if that's the case there isn't a whole lot you can do short of getting into the weeds with the database directly instead of trying to display data through the web console.

Also the stats might do weird things as they get past the hourly rollups into the daily rollup period, which usually happens after 30 days if you have default retention settings.