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!

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...

Ready, Set, SOAR: How Utility Apps Can Up Level Your Playbooks!

 WATCH NOW Powering your capabilities has never been so easy with ready-made Splunk® SOAR Utility Apps. Parse ...

DevSecOps: Why You Should Care and How To Get Started

 WATCH NOW In this Tech Talk we will talk about what people mean by DevSecOps and deep dive into the different ...