Splunk Search

How to get top 10 values for each multicolumn timechart?

sjim
Loves-to-Learn

Here's my query:

 

index=comp_logs "processed=" |
eval name=consumerGroupId |
timechart span=1h sum(processed) as processed by name 

 

it gives me this result: 

sjim_0-1678396893835.png


For each column, I'd like to get the top 10 values from descending order (we can remove the _time column). Is this possible with timechart? Thank you!

Labels (1)
0 Karma

Tom_Lundie
Contributor

Hi Sjim,

I think you're approaching this problem as if Splunk is a spreadsheet management tool (like Excel) whereby  columns can be unrelated and unlinked from neighbouring columns.

Even in a table view like your example, each row can be thought of as an "event" (of-sorts) and each column is a "field". Each cell is the respective field-value for the given "event" and "field". Each value in the row is explicitly linked (so there is no way to sort columns on an individual basis). What you're trying to do will disregard this concept and attempt to model the data outside of this abstract-model. Depending on what your ultimate use-case is for this data, you might find exporting this to a spreadsheet will make your life easier.

If you want to persevere with Splunk, then I've got a query for you to try but it is quite a complex one. Here is a run-anywhere example.

 

| makeresults count=100
| bin span=1h _time
| eval example1= random(), example2 = random(), example3 = random()
| streamstats count
| eval _time = _time + 3600 * count
| fields - count
| fields - _time
| stats values(*) as inscope___*
| eventstats max(*) as max___* 
| eval id___=mvrange(-9,1)
| rex field=id___ mode=sed "s/-//g"
| foreach inscope___* [| eval "<<FIELD>>" = mvzip(id___,mvindex(mvsort(mvmap('<<FIELD>>', substr(tostring(pow(10, 1 + (len('max___<<FIELD>>') - len('<<FIELD>>')))),2) + '<<FIELD>>')),-10, -1),"|") | rex field="<<FIELD>>" "\d\|0+(?<finalised___<<FIELD>>>\d+)"]
| fields - tmp___*, max___*, inscope___*, id___
| rename finalised___inscope___* as *

 

For your query specifically, you're going to be interested in the section including the "| fields - _time" command and onwards.

At a high-level, we're combining all of the fields into multivalue fields in a single row, multivalue fields have a very limited sorting capability,  so we generate a helper field called id___ (a reversed countdown from 9 to 0)  to help us reverse the order later. We also need to find the largest integer so that we can pad out the fields with trailing 0s, that's what the eventstats is for.

Next we step through each field, pad them out with with trailing 0s (as in n*"0"+ value) (where n is the difference between the length of the current value and the largest value + 1). After sufficiently padding the values we sort them lexicographically using mvsort and then take the last 10 values using mvindex. Finally, we use mvzip to append our helper id___ (reverse countdown) and then sort again to get the descending order as you desired. Then we use rex to remove the prepended id___ and remove the trailing zeros.

Finally, we dump all of the temporary fields that we generated to help us with this process using "| fields -".

Phew....  Thanks for sticking with me! Don't say I didn't warn you. Let me know how you get on!

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...