Splunk Search

Show/filter only events that existed 24 hours ago

st1
Explorer

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:

_timeHQ APRogueAPMacAddress
2023-10-05 12:56:41flr1-ap-5198-AP056e:e8:e9:cd:40:10
2023-10-06 04:09:29flr1-ap-51c4da:55:b8:8:db:b8
2023-10-06 08:42:14flr1-ap-514E_AP0784:fd:d1:fa:a7:3f
2023-10-06 08:53:12flr1-ap-518C-B920:25:0:ff:94:73
2023-10-06 09:20:22flr2-ap-51CA28: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

Labels (4)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

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.

View solution in original post

PickleRick
SplunkTrust
SplunkTrust

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.

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

st1
Explorer

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!

Tags (1)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

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.

Get Updates on the Splunk Community!

Welcome to the Splunk Community!

(view in My Videos) We're so glad you're here! The Splunk Community is place to connect, learn, give back, and ...

Tech Talk | Elevating Digital Service Excellence: The Synergy of Splunk RUM & APM

Elevating Digital Service Excellence: The Synergy of Real User Monitoring and Application Performance ...

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...