SQL macro queries in Traps/Syslog and Alerts

I use this a lot.

To use a SQL query in the trigger action you just wrap your SQL query in

${SQL: }

eg

${SQL: SELECT Location from Nodes WHERE Caption = '${Caption}'}
You can only return 1 value a SQL query like this, so for each set of data you want to return, you have to write a new SQL statement like that.

eg

Local Contact 1: ${SQL:SELECT First_Name FROM Contacts inner join Nodes on Contacts.Company = Nodes.Coop WHERE Contacts.Company = '${Nodes.Coop}' and Contacts.Contact_Num = '1'} ${SQL:SELECT Last_Name FROM Contacts inner join Nodes on Contacts.Company = Nodes.Coop WHERE Contacts.Company = '${Nodes.Coop}' and Contacts.Contact_Num = '1'}

Daniel

Does this work in traps too or just for alerts?

I copied and pasted the simple sql line, tried it, nothing, then swapped a few variable around.  ${IP_Address} or ${Node.IP_Address}, in traps, also does not return a value, it just replies with ${IP_Address} or {$Node.IP_Address}

I do not think queries work within trap triggered actions. It looks like you can only pull variables from the info that gets stored in the Traps table. ie the Trap triggers don't directly access the Nodes table. I can't get the SQL function call to work... you might be able to get it worked out if there was a SQL function.

You could probably cause a trap to trigger an intermediate action that Orion can react to and build the alert message in that alert action. Say, have the trigger change a value in the database that makes Orion alert trigger.

What I've had to do is create a different trap rule for any event that requires specific information about a node. Luckily just including the Trap Message is sufficient.

Daniel