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 (
FROM IPAM_IPHistoryReport a
max(IPAM_IPHistoryReport.Time) as tt
Group by ipnodeid
where a.FromValue='Available' and a.IntoValue='Used')
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?
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?
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 (...)?
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?
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.