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

Re: Report on Muted and Unmanaged Entities

Jump to solution

My Apologies for bringing this thread back to life.  Is it possible to manipulate the query to correctly ORDER BY the 'FROM' column?  I want to view the data from newest to oldest.  If I ORDER BY [from] desc'  It does the ordering alphabetically, and not by the Date.  It makes sense as to why, but I have no clue how to go about formatting the FROM column as a datetime.

IF this is not possible is there another field I can pull in to accomplish what i am looking for?

I am using a mix of all of the above queries.

The values in questions are in bold

SELECT 

'Unmanaged' as [Status] 

,n.Caption AS [Node] 

,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]  

,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]  

,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 

ORDER BY  [from] desc, [status] asc, [node] asc

pastedImage_0.png

0 Kudos
Highlighted

Re: Report on Muted and Unmanaged Entities

Jump to solution

might be fine to just change

,tostring(tolocal(n.UnManageFrom)) AS [From]

to

,tolocal(n.UnManageFrom) AS [From]

- Marc Netterfield, Github
0 Kudos
Highlighted
Level 8

Re: Report on Muted and Unmanaged Entities

Jump to solution

I tried this, and the columns appear but the data is blank? I did a copy/paste and did not change anything else.

0 Kudos
Level 8

Re: Report on Muted and Unmanaged Entities

Jump to solution

Fixed, the view had a limitation set so I removed the limitation and the table populated.

0 Kudos
Highlighted
Level 7

Re: Report on Muted and Unmanaged Entities

Jump to solution

Hi,

I am using below queries 😕

FIRST BOX

SELECT [N].Caption as [Element]
     , [N].DetailsURL as [_LinkFor_Element]
     , '/Orion/images/StatusIcons/Small-' + [N].StatusIcon AS [_IconFor_Element]
     -- , [N].IP_Address as [IP]
     -- , [N].DetailsURL as [_LinkFor_IP]
     , [Alerts].SuppressFrom as [Mute From]
     , [Alerts].SuppressUntil as [Mute Until]
FROM Orion.AlertSuppression AS [Alerts]
JOIN Orion.Nodes AS [N]
  ON [N].Uri = [Alerts].EntityURI

UNION

( SELECT [I].FullName as [Element]
     , [I].DetailsURL as [_LinkFor_Element]
     , '/Orion/images/StatusIcons/Small-' + [I].StatusIcon AS [_IconFor_Element]
     -- , [I].IP_Address as [IP]
     -- , [I].DetailsURL as [_LinkFor_IP]
     , [Alerts].SuppressFrom as [Mute from]
     , [Alerts].SuppressUntil as [Mute Until]
FROM Orion.AlertSuppression AS [Alerts]
JOIN Orion.NPM.Interfaces AS [I]
  ON [I].Uri = [Alerts].EntityURI

)

SECOND BOX

SELECT [N].Caption as [Element]
     , [N].DetailsURL as [_LinkFor_Element]
     , '/Orion/images/StatusIcons/Small-' + [N].StatusIcon AS [_IconFor_Element]
     -- , [N].IP_Address as [IP]
     -- , [N].DetailsURL as [_LinkFor_IP]
     , [Alerts].SuppressFrom as [Mute From]
     , [Alerts].SuppressUntil as [Mute Until]
FROM Orion.AlertSuppression AS [Alerts]
JOIN Orion.Nodes AS [N]
  ON [N].Uri = [Alerts].EntityURI

WHERE [Element] LIKE '%${SEARCH_STRING}%'

UNION

( SELECT [I].FullName as [Element]
     , [I].DetailsURL as [_LinkFor_Element]
     , '/Orion/images/StatusIcons/Small-' + [I].StatusIcon AS [_IconFor_Element]
     -- , [I].IP_Address as [IP]
     -- , [I].DetailsURL as [_LinkFor_IP]
     , [Alerts].SuppressFrom as [Mute from]
     , [Alerts].SuppressUntil as [Mute Until]
FROM Orion.AlertSuppression AS [Alerts]
JOIN Orion.NPM.Interfaces AS [I]
  ON [I].Uri = [Alerts].EntityURI

WHERE [Element] LIKE '%${SEARCH_STRING}%'
)

What modification I need to do in this query to have account name also in Dashboard.

0 Kudos
Highlighted
Level 9

Re: Report on Muted and Unmanaged Entities

Jump to solution

There are so many different variations of this above, and some work for me and some do not, but the ones that work do not give all the information I need.  What I am looking for is a report, that gives all unmanged and muted nodes and interfaces and the time range they are muted or unmanged for and also the user ID who muted or unmanged the device or interface.  Some work with the search box for me some do not.  I am pretty SQL dumb so if someone could post what would be needed to perform this in both the Custom SWQL query box and the Search SWQL query box to be able to do this it would be extremely helpful for me.  Any help would truly be appreciated.

0 Kudos
Highlighted

Re: Report on Muted and Unmanaged Entities

Jump to solution

look at the one that grta​ posted above, it shows unmanaged and muted for various object types, to include the ones you are asking about. If it doesn't work its probably because you don't have SAM, if that's the case you should just be able to remove the application monitor sections from the query and get Nodes and Interfaces

0 Kudos
Highlighted

Re: Report on Muted and Unmanaged Entities

Jump to solution

I have modified the query only for Windows device (incase if someone is looking for any certain Vendor related report). Added extra columns from DB & removed few unwanted one's.

SELECT

'Unmanaged' as [Status]

,n.Caption AS [Node],n.ip_address,n.machinetype

,tostring(tolocal(n.UnManageFrom)) AS [From]

,CASE

WHEN ae.accountID IS NULL THEN 'Audit Log Not Found'

ELSE ae.AccountID

END AS [Account]

FROM

Orion.Nodes n

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

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.machinetype like'%Windows%' or n.machinetype like'%VMware%'