Generate report that will list the alert and the associated email address that is getting this alert.

I have been going over posts here and can kind of find what I am looking for. The higher ups are looking for reports of what is going on with Solarwinds. Is there anyway to create a report that will list the Alert and the email address the alert will go to? It seems like that should be simple but alas not really. Ideally if I could generate a report that was Node, Configured Alert, and Email address that would be great but I doubt that can be done. Thanks!

Find more posts tagged with

Accepted answers

All comments

mesverrum

Getting a list of who the alert is set to go to is not terribly difficult, try this one.

select

ac.name as [Alert Name]

, aa.categorytype

, a.actiontypeid

, ap.propertyname

, ap.propertyvalue

from orion.actionsassignments aa

join orion.alertconfigurations ac on aa.parentid=ac.alertid

join orion.actions a on a.actionid=aa.actionid

join orion.actionsproperties ap on ap.actionid=a.actionid

where propertyname like 'EmailTo' or propertyname like 'EmailCC' or propertyname like 'EmailBCC'

order by ac.name, aa.categorytype desc, ap.propertyname desc

Alerts aren't something that node's have a direct relationship with, the alerts are just queries against the db and anything that happens to match the conditions at the time the alert checks fires off. The db isn't structured in a way that makes it easy to predictively analyze the ways a node could fall into each alert condition so your last request is a bit hard to do, especially where alert conditions are complex

martian_monster

Thank you! I will try this. I have had to put on my report writing cap and figure out SWQL and SQL reporting. And I completely understand the alert to node direct relationship these requests are coming from above so trying to explain under the hood of Solarwinds and SQL can be kind of hard. Another question for the select statement where do you find the list of values - so the aa.categorytype, a.actiontypeid - is there a list someplace? thanks!

mesverrum

I'm on mobile right now so I can't grab the link for you, but Google solarwinds sdk github and download the sdk to your server and you will be able to browse the swql layout

martian_monster

I have had that installed and have run some queries through that for some reports - usually I will find a query and do changes until it does what I wanted it to do. I think I might start be understanding a little bit the SWQL. WIth the SWQL studio I am seeing where part of the select statements come from. I found the query below and it kind of gets me where I need to go. Question on the Select statement - so it says SELECT then lists ac.AlertID, ac.Name, ac.Description etc.. are those the table IDs that it is pulling data out of? I am assuming those IDs are in here somewhere. And I apologize if my terminology is off I have been working with SW for awhile but reporting/SQL/queries are relatively new to me. Thanks!

martian_monster

This report is definitely cleaner than the other ones I have attempted. Thank you!!!

brookslaw

FWIW - I have an EmailContacts custom field for my nodes and applications and use the value from that in my Alerts. I also maintain that field in alpha order for consistency. I just find it cumbersome to maintain or report on that info from within the alerts themselves. (And I missed replying to you at 5:55 by three minutes - it would have amused me given your avatar and user name.)

tolunarob

Old post but I have a question on it, maybe someone can chime in... I used the report and it works well but I wanted to add content from the "GET or POST request to Web Server" action, the URL specifically. Where is that stored? I can't find the table that has that, even opened a case and they can't seem to find it.

Thanks,
Rob

jrouviere

Looks like these are some of the fields:

Property Name	Property Value
EscalationLevel	0
executionIfAknowledge	TRUE
executionRepeatTimeSpan	0
HttpRequestBody
HttpRequestMethod	1
HttpRequestUrl	http://www.example.com

I was able to find this with a query:

SELECT TOP 1000 ActionID, ActionTypeID, Title, Description, Enabled, SortOrder, a.Properties.PropertyName, a.Properties.PropertyValue

FROM Orion.Actions a

where title like '%GET OR POST%'

GET OR POST was just the name of the test action I created.

tolunarob

Hey thanks, that didn't entirely work for me for whatever reason but, you put me on the right track. After trying a couple things I stumbled on the ActionTypeID named SendHttpRequest with that and the Title and Description gives me what I was looking for... now to figure out the joins and what not to get the report to look good.

Thanks again!