topic Re: Search Conditioning based on date in Splunk Search

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

Becherer — Thu, 19 May 2022 19:12:27 GMT

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?

Re: Search Conditioning based on date

ITWhisperer — Fri, 13 May 2022 07:04:23 GMT

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

Re: Search Conditioning based on date

Becherer — Thu, 19 May 2022 19:10:56 GMT

@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

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

Re: Search Conditioning based on date

ITWhisperer — Fri, 20 May 2022 04:39:37 GMT

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?

Re: Search Conditioning based on date

Becherer — Fri, 20 May 2022 14:40:25 GMT

@ITWhisperer
Yes that is correct.

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

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

somesoni2 — Fri, 20 May 2022 15:58:15 GMT

Give this a try