This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Report on Muted and Unmanaged Entities

FormerMember over 7 years ago

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.

Top Replies

Parents

0 mesverrum over 7 years ago

This is the query I use for that,

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

0 FormerMember over 7 years ago in reply to mesverrum

Thank you for your assistance with this effort! This loks like exactly the kind if report we need. However, I seem to be struggling a bit on the implementation of this. In SWQL studio, your query returns a header line with an empty result set. Yet I know there are several nodes in a Unmanaged state.

My old Audit report demonstrates this fact.
If I try creating a datasource for the report writer using this query, I get a "Query is not Valid" error...
Am I doing something wrong? Any additional direction you can share on this is greatly appreciated!
Cancel
Vote Up 0 Vote Down

Cancel
0 mesverrum over 6 years ago in reply to tezdoll

First box
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
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
)
ORDER BY [node] asc, [status] desc
Second box
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
Cancel
Vote Up +10 Vote Down

Cancel
0 superfly over 6 years ago in reply to mesverrum

This is fantastic! Much better than the report writer version I've been using I left out the search function as there's not many nodes in the list.
Thanks for sharing!
Cancel
Vote Up 0 Vote Down

Cancel
0 stevenstadel over 6 years ago in reply to mesverrum

Excellent resource! Thank you
Cancel
Vote Up 0 Vote Down

Cancel
0 er.vansh17091 over 6 years ago in reply to mesverrum

Hi All,
Can we get the SQL query for Network devices (EffectiveCategory =1) muted status only.
Cancel
Vote Up 0 Vote Down

Cancel
0 Deltona over 6 years ago in reply to mesverrum

Hi,
Is there any way to get the Node Notes included in a column as well?
Cancel
Vote Up 0 Vote Down

Cancel
0 mesverrum over 6 years ago in reply to Deltona

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]
,nn.Note
FROM
Orion.Nodes n
join orion.nodenotes nn on nn.nodeid=n.nodeid
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]
,nn.Note
FROM Orion.AlertSuppression asup
join orion.nodes n on asup.entityuri=n.uri
join orion.nodenotes nn on nn.nodeid=n.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 = 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
Cancel
Vote Up +4 Vote Down

Cancel
0 Deltona over 6 years ago in reply to mesverrum

For some reason I'm getting an invalid query error. FYI I am not using a search box.
Is there anything that needs editing before this can be used?
Cancel
Vote Up 0 Vote Down

Cancel
0 mesverrum over 6 years ago in reply to Deltona

I just tried it and it worked in my environment as is, what version of orion are you using? Did you put this into a custom query resource or somewhere else?
Shouldnt need to make changes as I had commented out the search box parts, but in hindsight you might want to use a left join for the nodenotes table that way it shows the ones that didn't get notes as well.
Cancel
Vote Up +2 Vote Down

Cancel
0 Deltona over 6 years ago in reply to mesverrum

Sorry my bad. I placed it in a Custom Table resource instead of a Custom Query.
It is working now. Thanks!
EDIT: Does it only show UnManaged or Muted nodes if they have a Note added?
I've noticed that this query doesn't display the same results as the previous one in the thread.
Nodes that are muted but don't have a Note aren't being displayed.
Cancel
Vote Up 0 Vote Down

Cancel
0 dgsmith80 over 6 years ago in reply to Deltona

Try changing the JOIN Statement to a LEFT Join:
left join orion.nodenotes nn on nn.nodeid=n.nodeid
That should include entries where there is a NULL Value in the Notes field.
Cancel
Vote Up +1 Vote Down

Cancel

Reply

0 dgsmith80 over 6 years ago in reply to Deltona

Try changing the JOIN Statement to a LEFT Join:
left join orion.nodenotes nn on nn.nodeid=n.nodeid
That should include entries where there is a NULL Value in the Notes field.
Cancel
Vote Up +1 Vote Down

Cancel

Children

0 Deltona over 6 years ago in reply to dgsmith80

That did it. Thanks!
Cancel
Vote Up +1 Vote Down

Cancel