IPAM - How to Alert when an I.P Address Changes Status

I was recently tasked by ​mark.d with creating an alert based on the status of an I.P address changing. This is how he likes to spend Friday afternoons!!!

Here's how it went.

Looking at the native Orion alerting there is no suitable object to work with .

IPAM Alerts 1-1.png

Looking at a Custom SWQL alert does not provide any additional options

IPAM Alerts 2.png

To create this alert we need to use a Custom SQL alert which allows us to query the IPAM_NodeReportView view

IPAM Alerts 3.png

IPAM 4.png

There are a couple of fields of interest in this view.

For example to alert on all I.Ps which have Changed from Available to Used  the 'FromValue' and IntoValue' fields have the values needed.

We could just use: WHERE FromValue ='Available' and IntoValue='Used' however this would alert on ANY I.P which had ever gone from Available to Used regardless of when this happened = a very noisy alert!

So we need to limit the scope of the alert to the most recent record for each I.P. to to that we join on the same table selecting the maximum time for each I.P

The end result:

WHERE ipnodeid in (

SELECT a.ipnodeid

FROM IPAM_IPHistoryReport a





max(IPAM_IPHistoryReport.Time) as tt

from IPAM_IPHistoryReport

Group by ipnodeid

) b

on a.IPNodeId=b.IPNodeId



where a.FromValue='Available' and a.IntoValue='Used')

IPAM 5.png