1 Reply Latest reply on Mar 6, 2018 2:43 PM by RichardLetts

    Traps to Alert Conversion using custom SQL queries

    karthik.act

      Hi All,

       

      I got an requirement to create an alerts based on received traps.

      Configured alerts based on the previous posts available in Thwack but the challenge i facing here is embedding a trap message(Trap varbinds) in mail/message body.

       

      The trigger condition is like in last 1 min if i receive a specified trap type the alert will be triggered,

      but here i'm getting multiple Traps of same kind for a same Node in last 1 min.

      can someone help me in getting Traps varbinds in the message body for a particular trap in the above situation.

       

      RichardLetts : Hope you have some ideas to share

        • Re: Traps to Alert Conversion using custom SQL queries
          RichardLetts

          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:

          <TABLE>

          <TR><TD>Feb 23 2018 11:26AM</TD<TD>UPS: Self-Test passed.</TD></TR>
          <TR><TD>Feb 21 2018 11:53AM</TD<TD>Detected an unauthorized user attempting to access the Web interface from 10.155.0.28.</TD></TR>
          <TR><TD>Feb 21 2018 11:53AM</TD<TD>Detected an unauthorized user attempting to access the Web interface from 10.155.0.28.</TD></TR>
          <TR><TD>Feb 21 2018 11:50AM</TD<TD>Detected an unauthorized user attempting to access the Web interface from 10.155.0.28.</TD></TR>
          <TR><TD>Feb 21 2018 11:50AM</TD<TD>Detected an unauthorized user attempting to access the Web interface from 10.155.0.28.</TD></TR>

          </TABLE>