4 Replies Latest reply on Feb 10, 2017 6:32 AM by lynchnigel

    How to use SQL/SWQL queries in Advanced Alerts?

    v-kansin

      I have a advanced alert in which we are firing off an external application. What I want to do is send some additional data to this external application by running a SQL (or SWQL...which ever works), how can I make this happen?

       

      Here is the query:

      SELECT Syslog.Hostname_UNICODE, Syslog.Message FROM Syslog

      where Syslog.MessageType LIKE '%AAA-4-LOGIN_FAILED%' and Syslog.DateTime >= DATEADD(Minute, -5, GetDate())

       

      Trigger Action Command String:

      E:\Tools\CreateTicket.exe ${SQL: SELECT Syslog.Hostname_UNICODE, Syslog.Message FROM Syslogwhere Syslog.MessageType LIKE '%AAA-4-LOGIN_FAILED%' and Syslog.DateTime >= DATEADD(Minute, -5, GetDate())}"

        • Re: How to use SQL/SWQL queries in Advanced Alerts?
          mesverrum

          You can see in the trigger action string what it is passing to your other tool.  Add any additional columns of data to the SQL: Select ....} part and it will dump them all in.  Should be pretty straightforward if you know how to find things in SQL.

          • Re: How to use SQL/SWQL queries in Advanced Alerts?
            mesverrum

            Ah I assumed that this tool was already working and you wanted to add additional columns of data to it. Going at it from that perspective my first thought is, when you try to execute that SQL query from the orion database manager tool or sql studio does it return the results you expect from the beginning?  If you arent finding matches then you just need to work in sql until your query is correct. I tested it in my lab and it looks like it should work but i dont have similar events to match it against. 

             

            You don't need to think about SWQL for this at all, this is a SQL select so ignore anything about SWQL. 

             

            Looking at the command line part there are a few things to keep an eye out for.  First of all, alert actions run under the local system account unless you specify otherwise, so are there any permissions things that may be interfering with launching the executable?  Does the executable have the capability to output anything to any logs once it is initiated so you can validate if it is even launching? 

             

            Your query is also potentially going to output an array of responses which would probably jam up the syntax of passing the data to the .exe, you might need to do something like a select top 1 and think through how you want to handle it when multiple alerts match the criteria during that 5 minute window. 

             

            Also, unless that is a typo it looks like there is a necessary space missing between Syslog and Where.

            1 of 1 people found this helpful
            • Re: How to use SQL/SWQL queries in Advanced Alerts?
              lynchnigel

              Hi there

              I've been looking at this not too long ago and found this article and code very useful

               

              SolarWinds NPM - Tutorial on how to use SNMP traps in alerts