Hello!
I've been surfing around this board trying to piece together a report that will show me unmanaged/muted interfaces, nodes, and application monitors.
I've been able to get nodes and interfaces as a "Custom Query", but I'm only able to see Application monitors via a 'custom table'; The table doesn't look as nice as the custom query output does.
Does anyone have a custom query that will work for Application monitors?
I've found these queries around this board and they have been very helpful.
For the "Custom Table", this query was used, it has a specific section for Application Monitors:
-- 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}%')
)
I'd like to get the Application monitor to report like the things in the top two sections. If they could all be in one section that'd be great.
I dont know enough about these queries to smash them all together, but I just dont like the way the output of the table looks. Any help would be GREATLY appreciated!!
If it matters, i'm running Orion Platform HF1, NPM HF1, SAM HF1: 2020.2
Thank you.
