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 .
Looking at a Custom SWQL alert does not provide any additional options
To create this alert we need to use a Custom SQL alert which allows us to query the IPAM_NodeReportView view
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
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=b.tt
where a.FromValue='Available' and a.IntoValue='Used')