cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Highlighted
Level 9

Re: Report on Muted and Unmanaged Entities

Jump to solution

this information was fantastic .... is there any way to add groups to this...to be able to monitor groups that are muted also?

0 Kudos
Highlighted
Level 9

Re: Report on Muted and Unmanaged Entities

Jump to solution

This is great and works, is there a way to add interfaces as well to this query?

0 Kudos
Highlighted
Level 12

Re: Report on Muted and Unmanaged Entities

Jump to solution

this is awesome man.

0 Kudos
Highlighted

Re: Report on Muted and Unmanaged Entities

Jump to solution

Thanks for sharing this!  Exactly what I needed.

0 Kudos
Highlighted
Level 14

Re: Report on Muted and Unmanaged Entities

Jump to solution

Thanks for this report.  By the way, when I try it out, I get "Query is not valid" errors.  I tried SWQL and SQL but it still fails.  Thoughts???

Thanks again...

0 Kudos

Re: Report on Muted and Unmanaged Entities

Jump to solution

Are you using it inside a custom query resource (i suspect not because custom query doesn't allow you to choose between swql/sql, its swql only)?  Several others in this thread also had that issue if you read through.

- Marc Netterfield, Github
0 Kudos
Highlighted
Level 9

Re: Report on Muted and Unmanaged Entities

Jump to solution

What I mean is that I used the web based report Creator and picked the Swql option but it failed. I hardly use the standolne report writer because it is going to be deprecated.


0 Kudos
Highlighted
Level 9

Re: Report on Muted and Unmanaged Entities

Jump to solution

Sorry, misunderstood the last question.

I'm not sure why its not working.  I'll try to tinker w/ it.

Wes

0 Kudos
Highlighted

Re: Report on Muted and Unmanaged Entities

Jump to solution

It should work.The query runs on web based report as I have ran the same

and it worked for me.

On Thu, Dec 5, 2019, 11:29 PM wes.reneau

0 Kudos
Highlighted
Level 8

Re: Report on Muted and Unmanaged Entities

Jump to solution

Hi,

I added interfaces and application monitors in the original report.

To show all 3 types in one report I added column "Object" with shows Node for nodes or the interface or application monitor name.

Here the updated SWQL query:

-- Unmanaged Nodes
SELECT
'Unmanaged' as [Status]
,n.Caption AS [Node]
,'Node' AS [Object]
,tostring(tolocal(n.UnManageFrom)) AS [From]
,case when n.UnManageUntil is null or n.UnManageUntil = '9999-01-01 00:00:00' then 'Not set'
else tostring(tolocal(n.UnManageUntil)) end AS [Until]
,case when n.UnManageUntil is null or n.UnManageUntil = '9999-01-01 00:00:00' then '-'
else tostring(daydiff(getutcdate(), n.unmanageuntil)) end as [Days Left]
,n.DetailsURL AS [_LinkFor_Node]
, 'N/A' AS [_LinkFor_Object]
,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]
, 'N/A' AS [_IconFor_Object]
,CASE
WHEN ae.accountID IS NULL THEN 'Audit Log Not Found'
ELSE ae.AccountID
END AS [Account]
FROM
Orion.Nodes n
FULL JOIN (
    SELECT rec.NetObjectID, max(rec.timeloggedutc) as recent
    FROM Orion.AuditingEvents rec
    WHERE rec.auditingactiontype.actiontype = 'Orion.NodeUnmanaged'
    group BY rec.NetObjectID) mostrecent ON mostrecent.NetObjectID = n.NodeID
FULL JOIN (
    SELECT ae.NetObjectID, ae.AccountID, ae.timeloggedutc
    FROM Orion.AuditingEvents ae
    WHERE ae.auditingactiontype.actiontype = 'Orion.NodeUnmanaged') ae ON ae.NetObjectID = n.NodeID and ae.timeloggedutc=mostrecent.recent

