topic Re: Help using earliest, latest and finding the first occurrence of string in Splunk Search

Help using earliest, latest and finding the first occurrence of string

xploresplunk — Sat, 15 Jun 2019 16:26:51 GMT

I have 7 different fields that I need to get information from in different ways. They're all under the same index, source and sourcetype. Here are the 7 fields and the 7 different things that I need to achieve:

Field1: I need to go through the logs of this Field and find the EARLIEST time one of the values inside this field contains the word "Example1"
Field2: same as in Field1 but I need to go through Field2 and find the EARLIEST time the word “example2” is in one of its values.
Field3: find the EARLIEST value of Field3.
Field4: find the LATEST value of Field4
Field5: find the LATEST value of Field4.
Field6: I need to go through the values of this Field and find the LATEST time one of the values inside this field contains the word "Example6"
Field7: same as Field6 but I need to go through Field7 and find LATEST occurrence of the word “example7”.

I want to store each of these times acquired in each field as different values. My goal is to have a table displaying the times I found for each occurrence.
Further, some of the fields may not contain the world I'm looking for so I want it to stay blank. I do not want to restrict the number of events.

Re: Help using earliest, latest and finding the first occurrence of string

richgalloway — Sat, 15 Jun 2019 17:05:25 GMT

Please provide some sample data.

Re: Help using earliest, latest and finding the first occurrence of string

jnudell_2 — Sat, 15 Jun 2019 18:18:28 GMT

Your explanation is very confusing, and I think you are using the wrong terminology.

Sample data would be extremely helpful.

Questions:
- What is event1 (event2, event3, ... event7) ?

- Is that a particular event that happens in a log somewhere?

- How can you uniquely identify event1?

- Is there an event code you can reference?
- What is your overall goal for all of these searches?
- Are you creating alerts?
- Are you creating a report?
- Do you want to display results in a dashboard?

Providing context as well as sample data well go a long way in allowing others to consider options for your request.

Re: Help using earliest, latest and finding the first occurrence of string

woodcock — Sat, 15 Jun 2019 22:33:28 GMT

I agree with the other comments. Use the terms correctly, provide sample data, and a mockup of the desired final output. It looks like perhaps when you wrote event, you meant sourcetype?

Re: Help using earliest, latest and finding the first occurrence of string

jkat54 — Mon, 17 Jun 2019 13:11:52 GMT

Event1: I need to go through the logs of this event and find the EARLIEST occurrence of the word “example1” in one of the paragraphs it contains.

index=yourindex "example1" | stats earliest(_time) as earliest | convert ctime(earliest)
Event2: same as Event1 but I need to go through event2 and find EARLIEST occurrence of the word “example2”.
```
 index=yourindex "example2" | stats earliest(_time) as earliest | convert ctime(earliest) 
```

Event3: find the EARLIEST log of event3.

 index=yourindex "event3" | stats earliest(_time) as earliest | convert ctime(earliest)

Event4: find the LATEST log of event4

 index=yourindex "event4" | stats latest(_time) as latest| convert ctime(latest)

Event5: find the LATEST log of event [5].

 index=yourindex "event5" | stats latest(_time) as latest| convert ctime(latest)

Event6: I need to go through the logs of this event and find the LATEST occurrence of the word “example6” in one of the paragraphs it contains.
```
 index=yourindex "event6" "example6" | stats latest(_time) as latest| convert ctime(latest) 
```
Event7: same as Event6 but I need to go through event7 and find LATEST occurrence of the word “example7”.
```
 index=yourindex "event6" "example7" | stats latest(_time) as latest| convert ctime(latest) 
```

Re: Help using earliest, latest and finding the first occurrence of string

jnudell_2 — Wed, 30 Sep 2020 00:57:07 GMT

You can try this:

index=myindex ("example1" OR "example2" OR "event3" OR "event4" OR "event5" OR "example6" OR "example7")

| eval type = case(match(_raw, "example1"), "Event1", match(_raw, "example2"), "Event2", match(_raw, "event3"), "Event3", match(_raw, "event4"), "Event4", match(_raw, "event5"), "Event5", match(_raw, "example6"), "Event6", match(_raw, "example7"), "Event7")

