Splunk Search

How to search the last 30 days for all events to see what device still needs attention?

Becherer
Explorer

I have events from a device sent to splunk every day seen in the example below.

Here is an example of that I want to happen:
If I ran a search on 2022-05-02, my result would show "Event 1" because the "last_fixed" date is older than "last_found" date. But if I run the same search on 2022-05-04 it will show event 5 because the "last_fixed" date is newer than "last_found" date.

I am trying to search the last 30 days for all events to see what device still needs attention. I want to show the oldest event that has "last_found" date unless there's a newer event with the "last_fixed" date newer than "last_found"

My Search:
index=01 
| dedup Device IP_Address  sortby +_time
| table dest ip_address


Event 1: 
Time:
2022-04-29
Device: aaa.local
IP_Address:
10.10.10.5
last_fixed: 2022-04-04T21:07:01.592Z
last_found: 2022-04-29T05:52:57.742Z

Event2: 
Time: 2022-04-30
Device: aaa.local
IP_Address:
10.10.10.5
last_fixed: 2022-04-04T21:07:01.592Z
last_found: 2022-04-30T05:52:11.663Z

Event3: 
Time:
2022-05-01
Device: aaa.local
IP_Address:
10.10.10.5
last_fixed: 2022-04-04T21:07:01.592Z
last_found: 2022-05-01T05:53:36.270Z

Event4: 
Time:
2022-05-02
Device: aaa.local
IP_Address:
10.10.10.5
last_fixed: 2022-04-04T21:07:01.592Z
last_found: 2022-05-02T05:55:02.180Z

Event5: 
Time:
2022-05-03
Device: aaa.local
IP_Address:
10.10.10.5
last_fixed: 2022-05-03T05:54:03.611Z
last_found: 2022-05-02T05:55:02.180Z

 

Would the best way to do this is by using eval? 

Labels (2)
0 Karma

somesoni2
Revered Legend

Give this a try

your base search 
| sort 0 +_time 
| eval type=if(isnotnull(last_fixed) AND strptime(last_found,"%FT%T.%3N%Z")<strptime(last_fixed,"%FT%T.%3N%Z"),"Fixed","Not Fixed") 
| dedup Device type 
| sort 0 Device -_time 
| dedup Device
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Assuming Time is actually _time

| eventstats max(last_fixed) as latest_fixed by Device
| where last_fixed=latest_fixed
| stats earliest(*) as * earliest(_time) as _time by Device
0 Karma

Becherer
Explorer

@ITWhisperer,

Thank you very much! I made a mistake in my data example. The "last_fixed" date is not actually on every event. With that said, if I run what you provided on the date 2022-05-02, it doesn't show any events if I run it today it shows Event 5. 

Event 1: 
Time:
2022-04-29
Device: aaa.local
IP_Address:
10.10.10.5
last_found: 2022-04-29T05:52:57.742Z

Event2: 
Time: 2022-04-30
Device: aaa.local
IP_Address:
10.10.10.5
last_found: 2022-04-30T05:52:11.663Z

Event3: 
Time:
2022-05-01
Device: aaa.local
IP_Address:
10.10.10.5
last_found: 2022-05-01T05:53:36.270Z

Event4: 
Time:
2022-05-02
Device: aaa.local
IP_Address:
10.10.10.5
last_found: 2022-05-02T05:55:02.180Z

Event5: 
Time:
2022-05-03
Device: aaa.local
IP_Address:
10.10.10.5
last_fixed: 2022-05-03T05:54:03.611Z
last_found: 2022-05-02T05:55:02.180Z

 

Here is an example of that I want to happen:
If I ran a search on 2022-05-02, my result would show "Event 1" because the "last_fixed" date is older than "last_found" date. But if I run the same search on 2022-05-04 it will show event 5 because the "last_fixed" date is newer than "last_found" date.

Im sorry for the confusion. I really appreciate your help!

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

If you ran on 2022-05-02, presumably event 5 doesn't exist? If so, there is no event with last_fixed in your example, so do you just want the earliest event?

0 Karma

Becherer
Explorer

@ITWhisperer 
Yes that is correct. 

If there is no event with last_fixed in my example, I just want the earliest event.

0 Karma
Get Updates on the Splunk Community!

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...

Platform Highlights | January 2023 Newsletter

 January 2023Peace on Earth and Peace of Mind With Business ResilienceAll organizations can start the new year ...