Looking for SWQL to show Applications' Scheduled Maintenance

sredmond

We have a widget for Applications and I am trying to include a field to show if the application is in maintenance. 

I have not been able to find the field for the Application if it is in maintenance. 

Tried: 

SELECT c.Name as [Component Name] , c.DetailsUrl as [Component URL] , c.Application.DetailsUrl as [App URL] , c.Application.Status as [App Status] , c.Application.Node.DetailsUrl as [Node URL] , c.Application.Node.Status as [Node Status] , c.Application.Name as [App Name] , c.Application.Node.Caption as [Host Name] , c.Status as [Component Status Code] FROM Orion.APM.Component c WHERE c.Application.Node.Caption LIKE '%SMFS%' AND c.Name = 'Time Skew Seconds' ORDER BY c.Name

Seashore

Hi, below code was written for a custom query widget, to display both nodes, volumes, interfaces and application in maintenance mode, both unmanaged and muted. 

There is comments there to show what parts is for what type of entity. Take the parts you want from it:

(Sometimes the correct ActionTypeID's are different on different installations. Make sure you get the once that are correct for your installation. If not you will not see who set the maintenance.)

--Unmanaged Node
SELECT
    N.Caption AS [Node],
    'Node' AS [Object],
    DATETRUNC('minute', N.UnManageFrom) AS [Suppressed From],
    CASE
        WHEN ToString(N.UnManageUntil)='Jan  1 9999 12:00AM' THEN ' - Forever - '
        ELSE ToString(N.UnManageUntil)
    END AS [Suppressed TO],
    CONCAT(DayDiff(N.UnManageFrom,GetDate()), ' days') as [Suppressed days],
    AE.AccountID AS [Supressed By],
    'Unmanaged' AS [Type],
    N.CustomProperties.UnManage_Reason as [UnManage Reason],
    CONCAT('/NetPerfMon/Images/Vendors/',N.Icon) AS [_ICONFor_Node],
    N.DetailsUrl AS [_LinkFor_Node]
    --CONCAT('/Orion/images/StatusIcons/',N.StatusIcon) as Icon
FROM Orion.Nodes N
LEFT OUTER JOIN (
        SELECT
            AE.NetObjectID,
            Max(AE.AuditEventID) as [AuditEventID]
        FROM Orion.AuditingEvents AE
        where ActionTypeID = 28
        group by AE.NetObjectID
) AS [LastMuteEvents] ON LastMuteEvents.NetObjectID=N.NodeID
LEFT OUTER JOIN Orion.AuditingEvents AE ON AE.AuditEventID=LastMuteEvents.AuditEventid
WHERE
    N.UnManaged=1
   AND N.CustomProperties.UnManage_Reason NOT LIKE 'Down to long, will be deleted%'
  AND N.UnManageFrom < Getdate()
    --AND N.Caption LIKE '%${SEARCH_STRING}%'

UNION

( --Muted nodes
SELECT
    N.Caption AS [Node],
    'Node' AS [Object],
    Datetrunc('minute',Supp.SuppressFrom) AS [Suppressed From],
    ISNULL(ToString(Supp.SuppressUntil),' - Forever - ') AS [Suppressed TO],
    CONCAT(DayDiff(Supp.SuppressFrom,GetDate()), ' days') as [Suppressed days],
    AE.AccountID AS [Supressed By],
    'Muted' AS [Type],
    N.CustomProperties.UnManage_Reason as [UnManage Reason],
    CONCAT('/NetPerfMon/Images/Vendors/',N.Icon) AS [_ICONFor_Node],
    --CONCAT('/Orion/images/StatusIcons/',N.StatusIcon) as Icon,
    N.DetailsUrl AS [_LinkFor_Node]
FROM Orion.Nodes N
INNER JOIN Orion.AlertSuppression Supp on Supp.EntityUri=N.Uri
LEFT OUTER JOIN (
        SELECT
            AE.NetObjectID,
            Max(AE.AuditEventID) as [AuditEventID]        
        FROM Orion.AuditingEvents AE
        where ActionTypeID in (58,57)  --  'Orion.AlertSuppressionAdded' and 'Orion.AlertSuppressionChanged' 
        group by AE.NetObjectID
) AS [LastMuteEvents] ON LastMuteEvents.NetObjectID=N.NodeID
LEFT OUTER JOIN Orion.AuditingEvents AE ON AE.AuditEventID=LastMuteEvents.AuditEventid
WHERE
  Supp.SuppressFrom