| stats earliest(eval(strftime(case(type=="Event1", _time), "%F %T"))) as EarliestEvent1, earliest(eval(strftime(case(type=="Event2", _time), "%F %T"))) as EarliestEvent2, earliest(eval(strftime(case(type=="Event3", _time), "%F %T"))) as EarliestEvent3, latest(eval(strftime(case(type=="Event4", _time), "%F %T"))) as LatestEvent4, latest(eval(strftime(case(type=="Event5", _time), "%F %T"))) as LatestEvent5, latest(eval(strftime(case(type=="Event6", _time), "%F %T"))) as LatestEvent6, latest(eval(strftime(case(type=="Event7", _time), "%F %T"))) as LatestEvent7

Here is what it will look like with some dummy data:

Without more detail of what exactly you're looking for and a sample of data / mock-up of what you want to see, it's difficult to provide what you're looking for.

Re: Help using earliest, latest and finding the first occurrence of string

xploresplunk — Mon, 17 Jun 2019 18:29:01 GMT

I've updated the question. Let me know if you have any questions. Sorry for the confusion.

Re: Help using earliest, latest and finding the first occurrence of string

xploresplunk — Mon, 17 Jun 2019 18:30:20 GMT

I've updated the question. Sorry for the confusion. I cannot provide sample data as it contains sensitive information. Again, Im so sorry for the confusion. I'm a new user and confused myself with the terminology

Re: Help using earliest, latest and finding the first occurrence of string

jnudell_2 — Mon, 17 Jun 2019 20:09:03 GMT

You can provide sample data, with all sensitive information masked out.

Re: Help using earliest, latest and finding the first occurrence of string

xploresplunk — Tue, 18 Jun 2019 13:11:31 GMT

I've added a picture and I highlighted the word that I'm trying to get the time of (In that case, the earliest)

Re: Help using earliest, latest and finding the first occurrence of string

xploresplunk — Tue, 18 Jun 2019 13:11:42 GMT

I've added a picture and I highlighted the word that I'm trying to get the time of (In that case, the earliest)

Re: Help using earliest, latest and finding the first occurrence of string

xploresplunk — Wed, 30 Sep 2020 01:07:34 GMT

Utilizing earliest(_time) works for me, but when I use it twice the time doesn't update. For example if the first occurrence of the word "example1" is at 1:22 and the first occurrence of the word "example2" is at 1:07 then both store 1:07 as the time stamp. Is there any way to fix this? Can I clear earliest(_time)?

Re: Help using earliest, latest and finding the first occurrence of string

xploresplunk — Wed, 30 Sep 2020 01:07:37 GMT

This doesnt work. Earliestevent1,2,3 are displaying the same time. Utilizing earliest(_time) works for me, but when I use it twice the time doesn't update. For example if the first occurrence of the word "example1" is at 1:22 and the first occurrence of the word "example2" is at 1:07 then both store 1:07 as the time stamp. Is there any way to fix this? Can I clear earliest(_time)? I want to get the first time that "event1" was written and the first time "event2" was written and save them in two different values. Im trying to create a timeline so I need the specific times for each.

Re: Help using earliest, latest and finding the first occurrence of string

jkat54 — Thu, 27 Jun 2019 18:13:33 GMT

You can use stats min(_time} as earliest | concert ctime(earliest)

Or several as long as you use "as" clause to rename it.

Re: Help using earliest, latest and finding the first occurrence of string

xploresplunk — Thu, 27 Jun 2019 19:04:02 GMT

Could you refer to this post? https://answers.splunk.com/answers/755758/using-join-earliest-table-and-latest.html?minQuestionBodyLength=80

It explains my issue a little bit more. I tried using min and it still doesnt work. Thank you so much

Re: Help using earliest, latest and finding the first occurrence of string

xploresplunk — Thu, 27 Jun 2019 19:14:10 GMT

I was able to figure out a lot of it. I'm running into a problem when using earliest(_time) several times: https://answers.splunk.com/answers/755758/using-join-earliest-table-and-latest.html?minQuestionBodyLength=80