cancel
Showing results for 
Search instead for 
Did you mean: 
m-milligan
Level 13

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

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  
mat12
Level 11

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

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.

alphabits
Level 11

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

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.

Highlighted
Seashore
Level 12

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

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

)

wilder
Level 9

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

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
Seashore
Level 12

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

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