How can I exclude a group of the mac address found at specific time?

[jpjconti]

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?

[somesoni2]

Do you search data for a single day OR multiple days?

[jpjconti]

Hello. Multiple days.

[vasildavid]

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]

[martin_mueller · Accepted solution]

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.

[jpjconti]

Martin, thanks for your help. It worked.

[Richfez]

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?