54 Replies Latest reply on Nov 2, 2018 4:43 AM by rajasekar

    Report showing user who unmanaged elements and muted alerts

    patriot

      I have found a few threads on a similar subject, but nothing that is exactly what I want. I need a report that shows the following in a single query, or in 2 separate queries if necessary:

       

      1. Nodes CURRENTLY Unmanaged showing when they were unmanaged, when they will be re-managed if available, and WHO did it.

       

      2. List nodes that currently have their alerts muted, also along with who muted the alerts and when.

       

      In each case, I need to be able to filter the report by using a particular node custom property.

       

      Will this be a custom SWQL or SQL report? I cannot see how to include all the needed fields using Manage Reports.

       

      Thanks for any help.

        • Re: Report showing user who unmanaged elements and muted alerts
          m-milligan

          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

              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

                  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

                      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

                          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

                              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

                                  This includes the node name (N.Caption):

                                   

                                  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

                                      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

                                          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

                                              I'm getting data in the query resource now, but any idea why the last three columns are empty?

                                               

                                                • Re: Report showing user who unmanaged elements and muted alerts
                                                  m-milligan

                                                  "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

                                                      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

                                                          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

                                                              Where is the SWQL Studio? On the Start menu on the Primary polling server?

                                                              • Re: Report showing user who unmanaged elements and muted alerts
                                                                patriot

                                                                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

                                                                    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
                                                                          m-milligan

                                                                          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
                                                                              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.

                                                                               

                                                                              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

                                                                              1 of 1 people found this helpful
                                                                                • Re: Report showing user who unmanaged elements and muted alerts
                                                                                  m-milligan

                                                                                  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

                                                              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

                                                      OK, 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  
                                                      
                                                      1 of 1 people found this helpful
                                                  • Re: Report showing user who unmanaged elements and muted alerts
                                                    rajasekar

                                                    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
                                                      paul326

                                                      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-022018-08-22 16:52:46.58NULL2018-08-22 16:52:46.987
                                                      NAP-VLNX-FAZ-012018-08-12 01:28:20.327NULL2018-08-12 01:28:20.78
                                                      pla-esx6-vmw-02.eby-brown.com2018-06-21 16:25:09.177NULL2018-06-21 16:25:09.55
                                                      ROC-FS-C3560CX-SW12018-07-15 00:52:08.097NULL2018-07-15 00:52:08.55
                                                      TST-V2K3-VPK-012018-09-10 20:54:46.093NULL2018-09-10 20:54:46.39
                                                      TST-V2K3-VPK-022018-04-07 21:59:17.117NULL2018-04-07 21:59:17.553
                                                      TST-V2K3-VPK-032018-04-07 21:59:17.553NULL2018-04-07 21:59:18.007
                                                      TST-V2K3-VPK-042018-04-07 21:59:18.007NULL2018-04-07 21:59:18.443

                                                       

                                                      want to exclude anything with 'TST' in the name.

                                                       

                                                      Thanks,

                                                      Paul