cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 7

How to use SQL/SWQL queries in Advanced Alerts?

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())}"

Labels (2)
0 Kudos
4 Replies
Level 12

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

0 Kudos

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.

- Marc Netterfield, Github

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.

- Marc Netterfield, Github
0 Kudos

Well I have tried the above query and it did not return anything, I even cut a ticket to Solarwinds and they said it is possible but to ask here on how to convert SQL to SWQL....maybe thats what I am missing?

0 Kudos