SMTP server associated with Action

Anyone have knowledge on how to get the SMTP servers that are associated with Actions? We have 900+ email actions and I was curious if anyone was able to get this to show correctly? If I added a INNER JOIN it balloons up to 3-4 times the amount as SolarWinds appears to match against multiple SMTP servers for some reason. The SMTPServers table doesn't appear to have anything that relates to the Actions table. I've dug into many different tables to somehow bridge the gap but nothing that I do appear to work.

SELECT

[Actions].ActionTypeID AS ActionType,
[Actions].Title AS ActionName,
[Actions].Description AS Email,
[Actions].Assignments.CategoryType AS Action

FROM Orion.Actions AS [Actions]

WHERE [Actions].ActionTypeID = 'Email'

Find more posts tagged with

actions

smtp

sql

SWQL

Alerting

Accepted answers

All comments

maciej.sandecki

Try this:

SELECT

[Actions].ActionTypeID AS ActionType,
[Actions].Title AS ActionName,
[Actions].Description AS Email,
[Actions].Assignments.CategoryType AS Action,
[Actions].Properties.PropertyValue

FROM Orion.Actions AS [Actions]

WHERE [Actions].ActionTypeID = 'Email' AND [Actions].Properties.PropertyName = 'SmtpServerID'

If the PropertyValue is empty (not NULL), then the Default SMTP server is being used for action, otherwise you will have SMTP Server ID in this field. The SMTP servers are in Orion.SMTPServers.

smalleggo

For some reason this truncates the results in either a widget or report. I'll need to look into the PropertyValue as it looks like the location of what I am trying to collect.

maciej.sandecki

Could you share the sample output you are getting and tell what is missing? Does it truncate rows, columns or values displayed?

smalleggo

Looks to be a fault in our system that day as it now provides the roughly 920 actions as I expected. Our system says it has 924 actions related to Email however your script provides 918 while my script shows 930. I believe using both I have validated we 'should' be good.