Hi all
UPD: Check comments please (validation error)
I want to make an alert with this condition:
IP status have been changed AND this IP is located in '%External%' subnet
(IP status is meant IPAM status - e.g. Used, Reserved, Transient, etc.)
We have IPAM structure like that:
- IP Networks
-- External
--- Location1 External
---- Subnet1 External
----- IP1: 22.250.111.13
---- Subnet2 External
----- IP2: 243.18.200.38
----- IP3: 243.18.200.43
--- Location2 External
---- Subnet3 External
----- IP4: 250.0.222.16
---- Subnet4 External
-- Internal
The only way is to use custom SQL or SWQL alert.
I found two ways how this event can be collected:
1: from the [dbo].[IPAM_Events] table:
SELECT TOP 10 * FROM [dbo].[IPAM_Events] WHERE Message like '%status%' ORDER BY EventTime DESC
Example result:
16965915 9/22/2022 8:03 AM 942 Collector The IP '22.250.111.13' fields have been changed: Status to 'Used' NULL NULL 0 False NULL
16965805 9/22/2022 6:49 AM 942 Collector The IP '243.18.200.38' fields have been changed: Status to 'Available' NULL NULL 0 False NULL
2: from the [dbo].[IPAM_IPHistoryReport] table:
SELECT top 100 * FROM [dbo].[IPAM_IPHistoryReport] WHERE HistoryType='Status' ORDER BY Time DESC
Example result:
13783725 172778 22.250.111.13 9/22/2022 8:03 AM Available Used Collector Status
13762789 172778 22.250.111.13 9/20/2022 0:16 PM Used Available Collector Status
But there is no mention of IP's parent subnet.
How to add an additional filter to include only IPs from '%External%' subnets?
Obviously it's possible to JOIN <something> and add a condition WHERE SubnetName like '%External%', but I'm not sure which tables I should join to gather parent subnet name.
