19 Replies Latest reply on Nov 15, 2017 4:42 PM by njoylif

    SQL variable definition to return multiple rows

    Detroiter

      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}}

        • Re: SQL variable definition to return multiple rows
          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)

          1 of 1 people found this helpful
            • Re: SQL variable definition to return multiple rows
              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>

                • Re: SQL variable definition to return multiple rows
                  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

                    • Re: SQL variable definition to return multiple rows
                      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!

                        • Re: SQL variable definition to return multiple rows
                          scottcarlson

                          Detroiter,

                           

                          I know this is a super old thread, but I was having the same issue before I found this. I tested your custom SQL and it works great!

                           

                          My only question is: Is there a way to list ONLY the component with the problem instead of all of the components in a particular monitor? This works great in email format, but for SMS, it is too much info. If the broken component is at the bottom of the list of several components being monitored, it will not show up in an SMS (160 characters). Does that make sense?

                           

                          I am trying to find a happy medium for email and SMS.

                  • Re: SQL variable definition to return multiple rows
                    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?

                    1 of 1 people found this helpful
                      • Re: SQL variable definition to return multiple rows
                        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?

                        • Re: SQL variable definition to return multiple rows
                          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('')}

                            • Re: SQL variable definition to return multiple rows
                              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.

                                • Re: SQL variable definition to return multiple rows
                                  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.