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
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
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 150,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process. Learn more today by joining now.