Splunk Search

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

New Member

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:

  1. 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"
  2. 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.
  3. Field3: find the EARLIEST value of Field3.
  4. Field4: find the LATEST value of Field4
  5. Field5: find the LATEST value of Field4.
  6. 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"
  7. 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.

alt text

0 Karma

Builder

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:
alt text

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.

0 Karma

New Member

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

0 Karma

Builder

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

0 Karma

New Member

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

0 Karma

New Member

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.

0 Karma

SplunkTrust
SplunkTrust
  1. 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)

  2. 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) 
    
  3. Event3: find the EARLIEST log of event3.

     index=yourindex "event3" | stats earliest(_time) as earliest | convert ctime(earliest) 
    
  4. Event4: find the LATEST log of event4

     index=yourindex "event4" | stats latest(_time) as latest| convert ctime(latest) 
    
  5. Event5: find the LATEST log of event [5].

     index=yourindex "event5" | stats latest(_time) as latest| convert ctime(latest) 
    
  6. 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) 
    
  7. 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) 
    
0 Karma

New Member

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)?

0 Karma

SplunkTrust
SplunkTrust

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

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

0 Karma

New Member

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

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

0 Karma

Esteemed Legend

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?

0 Karma

New Member

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

0 Karma

New Member

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?minQuestionBodyL...

0 Karma

Builder

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.

0 Karma

New Member

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

0 Karma

SplunkTrust
SplunkTrust

Please provide some sample data.

---
If this reply helps you, an upvote would be appreciated.
0 Karma