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

Report on Muted and Unmanaged Entities

Jump to solution

I'm looking for an SQL/SWQL query or Report that will  show all muted and/or unmanaged entities in Orion with from and to dates and the user name that has made this configuration.  Currently I have two separated reports. One provides all the audit events for unmanaged and muted entities for the last year.  The other report identifies all unmanaged nodes, interfaces and applications from Orion.  What is really needed is a way to create a single view / join to take the current unmanaged / muted inventory an add the user detail from audit to output.  If this report exists already somewhere in Orion, or if someone has solved this already please point me in the right direction.  It would seem that a report showing all muted entities and by whom. would be something all Orion Admins would find beneficial.  Thanks!  As a side note, my current unmanaged device and application report has my entities grouped by the custom field Department.  

1 Solution

This is the query I use for that,

pastedImage_0.png

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

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.caption like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%')

union all

(SELECT

'Muted' as [Status]

,n.caption

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

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

, 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 (n.caption like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%')

)

ORDER BY [node] asc, [status] desc

- Marc Netterfield, Github

View solution in original post

77 Replies

Thanks Chris - I'm actually looking to add the Customer custom property field in the SELECT statement.

This way we can pull all Customers and sort as needed, instead of doing the WHERE clause for each client.

I surmise the Customer property field needs to be placed correctly inside each Union portion of the query. Still hacking away at it, but not getting anywhere. SQL newbs.

  -- Unmanaged Nodes

    SELECT

    'Unmanaged' as [Status]

    ,n.Caption AS [Node] 
  ,n.customproperties.customer AS [Customer] 
    ,'Node' AS [Object]

0 Kudos

Oh I understand now. To do that you'd have to put the line you have above in every select statement below is an example of it modified for every beginning select statement and a screenshot of it working

-- Unmanaged Nodes
SELECT
'Unmanaged' as [Status]
,n.Caption AS [Node]
,'Node' AS [Object]
,n.customproperties.customer as [Customer]
,tostring(tolocal(n.UnManageFrom)) AS [From]

union all
-- Muted Nodes
(SELECT
'Muted' as [Status]
,n.caption
,n.customproperties.customer as [Customer]
,'Node' AS [Object]

UNION ALL
-- Unmanaged Interfaces
(SELECT
'Unmanaged' as [Status]
,n.Caption AS [Node]
,n.customproperties.customer as [Customer]
,i.interfacename as [Interface]

UNION ALL
-- Muted Interfaces
(SELECT
'Muted' as [Status]
,n.Caption AS [Node]
,n.customproperties.customer as [Customer]
,i.interfacename as [Interface]

UNION ALL
-- Unmanaged Application monitors
(
SELECT
'Unmanaged' as [Status]
,n.Caption AS [Node]
,n.customproperties.customer as [Customer]
,A.DisplayName as [Application]

UNION ALL
-- Muted Application monitors
(SELECT
'Muted' as [Status]
,n.Caption AS [Node]
,n.customproperties.customer as [Customer]
,A.DisplayName as [Application]

pastedImage_2.png

0 Kudos

Thank you so much Chris.

Code is Poetry.

Have you seen an issue with this in 12.3 or greater? When I use this query in a widget in 12.3 or greater it does not display correctly. At the bottom right corner, it does not display the total number of nodes and doesn't show the muted objects. in the newer versions did the functionality for a Union all break? Below is a screenshot of the bottom part of the widget

pastedImage_0.png

Should be expecting 125 objects (thats what gets returned in SWQL Studio) instead of 1-0

0 Kudos

I can check tomorrow as I’m on a customer site who has 12.1 and 12.3

- David Smith
0 Kudos

Thanks, the above screenshot was taken in a 12.4 environment

0 Kudos

So interestingly I get an error on both 12.2 and 12.4 Systems which is:

"There was an error processing the request"

Yet both servers respond if I run the query directly in SWQL Studio

- David Smith
0 Kudos

did you put an "Order By" at the end? i had to ensure i put that at the end of the query in the widget in order to not get the same error you talked about.

i just threw this in there

Order By [Days Left]

0 Kudos

Yep - that's working fine now on my demo lab which is 12.4

- David Smith
0 Kudos

So if there's more than the row limit it displays the widget correctly? Meaning that it shows the total number in the bottom right-hand corner? I noticed that if you go over the limit for the number of rows than it doesn't display correctly. e.g: by default the row limit is 5 if I left it at 5 and there were 10 nodes in maintenance mode the widget would display like the screenshot in my previous comment. But if I change the max rows to 11 than the widget displays correctly. I also noticed that my muted nodes do not show up

Can you show a screenshot of the lower part of the widget like the one i provided?

0 Kudos

Ah yes I am able to replicate that issue. When I have less than 5 (As an example) and the "Number of Rows per Page" set to 5 then it works fine. If I then unmanaged some additional devices I get the following issue:

pastedImage_1.png

But if I increase the page count to 10 it works fine. EDIT: I noticed this is also showing up in the original query from mesverrum but not in all SWQL Query's I have on my system so it's potentially a code issue rather than a SW Engine issue.

- David Smith
0 Kudos

can you test it in the 12.1 environment you spoke of? I have a co-worker who said that this was not the case prior to 12.3 I suspect starting in 12.3 it doesn't like the union all parts of the query, because when you do each query individually in separate widgets the widget behaves as expected.

0 Kudos

That was a customer environment that I no longer have access to, I will test it at my next opportunity - If anyone out there can validate if this is working in NPM12.1 we would appreciate it.

- David Smith
0 Kudos

Submitted a support case for this. Not getting my hopes up but I'll let you know what the results are of the support case if any.

0 Kudos

Ok - Maybe mesverrum​ will be able to shed some light as the original code is his.

- David Smith
0 Kudos

I took a look in my lab, several of the queries I have written using Unions show this same behavior, but some of them still count like normal...  I'll need to spend some time pinning down what syntax exactly is breaking the page count mechanism in the Custom Query resource

- Marc Netterfield, Github
0 Kudos

SolarWinds support got back to me today. They are sending the case to development as a bug with the custom query widget. As they update me I will update here.

SolarWinds support got back to me today and said thanks for the bug report, with no timeline for fixing it. They recommended that I put in a feature request as well to raise awareness.

Here's the feature request for it.

0 Kudos

Was able to test it in a 12.1 environment and it works as expected

pastedImage_0.png

0 Kudos
Level 14

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

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