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

[Becherer]

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? 

[somesoni2]

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

[ITWhisperer]

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

[Becherer]

@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!

[ITWhisperer]

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?

[Becherer]

@ITWhisperer 
Yes that is correct. 

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