This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Parsing Variables for Email Alerts

Hi there!

I have an alert configured for an application monitor that will send an email every time a certain event occurs. For the email message I have the following variable specified ${WindowsEventMessages} this send an email with the entire event log details in it. Is there a way to parse out this info so that it only includes certain lines? Or better yet certain strings of text withing some lines? Any suggestions are appreciated.

  • So in case anyone else has this issue in the future I thought I'd come back with what I've found on the topic. There is a way to do it, but it's not very simple. Basically in the event message you can select to insert a variable. From there check the box for "Define SQL/SWQL Variable (Advanced)" then you can do a SQL query to get the desired results. There are 2 different formats for the SQL query that you can do.

    One is "${SQL: Select SUBSTRING(message, CHARINDEX('text1', message), 40) From APM_WindowsEvent_Detail where ComponentID in ('${N=SwisEntity;M=ComponentID}') order by TimeGeneratedUtc desc}"

    This basically selects the text beginning with text1 from the message column in the APM_WindowsEvent_Detail table of the Solarwinds database (Where all the event logs are stored) and then takes the next 40 characters. The "where" part specifies the Componenet ID that you are alerting for and links it to only use that component ID in the query. The part after "Order by" sorts it so the latest event is first. (This query only shows the first event in the table) in theory this should be the event that is alert for (because it just occurred). I wasn't able to filter the results out to just show the event at the time the alert occured becasue the database stores it in UTC format and there aren't any solarwinds variable to get the time of the event in the same UTC format as the database stores it in.

    The other query you can use is ${SQL: Select SUBSTRING(message, CHARINDEX('text1', message), CHARINDEX('text2', message) - CHARINDEX('text1', message) + LEN('text2')) From APM_WindowsEvent_Detail where ComponentID in ('${N=SwisEntity;M=ComponentID}') order by TimeGeneratedUtc desc}

    This does a very similar function as the last query but selects the text in between 2 sets of text (Rather than a certain number of characters) you can adjust the text1 (beginning text) and text2 (End text) according to your needs. The + just before "LEN" causes the ending text to be included in the message. If you change it to a - then the ending text will not be included.

    I would note that for both of these queries the text queries have to be unique or it will only find the first occurrence of the text. There is probably some adjustments you can make to have it start the query at a certain point but I'm not sure what that is. Hopefully this helps, if you need further customization there are quite a few good articles out there about SQL substring command and selecting various text. Personally I ran these queries directly on the database server originally till I got the desired result then put them into solarwinds (I just had to change the component ID variable to the actual number within SQL Server Management Studio)

  • Thanks so much timiller96 for putting this here! I was able to get what I needed from your queries above.

    My issue is, when I send this information in an email it is displayed correctly. However when I try to define this custom variable in the "Messaged Displayed" section of the alert (so that it shows up in Active Alerts) I receive the following error: MACRO SQL ERROR - ExecuteScalar requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.

    Did you experience this same issue when applying the queries to the alert message, rather than an email action?

  • Hello ss185547,

    i have the same problem. In my case i want to view trap-messages with a sql-query. the query works in the Email notification, but it fails with: MACRO SQL ERROR - ExecuteScalar requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.”

    if the same query is in the alert message

    Query: ${SQL:SELECT TOP 1 t.Message FROM Traps t WITH(NOLOCK) WHERE t.NodeID = '${N=SwisEntity;M=NodeID}' ORDER BY t.DateTime Desc}

  • Hi fum42083,

    To resolve this I just had to convert the query from SQL to SWQL. It then worked in the alert message and in email. I know you're looking for traps, but for anyone looking for Windows Event Logs parsing, this is the SWQL I used:

    ${N=SWQL;M=SELECT Message FROM Orion.APM.WindowsEvent where componentID = ${N=SwisEntity;M=ComponentID} order by timegeneratedutc desc}

  • Hi ss185547,

    converting the SQL query in the alert message to a SWQL query did the trick. Thanks!.

    But it think it is just a Workaround, because there might be cases were you need plain SQL in the email and the alert message.