This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Database Maintenance Results Widget - SWQL

SELECT Distinct
TOLOCAL(ST.EventTime) AS [Start Time]
,TOLOCAL(ET.EventTime) AS [Finish Time]
,CONCAT(--Downtime formatted  
        (CASE WHEN (SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))/60/60) >= 1 --Hours  
            THEN CONCAT(SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))/60/60%24, 'h ') ELSE '' END),  
        (CASE WHEN (SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))/60) >= 1 --Minutes  
            THEN CONCAT(SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))/60%60, 'm ') ELSE '' END),  
        (CASE WHEN (SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))) >= 1 --Seconds  
            THEN CONCAT(SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))%60, 's ') ELSE '' END)) AS [Duration]
FROM Orion.Events ST
LEFT JOIN Orion.Events ET ON ET.NetObjectID = ST.NetObjectID 
    AND ET.EventTime = (SELECT TOP 1 x.EventTime FROM Orion.Events x WHERE
                        x.EventTime >= ST.EventTime AND x.EventType = 1000
                        AND x.Message = 'Nightly Maintenance Completed'
                        AND x.NetObjectID = ST.NetObjectID
                        ORDER BY x.EventTime)
WHERE
    ST.EventType = '1000'
    AND ST.Message = 'Starting Nightly Maintenance'
    AND ST.EventTime > ADDDAY(-7,GETUTCDATE())
ORDER BY ST.EventTime DESC

  • You should save that query as a SWQL file and then share it to the Custom Query Widget Content Exchange.

  • Something to note, the event ID's changed on version 2023.1 They are now 5100 and 5102 so you will need to change the query to reflect those values. 

    Nightly Maintenance Completed 5101
    Starting Nightly Maintenance 5100
  • For anyone lazy I made a quick update to the OP to include this update.  Seems like finish time stopped working but not a big deal to me if we have start and duration.

    SELECT Distinct
    TOLOCAL(ST.EventTime) AS [Start Time]
    --,TOLOCAL(ET.EventTime) AS [Finish Time]
    ,CONCAT(--Downtime formatted  
            (CASE WHEN (SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))/60/60) >= 1 --Hours  
                THEN CONCAT(SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))/60/60%24, 'h ') ELSE '' END),  
            (CASE WHEN (SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))/60) >= 1 --Minutes  
                THEN CONCAT(SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))/60%60, 'm ') ELSE '' END),  
            (CASE WHEN (SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))) >= 1 --Seconds  
                THEN CONCAT(SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))%60, 's ') ELSE '' END)) AS [Duration]
    FROM Orion.Events ST
    LEFT JOIN Orion.Events ET ON ET.NetObjectID = ST.NetObjectID 
        AND ET.EventTime = (SELECT TOP 1 x.EventTime FROM Orion.Events x WHERE
                            x.EventTime >= ST.EventTime AND (x.EventType = 5100 OR x.EventType = 5102)
                            AND x.Message = 'Nightly Maintenance Completed'
                            AND x.NetObjectID = ST.NetObjectID
                            ORDER BY x.EventTime)
    WHERE
        (ST.EventType = '5100' OR ST.EventType = '5102')
        AND ST.Message = 'Starting Nightly Maintenance'
        AND ST.EventTime > ADDDAY(-7,GETUTCDATE())
    ORDER BY ST.EventTime DESC

  • SELECT Distinct
    TOLOCAL(ST.EventTime) AS [Start Time]
    ,TOLOCAL(ET.EventTime) AS [Finish Time]
    ,CONCAT(--Downtime formatted  
            (CASE WHEN (SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))/60/60) >= 1 --Hours  
                THEN CONCAT(SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))/60/60%24, 'h ') ELSE '' END),  
            (CASE WHEN (SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))/60) >= 1 --Minutes  
                THEN CONCAT(SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))/60%60, 'm ') ELSE '' END),  
            (CASE WHEN (SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))) >= 1 --Seconds  
                THEN CONCAT(SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))%60, 's ') ELSE '' END)) AS [Duration]
    FROM Orion.Events ST
    LEFT JOIN Orion.Events ET ON ET.NetObjectID = ST.NetObjectID 
        AND ET.EventTime = (SELECT TOP 1 x.EventTime FROM Orion.Events x WHERE
                            x.EventTime >= ST.EventTime 
                            AND x.Message = 'Nightly Maintenance Completed'
                            AND x.NetObjectID = ST.NetObjectID
                            ORDER BY x.EventTime)
    WHERE
       ST.Message = 'Starting Nightly Maintenance'
        AND ST.EventTime > ADDDAY(-7,GETUTCDATE())
    ORDER BY ST.EventTime DESC