cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 12

Report showing user who unmanaged elements and muted alerts

Jump to solution

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.

1 Solution

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

View solution in original post

65 Replies
Level 12

Thanks all for the inspiration! Built some more on this code:

pastedImage_0.png

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

)

I like this look.  How do you get the Node to have the URL link?  The link is showing up as its own column.

0 Kudos

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],

0 Kudos
Level 11

Building 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

pastedImage_1.png

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.

Level 11

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!

0 Kudos

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

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.

0 Kudos

This 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  

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.

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)

Thanks for the help and information conman unfortunately, no results came up with the query provided.

0 Kudos

NICE!

0 Kudos
Level 7

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

0 Kudos

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

Great!

Level 12

Dear team,

I need one query to see the future scheduled unmange activity along with the present unmanage schedule activity

0 Kudos

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 
0 Kudos

Hi m-milligan

Am using your query but in that some utc and account ID is empty can you suggest for this.

pastedImage_1.png

0 Kudos

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.

0 Kudos

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.

0 Kudos