I have a dataset with a lot of mac address captured. I would like to excluded all mac address that arrived between 0h until 6h. But these mac can still appear after 6h. For example:
DATE TIME MAC
01/01/2015 01:00:00 00:00:00:00:00:01
01/01/2015 03:00:00 00:00:00:00:00:01
01/01/2015 09:00:00 00:00:00:00:00:01
01/01/2015 10:00:00 00:00:00:00:00:02
As you can see above, just 00:00:00:00:02 mac wil should remain in dataset, because the other arrive between 0 until 6h.
How can I do the search?
This isn't very pretty, but should work:
base search | eventstats min(date_hour) as min_date_hour by mac | where min_date_hour >= 6
This will look for the first occurrence of each mac, and only keep macs that didn't occur before 6am on any given day in the time range.
Do you search data for a single day OR multiple days?
Hello. Multiple days.
If I understand the question, you want to ignore events with MAC addresses that occurred between 0000-0600. You could probably do this with a subsearch that finds MAC addresses that had events during the time period 0-6h and then exclude those from your main search.
eventtype=mac_activity | where NOT [eventtype=mac_activity (date_hour>0 AND date_hour<6) | dedup mac | fields mac]
This isn't very pretty, but should work:
base search | eventstats min(date_hour) as min_date_hour by mac | where min_date_hour >= 6
This will look for the first occurrence of each mac, and only keep macs that didn't occur before 6am on any given day in the time range.
Martin, thanks for your help. It worked.
If this answered your question, could you mark this as Answered so people searching for it afterwards will know it was yet another wonderfully correct answer by the awesome martin_mueller?