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
SplunkTrust
SplunkTrust

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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...