Show/filter only events that existed 24 hours ago

[st1]

I have the following search

index=cisco sourcetype=cisco:wlc snmpTrapOID_0="CISCO-LWAPP-AP-MIB::ciscoLwappApRogueDetected"
|rename cLApName_0 as "HQ AP"
|dedup "HQ AP" 
|stats list(*) as * by "_time"
|table _time, "HQ AP", RogueApMacAddress

Example results:

_time	HQ AP	RogueAPMacAddress
2023-10-05 12:56:41	flr1-ap-5198-AP05	6e:e8:e9:cd:40:10
2023-10-06 04:09:29	flr1-ap-51c4	da:55:b8:8:db:b8
2023-10-06 08:42:14	flr1-ap-514E_AP07	84:fd:d1:fa:a7:3f
2023-10-06 08:53:12	flr1-ap-518C-B92	0:25:0:ff:94:73
2023-10-06 09:20:22	flr2-ap-51CA	28:24:ff:fd:a6:c0
2023-10-06 09:30:58	flr1-ap-51C2 flr2-ap-463C-AP02	32:b:61:48:a3:c3
2023-10-07 04:09:29	flr1-ap-444x-B11	da:55:b8:8:db:b8
2023-10-07 08:53:12	flr1-ap-69x4	0:25:0:ff:94:73

The search is showing access points in our office that have detected unauthorized access points. I have my search to look at the last 24 hours. I only want to filter for RogueApMacAddresses that have been present/detected for over 24 hours. In this example, both the red and blue events have been there for over the last 24 hours.

How can I alert on just those events and disregard the rest? Thanks for any help

[PickleRick]

I'm not fully sure what you want to achieve, especially with this dedup. I tend to avoid this command altogether because it leaves only first occurrence of given field value, regardless of the actual order of events at given point of the search pipeline.

If - assuming that your search makes sense - you want to just find all those events for which first occurrence of given MAC was over 24h ago, use eventstats to find min(_time) by RogueMACAddress and then you can do "where" to find those that are lower than now()-86400.

[gcusello]

Hi @st1,

the logic of your search isn't so clear for me.

Anyway, if you want to have as results only the events in the ast 24 hours, you can run (not changing your search!):

index=cisco sourcetype=cisco:wlc snmpTrapOID_0="CISCO-LWAPP-AP-MIB::ciscoLwappApRogueDetected"
| rename cLApName_0 as "HQ AP"
| dedup "HQ AP" 
| stats list(*) as * by _time
| where _time>now()-86400
| table _time, "HQ AP", RogueApMacAddress

Ciao.

Giuseppe

[st1]

Hi @gcusello 

What I'm looking to do is find any duplicate occurrence of a RogueApMacAddress (any particular value that repeats more than once) within 24 hours. Including the command you provided wouldn't help with that. But I hope you understood what I'm trying to do. Let me know if not!

[yuanliu · Accepted solution]

So, this is a duration test.  In that case, why not use min/max to test boundary?

index=cisco sourcetype=cisco:wlc snmpTrapOID_0="CISCO-LWAPP-AP-MIB::ciscoLwappApRogueDetected"
|dedup cLApName_0 
|stats list(cLApName_0) as cLApName_0 min(_time) as first_rogue max(_time) last_rogue by RogueApMacAddress
|where last_rogue - first_rogue > 86400
|rename cLApName_0 as "HQ AP"
| fieldformat first_rogue = strftime(first_rogue, "%F %H:%M:%S")
| fieldformat last_rogue = strftime(last_rogue, "%F %H:%M:%S")

You should probably use values instead of list, too.  Not sure what value list adds to your quest.  But if you use values, dedup is no longer needed.