Extracting fields and times as nested table

[v33jay]

I have a log with the following entries among others and I am looking for a way to display the top 2 times by each action.

Calculated ABC. Action took 100 milliseconds

Calculated XYZ. Action took 122450 milliseconds

Calculated ABC. Action took 10 milliseconds

Calculated XYZ. Action took 67543 milliseconds

Calculated ABC. Action took 11 milliseconds

Calculated XYZ. Action took 5 milliseconds

Calculated ABC. Action took 600 milliseconds

I can extract the fields just fine using regex and can display the entry with max time by action using the below search

source="*test.log*" "Calculated" | rex field=_raw "^.*Calculated (?<ACTION>.+)" | rex field=_raw "^.*Action took (?<DURATION>.+) milliseconds" | stats max(DURATION) by ACTION

ACTION	DURATION
ABC	600
XYZ	122450

However I'm lost as to how to get the top 2 transactions reported like below

ACTION	DURATION
ABC	600
	100
XYZ	122450
	67453

[v33jay]

@jacobpevans , Can I ask a related question. I would like to extract the time associated with the events as well. I used the below search which is giving me the Time values but the times themselves are sorted in ascending order and not really related to the events with the maximum duration. Any pointers as to what I got wrong?

          | makeresults | eval _raw = "2021-02-17 09:09:50 Calculated ABC. Action took 100 milliseconds"
| append [ | makeresults | eval _raw = "2021-02-17 10:09:50 Calculated XYZ. Action took 122450 milliseconds" ]
| append [ | makeresults | eval _raw = "2021-02-17 11:09:50 Calculated ABC. Action took 10 milliseconds" ]
| append [ | makeresults | eval _raw = "2021-02-17 12:09:50 Calculated XYZ. Action took 67543 milliseconds" ]
| append [ | makeresults | eval _raw = "2021-02-17 14:09:50 Calculated ABC. Action took 11 milliseconds" ]
| append [ | makeresults | eval _raw = "2021-02-17 15:09:50 Calculated XYZ. Action took 5 milliseconds" ]
| append [ | makeresults | eval _raw = "2021-02-17 16:09:50 Calculated ABC. Action took 600 milliseconds" ]
| rex field=_raw "Calculated (?<ACTION>[^\.]+)\."
| rex field=_raw "Action took (?<DURATION>\d+) milliseconds"
| eval _time = strftime(_time,"%F %H:%M:%S")
| sort 0 ACTION -DURATION
| streamstats count by ACTION
| where count <= 2
| stats list(DURATION) as "Top 2 Durations", values(_time) as Time by ACTION

[jacobpevans]

I'm not completely following what you're asking, but there are definitely some things to fix.

When you use | makeresults, it automatically creates a _time field equal to the current time. That is the "time" field that you are trying to manipulate (which is the same for every row). What you're looking for is something like this:

           | makeresults | eval _raw = "2021-02-17 09:09:50 Calculated ABC. Action took 100 milliseconds"
| append [ | makeresults | eval _raw = "2021-02-17 10:09:50 Calculated XYZ. Action took 122450 milliseconds" ]
| append [ | makeresults | eval _raw = "2021-02-17 11:09:50 Calculated ABC. Action took 10 milliseconds"     ]
| append [ | makeresults | eval _raw = "2021-02-17 12:09:50 Calculated XYZ. Action took 67543 milliseconds"  ]
| append [ | makeresults | eval _raw = "2021-02-17 14:09:50 Calculated ABC. Action took 11 milliseconds"     ]
| append [ | makeresults | eval _raw = "2021-02-17 15:09:50 Calculated XYZ. Action took 5 milliseconds"      ]
| append [ | makeresults | eval _raw = "2021-02-17 16:09:50 Calculated ABC. Action took 600 milliseconds"    ]
| rex field=_raw "Calculated (?<ACTION>[^\.]+)\."
| rex field=_raw "Action took (?<DURATION>\d+) milliseconds"
| eval time  = substr(_raw, 0, 20)
| eval _time = strptime(time,  "%F %H:%M:%S")
| eval time  = strftime(_time, "%F %H:%M:%S")
| sort 0 ACTION -DURATION
| streamstats count by ACTION
| where count <= 2
| stats list(DURATION) as "Top 2 Durations", list(time) as Time by ACTION

Keep in mind that _time should always be an epoch so you should keep your human-readable time in a non-_time field. That's why I seem to switch back and forth.

As a side note, assigning the _time and the two rex statements should all be done at the sourcetype definition level. You should NOT actually be doing any of this in a search in production.

https://docs.splunk.com/Documentation/Splunk/latest/Data/Createsourcetypes

[v33jay]

That's what I was looking for, Thanks again @jacobpevans 

[v33jay]

Thank you @jacobpevans, that worked like a charm!

[jacobpevans · Accepted solution]

Greetings @v33jay,

Try this run-anywhere search using your sample data. I had to change the rex because yours were too aggressive.

           | makeresults | eval _raw = "Calculated ABC. Action took 100 milliseconds"
| append [ | makeresults | eval _raw = "Calculated XYZ. Action took 122450 milliseconds" ]
| append [ | makeresults | eval _raw = "Calculated ABC. Action took 10 milliseconds" ]
| append [ | makeresults | eval _raw = "Calculated XYZ. Action took 67543 milliseconds" ]
| append [ | makeresults | eval _raw = "Calculated ABC. Action took 11 milliseconds" ]
| append [ | makeresults | eval _raw = "Calculated XYZ. Action took 5 milliseconds" ]
| append [ | makeresults | eval _raw = "Calculated ABC. Action took 600 milliseconds" ]
| rex field=_raw "Calculated (?<ACTION>[^\.]+)\."
| rex field=_raw "Action took (?<DURATION>\d+) milliseconds"

| sort 0 ACTION -DURATION
| streamstats count by ACTION
| where count <= 2
| stats list(DURATION) as "Top 2 Durations" by ACTION