How to create an alert for rogue devices via Mac address?

[nicholas_bergma]

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 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 query to obtain the baseline lookup table:

index=cisco-meraki sourcetype="cisco:meraki" src=192.168.12.* | stats min(_time) as first_time by src_mac,device_name | outputlookup mac_tracker.csv

**I ran this query as a suggestion from another community question. The query writes over my 'first_time' 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 'first_time' 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 first_time by src_mac,device_name | inputlookup append=true mac_tracker.csv | stats min(_time) as first_time by src_mac,device_name | outputlookup mac_tracker.csv

[somesoni2]

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

[DalJeanis]

Your second min(_time) in the update search needs to be min(first_time) or the whole first_time field will be null.

Also, set append=true on the outputcsv command.