SQL Variable in an Email

bsciencefiction.tv

I have created a trigger action to email me with some sql out put to try to troubleshoot an issue with our pollers stopping arbitrally at night.  wanting to track down a pattern, we have 3.

This is the trigger action

Engine Count

${SQL:Select EngineID, Count (EngineID) From Nodes Where Status not like '9'  and LastSync <= dateadd(minute, -5, getdate()) Group by EngineID}

Nodes Affected

${SQL:Select Caption, LastSync, Status,EngineID From Nodes Where Status not like '9' and LastSync <= dateadd(minute, -5, getdate()) Order By EngineID, LastSync}

This is the email out put whenI test

Engine Count
${SQL:Select EngineID, Count (EngineID) From Nodes Where Status not like '9' and LastSync <= dateadd(minute, -5, getdate()) Group by EngineID}
Nodes Affected
${SQL:Select Caption, LastSync, Status,EngineID From Nodes Where Status not like '9' and LastSync <= dateadd(minute, -5, getdate()) Order By EngineID, LastSync}

Can NPM not do complicated SQL query variables or am I missing something?

zzz

It can do complicated SQL SELECT statements- with some restrictions.

For one, it doesn't display tables. It'll automatically trunc it to the first entry. (so the first sql will only give the first engineID, while the second gives the node.caption)

Unless the table is empty. Then it displays the query, which is what you are seeing now.

I'd try to keep those sql statements simple because debugging/testing on the alert writer is cumbersome, but if you need to check, I'd use report writer to test the output as it should display the same things.