I want to be alerted when a new Mac address (device) is connected to a specific VLAN. I've started the process by creating a baseline lookup table of the attached Mac addresses on the VLAN; I did a min(_time) over a 90-day period. Now I want to run a reoccurring search that will look at the MAC addresses within the past 15 minutes and compare those Mac addresses against the lookup table to determine if the Mac address is new.
I ran this search to obtain the baseline lookup table:
index=cisco-meraki sourcetype="cisco:meraki" src=192.168.12.* | stats min(time) as firsttime by srcmac,devicename | outputlookup mac_tracker.csv
**I ran this search as a suggestion from another community question. The search writes over my 'firsttime' field with blank data. I believe the query is supposed to append and new Macs to the existing lookup table. Theoretically, I could then run a query that looks at the 'firsttime' field to determine which Mac address has been added most recently.
index=cisco-meraki sourcetype="cisco:meraki" src=192.168.12.* | stats min(time) as firsttime by srcmac,devicename | inputlookup append=true mactracker.csv | stats min(time) as firsttime by srcmac,devicename | outputlookup mactracker.csv
Since you're already updating the mac_tracker.csv frequently (assuming every 15 mins), then you can setup your alert based on the lookup data only. Assuming your lookup update search runs with cron
2,17,32,47 * * * *, Run the alert search (below) after your update the lookup say at cron
7,22,37,52 * * * *.
| inputlooku mac_tracker.csv | where first_time>=relative_time(now(),"-20m@m")