Traps to Alert Conversion using custom SQL queries

I'm going to make some assumptions here:

a) you have the basic format of the email message you want to send out, and you are trying to figure out how to put a blob of text with the SNMP traps into the body of it.

[I'm going to skip explaining this bit, because that should be trivially easy. If you can't even get an email out then that is a different problem than i'm going to address]

b) Your have some query that returns all of the rows relating to the alert that you want to put in the message

e,g something like the last 5 mac move syslog messages:

select top 5 message from syslog where nodeid=${NODEID}
and MessageType='MAC_LIMIT_ALERT'
order by datetime desc

or the last 5 Traps from an UPS:

SELECT top 5 OIDVALUE FROM [dbo].[Traps] T
inner join trapvarbinds TVB on  T.TrapID=TVB.trapid
where nodeid=${NODEID} and OID='1.3.6.1.4.1.318.2.3.3.0' and datetime<dateadd(hour,-1,getdate())
order by datetime desc

[you can test these in the database manager]

I don't know what traps you have here, so i can't relaly help pick what values are useful to you.

c) you're having difficulty gluing these together.

in this case you need to use a custom SQL variable in the email body.

the caveat here is that it can ONLY return a single value ... which is a problem if you have multple rows from your query..

in this case you have to wrap the above query in some SQLserver magic...

SELECT stuff( (SELECT top 5 OIDVALUE+char(10) FROM [dbo].[Traps] T
inner join trapvarbinds TVB on  T.TrapID=TVB.trapid
where nodeid=${NODEID} and OID='1.3.6.1.4.1.318.2.3.3.0' and datetime<dateadd(hour,-1,getdate())
order by datetime desc
               FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
            ,1,0,'')
       AS string

which produces something like:

UPS: Self-Test passed.
Detected an unauthorized user attempting to access the Web interface from 10.155.0.28.
Detected an unauthorized user attempting to access the Web interface from 10.155.0.28.
Detected an unauthorized user attempting to access the Web interface from 10.155.0.28.
Detected an unauthorized user attempting to access the Web interface from 10.155.0.28.

the char(10) puts a new line in there

[again, you can test this in the database manager to make sure you only get one cell returned)

and... finally.. you can't have any newlines in your SQL... it it looks like this:

${SQL: SELECT  stuff( (SELECT top 5 OIDVALUE+CHAR(10) FROM [dbo].[Traps] T inner join trapvarbinds TVB on  T.TrapID=TVB.trapid where nodeid=${N=Alerting;M=AlertObjectID} and OID='1.3.6.1.4.1.318.2.3.3.0' and datetime<dateadd(hour,-1,getdate()) order by datetime desc FOR XML PATH(''), TYPE).value('.', 'varchar(max)')  ,1,0,'') AS string }}

If you're sending the email as a HTML, then you're also able to wrap it nicely in a table like this:

<TABLE>

${SQL: SELECT  stuff( (SELECT top 5 '<TR><TD>'+CAST(DATETIME as varchar)+'</TD<TD>'+OIDVALUE+'</TD></TR>'+CHAR(10) FROM [dbo].[Traps] T inner join trapvarbinds TVB on  T.TrapID=TVB.trapid where nodeid=${N=Alerting;M=AlertObjectID} and OID='1.3.6.1.4.1.318.2.3.3.0' and datetime<dateadd(hour,-1,getdate()) order by datetime desc FOR XML PATH(''), TYPE).value('.', 'varchar(max)')  ,1,0,'') AS string }}

</TABLE>

and your email will contain: