Need help with a SWQL query

surnaihai899

I wrote this query to get the MWs created on SolarWinds. I wanted to check if we could add a column which shows the date and time the MW was created in UTC

SELECT TOP 1000 EntityUri, Caption, IPAddress, tostring(SuppressFrom) as [Suppression From],
case when SuppressUntil is null or SuppressUntil = '9999-01-01 00:00:00' then 'Not set'
else tostring(SuppressUntil ) end AS [Suppression To]

,case when SuppressUntil is null or SuppressUntil = '9999-01-01 00:00:00' then 'Unlimited Time Period'
else tostring(daydiff(getutcdate(), asu.SuppressUntil)) end as [Days Left],
NCP.MWTaskNumber

FROM Orion.AlertSuppression asu
join orion.nodes N on asu.entityuri=N.uri
LEFT JOIN Orion.NodesCustomProperties NCP on N.NodeID = NCP.NodeID

Lofstrand

Hi, below gives you a little more than that but use the parts you want.

Includes both muted and unmanaged nodes, who made it and when:

Code is made for a "custom query widget", but can be used for reports also. Some columns can then be removed.

--Unmanaged
SELECT
    N.Caption AS [Node],
    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],
    AE.TimeLoggedUtc AS [Supressed at (UTC)],
    'Unmanaged' AS [Type],
    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 AE.AuditingActionType.ActionType ='Orion.NodeUnmanaged'
        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.Caption LIKE '%${SEARCH_STRING}%'

UNION

( --Muted
SELECT
    N.Caption AS [Node],
    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],
    AE.TimeLoggedUtc AS [Supressed at (UTC)],
    'Muted' AS [Type],
    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 AE.AuditingActionType.ActionType IN ('Orion.AlertSuppressionAdded','Orion.AlertSuppressionChanged' ) 
        group by AE.NetObjectID
) AS [LastMuteEvents] ON LastMuteEvents.NetObjectID=N.NodeID
LEFT OUTER JOIN Orion.AuditingEvents AE ON AE.AuditEventID=LastMuteEvents.AuditEventid
--    AND N.Caption LIKE '%${SEARCH_STRING}%'
)
ORDER BY [Suppressed From] DESC