SQL variable definition to return multiple rows

I have been struggling to find a way to alert on Application status, but provide Component status in the alert e-mail. I know that there are several macros that give me some of this information (ComponentsWithProblems, etc), but that is not enough. I want the error message associated with each component.

I found the field I need in dbo.APM_AlertsAndReportsData.StatusOrErorDescription. I attempted to design my own query that would pull this data based on the ApplicationID (which is unique to each assigned application monitor). However, my testing only returns the first value. I feel I am close, but just need some help with the SQL.

${SQL:SELECT [StatusOrErrorDescription] FROM [EG-ORION].[dbo].[APM_AlertsAndReportsData] where ApplicationId = ${ID}}

Find more posts tagged with

advanced_alert

sql_variable

sql_query

Accepted answers

Detroiter

So I think I am as close as I can get. The final statement below is the one I am going with. The only issue is that I HAVE to send the emails out as "plain text" rather than the preferred HTML. This is the only way I could get the proper carriage returns. This is done with the "CHAR(10) + CHAR(13)" combination. The "<br>" HTML tag cannot be used within the XML statement even with the ![<CDATA>] breakout command.

${SQL:SELECT cast(cast([ComponentName]as varchar(max)) + '' + '--' as XML), cast(cast([StatusOrErrorDescription]as varchar(max)) + '' + CHAR(10) + CHAR(13) as XML) FROM [EG-ORION].[dbo].[APM_AlertsAndReportsData] where ApplicationId = ${ID} FOR XML PATH('')}

Thanks to all for the help! And let me know if there is ever a way to get this back into HTML, that'd be great!

All comments

netlogix

Try: ${SQL:SELECT [StatusOrErrorDescription] FROM [EG-ORION].[dbo].[APM_AlertsAndReportsData] where ApplicationId = ${ID} FOR XML PATH('')} (*updated to remove extra command that was not needed)

Detroiter

This is veeeery close. I can see that all the correct messages are being returned but there appears to be a lot of extra formatting data in the returned value. I have included the exact result below. If there is a way to remove the "<StatusOrErrorDescription>" entries, this would be perfect.

<StatusOrErrorDescription>Component is up. "Server" service status is - Running.</StatusOrErrorDescription><StatusOrErrorDescription>Network connection failed. Cannot connect to JMX server</StatusOrErrorDescription><StatusOrErrorDescription>Network connection failed. Cannot connect to JMX server</StatusOrErrorDescription><StatusOrErrorDescription>Network connection failed. Cannot connect to JMX server</StatusOrErrorDescription><StatusOrErrorDescription>Network connection failed. Cannot connect to JMX server</StatusOrErrorDescription>

Detroiter

Gotta love the old Google machine. Found a working way to strip the XML headers, it and the results are below. Now if only I could get them to display on individual lines...or am I getting greedy?

${SQL:SELECT cast(cast([StatusOrErrorDescription]as nvarchar(max)) + '' as XML) FROM [EG-ORION].[dbo].[APM_AlertsAndReportsData] where ApplicationId = ${ID} FOR XML PATH('')}

Component is up. "Server" service status is - Running.Network connection failed. Cannot connect to JMX serverNetwork connection failed. Cannot connect to JMX serverNetwork connection failed. Cannot connect to JMX serverNetwork connection failed. Cannot connect to JMX server

Detroiter

Thanks to all for the help! And let me know if there is ever a way to get this back into HTML, that'd be great!

LDave

Hi all, I'll take this post because I need to obtain something similar using an alert sql variable, in particular I'm trying to obtain the crlf to work.

I've got many 'optional' custom properties assigned to node, but I work trying to standardize alerts the most, so that an alert definition will be the same for a server or a network device, if the criticality of the system is the same.

That said, I would like to have the custom fields appearing inside the mail only when they are populated.

I've created a sql variable like this one:

${SQL:select case when EscalationLevel1Phone IS NULL then '' Else 'Escalation 1 phone: ' + EscalationLevel1Phone + Char(13) + Char(10) end from nodes where NodeID = '${Node.NodeID}'}

In this way I could have concatenate multiple variables and have only the populated ones, with their crlf.

Unfortunately I can't get the char(13)+char(10) combination to work, apparently the two characters aren't inserted inside the mail at all.

Any Idea?

johnryder_cocc

Sorry to Necro this, but I set up an Application monitor with 100 HTTPS/HTTP monitors and used this SQL Macro;

${SQL:SELECT cast(cast([ComponentName]as varchar(max)) + '' + ' ' + '-' + ' ' as XML), cast(cast([StatusOrErrorDescription]as varchar(max)) + '' + CHAR(10) + CHAR(13) as XML) FROM [SolarWindsOrion].[dbo].[APM_AlertsAndReportsData] where ApplicationId = ${N=SwisEntity;M=ApplicationID} AND APM_AlertsAndReportsData.ComponentStatus != 'up' FOR XML PATH('')}

It works great, except the alert email appears to have a limit of 26 down sites. Is there a character limit to alert emails in Orion, or am I hitting some other limit?

jdwinns

I'm having a similar issue using the query below in an alert e-mail. The output comes out as one continuous string, as if the line breaks are never inserted. Does anyone know how to resolve this?

${SQL:SELECT cast(cast(ComponentName as varchar(max)) + '' + '--' as XML),

cast(cast(MultiValueMessages as varchar(max)) + '' + CHAR(10) + CHAR(13) as XML)

FROM dbo.APM_AlertsAndReportsData

WHERE ApplicationId = '153' FOR XML PATH('')}

njoylif

search thwack for "for xml path". I have some posts that show you how to integrate output into html table.

it can be hair pulling fun though...fair warning.

jdwinns

Cool! Will this still work even though the alert isn't necessarily a trap/syslog?

I'm using the Log Parser (Powershell) script to parse one of our custom logs. It seems to store the data in MultiValueMessages in the dbo.APM_AlertsAndReportsData table.

Usually the output is 5-10 strings from the log but they come out as one long string. I can't seem to get it to break.

Example:

Total number of lines that match search criteria: 4. Lines that have search string: 2017-10-27 14:28:14,583 ERROR [ajp-0.0.0.0] This is an example of the data in my string. 2013 10-28 13:00:41,120 ERROR [ajp-0.0.0.0] This is another example of the data in my string. 2013-10-28 13:37:09,007 ERROR [ajp-0.0.0.0] This is yet another example of the data in my string. 2013-10-28 13:37:09,007 ERROR [ajp-0.0.0.0] Final example of my string.

I would like it to output like this if possible:

Total number of lines that match search criteria: 4. Lines that have search string:

2017-10-27 14:28:14,583 ERROR [ajp-0.0.0.0] This is an example of the data in my string.

2013 10-28 13:00:41,120 ERROR [ajp-0.0.0.0] This is another example of the data in my string.

2013-10-28 13:37:09,007 ERROR [ajp-0.0.0.0] This is yet another example of the data in my string.

2013-10-28 13:37:09,007 ERROR [ajp-0.0.0.0] Final example of my string.

EDIT: I'm surprised I have any hair left after the amount of time I've spent on this already.