WHERE n.Status = 9
--and (n.caption like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%')

union all
-- Muted Nodes
(SELECT
'Muted' as [Status]
,n.caption
,'Node' AS [Object]
,tostring(tolocal(SuppressFrom)) as [From]
,case when SuppressUntil is null or SuppressUntil = '9999-01-01 00:00:00' then 'Not set'
else tostring(tolocal(SuppressUntil )) end AS [Until]
,case when SuppressUntil is null or SuppressUntil = '9999-01-01 00:00:00' then '-'
else tostring(daydiff(getutcdate(), asup.SuppressUntil)) end as [Days Left]
,n.DetailsURL AS [_LinkFor_Node]
, 'N/A' AS [_LinkFor_Object]
,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]
, 'N/A' AS [_IconFor_Object]
, ae.AccountID AS [Account]
FROM Orion.AlertSuppression asup
join orion.nodes n on asup.entityuri=n.uri
join (
    SELECT ae.NetObjectID, max(ae.timeloggedutc) as recent
    FROM Orion.AuditingEvents ae
    WHERE ae.auditingactiontype.actiontype in ('Orion.AlertSuppressionChanged','Orion.AlertSuppressionAdded')
    group BY ae.netobjectid) mostrecent ON mostrecent.NetObjectID = n.NodeID
join (
    SELECT ae.NetObjectID, ae.AccountID, ae.timeloggedutc
    FROM Orion.AuditingEvents ae
    WHERE ae.auditingactiontype.actiontype in ('Orion.AlertSuppressionChanged','Orion.AlertSuppressionAdded')
    Order BY ae.TimeLoggedUtc desc) ae ON ae.NetObjectID = n.NodeID and ae.timeloggedutc=mostrecent.recent
where
-- Added to remove outdated period of muting
tolocal(SuppressUntil) > GETDATE()
AND tolocal(SuppressFrom) < GETDATE()
-- AND  (n.caption like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%')
)
UNION ALL
-- Unmanaged Interfaces
(SELECT
'Unmanaged' as [Status]
,n.Caption AS [Node]
,i.interfacename as [Interface]
,tostring(tolocal(i.UnManageFrom)) AS [From]
,case when i.UnManageUntil is null or i.UnManageUntil = '9999-01-01 00:00:00' then 'Not set'
else tostring(tolocal(i.UnManageUntil)) end AS [Until]
,case when i.UnManageUntil is null or i.UnManageUntil = '9999-01-01 00:00:00' then '-'
else tostring(daydiff(getutcdate(), i.unmanageuntil)) end as [Days Left]
,n.DetailsURL AS [_LinkFor_Node]
,i.DetailsURL AS [_LinkFor_Interface]
,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]
,'/Orion/images/StatusIcons/Small-' + i.StatusLED AS [_IconFor_Interface]
,CASE
WHEN ae.accountID IS NULL THEN 'Audit Log Not Found'
ELSE ae.AccountID
END AS [Account]
FROM
Orion.npm.interfaces i
left join orion.nodes n on n.nodeid=i.nodeid
FULL JOIN (
    SELECT rec.NetObjectID, max(rec.timeloggedutc) as recent
    FROM Orion.AuditingEvents rec
    WHERE rec.auditingactiontype.actiontype = 'Orion.InterfaceUnmanaged'
    group BY rec.NetObjectID) mostrecent ON mostrecent.NetObjectID = i.interfaceid
FULL JOIN (
    SELECT ae.NetObjectID, ae.AccountID, ae.timeloggedutc
    FROM Orion.AuditingEvents ae
    WHERE ae.auditingactiontype.actiontype = 'Orion.InterfaceUnmanaged' ) ae ON ae.NetObjectID = i.interfaceid and ae.timeloggedutc=mostrecent.recent

WHERE i.Status = 9 and n.status != 9
--and (i.caption like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%' or n.caption like '%${SEARCH_STRING}%')
)
UNION ALL
-- Muted Interfaces
(SELECT
'Muted' as [Status]
,n.Caption AS [Node]
,i.interfacename as [Interface]
,tostring(tolocal(SuppressFrom)) as [From]
,case when SuppressUntil is null or SuppressUntil = '9999-01-01 00:00:00' then 'Not set'
else tostring(tolocal(SuppressUntil )) end AS [Until]
,case when SuppressUntil is null or SuppressUntil = '9999-01-01 00:00:00' then '-'
else tostring(daydiff(getutcdate(), asup.SuppressUntil)) end as [Days Left]
,n.DetailsURL AS [_LinkFor_Node]
,i.DetailsURL AS [_LinkFor_Interface]
,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]
,'/Orion/images/StatusIcons/Small-' + i.StatusLED AS [_IconFor_Interface]
,CASE
WHEN ae.accountID IS NULL THEN 'Audit Log Not Found'
ELSE ae.AccountID
END AS [Account]
FROM Orion.AlertSuppression asup
join Orion.npm.interfaces I on asup.entityuri=I.Uri
left join orion.nodes n on n.nodeid=i.nodeid
join (
    SELECT ae.NetObjectID, max(ae.timeloggedutc) as recent
    FROM Orion.AuditingEvents ae
    WHERE ae.auditingactiontype.actiontype in ('Orion.AlertSuppressionChanged','Orion.AlertSuppressionAdded')
    group BY ae.netobjectid) mostrecent ON mostrecent.NetObjectID = I.InterfaceID
join (
    SELECT ae.NetObjectID, ae.AccountID, ae.timeloggedutc
    FROM Orion.AuditingEvents ae
    WHERE ae.auditingactiontype.actiontype in ('Orion.AlertSuppressionChanged','Orion.AlertSuppressionAdded')
    Order BY ae.TimeLoggedUtc desc) ae ON ae.NetObjectID = I.InterfaceID and ae.timeloggedutc=mostrecent.recent
where
n.status != 9
-- Added to remove outdated period of muting
AND tolocal(SuppressUntil) > GETDATE()
AND tolocal(SuppressFrom) < GETDATE()
-- AND (n.caption like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%')
)
UNION ALL
-- Unmanaged Application monitors
(
SELECT
'Unmanaged' as [Status]
,n.Caption AS [Node]
,A.DisplayName as [Application]
,tostring(tolocal(A.UnManageFrom)) AS [From]
,case when A.UnManageUntil is null or A.UnManageUntil = '9999-01-01 00:00:00' then 'Not set'
else tostring(tolocal(A.UnManageUntil)) end AS [Until]
,case when A.UnManageUntil is null or A.UnManageUntil = '9999-01-01 00:00:00' then '-'
else tostring(daydiff(getutcdate(), A.unmanageuntil)) end as [Days Left]
,n.DetailsURL AS [_LinkFor_Node]
,A.DetailsURL AS [_LinkFor_Application]
,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]
,'/Orion/images/StatusIcons/Small-' + A.StatusLED AS [_IconFor_Application]
,CASE
WHEN ae.accountID IS NULL THEN 'Audit Log Not Found'
ELSE ae.AccountID
END AS [Account]
FROM
Orion.APM.Application A
left join orion.nodes n on n.nodeid=A.nodeid
FULL JOIN (
    SELECT rec.NetObjectID, max(rec.timeloggedutc) as recent
    FROM Orion.AuditingEvents rec
    WHERE rec.auditingactiontype.actiontype = 'Orion.APM.ApplicationUnmanaged'
    group BY rec.NetObjectID) mostrecent ON mostrecent.NetObjectID = N.NodeID
FULL JOIN (
    SELECT ae.NetObjectID, ae.AccountID, ae.timeloggedutc, ae.DisplayName
    FROM Orion.AuditingEvents ae
    WHERE ae.auditingactiontype.actiontype = 'Orion.APM.ApplicationUnmanaged' ) ae ON
        ae.NetObjectID = N.NodeID
       
        AND ae.timeloggedutc=mostrecent.recent
-- In some cases NetObjectID from Orion.AuditingEvents does not hold the Application ID. So I tried this.
        AND ae.DisplayName LIKE  'User % unmanaged application '+A.DisplayName+' on node '+N.Caption
WHERE A.Status = 9 and n.status != 9
--and (A.DisplayName like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%' or n.caption like '%${SEARCH_STRING}%')
)
UNION ALL
-- Muted Application monitors
(SELECT
'Muted' as [Status]
,n.Caption AS [Node]
,A.DisplayName as [Application]
,tostring(tolocal(SuppressFrom)) as [From]
,case when SuppressUntil is null or SuppressUntil = '9999-01-01 00:00:00' then 'Not set'
else tostring(tolocal(SuppressUntil )) end AS [Until]
,case when SuppressUntil is null or SuppressUntil = '9999-01-01 00:00:00' then '-'
else tostring(daydiff(getutcdate(), asup.SuppressUntil)) end as [Days Left]
,n.DetailsURL AS [_LinkFor_Node]
,A.DetailsURL AS [_LinkFor_Application]
,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]
,'/Orion/images/StatusIcons/Small-' + A.StatusDescription + '.gif' AS [_IconFor_Application]
,CASE
WHEN ae.accountID IS NULL THEN 'Audit Log Not Found'
ELSE ae.AccountID
END AS [Account]
FROM Orion.AlertSuppression asup
join Orion.APM.Application A on asup.entityuri=A.Uri
left join orion.nodes n on n.nodeid=A.nodeid
join (
    SELECT ae.NetObjectID, max(ae.timeloggedutc) as recent
    FROM Orion.AuditingEvents ae
    WHERE ae.auditingactiontype.actiontype in ('Orion.AlertSuppressionChanged','Orion.AlertSuppressionAdded')
    group BY ae.netobjectid) mostrecent ON mostrecent.NetObjectID = A.ApplicationID
join (
    SELECT ae.NetObjectID, ae.AccountID, ae.timeloggedutc
    FROM Orion.AuditingEvents ae
    WHERE ae.auditingactiontype.actiontype in ('Orion.AlertSuppressionChanged','Orion.AlertSuppressionAdded')
    Order BY ae.TimeLoggedUtc desc) ae ON ae.NetObjectID = A.ApplicationID and ae.timeloggedutc=mostrecent.recent
where
-- Added to remove outdated period of muting
tolocal(SuppressUntil) > GETDATE()
AND tolocal(SuppressFrom) < GETDATE()
-- AND (A.DisplayName like '%${SEARCH_STRING}%' or n.caption like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%')
)

Here the result in SWQL Studio:

pastedImage_4.png

Regards,

Ralph