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

Version 1

    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')