Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

How do I include SQL query results in an alert's email body?

As someone new to SolarWinds, I have set up Application Monitors that conduct periodic Oracle database checks for irregularities like duplicate entries. These monitors run queries typically structured as "select count(*) ...," flagging any non-zero counts as critical in the component status. If a monitor detects a critical status, an alert with the specific component name is triggered, and an email notification is sent to me. While this alerts me to the presence of an issue, it doesn't specify the actual problem. I want to enhance the email alerts by including the SQL query results—ideally indicating who caused the alert, such as "John Doe."

My research indicates that I should be able to include the results in the email using this format: "${SQL:SELECT [value] FROM [schema.table] WHERE [conditions]}", with specific values, schema.table, and conditions pertinent to my desired output. Despite this, when I preview it, the email body shows "MACRO SQL ERROR - Invalid object name [schema.table]." Can results from an external database be incorporated into the body of an alert email? And if so, what could be the error in my approach?

I appreciate your assistance!

Find more posts tagged with

Accepted answers

mesverrum

SQL components do allow a string message optionally, you just have to structure your query so the first row, first column is the statistic and the second column is the message. I do this pretty much any time i use a SQL monitor.

There is also a trick where you can use the SQL option FOR XML PATH to take a whole table of results and sql will make it show up as a single cell of data all jammed together. If you search for it there have been many posts in thwack about how to use that loophole to improve the alert messages.

To OP, if you are getting invalid object names that kind of points at a database permission issue. By default you would only be able to do SQL selects from the Orion database, but the way you are writing your question makes me wonder, are you trying to display data from something that is not the SW database?

All comments

weston.hood

If I understand correctly, you wouldn't necessarily need to use a custom SQL variable. You could just use the component statistic variable. ${N=SwisEntity;M=ComponentAlert.StatisticData}

Which should display the current script result.

schill27

Thank you for the response. What you provided does work in that it returns the application monitor's query result. In my test case, it returns a value of "1", which being > 0, triggers the alert. What I'm trying to accomplish is identifying the actual data that caused the count to exceed 0, e.g., what's the name of the person who is the "1". The application monitor's query only includes the numeric value to trigger the status change, not the actual data itself. What I hope to achieve is, if John Doe is who the 1 equates to, then include John Doe's name in the alert email's body. Thoughts? Thank you!

weston.hood

Yeah thats the limitation with sql query components is they require integer only return and there is no string return that is supported. Unless you went through the work of making a powershell monitor that executes 2 sql queries; one to grab the integer and one to grab the string data. Script monitors can optionally store string data unlike sql query components.

Alternatively if that database sits on the same SQL instance as your SolarWinds database, you could possibly write a custom sql variable that queries that database. You would have to give your solarwindsoriondatabaseuser access to query that database of course and specify the database name.

${SQL:SELECT [value] FROM [DatabaseName].[schema.table] WHERE [conditions]}

mesverrum

adam.beedell

Something like ${N=SwisEntity;M=ComponentAlert.ComponentMessage} as @weston.hood said is the way to go
If you had an issue with that one, then you could use SWQL, then if you cant use that one, then use SQL. SQL should be waaaay down the list as it may break during patching