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

SQL trigger actions

I've created a Custom SQL Alert trigger condition on IPAM nodes which looks like this:

select IPAM_NodeReportView.IPAddress, IPAM_NodeReportView.IPNodeId from IPAM_NodeReportView WHERE ipnodeid in (SELECT a.ipnodeid FROM IPAM_IPHistoryReport a INNER JOIN (SELECT ipnodeid, max(IPAM_IPHistoryReport.Time) as tt from IPAM_IPHistoryReport Group by ipnodeid) b on a.IPNodeId=b.IPNodeId and a.Time > dateadd(minute, -100, GetDate()) where (a.FromValue='Available' and a.IntoValue='Used') or (a.FromValue='Available' and a.IntoValue='Reserved'))

The SQL works, the alert is triggered and it will detect a status change of an IP in IPAM.

Eventually, I would like to run a powershell program that will use the IPs from the above query. I've started with sending myself an email with the exact query in the message.

But the mail I get is:

MACRO SQL ERROR - String cannot be of zero length.

Parameter name: oldValue

0 Kudos
3 Replies
nicodm
Level 7

Re: SQL trigger actions

Hi, did you find the solution for this one?, I have the same problem.

Thanks!.

0 Kudos
mesverrum
Level 20

Re: SQL trigger actions

For any case where you are trying to include sql in the alert message it does not support displaying a table of values, you have to set up the SQL in such a way as to get the entire output in a single cell.

A workaround for cases like this where you have an array you want to display is to write your query in SQL and leverage the FOR XML PATH function that will convert a whole block of data into a single long string

An example of how people use it is discussed in this thread, but there are many others on thwack that also mention that function.SQL variable definition to return multiple rows

- Marc Netterfield, Github
0 Kudos
nicodm
Level 7

Re: SQL trigger actions

Yes, I did that and the query is correct but in the email I'm getting the error:

MACRO SQL ERROR - String cannot be of zero length.

Parameter name: oldValue

0 Kudos