Is substring possible in ${SQL:}?

dkeyser

I am pretty new to using the SQL variable in the advanced alert manager. I know we can define our own custom SQL queries in the trigger actions like email. I have a large event message I'd like to get just the body of information from. Is this possible or is the ${SQL:} variable not capable of this?

RichardLetts

any valid sql server select statement that returns a single column and row works

Here is an example using substring and an xmlpath trick to concatenate rows into a single comma-delimited list

${SQL:SELECT A.peerip + ', ' AS [text()] FROM   (SELECT DISTINCT Substring(tv.oid, 22, 20) peerIP FROM   traps INNER JOIN trapvarbinds tv ON traps.trapid = tv.trapid WHERE  traps.traptype = 'BGP4-MIB:bgpTraps.0.2' AND Datediff(hour, traps.datetime, Sysdatetime()) <= 1 AND acknowledged = 0 AND tv.oid LIKE '1.3.6.1.2.1.15.3.1.2.%' AND tv.oidvalue = 'idle(1)' AND nodeid = ${NodeID}) A FOR xml path('')}