topic Re: Extracting fields and times as nested table in Splunk Search

Extracting fields and times as nested table

v33jay — Wed, 17 Feb 2021 17:57:33 GMT

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

Re: Extracting fields and times as nested table

jacobpevans — Wed, 17 Feb 2021 19:31:30 GMT

Greetings @v33jay,

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

Re: Extracting fields and times as nested table

v33jay — Wed, 17 Feb 2021 21:37:44 GMT

Thank you @jacobpevans, that worked like a charm!

Re: Extracting fields and times as nested table

v33jay — Thu, 18 Feb 2021 00:09:57 GMT

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

Re: Extracting fields and times as nested table

jacobpevans — Thu, 18 Feb 2021 12:06:56 GMT

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:

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

Re: Extracting fields and times as nested table

v33jay — Fri, 19 Feb 2021 21:05:12 GMT

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