Log who last modified/deleted/added subnets or IP addresses

"Last 25 IPAM events" resource is also showing all IPAM related events.But the use case here is events related to user.

List of Event Types

=================

EventType    Name

915    IPAM IPv6 Site Added

916    IPAM IPv6 Site Removed

917    IPAM IPv6 Site Changed

920    IPAM Subnet Added

921    IPAM Subnet Removed

922    IPAM Subnet Changed

940    IPAM IP Node Added

941    IPAM IP Node Removed

942    IPAM IP Node Changed

960    IPAM Subnet Scan Information

961    IPAM Subnet Scan Failure

962    IPAM Subnet Scan Canceled

963    IPAM Subnet Scan MacMismatch

964    IPAM Subnet Scan Timeout

995    IPAM IPv6 Global Prefix Added

996    IPAM IPv6 Global Prefix Removed

997    IPAM IPv6 Global Prefix Changed

If you need more events numbers then paste below query in SQL server management studio

SELECT *  FROM [dbo].[EventTypes] where  name like '%IPAM %'

Above list shows the list of events tracked by IPAM.Event types and their event description.Choose the right events for you.

You may create the new custom query resource which will show the list of IPAM events by following the below procedure

1.Go to 'IPAM summary page'

2.Cllick 'customize page' link.

3. Click "+" icon in either column1 or column 2

4. Search for 'Custom Query' in search bar

5. Select 'custom query' resource.

6.click 'Add selected resources'

7. Click done.Page will be redirected to 'IPAM summary page' which will have new resource with name 'Custom query'

8.Click Edit button

9.Paste the below query

SELECT  EventTime, UserName, Message

FROM IPAM.Event where userName <>'SYSTEM' and eventtype in (920,921,922,960,961,962,963,964)

10. You may change the eventtype numbers based on above provided list

11.Click submit

12.You will have new resource with your requirements

Note:Don't remove username <> 'SYSTEM' in where condition you will end up with flooding of events

Thank you very much!!

I added the custom query like you described, but there is nothing displayed. After some time of loading the following error message is displayed:

So we adapted the query as following in order to make it work

SELECT EventTime, UserName, Message

FROM IPAM.Event where userName <>'SYSTEM' and eventtype in (920)

ORDER BY EventTime DESC;

Maybe the amount of events was too big and so the query failed, so we reduced it to only one type of events and created three different ones for each added, changed and removed subnets.

However, i cannot search for a specific subnet in this custom query when I want to find out when and from who it was deleted or who was the last person to modify some ip addresses. I mean I can do it if i somehow export it (which doesn't work half of the time) and then search through it with CRTL+F.

But isn't the Message Center there for exact this purpose?

The problem is, I don't get any results when i choose IPAM Subnet Added for the last 12 Month. According to the query there are over 4000 "IPAM SUBNET Added" events, so why aren't they displayed? This would literally solve everything because the Message Center is capable of filtering and searching as well

1.Click edit button in custom query resource.

2.Enable search option on it

3.Paste the below query on it

SELECT EventTime, UserName, Message

FROM IPAM.Event

where

userName <>'SYSTEM'

and eventtype in (920)

and (UserName LIKE '%13%' or Message LIKE '%13%' or

EventTime LIKE '%13%')

4. Click submit.

5.Now the resource is search capable.You may search the columns "EventTime, UserName, Message" . Here IPAM will search the text which contains your search keyword.

This will be userful to search who has modified which subnet from huge list.

If you face problems apart from this please keep posting in this section.

Thanks a lot!! You are a life safer

These are the queries I'm using now:

SELECT EventTime, UserName, Message

FROM IPAM.Event where userName <>'SYSTEM' and eventtype in (920)

order by EventTime desc

SELECT EventTime, UserName, Message

FROM IPAM.Event

where

userName <>'SYSTEM'

and eventtype in (920)

and (UserName LIKE '%${SEARCH_STRING}%' or Message LIKE '%${SEARCH_STRING}%' or

EventTime LIKE '%${SEARCH_STRING}%')

I created separate custom queries for the different events in order to make it work because I always got the error message that the query failed. I also still get this error for changed IP Addresses

I assume the reason for this error is the high amount of Event Messages. Do you have an idea what to do about this problem?

Try to add Top100 in your query.If records are huge then Top 100 records only filtered out

SELECT Top 100 EventTime, UserName, Message

FROM IPAM.Event where userName <>'SYSTEM' and eventtype in (920)

order by EventTime desc

SELECT Top 100 EventTime, UserName, Message

FROM IPAM.Event

where

userName <>'SYSTEM'

and eventtype in (920)

and (UserName LIKE '%${SEARCH_STRING}%' or Message LIKE '%${SEARCH_STRING}%' or

EventTime LIKE '%${SEARCH_STRING}%')

Thank you very much!!

I figured I would try something like that which would deliver only the changes of the last 7 days, but I don't know how to write it correctly:

SELECT EventTime, UserName, Message

FROM IPAM.Event where userName <>'SYSTEM' and eventtype in (920)

and eventtime > *today()-7 days*

order by EventTime desc