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

Report that shows nodes with assigned alerts

Jump to solution

I am trying to create a report that shows a node with assigned alerts... Anybody have any Ideas? Thanks.

0 Kudos
1 Solution

Here is a script to get you going ...

 

SELECT
 CASE 
  WHEN ao.RelatedNodeId IS NULL 
  THEN 'STANDALONE ALERT'
  ELSE ao.RelatedNodeCaption
 END AS 'RelatedNodeCaption',
 n.Environment, --this is custom property
 ad.AlertName
FROM AlertStatusView asv
JOIN AlertDefinitionsView ad ON ad.AlertDefID = asv.AlertDefID
JOIN AlertObjects ao ON ao.AlertObjectID = asv.AlertObjectID
LEFT JOIN Nodes n ON n.NodeID = ao.RelatedNodeId
ORDER BY ao.RelatedNodeCaption

 

Couple things to note:

  1. Notice CASE in select statement - it will return "STANDALONE ALERT" if alert does not have associated node. Not all alerts are associated with nodes, such as for example WPM alerts, Datastore alerts, etc.
  2. I have Environment custom property and I have added it into output as an example. You can add more of those custom properties to output
  3. When you use this script in the reporting - select GROUP BY RelatedNodeCaption. This way your report will have a nice nested output with nodes and associated alerts. 
  4. Use Custom Table and Advance DB method to extract data. Select SQL and paste code above

AlexSoul_0-1592389004371.png

 

AlexSoul_1-1592389045789.png

This is how it looks (here I have grouped by Environment and then by Caption)

AlexSoul_2-1592389854261.png

 

 

 

View solution in original post

8 Replies

just one particular node or all of them?

All of them, apparently a detailed memo is not good enough for certain parties. Let me stop complaining. I have reports that show all nodes in my environment. They are divided by custom properties... the main being Server_node and Network_node. From there I have listed all their other custom properties respectively. I know need to see if in those reports I can list what alerts are assigned to what node.

0 Kudos

ok, I just had a quick check and it may not be possible with report builder. You may need to use SQL/SWQL query instead. Are you comfortable with script? I can give you a SQL script example that will do the job, but you would need to expand it further to include all the rest stuff and custom properties that you need

0 Kudos

Lets just say I am not uncomfortable with it. I will gladly take whatever you can offer. I will try to figure it out. THANKS!!!

0 Kudos

Here is a script to get you going ...

 

SELECT
 CASE 
  WHEN ao.RelatedNodeId IS NULL 
  THEN 'STANDALONE ALERT'
  ELSE ao.RelatedNodeCaption
 END AS 'RelatedNodeCaption',
 n.Environment, --this is custom property
 ad.AlertName
FROM AlertStatusView asv
JOIN AlertDefinitionsView ad ON ad.AlertDefID = asv.AlertDefID
JOIN AlertObjects ao ON ao.AlertObjectID = asv.AlertObjectID
LEFT JOIN Nodes n ON n.NodeID = ao.RelatedNodeId
ORDER BY ao.RelatedNodeCaption

 

Couple things to note:

  1. Notice CASE in select statement - it will return "STANDALONE ALERT" if alert does not have associated node. Not all alerts are associated with nodes, such as for example WPM alerts, Datastore alerts, etc.
  2. I have Environment custom property and I have added it into output as an example. You can add more of those custom properties to output
  3. When you use this script in the reporting - select GROUP BY RelatedNodeCaption. This way your report will have a nice nested output with nodes and associated alerts. 
  4. Use Custom Table and Advance DB method to extract data. Select SQL and paste code above

AlexSoul_0-1592389004371.png

 

AlexSoul_1-1592389045789.png

This is how it looks (here I have grouped by Environment and then by Caption)

AlexSoul_2-1592389854261.png

 

 

 

View solution in original post

Thanks!!! The report is just what I was looking for. I was able to add some custom properties in order to sort it better. I have one question would there be a way to show ALL alerts and their assigned nodes? Something like the "alerts this object can trigger" pane in the Node Summary view? Thanks.

0 Kudos
Quick answer - no, it is not possible to correctly identify which alerts can trigger for a particular node. SolarWinds has something like this out of the box as a widget, but I recall it was never accurate, especially with triggers based on queries. I am not blaming them here, from what I can see - it is physically simply impossible to produce something like this, but they had a good try on it.

Thanks so much!!! I will try it today... when they let me breath.

0 Kudos