cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
tony.johnson Product Manager
Product Manager

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

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

IPAM 5.png

5 Replies

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

This is exactly what I was looking for! I can't believe something like this isn't built in.

Is there a way to limit this to certain subnets?

0 Kudos
Reply
tony.johnson Product Manager
Product Manager

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

Hi,

Nice to hear this was of use to you!

As the address is stored as string we can'y easily limit this for a certain subnet such as 10.160.1.0/24 although it would be possible to use a sql wildcard card at the end of the query

and IPAddress like '10.160.1%'

This would also match 10.160.100, 101, 102 and so on

There may be another approach to parse the string into its I.P Octets and use this for the additional WHERE clause.

How many subsets do you want to limit to?

0 Kudos
Reply

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

Six subnets.

0 Kudos
Reply

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

I was thinking the wildcard route was what I was going to do, but wanted your expert opinion. I guess I would need and IPAddress IN (...)?

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

Hi,

I created the alert based on your sql, and when an IP changed the status to used from available, the alert has been triggered, but It doesn't show the IP address, the object name is empty. how can I know what IP address has been changed status?

ScreenHunter 33.png

0 Kudos
Reply