-
Re: Report showing user who unmanaged elements and muted alerts
m-milligan May 3, 2018 11:04 AM (in response to patriot)This SWQL query* will get you a list of all nodes that are currently unmanaged, the unmanage start time, when the node will be re-managed, the most recent occasion when someone unmanaged the node, and the account ID of the user who did it:
Select N.Caption, N.UnManageFrom, N.UnManageUntil, LastUnmanageEvent.TimeLoggedUtc, AE.AccountID from Orion.Nodes N LEFT OUTER JOIN ( SELECT NetObjectID, Max(AuditEventID) as [AuditEventID], Max(TimeLoggedUtc) as [TimeLoggedUtc] FROM Orion.AuditingEvents where ActionTypeID= ( SELECT ActionTypeID FROM Orion.AuditingActionTypes where ActionType = 'Orion.NodeUnmanaged' ) group by NetObjectID ) AS [LastUnmanageEvent] on LastUnmanageEvent.NetObjectID = N.NodeID LEFT OUTER JOIN Orion.AuditingEvents AE on AE.AuditEventID = LastUnmanageEvent.AuditEventID Where Unmanaged = TRUE ORDER BY N.Caption
Edited 2018-05-03 to account for variation in Orion.AuditingEvents.ActionTypeID across installations.
Select N.Caption, N.UnManageFrom, N.UnManageUntil, LastUnmanageEvent.TimeLoggedUtc, AE.AccountID from Orion.Nodes N LEFT OUTER JOIN ( SELECT NetObjectID, Max(AuditEventID) as [AuditEventID], Max(TimeLoggedUtc) as [TimeLoggedUtc] FROM Orion.AuditingEvents where ActionTypeID= ( SELECT ActionTypeID FROM Orion.AuditingActionTypes where ActionType = 'Orion.NodeUnmanaged' ) group by NetObjectID ) AS [LastUnmanageEvent] on LastUnmanageEvent.NetObjectID = N.NodeID LEFT OUTER JOIN Orion.AuditingEvents AE on AE.AuditEventID = LastUnmanageEvent.AuditEventID Where Unmanaged = TRUE ORDER BY N.Caption
-
Re: Report showing user who unmanaged elements and muted alerts
patriot May 1, 2018 11:14 AM (in response to m-milligan)Thanks for the help m-milligan. I used the SWQL in a custom query resource - one e3ach for unmanaged nodes and muted alerts and am not getting the expected results. I unmanaged three nodes, but only two of them show in the query results. I muted alerts on three nodes and the query is empty. Any ideas how to troubleshoot? Thanks again.
-
Re: Report showing user who unmanaged elements and muted alerts
m-milligan May 1, 2018 12:36 PM (in response to patriot)I can't reproduce the discrepancy in muted nodes. Can you check the number of currently muted nodes with this query? It will exclude any muted items that are not nodes.
SELECT ID, EntityUri, SuppressFrom, SuppressUntil FROM Orion.AlertSuppression Supp where Supp.EntityUri like '%NodeID%' and Supp.EntityUri not like '%NodeID%/%'
I'll continue looking at the unmanaged nodes query.
-
Re: Report showing user who unmanaged elements and muted alerts
patriot May 1, 2018 12:26 PM (in response to m-milligan)That query produces an error in the resource. Are there any characters out of place or mis-typed by any chance?
-
Re: Report showing user who unmanaged elements and muted alerts
m-milligan May 1, 2018 12:39 PM (in response to patriot)I had a clause in the query that apparently doesn't play nicely with custom query resources. I've corrected my query. Use this:
SELECT ID, EntityUri, SuppressFrom, SuppressUntil
FROM Orion.AlertSuppression Supp
where Supp.EntityUri like '%NodeID%' and Supp.EntityUri not like '%NodeID%/%'
-
Re: Report showing user who unmanaged elements and muted alerts
patriot May 1, 2018 2:22 PM (in response to m-milligan)Thanks for the update. I removed the 'not like' part at the end of the query and find that it shows the expected nodes (which is all I am interested in for now). Now, how do I include the node name in the muted alerts query?
-
Re: Report showing user who unmanaged elements and muted alerts
m-milligan May 31, 2019 12:41 PM (in response to patriot)This includes the node name (N.Caption):
Edited 2018-05-03 to account for variation in Orion.AuditingActionTypes.ActionTypeID across installations.
Edited 2019-05-31 to ensure that only mute events on nodes are considered.
Select N.Caption, Supp.SuppressFrom, Supp.SuppressUntil, AE.TimeLoggedUtc, AE.AccountID from Orion.Nodes N INNER JOIN Orion.AlertSuppression Supp on Supp.EntityUri = N.Uri LEFT OUTER JOIN ( SELECT NetObjectID, Max(AuditEventID) as [AuditEventID] FROM Orion.AuditingEvents where ActionTypeID in ( SELECT ActionTypeID FROM Orion.AuditingActionTypes where ActionType = 'Orion.AlertSuppressionAdded' or ActionType = 'Orion.AlertSuppressionChanged' ) and NetObjectType='N' group by NetObjectID ) AS [LastMuteEvent] on LastMuteEvent.NetObjectID = N.NodeID LEFT OUTER JOIN Orion.AuditingEvents AE on AE.AuditEventID = LastMuteEvent.AuditEventID ORDER BY N.Caption
-
Re: Report showing user who unmanaged elements and muted alerts
patriot May 1, 2018 3:37 PM (in response to m-milligan)Well... something changed again because going from the brief 3 line query:
SELECT ID, EntityUri, SuppressFrom, SuppressUntil
FROM Orion.AlertSuppression Supp
where Supp.EntityUri like '%NodeID%'
which works to what you have above... yields a blank result again.
-
Re: Report showing user who unmanaged elements and muted alerts
m-milligan May 3, 2018 11:06 AM (in response to patriot)How about this query? This will also get you the currently muted nodes, etc.
Edited 2018-05-03 to account for variation in Orion.AuditingActionTypes.ActionTypeID across installations.
Select N.Caption, Supp.SuppressFrom, Supp.SuppressUntil, LastMuteEvent.TimeLoggedUtc, AE.AccountID from Orion.Nodes N INNER JOIN Orion.AlertSuppression Supp on Supp.EntityUri = N.Uri LEFT OUTER JOIN ( SELECT NetObjectID, Max(AuditEventID) as [AuditEventID], Max(TimeLoggedUtc) as [TimeLoggedUtc] FROM Orion.AuditingEvents where ActionTypeID in ( SELECT ActionTypeID FROM Orion.AuditingActionTypes where ActionType = 'Orion.AlertSuppressionAdded' or ActionType = 'Orion.AlertSuppressionChanged' ) group by NetObjectID ) AS [LastMuteEvent] on LastMuteEvent.NetObjectID = N.NodeID LEFT OUTER JOIN Orion.AuditingEvents AE on AE.AuditEventID = LastMuteEvent.AuditEventID ORDER BY N.Caption
-
Re: Report showing user who unmanaged elements and muted alerts
patriot May 1, 2018 3:59 PM (in response to m-milligan)-
Re: Report showing user who unmanaged elements and muted alerts
m-milligan May 1, 2018 4:20 PM (in response to patriot)"SuppressUntil" is empty because no end time was specified when the node was muted. The node will stay muted until it's explicitly un-muted. That's the default behavior if a user just clicks Maintenance Mode - Mute Alerts Now, like this:
The other columns could be empty if the alert had been muted a long time ago and the events had since been purged from Orion.AuditEvents. However, I see the SuppressFrom dates are from today, so I'm assuming you just did these, correct?
Run this query in SWQL Studio and see if the mute events are being logged in there. That's where my query gets that data; if the events are not being logged, the TimeLoggedUtc and AccountID columns will be empty. Do you see a row with a recent timestamp for each node you muted?
SELECT NetObjectID, Max(AuditEventID) as [AuditEventID], Max(TimeLoggedUtc) as [TimeLoggedUtc]
FROM Orion.AuditingEvents
where ActionTypeID in (102,103)
group by NetObjectID
-
Re: Report showing user who unmanaged elements and muted alerts
patriot May 1, 2018 4:28 PM (in response to m-milligan)If you mean to run the query in Database Manager, I did that after removing the "Orion." in front of AuditingEvents in line 2. However, there were no returned results. Not sure what that means though.
And yes, I muted some alerts and unmanaged some nodes just today for testing. I would have expected to see my user account and the timestamp for when I executed the action.
Strange.
-
Re: Report showing user who unmanaged elements and muted alerts
m-milligan May 1, 2018 4:38 PM (in response to patriot)No, I mean run the query in the SWQL Studio application. That's the SWQL equivalent to Database Manager. It should have been installed when you installed Solarwinds.
In this case, running that query in Database Manager (after removing "Orion.") should produce the same result as running it in SWQL Studio. Does the AuditingEvents table contain any rows at all? What do you get with these two queries in Database Manager?
select count('x') from AuditingEvents
select count('x') from AuditingEvents where ActionTypeID in (102,103)
What version of Solarwinds are you running?
-
Re: Report showing user who unmanaged elements and muted alerts
patriot May 2, 2018 2:20 PM (in response to m-milligan)Where is the SWQL Studio? On the Start menu on the Primary polling server?
-
Re: Report showing user who unmanaged elements and muted alerts
m-milligan May 2, 2018 3:16 PM (in response to patriot)It should be on the Start menu on your main Orion poller, in the Solarwinds Orion SDK group.
-
-
Re: Report showing user who unmanaged elements and muted alerts
patriot May 2, 2018 2:22 PM (in response to m-milligan)The first query gives a result of 980. The second one a result of 0. Hmmm.
-
Re: Report showing user who unmanaged elements and muted alerts
m-milligan May 2, 2018 3:20 PM (in response to patriot)OK, that suggests that Solarwinds is not logging auditing events for Alert Suppression (muting). That's why those three columns are empty in the query results - there is no matching data in the table that logs the auditing events.
When you run the query below, what do you get? I wonder if your installation has a Action Type ID for these events.
SELECT ActionTypeID, ActionType, ActionTypeDisplayName FROM Orion.AuditingActionTypes where ActionType like '%Suppression%'
-
Re: Report showing user who unmanaged elements and muted alerts
patriot May 2, 2018 3:26 PM (in response to m-milligan)-
Re: Report showing user who unmanaged elements and muted alerts
m-milligan May 2, 2018 3:33 PM (in response to patriot)OK, your installation is using different IDs for those actions. Replace (102,103) in the query I provided with (55,56).
-
Re: Report showing user who unmanaged elements and muted alerts
patriot May 2, 2018 3:47 PM (in response to m-milligan)-
Re: Report showing user who unmanaged elements and muted alerts
m-milligan May 3, 2018 11:08 AM (in response to patriot)3 of 3 people found this helpfulGreat. So if you run the original query to show muted nodes, but you refer to EventTypes 55 and 56, does that produce the results you expected? Here's the revised query using your EventTypeIDs:
Edited 2018-05-03 to account for variation in Orion.AuditingActionTypes.ActionTypeID across installations.
Select N.Caption, Supp.SuppressFrom, Supp.SuppressUntil, LastMuteEvent.TimeLoggedUtc, AE.AccountID from Orion.Nodes N INNER JOIN Orion.AlertSuppression Supp on Supp.EntityUri = N.Uri LEFT OUTER JOIN ( SELECT NetObjectID, Max(AuditEventID) as [AuditEventID], Max(TimeLoggedUtc) as [TimeLoggedUtc] FROM Orion.AuditingEvents where ActionTypeID in ( SELECT ActionTypeID FROM Orion.AuditingActionTypes where ActionType = 'Orion.AlertSuppressionAdded' or ActionType = 'Orion.AlertSuppressionChanged' ) group by NetObjectID ) AS [LastMuteEvent] on LastMuteEvent.NetObjectID = N.NodeID LEFT OUTER JOIN Orion.AuditingEvents AE on AE.AuditEventID = LastMuteEvent.AuditEventID ORDER BY N.Caption
-
Re: Report showing user who unmanaged elements and muted alerts
patriot May 2, 2018 4:14 PM (in response to m-milligan)That fixed it. Thanks much friend.
-
Re: Report showing user who unmanaged elements and muted alerts
m-milligan May 2, 2018 4:16 PM (in response to patriot)Excellent! Glad that did it!
-
Re: Report showing user who unmanaged elements and muted alerts
patriot May 2, 2018 4:20 PM (in response to m-milligan)So, I assume that I could use these SWQL queries in a report as well as in the "custom query" resource on a view, correct?
-
Re: Report showing user who unmanaged elements and muted alerts
m-milligan May 2, 2018 4:22 PM (in response to patriot)Absolutely.
-
-
Re: Report showing user who unmanaged elements and muted alerts
patriot May 3, 2018 9:38 AM (in response to m-milligan)Oooops. Now that I look at the "unmanage" query, I see that it too does not populate the AccountID column even though it looks to me like the Join statement is there:
Select N.Caption, N.UnManageFrom, N.UnManageUntil, LastUnmanageEvent.TimeLoggedUtc, AE.AccountID
from Orion.Nodes N
LEFT OUTER JOIN (
SELECT NetObjectID, Max(AuditEventID) as [AuditEventID], Max(TimeLoggedUtc) as [TimeLoggedUtc]
FROM Orion.AuditingEvents
where ActionTypeID=27
group by NetObjectID
) AS [LastUnmanageEvent] on LastUnmanageEvent.NetObjectID = N.NodeID
LEFT OUTER JOIN Orion.AuditingEvents AE on AE.AuditEventID = LastUnmanageEvent.AuditEventID
Where Unmanaged = TRUE
ORDER BY N.Caption
Ideas?
-
Re: Report showing user who unmanaged elements and muted alerts
mesverrumMay 3, 2018 10:37 AM (in response to patriot)
1 of 1 people found this helpfulThe hard coded action type is not correct for your environment, the numbers change based on the order you installed modules and what version you started with as they have added additional actions in over the years. Look at my query above to see how to look for an action by name instead of specific ID
-
Re: Report showing user who unmanaged elements and muted alerts
m-milligan May 3, 2018 10:59 AM (in response to patriot)See my reply to mesverrum. Here's the revised query for unmanaged nodes (the revised query for muted nodes is in my reply):
Select N.Caption, N.UnManageFrom, N.UnManageUntil, LastUnmanageEvent.TimeLoggedUtc, AE.AccountID from Orion.Nodes N LEFT OUTER JOIN ( SELECT NetObjectID, Max(AuditEventID) as [AuditEventID], Max(TimeLoggedUtc) as [TimeLoggedUtc] FROM Orion.AuditingEvents where ActionTypeID= ( SELECT ActionTypeID FROM Orion.AuditingActionTypes where ActionType = 'Orion.NodeUnmanaged' ) group by NetObjectID ) AS [LastUnmanageEvent] on LastUnmanageEvent.NetObjectID = N.NodeID LEFT OUTER JOIN Orion.AuditingEvents AE on AE.AuditEventID = LastUnmanageEvent.AuditEventID Where Unmanaged = TRUE ORDER BY N.Caption
-
Re: Report showing user who unmanaged elements and muted alerts
rajasekar Aug 3, 2018 3:41 AM (in response to m-milligan)Thank you very much for this. Can you include the future scheduled mute also in this query.
-
-
-
-
-
Re: Report showing user who unmanaged elements and muted alerts
trilobite rex Sep 7, 2018 8:14 AM (in response to m-milligan)I have a bunch of different dashboards I would like to apply this to. Is there any way to add a filter based on a custom property?
-
Re: Report showing user who unmanaged elements and muted alerts
m-milligan Sep 7, 2018 9:39 AM (in response to trilobite rex)1 of 1 people found this helpfulI should think so. Say the name of your node custom property is "Department" and you want to filter for nodes where the Department is "Financials". You'd need to add a line like this to the WHERE clause at the end:
AND N.CustomProperties.Department = 'Financials'
So the final query would look like:
Select N.Caption, N.UnManageFrom, N.UnManageUntil, LastUnmanageEvent.TimeLoggedUtc, AE.AccountID from Orion.Nodes N LEFT OUTER JOIN ( SELECT NetObjectID, Max(AuditEventID) as [AuditEventID], Max(TimeLoggedUtc) as [TimeLoggedUtc] FROM Orion.AuditingEvents where ActionTypeID= ( SELECT ActionTypeID FROM Orion.AuditingActionTypes where ActionType = 'Orion.NodeUnmanaged' ) group by NetObjectID ) AS [LastUnmanageEvent] on LastUnmanageEvent.NetObjectID = N.NodeID LEFT OUTER JOIN Orion.AuditingEvents AE on AE.AuditEventID = LastUnmanageEvent.AuditEventID Where Unmanaged = TRUE AND N.CustomProperties.Department = 'Financials' ORDER BY N.Caption
-
Re: Report showing user who unmanaged elements and muted alerts
mesverrumSep 7, 2018 9:46 AM (in response to m-milligan)
1 of 1 people found this helpfulAlso worth pointing out that if you have a lot of dashboards it is immensely easier to just apply a view limitation to the entire view instead of editing every resource on the page with the same filters.
Go to custom view and find what you need here
If your custom property isn't in the list then you need to run a tool on your Orion server called:
-
Re: Report showing user who unmanaged elements and muted alerts
trilobite rex Sep 10, 2018 7:27 AM (in response to m-milligan)Thank you so much! That did the trick.
-
-
-
-
-
Re: Report showing user who unmanaged elements and muted alerts
mesverrumMay 3, 2018 12:15 AM (in response to m-milligan)
1 of 1 people found this helpfulSo as an FYI, depending how old someone's instance is, and how many modules they have installed the audit events and regular events tables will have all kinds of different numbers for eventids. To make a query work in other people's environments I find it is 100% required to write queries with a join to the relevant types tables tables and filter based on the name strings.
As an example, this is my query for unmanaged or muted nodes
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 showing user who unmanaged elements and muted alerts
m-milligan May 3, 2018 10:56 AM (in response to mesverrum)So as an FYI, depending how old someone's instance is, and how many modules they have installed the audit events and regular events tables will have all kinds of different numbers for eventids. To make a query work in other people's environments I find it is 100% required to write queries with a join to the relevant types tables tables and filter based on the name strings.
Who thought that was a good idea?
(Breathe)
This query will get the muted nodes regardless of what values your particular installation has for those audit event IDs:
Select N.Caption, Supp.SuppressFrom, Supp.SuppressUntil, LastMuteEvent.TimeLoggedUtc, AE.AccountID from Orion.Nodes N INNER JOIN Orion.AlertSuppression Supp on Supp.EntityUri = N.Uri LEFT OUTER JOIN ( SELECT NetObjectID, Max(AuditEventID) as [AuditEventID], Max(TimeLoggedUtc) as [TimeLoggedUtc] FROM Orion.AuditingEvents where ActionTypeID in ( SELECT ActionTypeID FROM Orion.AuditingActionTypes where ActionType = 'Orion.AlertSuppressionAdded' or ActionType = 'Orion.AlertSuppressionChanged' ) group by NetObjectID ) AS [LastMuteEvent] on LastMuteEvent.NetObjectID = N.NodeID LEFT OUTER JOIN Orion.AuditingEvents AE on AE.AuditEventID = LastMuteEvent.AuditEventID ORDER BY N.Caption
This query will get the unmanaged nodes regardless of what values your particular installation has for those audit event IDs:
Select N.Caption, N.UnManageFrom, N.UnManageUntil, LastUnmanageEvent.TimeLoggedUtc, AE.AccountID from Orion.Nodes N LEFT OUTER JOIN ( SELECT NetObjectID, Max(AuditEventID) as [AuditEventID], Max(TimeLoggedUtc) as [TimeLoggedUtc] FROM Orion.AuditingEvents where ActionTypeID= ( SELECT ActionTypeID FROM Orion.AuditingActionTypes where ActionType = 'Orion.NodeUnmanaged' ) group by NetObjectID ) AS [LastUnmanageEvent] on LastUnmanageEvent.NetObjectID = N.NodeID LEFT OUTER JOIN Orion.AuditingEvents AE on AE.AuditEventID = LastUnmanageEvent.AuditEventID Where Unmanaged = TRUE ORDER BY N.Caption
-
-
-
-
-
-
-
-
-
-
-
-
Re: Report showing user who unmanaged elements and muted alerts
mfanderson01 Sep 10, 2018 2:54 PM (in response to m-milligan)M,
first thing thank you for sharing your query. I am having an issue with the join statement on the alert suppression table. where are you getting the N.Uri part? I do not see anything with Uri in my nodes table. In fact not finding anything to link the two tables at all
thanks
-
Re: Report showing user who unmanaged elements and muted alerts
m-milligan Sep 12, 2018 2:25 PM (in response to mfanderson01)Are you looking at the tables (and the query) with SWQL Studio or with a traditional SQL tool (SQL Query Analyzer, Solarwinds Database Manager)? You'll only see the Uri column if you're using SWQL Studio, because Orion.Nodes is an entity in SWIS (it's based on the Nodes view in the SolarwindsOrion database, but it's not the same as the view).
-
-
-
-
-
-
-
Re: Report showing user who unmanaged elements and muted alerts
m-milligan May 3, 2018 10:58 AM (in response to patriot)1 of 1 people found this helpfulOK, this query will get you all of the unmanaged nodes. The original query didn't account for nodes that have been unmanaged for a very long time - long enough that the original unmanage event has been purged from Orion.AuditEvents.
Edited 2018-05-03 to account for variation in Orion.AuditingEvents.ActionTypeID across installations.
Select N.Caption, N.UnManageFrom, N.UnManageUntil, LastUnmanageEvent.TimeLoggedUtc, AE.AccountID from Orion.Nodes N LEFT OUTER JOIN ( SELECT NetObjectID, Max(AuditEventID) as [AuditEventID], Max(TimeLoggedUtc) as [TimeLoggedUtc] FROM Orion.AuditingEvents where ActionTypeID= ( SELECT ActionTypeID FROM Orion.AuditingActionTypes where ActionType = 'Orion.NodeUnmanaged' ) group by NetObjectID ) AS [LastUnmanageEvent] on LastUnmanageEvent.NetObjectID = N.NodeID LEFT OUTER JOIN Orion.AuditingEvents AE on AE.AuditEventID = LastUnmanageEvent.AuditEventID Where Unmanaged = TRUE ORDER BY N.Caption
-
-
-
Re: Report showing user who unmanaged elements and muted alerts
rajasekar Jul 30, 2018 8:51 AM (in response to patriot)Dear team,
I need one query to see the future scheduled unmange activity along with the present unmanage schedule activity
-
Re: Report showing user who unmanaged elements and muted alerts
m-milligan Jul 30, 2018 12:42 PM (in response to rajasekar)Hi rajasekar,
This query includes future scheduled unmanagement:
Select N.Caption, N.UnManageFrom, N.UnManageUntil, LastUnmanageEvent.TimeLoggedUtc, AE.AccountID from Orion.Nodes N LEFT OUTER JOIN ( SELECT NetObjectID, Max(AuditEventID) as [AuditEventID], Max(TimeLoggedUtc) as [TimeLoggedUtc] FROM Orion.AuditingEvents where ActionTypeID= ( SELECT ActionTypeID FROM Orion.AuditingActionTypes where ActionType = 'Orion.NodeUnmanaged' ) group by NetObjectID ) AS [LastUnmanageEvent] on LastUnmanageEvent.NetObjectID = N.NodeID LEFT OUTER JOIN Orion.AuditingEvents AE on AE.AuditEventID = LastUnmanageEvent.AuditEventID Where (N.Unmanaged = TRUE OR N.UnManageFrom > GETUTCDATE()) ORDER BY N.Caption
-
Re: Report showing user who unmanaged elements and muted alerts
rajasekar Aug 3, 2018 3:50 AM (in response to m-milligan)Thank you very much for this. Can you include the Current status column and future scheduled mute also in this query.
-
Re: Report showing user who unmanaged elements and muted alerts
m-milligan Aug 6, 2018 2:33 PM (in response to rajasekar)Sure. This query gets only current and future mute events:
Select N.Caption, Supp.SuppressFrom, Supp.SuppressUntil, LastMuteEvent.TimeLoggedUtc, AE.AccountID, N.StatusDescription from Orion.Nodes N INNER JOIN Orion.AlertSuppression Supp on Supp.EntityUri = N.Uri LEFT OUTER JOIN ( SELECT NetObjectID, Max(AuditEventID) as [AuditEventID], Max(TimeLoggedUtc) as [TimeLoggedUtc] FROM Orion.AuditingEvents where ActionTypeID in ( SELECT ActionTypeID FROM Orion.AuditingActionTypes where ActionType = 'Orion.AlertSuppressionAdded' or ActionType = 'Orion.AlertSuppressionChanged' ) group by NetObjectID ) AS [LastMuteEvent] on LastMuteEvent.NetObjectID = N.NodeID LEFT OUTER JOIN Orion.AuditingEvents AE on AE.AuditEventID = LastMuteEvent.AuditEventID WHERE ( ToUTC(Supp.SuppressFrom) >= TOLOCAL(GETDATE()) OR ToUTC(Supp.SuppressFrom) <= TOLOCAL(GETDATE()) AND ToUTC(Supp.SuppressUntil) >= TOLOCAL(GETDATE()) ) ORDER BY N.Caption
This query puts the future unmanage and future mute events into a single table:
Select N.Caption, N.UnManageFrom, N.UnManageUntil, LastUnmanageEvent.TimeLoggedUtc, AE.AccountID, N.StatusDescription from Orion.Nodes N LEFT OUTER JOIN ( SELECT NetObjectID, Max(AuditEventID) as [AuditEventID], Max(TimeLoggedUtc) as [TimeLoggedUtc] FROM Orion.AuditingEvents where ActionTypeID= ( SELECT ActionTypeID FROM Orion.AuditingActionTypes where ActionType = 'Orion.NodeUnmanaged' ) group by NetObjectID ) AS [LastUnmanageEvent] on LastUnmanageEvent.NetObjectID = N.NodeID LEFT OUTER JOIN Orion.AuditingEvents AE on AE.AuditEventID = LastUnmanageEvent.AuditEventID Where (N.Unmanaged = TRUE OR N.UnManageFrom > GETUTCDATE()) UNION ( Select N.Caption, Supp.SuppressFrom, Supp.SuppressUntil, LastMuteEvent.TimeLoggedUtc, AE.AccountID, N.StatusDescription from Orion.Nodes N INNER JOIN Orion.AlertSuppression Supp on Supp.EntityUri = N.Uri LEFT OUTER JOIN ( SELECT NetObjectID, Max(AuditEventID) as [AuditEventID], Max(TimeLoggedUtc) as [TimeLoggedUtc] FROM Orion.AuditingEvents where ActionTypeID in ( SELECT ActionTypeID FROM Orion.AuditingActionTypes where ActionType = 'Orion.AlertSuppressionAdded' or ActionType = 'Orion.AlertSuppressionChanged' ) group by NetObjectID ) AS [LastMuteEvent] on LastMuteEvent.NetObjectID = N.NodeID LEFT OUTER JOIN Orion.AuditingEvents AE on AE.AuditEventID = LastMuteEvent.AuditEventID WHERE ( ToUTC(Supp.SuppressFrom) >= TOLOCAL(GETDATE()) OR ToUTC(Supp.SuppressFrom) <= TOLOCAL(GETDATE()) AND ToUTC(Supp.SuppressUntil) >= TOLOCAL(GETDATE()) ) ) ORDER BY N.Caption
-
-
Re: Report showing user who unmanaged elements and muted alerts
rajasekar Aug 3, 2018 8:07 AM (in response to m-milligan)Hi m-milligan
Am using your query but in that some utc and account ID is empty can you suggest for this.
-
Re: Report showing user who unmanaged elements and muted alerts
m-milligan Aug 6, 2018 2:37 PM (in response to rajasekar)Those are nodes that have never been unmanaged before. TimeLoggedUTC and AccountID come from Orion.AuditingEvents. If the node hasn't ever been unmanaged, it won't have a record in Orion.AuditingEvents.
-
Re: Report showing user who unmanaged elements and muted alerts
rajasekar Aug 7, 2018 1:34 AM (in response to m-milligan)The nodes which is not unmanaged before got unmanaged now means who done unmanage now that account ID need to come need to come.
-
Re: Report showing user who unmanaged elements and muted alerts
rajasekar Aug 17, 2018 9:13 AM (in response to m-milligan)Hi m-milligan in the above query am getting the account ID as previously done the scheduled activity but i need the account ID that done the scheduled activity now at the last.
-
Re: Report showing user who unmanaged elements and muted alerts
m-milligan Aug 17, 2018 9:41 AM (in response to rajasekar)Do you mean you need to know the account ID that did it the previous time, not the current one?
-
Re: Report showing user who unmanaged elements and muted alerts
rajasekar Aug 18, 2018 5:17 AM (in response to m-milligan)I need the current account ID only but as per your script it showing the previous account ID so please do the modification and share that well help us.
-
Re: Report showing user who unmanaged elements and muted alerts
rajasekar Sep 10, 2018 4:28 AM (in response to m-milligan)Hi m-milligan
Most of the event account ID is empty and some are not showing the correct account ID who is doing unmanage.
-
Re: Report showing user who unmanaged elements and muted alerts
rajasekar Nov 2, 2018 4:43 AM (in response to rajasekar)Hi m-milligan
Kindly help me am using your script but it showing the wrong time and wrong account ID.
-
-
-
-
-
-
-
-
Re: Report showing user who unmanaged elements and muted alerts
paul326 Sep 10, 2018 4:19 PM (in response to patriot)This is excellent stuff, especially since I'm a n00b with SQL queries. I've been trying to add line to exclude specific N.Caption names to exclude nodes we don't want reported (items to manage but not alert, 100% of the time muted)
This query gets me exactly what I need except excluding caption name that contains 'TST' Any help appreciated.
Select N.Caption, Supp.SuppressFrom, Supp.SuppressUntil, LastMuteEvent.TimeLoggedUtc, AE.AccountID
from Orion.Nodes N
INNER JOIN Orion.AlertSuppression Supp on Supp.EntityUri = N.Uri
LEFT OUTER JOIN (
SELECT NetObjectID, Max(AuditEventID) as [AuditEventID], Max(TimeLoggedUtc) as [TimeLoggedUtc]
FROM Orion.AuditingEvents
where ActionTypeID in (
SELECT ActionTypeID
FROM Orion.AuditingActionTypes
where ActionType = 'Orion.AlertSuppressionAdded'
or ActionType = 'Orion.AlertSuppressionChanged'
)
group by NetObjectID
) AS [LastMuteEvent] on LastMuteEvent.NetObjectID = N.NodeID
LEFT OUTER JOIN Orion.AuditingEvents AE on AE.AuditEventID = LastMuteEvent.AuditEventID
ORDER BY N.Caption
Results:
NAP-DMZSW-02 2018-08-22 16:52:46.58 NULL 2018-08-22 16:52:46.987 NAP-VLNX-FAZ-01 2018-08-12 01:28:20.327 NULL 2018-08-12 01:28:20.78 pla-esx6-vmw-02.eby-brown.com 2018-06-21 16:25:09.177 NULL 2018-06-21 16:25:09.55 ROC-FS-C3560CX-SW1 2018-07-15 00:52:08.097 NULL 2018-07-15 00:52:08.55 TST-V2K3-VPK-01 2018-09-10 20:54:46.093 NULL 2018-09-10 20:54:46.39 TST-V2K3-VPK-02 2018-04-07 21:59:17.117 NULL 2018-04-07 21:59:17.553 TST-V2K3-VPK-03 2018-04-07 21:59:17.553 NULL 2018-04-07 21:59:18.007 TST-V2K3-VPK-04 2018-04-07 21:59:18.007 NULL 2018-04-07 21:59:18.443 want to exclude anything with 'TST' in the name.
Thanks,
Paul
-
Re: Report showing user who unmanaged elements and muted alerts
paul326 Sep 11, 2018 10:13 AM (in response to paul326)Figured it out... was adding the 'where' statement in the wrong place. Added it to the LEFT OUTER statement and all good
where N.Caption NOT LIKE 'TST%'
Paul
-
Re: Report showing user who unmanaged elements and muted alerts
m-milligan Sep 12, 2018 12:43 PM (in response to paul326)1 of 1 people found this helpfulGreat!
-
-
-
Re: Report showing user who unmanaged elements and muted alerts
rschroederSep 12, 2018 2:51 PM (in response to patriot)
NICE!
-
Re: Report showing user who unmanaged elements and muted alerts
mat12 May 27, 2019 4:08 AM (in response to patriot)Thanks for sharing your queries.
However, I noticed that the user account ID showing is incorrect if the scenario is:
1. User1 muted node1.
2. User2 Unmute node1.
3. User2 muted node1 again - this time, the account ID column is still showing User1 not User2.
Even if User2 unmute and mute the node multiple times, the User1 is still showing instead of the current user who muted the alert (User2).
is this another bug in mute alert feature?
Thanks in advance!
-
Re: Report showing user who unmanaged elements and muted alerts
conman May 31, 2019 1:23 PM (in response to mat12)There are limitations with these queries as we are matching the tables on timestamp. We are joining AlertSuppression and AuditingEvents on the minute that they were inserted into the database. There could be a scenario with slow SQL performance where the mute happens at one minute and the event is logged in the next minute in this case the muted entity will not appear in the list as we are unable to match on the timestamp.
----- active muted alerts for nodes ------SELECT n.caption as NodeCaption, ae.timeloggedutc as AuditTimeLoggedUTC, a.SuppressFrom as AlertSuppressFrom, a.SuppressUntil as AlertSuppressUntil, AE.accountid as Account, ae.auditeventmessage as AuditMessage
FROM AlertSuppression2 as a
INNER JOIN Nodes as n on n.nodeid = cast((REVERSE(LEFT(REVERSE(entityuri), CHARINDEX('=', REVERSE(entityuri)) - 1))) as int)
INNER JOIN AuditingEvents as AE on AE.netobjectid = cast((REVERSE(LEFT(REVERSE(entityuri), CHARINDEX('=', REVERSE(entityuri)) - 1))) as int)
where a.entityuri not like '%interfaces%'
and AE.actiontypeid in (77,79)
and cast(ae.timeloggedutc as char) = cast(a.SuppressFrom as char)
------ active muted alerts for interfaces ------SELECT i.interfacename as InterfaceName, ae.timeloggedutc as AuditTimeLoggedUTC, a.SuppressFrom as AlertSuppressFrom, a.SuppressUntil as AlertSuppressUntil,
AE.accountid as Account, ae.auditeventmessage as AuditMessage
FROM AlertSuppression2 as a
INNER JOIN Interfaces as i on i.interfaceid = cast((REVERSE(LEFT(REVERSE(entityuri), CHARINDEX('=', REVERSE(entityuri)) - 1))) as int)
INNER JOIN AuditingEvents as AE on AE.netobjectid = cast((REVERSE(LEFT(REVERSE(entityuri), CHARINDEX('=', REVERSE(entityuri)) - 1))) as int)
where a.entityuri like '%interfaces%'
and AE.actiontypeid in (77,79)
and cast(ae.timeloggedutc as char) = cast(a.SuppressFrom as char)-
Re: Report showing user who unmanaged elements and muted alerts
mat12 Jun 3, 2019 5:23 AM (in response to conman)Thanks for the help and information conman unfortunately, no results came up with the query provided.
-
-
Re: Report showing user who unmanaged elements and muted alerts
m-milligan May 31, 2019 12:40 PM (in response to mat12)1 of 1 people found this helpfulHi mat12,
I tested this with my own system and I can't reproduce the behavior at all. I tried having User1 and User2 mute and unmute a node in various combinations (User1 mutes and unmutes, then User2 mutes versus User1 mutes, User2 unmutes and mutes again). The query always correctly showed the ID of the last user to mute the node.
I have made a minor adjustment to the query to ensure that it only looks at actions that muted nodes (not other kinds of Solarwinds entities). The revised query is:
Select N.Caption, Supp.SuppressFrom, Supp.SuppressUntil, AE.TimeLoggedUtc, AE.AccountID from Orion.Nodes N INNER JOIN Orion.AlertSuppression Supp on Supp.EntityUri = N.Uri LEFT OUTER JOIN ( SELECT NetObjectID, Max(AuditEventID) as [AuditEventID] FROM Orion.AuditingEvents where ActionTypeID in ( SELECT ActionTypeID FROM Orion.AuditingActionTypes where ActionType = 'Orion.AlertSuppressionAdded' or ActionType = 'Orion.AlertSuppressionChanged' ) and NetObjectType='N' group by NetObjectID ) AS [LastMuteEvent] on LastMuteEvent.NetObjectID = N.NodeID LEFT OUTER JOIN Orion.AuditingEvents AE on AE.AuditEventID = LastMuteEvent.AuditEventID ORDER BY N.Caption
Can you try again with this query and let me know if you still notice the error? Perhaps the old query was picking up mute events for non-nodes.
-
Re: Report showing user who unmanaged elements and muted alerts
mat12 Jun 3, 2019 5:26 AM (in response to m-milligan)Hi m-milligan,
Cool stuff! thank you very much with your updated query as I am able to see a unique results (without duplicates) and no old information showing up.
Very much appreciated! Thanks again!! I just need to find out how can I put a link for node details in the caption for this SWQL.
-
Re: Report showing user who unmanaged elements and muted alerts
m-milligan Jun 3, 2019 10:57 AM (in response to mat12)3 of 3 people found this helpfulThis query uses the _linkfor_(column name) format to link to the node detail page. It's useful if you want to use a custom query resource on a page in the Orion GUI:
Select N.Caption, Supp.SuppressFrom, Supp.SuppressUntil, AE.TimeLoggedUtc, AE.AccountID , N.DetailsUrl AS [_linkfor_Caption] from Orion.Nodes N INNER JOIN Orion.AlertSuppression Supp on Supp.EntityUri = N.Uri LEFT OUTER JOIN ( SELECT NetObjectID, Max(AuditEventID) as [AuditEventID] FROM Orion.AuditingEvents where ActionTypeID in ( SELECT ActionTypeID FROM Orion.AuditingActionTypes where ActionType = 'Orion.AlertSuppressionAdded' or ActionType = 'Orion.AlertSuppressionChanged' ) and NetObjectType='N' group by NetObjectID ) AS [LastMuteEvent] on LastMuteEvent.NetObjectID = N.NodeID LEFT OUTER JOIN Orion.AuditingEvents AE on AE.AuditEventID = LastMuteEvent.AuditEventID ORDER BY N.Caption
If you want to create a Solarwinds report and make the node name clickable, use this version. In the report writer, format the Caption column to link to the details page:
Select N.Caption, Supp.SuppressFrom, Supp.SuppressUntil, AE.TimeLoggedUtc, AE.AccountID , N.DetailsUrl from Orion.Nodes N INNER JOIN Orion.AlertSuppression Supp on Supp.EntityUri = N.Uri LEFT OUTER JOIN ( SELECT NetObjectID, Max(AuditEventID) as [AuditEventID] FROM Orion.AuditingEvents where ActionTypeID in ( SELECT ActionTypeID FROM Orion.AuditingActionTypes where ActionType = 'Orion.AlertSuppressionAdded' or ActionType = 'Orion.AlertSuppressionChanged' ) and NetObjectType='N' group by NetObjectID ) AS [LastMuteEvent] on LastMuteEvent.NetObjectID = N.NodeID LEFT OUTER JOIN Orion.AuditingEvents AE on AE.AuditEventID = LastMuteEvent.AuditEventID ORDER BY N.Caption
-
Re: Report showing user who unmanaged elements and muted alerts
mat12 Jun 4, 2019 2:15 AM (in response to m-milligan)Hi m-milligan,
This is exactly what I am looking for!
Thanks a bunch and I really appreciate sharing your time on this!
SW Tech Support also file a feature request related to this and hoping it will be rolled out in the upcoming versions.
-
-
-
-
-
Re: Report showing user who unmanaged elements and muted alerts
alphabits Oct 8, 2019 3:12 PM (in response to patriot)1 of 1 people found this helpfulBuilding on this original post and the great work by @m-milligan I added some. I used it to add a custom query resource to a customer summary page. This shows devices in maintenance mode, suppressed from/to, the latest note, and the timestamp that note was added.
It took some work to get only the latest note to show up (the MAX timestamp area).
SELECT
N.Caption as [Node],
SuppressFrom,
SuppressUntil,
NN.Note as [Note],
NN.TimeStamp,
n.DetailsURL AS [_LinkFor_Node],
'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]
FROM
(select Nodeid, MAX(Timestamp) as "Timestamp"
from Orion.NodeNotes
group by Nodeid) X
JOIN Orion.NodeNotes NN on X.Nodeid=NN.NodeID
and NN.TimeStamp=X.Timestamp
JOIN Orion.Nodes N
on N.Nodeid=NN.NodeID
JOIN Orion.AlertSuppression oa
on oa.EntityUri=N.Uri
where EntityUri not like '%/Interfaces%'
order by Timestamp desc
Hope this helps somebody. It was tough to be sure.
I may make this an original post afterwards due to how much has changed from OPs post.
-
Re: Report showing user who unmanaged elements and muted alerts
SeashoreNov 22, 2019 9:18 AM (in response to patriot)
1 of 1 people found this helpfulThanks all for the inspiration! Built some more on this code:
SELECT
N.Caption AS [Node],
ToString(N.UnManageFrom) AS [Suppressed From],
CASE
WHEN ToString(N.UnManageUntil)='Jan 1 9999 12:00AM' THEN ' - Forever - '
ELSE ToString(N.UnManageUntil)
END AS [Suppressed TO],
CONCAT('/NetPerfMon/Images/Vendors/',N.Icon) AS [_ICONFor_Node],
N.DetailsUrl AS [_LinkFor_Node],
--CONCAT('/Orion/images/StatusIcons/',N.StatusIcon) as Icon,
CONCAT(DayDiff(N.UnManageFrom,GetDate()), ' days') as [Suppressed days],
AE.AccountID AS [Supressed By],
'Unmanaged' AS [Type]
FROM Orion.Nodes N
LEFT OUTER JOIN (
SELECT
AE.NetObjectID,
Max(AE.AuditEventID) as [AuditEventID],
Max(AE.TimeLoggedUtc) as [TimeLoggedUtc]
FROM Orion.AuditingEvents AE
where ActionTypeID in
(
SELECT ActionTypeID
FROM Orion.AuditingActionTypes
where
ActionType = 'Orion.AlertSuppressionAdded'
or ActionType = 'Orion.AlertSuppressionChanged'
)
group by AE.NetObjectID
) AS [LastMuteEvents] ON LastMuteEvents.NetObjectID=N.NodeID
LEFT OUTER JOIN Orion.AuditingEvents AE ON AE.AuditEventID=LastMuteEvents.AuditEventid
WHERE
N.UnManaged=1
UNION
(
SELECT
N.Caption AS [Node],
ToString(Supp.SuppressFrom) AS [Suppressed From],
ISNULL(ToString(Supp.SuppressUntil),' - Forever - ') AS [Suppressed TO],
CONCAT('/NetPerfMon/Images/Vendors/',N.Icon) AS [_ICONFor_Node],
N.DetailsUrl AS [_LinkFor_Node],
--CONCAT('/Orion/images/StatusIcons/',N.StatusIcon) as Icon,
CONCAT(DayDiff(Supp.SuppressFrom,GetDate()), ' days') as [Suppressed days],
AE.AccountID AS [Supressed By],
'Muted' AS [Type]
FROM Orion.Nodes N
INNER JOIN Orion.AlertSuppression Supp on Supp.EntityUri=N.Uri
LEFT OUTER JOIN (
SELECT
AE.NetObjectID,
Max(AE.AuditEventID) as [AuditEventID],
Max(AE.TimeLoggedUtc) as [TimeLoggedUtc]
FROM Orion.AuditingEvents AE
where ActionTypeID in
(
SELECT ActionTypeID
FROM Orion.AuditingActionTypes
where
ActionType = 'Orion.AlertSuppressionAdded'
or ActionType = 'Orion.AlertSuppressionChanged'
)
group by AE.NetObjectID
) AS [LastMuteEvents] ON LastMuteEvents.NetObjectID=N.NodeID
LEFT OUTER JOIN Orion.AuditingEvents AE ON AE.AuditEventID=LastMuteEvents.AuditEventid
)
-
Re: Report showing user who unmanaged elements and muted alerts
wilder Nov 26, 2019 1:57 PM (in response to Seashore)I like this look. How do you get the Node to have the URL link? The link is showing up as its own column.
-
Re: Report showing user who unmanaged elements and muted alerts
SeashoreNov 27, 2019 2:03 AM (in response to wilder)
As you can see in the query I have named to column for N.Caption to "Node" (N.caption AS [Node])
Then there is two special things you can do:
- [_ICONFor_xxx]
- [_LinkFor_xxx]
where xxx is the name of the column you want an icon and a link on. In above case the whole code for this is:
N.Caption AS [Node],
CONCAT('/NetPerfMon/Images/Vendors/',N.Icon) AS [_ICONFor_Node],
N.DetailsUrl AS [_LinkFor_Node],
-
-