-
Re: Report on Muted and Unmanaged Entities
mesverrumSep 19, 2017 1:32 PM (in response to forstgre)
9 of 9 people found this helpfulThis 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
-
Re: Report on Muted and Unmanaged Entities
forstgre Sep 21, 2017 1:22 PM (in response to mesverrum)Super, thanks a lot! I wll definietly give this a shot!
Typo disclaimer: Sent from my Samsung tablet
Greg Forsthoefel
Home / Office: 941-493-1300
Mobile: 941-705-1387
-
Re: Report on Muted and Unmanaged Entities
forstgre Sep 27, 2017 7:21 AM (in response 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!
-
Re: Report on Muted and Unmanaged Entities
mesverrumSep 27, 2017 8:53 AM (in response to forstgre)
1 of 1 people found this helpfulThis is meant to be used inside the custom query resource, all the linkfor and iconfor stuff won't work in the reportwriter. In custom query there is a check box to make the query work with a search input, paste then query into that second box. In the first box (this would be the version without the search input) just comment out the lines that have
(n.caption like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%')
There are two of them as this query is a union of two separate searches.
-
Re: Report on Muted and Unmanaged Entities
forstgre Sep 29, 2017 10:04 AM (in response to mesverrum)Thanks, I'll look for that! Is this done by adding a widget to the Portal page and assigning this "custom query" as the content?
Sorry for the newbie questions...
-
Re: Report on Muted and Unmanaged Entities
mesverrumSep 29, 2017 6:45 PM (in response to forstgre)
1 of 1 people found this helpfulyep you got it
-
Re: Report on Muted and Unmanaged Entities
tezdoll Nov 7, 2017 4:12 PM (in response to mesverrum)Are you able to show a screen shot. I got the muted alerts piece to work but not unmanaged as well. I'm not a SQL guru.
Top windows:
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.recentWHERE 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
)
ORDER BY [node] asc, [status] desc
Bottom Window (Search box clicked)
where (n.caption like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%')
-
Re: Report on Muted and Unmanaged Entities
mesverrumNov 7, 2017 5:03 PM (in response to tezdoll)
7 of 7 people found this helpfulFirst 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
-
Re: Report on Muted and Unmanaged Entities
superfly99Nov 7, 2017 11:00 PM (in response 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!
-
Re: Report on Muted and Unmanaged Entities
stevenstadel Nov 29, 2017 9:27 AM (in response to mesverrum)Excellent resource! Thank you
-
Re: Report on Muted and Unmanaged Entities
er.vansh17091 May 17, 2018 6:47 PM (in response to mesverrum)Hi All,
Can we get the SQL query for Network devices (EffectiveCategory =1) muted status only.
-
Re: Report on Muted and Unmanaged Entities
DeltonaJul 5, 2018 8:52 AM (in response to mesverrum)
Hi,
Is there any way to get the Node Notes included in a column as well?
-
Re: Report on Muted and Unmanaged Entities
mesverrumJul 6, 2018 1:10 PM (in response to Deltona)
4 of 4 people found this helpfulSELECT
'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
-
Re: Report on Muted and Unmanaged Entities
DeltonaJul 9, 2018 3:23 AM (in response 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?
-
Re: Report on Muted and Unmanaged Entities
mesverrumJul 9, 2018 1:46 PM (in response to Deltona)
1 of 1 people found this helpfulI 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.
-
Re: Report on Muted and Unmanaged Entities
DeltonaJul 10, 2018 3:40 AM (in response 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.
-
Re: Report on Muted and Unmanaged Entities
David SmithJul 10, 2018 4:11 AM (in response 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.
-
Re: Report on Muted and Unmanaged Entities
DeltonaJul 10, 2018 4:39 AM (in response to David Smith)
That did it. Thanks!
-
-
-
-
-
Re: Report on Muted and Unmanaged Entities
whomademesme Jul 25, 2018 10:27 AM (in response to mesverrum)2 of 2 people found this helpfulThis is excellent!
I was able to reverse engineer your query and make it work for interfaces. The below query will return interfaces that are unmanaged, when the node is NOT UNmanaged.
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
JOIN (
SELECT rec.NetObjectID, max(rec.timeloggedutc) as recent
FROM Orion.AuditingEvents rec
WHERE rec.actiontypeid = 33
group BY rec.NetObjectID) mostrecent ON mostrecent.NetObjectID = i.interfaceid
JOIN (
SELECT ae.NetObjectID, ae.AccountID, ae.timeloggedutc
FROM Orion.AuditingEvents ae
WHERE ae.actiontypeid = 33 ) 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}%')
--uncomment above line for search function
union all
(SELECT
'Muted' as [Status]
,n.caption
,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]
, ae.AccountID 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.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 = i.NodeID and ae.timeloggedutc=mostrecent.recent
--and (i.caption like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%' or n.caption like '%${SEARCH_STRING}%')
--uncomment above line for search function
)
ORDER BY [node] asc, [status] desc
-
Re: Report on Muted and Unmanaged Entities
bhollowell Jan 18, 2019 8:53 AM (in response to whomademesme)First off, THANK YOU for doing this - You're awesome! Second....I can't seem to get it to return any results, even though I know I have individual interfaces in unmanaged mode
-
Re: Report on Muted and Unmanaged Entities
mesverrumJan 18, 2019 10:09 AM (in response to bhollowell)
2 of 2 people found this helpfulI noticed his edit of my query is keying into the actiontype, but those id's are not always the same in all environments, it depends which modules you have installed.
You may want to check your orion.auditingevents table to confirm what the correct actiontypeid is for your environment.
This is the section of code I'm referring to
JOIN (
SELECT rec.NetObjectID, max(rec.timeloggedutc) as recent
FROM Orion.AuditingEvents rec
WHERE rec.actiontypeid = 33
group BY rec.NetObjectID) mostrecent ON mostrecent.NetObjectID = i.interfaceid
JOIN (
SELECT ae.NetObjectID, ae.AccountID, ae.timeloggedutc
FROM Orion.AuditingEvents ae
WHERE ae.actiontypeid = 33 ) ae ON ae.NetObjectID = i.interfaceid and ae.timeloggedutc=mostrecent.recent
-
Re: Report on Muted and Unmanaged Entities
bhollowell Jan 18, 2019 12:01 PM (in response to mesverrum)That was it, thanks!
-
Re: Report on Muted and Unmanaged Entities
David SmithJan 21, 2019 3:08 AM (in response to mesverrum)
I changed mine to:
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 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
Hopefully, this will mean it can work, regardless of the modules installed as it's using the name, not the unique ID.
-
Re: Report on Muted and Unmanaged Entities
mesverrumJan 21, 2019 8:26 AM (in response to David Smith)
Yep that's how I do them as well
-
Re: Report on Muted and Unmanaged Entities
superfly99Jan 21, 2019 5:08 PM (in response to David Smith)
Thanks! That works beaut.
Is there a way to show Muted Interfaces as well? This is the query I'm using but it only shows unmanaged interfaces.
Thanks!
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
join orion.nodes n on n.nodeid=i.nodeid
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
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
union all
(SELECT
'Muted' as [Status]
,n.caption
,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]
, ae.AccountID AS [Account]
FROM Orion.AlertSuppression asup
join orion.npm.interfaces i on asup.entityuri=i.uri
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.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 = i.NodeID and ae.timeloggedutc=mostrecent.recent
)
ORDER BY [node] asc, [status] desc
-
-
-
-
-
-
-
Re: Report on Muted and Unmanaged Entities
rajasekar Aug 21, 2018 7:40 AM (in response to mesverrum)How i will exclude only one node example its name abcd in the below script
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
-
Re: Report on Muted and Unmanaged Entities
rajasekar Nov 12, 2018 9:18 AM (in response to mesverrum)Can you share the script that need to show the details what are the node scheduled to unmange in future.
-
Re: Report on Muted and Unmanaged Entities
hpstech Jan 19, 2019 11:11 AM (in response to mesverrum)Whenever I try this widget, I get rows returned of objects that I did not personally put into maintenance mode, but yet my user name is attached to them.
I'm not sure why the system thinks that my user name put multiple objects into maintenance mode when it was another engineer on another team.
I wish this widget worked for me. So useful and necessary.
Any ideas why the table has wrong user names attached to these vents?
-
Re: Report on Muted and Unmanaged Entities
mesverrumJan 19, 2019 12:19 PM (in response to hpstech)
You'd have to open up the tables I reference in the db and see what the raw data looks like for one of the incidents with the wrong user. The logic it uses its first to get all the nodes that are currently unmanaged, then it checks the audit history for the most recent event where a user unmanaged that node and it pulls up the user name from that event. Thinking that through the only way I can imagine there would be wrong info in there is if you guys are unmanaging nodes in a way that doesn't generate an audit event.
-
-
Re: Report on Muted and Unmanaged Entities
cathsheh1 Jun 22, 2019 7:16 PM (in response to mesverrum)this information was fantastic .... is there any way to add groups to this...to be able to monitor groups that are muted also?
-
Re: Report on Muted and Unmanaged Entities
johnlad Jul 31, 2019 9:59 AM (in response to mesverrum)This is great and works, is there a way to add interfaces as well to this query?
-
-
-
-
-
-
-
Re: Report on Muted and Unmanaged Entities
justin.benner Nov 4, 2017 2:50 PM (in response to mesverrum)Thanks for sharing this! Exactly what I needed.
-
-
Re: Report on Muted and Unmanaged Entities
mr.e Nov 11, 2018 7:42 AM (in response to forstgre)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...
-
Re: Report on Muted and Unmanaged Entities
mesverrumNov 12, 2018 9:48 AM (in response to mr.e)
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.
-
Re: Report on Muted and Unmanaged Entities
Al Ma Dec 5, 2019 11:52 AM (in response to mesverrum)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.
-
Re: Report on Muted and Unmanaged Entities
wes.reneau Dec 5, 2019 11:59 AM (in response to Al Ma)Sorry, misunderstood the last question.
I'm not sure why its not working. I'll try to tinker w/ it.
Wes
-
Re: Report on Muted and Unmanaged Entities
mnsh_majumderDec 6, 2019 10:07 AM (in response to wes.reneau)
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
-
-
-
-
-
Re: Report on Muted and Unmanaged Entities
grta Jan 24, 2019 12:50 PM (in response to forstgre)2 of 2 people found this helpfulHi,
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:
Regards,
Ralph
-
Re: Report on Muted and Unmanaged Entities
christopher.t.jones123Mar 19, 2019 12:19 PM (in response to grta)
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
Should be expecting 125 objects (thats what gets returned in SWQL Studio) instead of 1-0
-
Re: Report on Muted and Unmanaged Entities
David SmithMar 20, 2019 3:59 PM (in response to christopher.t.jones123)
I can check tomorrow as I’m on a customer site who has 12.1 and 12.3
-
Re: Report on Muted and Unmanaged Entities
christopher.t.jones123Mar 21, 2019 8:58 AM (in response to David Smith)
Thanks, the above screenshot was taken in a 12.4 environment
-
Re: Report on Muted and Unmanaged Entities
David SmithMar 22, 2019 7:10 AM (in response to christopher.t.jones123)
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
-
Re: Report on Muted and Unmanaged Entities
christopher.t.jones123Mar 25, 2019 11:49 AM (in response to David Smith)
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]
-
Re: Report on Muted and Unmanaged Entities
David SmithMar 26, 2019 5:13 AM (in response to christopher.t.jones123)
Yep - that's working fine now on my demo lab which is 12.4
-
Re: Report on Muted and Unmanaged Entities
christopher.t.jones123Mar 26, 2019 7:43 PM (in response to David Smith)
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?
-
Re: Report on Muted and Unmanaged Entities
David SmithMar 27, 2019 8:07 AM (in response to christopher.t.jones123)
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:
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.
-
Re: Report on Muted and Unmanaged Entities
christopher.t.jones123Mar 27, 2019 8:49 AM (in response to David Smith)
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.
-
Re: Report on Muted and Unmanaged Entities
David SmithMar 27, 2019 9:18 AM (in response to christopher.t.jones123)
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.
-
Re: Report on Muted and Unmanaged Entities
christopher.t.jones123Mar 27, 2019 1:12 PM (in response to David Smith)
-
Re: Report on Muted and Unmanaged Entities
christopher.t.jones123Mar 29, 2019 10:01 AM (in response to David Smith)
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.
-
Re: Report on Muted and Unmanaged Entities
David SmithMar 30, 2019 8:39 AM (in response to christopher.t.jones123)
Ok - Maybe mesverrum will be able to shed some light as the original code is his.
-
Re: Report on Muted and Unmanaged Entities
mesverrumMar 30, 2019 12:52 PM (in response to David Smith)
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
-
Re: Report on Muted and Unmanaged Entities
christopher.t.jones123Apr 1, 2019 5:38 PM (in response to mesverrum)
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.
-
Re: Report on Muted and Unmanaged Entities
christopher.t.jones123Apr 26, 2019 4:59 PM (in response to christopher.t.jones123)
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. FEATURE REQUEST - Fix 'UNION ALL' in Custom Query Widget
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
Re: Report on Muted and Unmanaged Entities
hpstech Mar 26, 2019 12:12 PM (in response to grta)Such a beautiful resource!
Can a SQL genius add a custom property call Customer for this to the query?
I worship your Epicness! Coding is an art, a talent, something many of us mere mortals lack unfortunately.
-
Re: Report on Muted and Unmanaged Entities
christopher.t.jones123Mar 26, 2019 7:46 PM (in response to hpstech)
This can be accomplished by adding in the appropriate custom property in the where statement, below is a modified one using your example as a node custom property called customer with a value of Test. I didn't test it for the interfaces and the applications portion, but because it's still referencing the node table you should still be able to insert it to their corresponding where statements and recreate it.
-- 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.DetailsURL AS [_LinkFor_Object] ,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node] ,'/Orion/images/StatusIcons/Small-' + n.StatusLED 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.customproperties.customer = 'Test'
-
Re: Report on Muted and Unmanaged Entities
hpstech Mar 27, 2019 5:29 PM (in response to christopher.t.jones123)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]-
Re: Report on Muted and Unmanaged Entities
christopher.t.jones123Mar 27, 2019 5:30 PM (in response to hpstech)
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]
-
Re: Report on Muted and Unmanaged Entities
hpstech Mar 27, 2019 5:37 PM (in response to christopher.t.jones123)Thank you so much Chris.
Code is Poetry.
-
-
-
-
-
-
Re: Report on Muted and Unmanaged Entities
Al Ma Oct 31, 2019 12:58 PM (in response to wes.reneau)Same here! used the last query here, removed 2 Application sections, added Order by at the end and used without Search option. I only see Unmanaged nodes in Custom query as well as SWQL studios.
I am on 12.4 2018.4 HF2
-
Re: Report on Muted and Unmanaged Entities
wes.reneau Dec 3, 2019 8:16 AM (in response to wes.reneau)Finally got back to this and I now have muted and unmanaged. I added an OR to capture those nodes w/ suppressuntil unset. This is a tweak on the previous version supplied. Your mileage may vary...
OR
--Added to capture those nodes where the suppress until time is not set
SuppressUntil IS NULL OR SuppressUntil = '9999-01-01 00:00:00'
Full SWQL follows
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
--added by WR
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}%')
OR
--Added to capture those nodes where the suppress until time is not set
SuppressUntil IS NULL OR SuppressUntil = '9999-01-01 00:00:00'
)
ORDER BY [node] asc, [status] desc
-- The following is what is used to search the results, it'll go in the Search SWQL Query pane on the custom query widget
'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
-
Re: Report on Muted and Unmanaged Entities
Al Ma Dec 5, 2019 10:50 AM (in response to wes.reneau)Thanks Wes!
The query works in SWQL studio but not in Web report writer. it says query is invalid. What could be causing that?
Also is there a way to also add a column which shows the entity type whether it is a node or interface that is unmanaged/muted? And a condition to use custom properties table?
This will really be appreciated!
-
Re: Report on Muted and Unmanaged Entities
wes.reneau Dec 5, 2019 11:42 AM (in response to Al Ma)This is SWQL only. If I'm not mistaken Report Writer is SQL only.
Also, the rumor is that Report Writer is being depreciated, at least I think I read that someplace.Thanks
Wes
-
Re: Report on Muted and Unmanaged Entities
Al Ma Dec 11, 2019 12:54 PM (in response to wes.reneau)Hi Wes,
I might have confused you by saying report writer but I meant that I used the web report writer front end with the choice of SWQL and it failed. When I pasted in SWQL studio it worked...
Hope you get some time to review this.
Not sure if others are able to see all 4 categories:
Unmanaged Nodes
Unmanaged Interfaces
Muted Nodes
Muted Interfaces
Ability to see the date/time stamp
Ability to use custom property condition for a particular suport group, etc
This will be an amazing report if working successfully.
Thanks in advance Wes!
-
-
-
-
-
-
Re: Report on Muted and Unmanaged Entities
mharvey Jan 30, 2019 11:26 AM (in response to forstgre)I've been using this resource, but noticed recently that devices that are no longer muted still show up in the report even after the the time has gone by and the nodes are no longer muted. Was there a change that would have caused this? Is there a way to update it to only show items where Days Left is greater than 0?
-
Re: Report on Muted and Unmanaged Entities
mesverrumJan 30, 2019 4:22 PM (in response to mharvey)
I noticed that too, there are scenarios where previous unmanage events just sit in the DB forever, and other cases where they get deleted. The example GRTA posted has logic to avoid showing the stale ones, where suppressuntil > getdate()
-
Re: Report on Muted and Unmanaged Entities
mharvey Jan 31, 2019 9:02 AM (in response to mesverrum)Thanks for pointing that out. Didn't see that. Adding that was just what I needed.
-
-
-
Re: Report on Muted and Unmanaged Entities
mfortner Jun 24, 2019 7:55 AM (in response to forstgre)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
-
Re: Report on Muted and Unmanaged Entities
mesverrumJun 24, 2019 11:11 AM (in response to mfortner)
might be fine to just change
,tostring(tolocal(n.UnManageFrom)) AS [From]
to
,tolocal(n.UnManageFrom) AS [From]
-
-
Re: Report on Muted and Unmanaged Entities
tsanchez11 Jul 10, 2019 8:31 AM (in response to forstgre)I tried this, and the columns appear but the data is blank? I did a copy/paste and did not change anything else.
-
Re: Report on Muted and Unmanaged Entities
tsanchez11 Jul 10, 2019 8:31 AM (in response to tsanchez11)Fixed, the view had a limitation set so I removed the limitation and the table populated.
-
-
Re: Report on Muted and Unmanaged Entities
ankitaja Jul 10, 2019 4:53 AM (in response to forstgre)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].EntityURIUNION
( 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].EntityURIWHERE [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].EntityURIWHERE [Element] LIKE '%${SEARCH_STRING}%'
)What modification I need to do in this query to have account name also in Dashboard.
-
Re: Report on Muted and Unmanaged Entities
johnlad Jul 30, 2019 8:19 AM (in response to forstgre)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.
-
Re: Report on Muted and Unmanaged Entities
christopher.t.jones123Oct 16, 2019 7:58 AM (in response to johnlad)
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
-
-
Re: Report on Muted and Unmanaged Entities
mnsh_majumderNov 25, 2019 8:38 AM (in response to forstgre)
1 of 1 people found this helpfulI 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%'