Splunk Search

How to sort based on column values?

mldavis195
Engager

I have some data that's coming in as follows:

 

"data": { "a": 100, "b": 200 }
"data": { "a": 50, "c": 75 }
...

 

 

 

I want to aggregate the values so I end up with a table of the sum of values by key:

 

 

<search> | chart sum(data.*) as *

 

 

 This gives me the table:

a b c
150 200 75

 

Now I want to sort the columns by value so that it is in the order b, a, c. It looks like the "sort" keyword sorts by rows and not columns. How would I do this?

Note this is an extremely simplified example and the actual data will have tons of keys which are arbitrary uuids and there will be a lot of rows to sum. I need to aggregate and then sort by value to have the highest on the left-hand-side. I would also like to only keep the first n columns. It looks like "head" also works by rows and not columns.

Any help would be greatly appreciated. Thanks.

Labels (1)
Tags (2)
0 Karma
1 Solution

rymundo_splunk
Splunk Employee
Splunk Employee

This also worked for me (I changed the field names slightly because I didn't want to deal with json 🙂 but the same idea applies ) 

 

|makeresults | eval data_a=100, data_b=200
|append [|makeresults | eval data_a=50, data_c=75]
| chart sum(data_*) as *
| eval blah="blah"
| untable blah key value
| sort 0 -value
| head 10
| transpose 0 header_field=key
| search column=value
| fields - blah column

View solution in original post

rymundo_splunk
Splunk Employee
Splunk Employee

This also worked for me (I changed the field names slightly because I didn't want to deal with json 🙂 but the same idea applies ) 

 

|makeresults | eval data_a=100, data_b=200
|append [|makeresults | eval data_a=50, data_c=75]
| chart sum(data_*) as *
| eval blah="blah"
| untable blah key value
| sort 0 -value
| head 10
| transpose 0 header_field=key
| search column=value
| fields - blah column

mldavis195
Engager

This worked like a charm. Thanks!

0 Karma

somesoni2
Revered Legend

See if something like this would work for you (everything before the chart command is to generate sample data, change it with your search)

| makeresults | eval temp="{\"data\": { \"a\": 100, \"b\": 200 }}#{\"data\": { \"a\": 50, \"c\": 75 }}" | makemv delim="#" temp | mvexpand temp | rename temp as _raw | extract 
| chart sum(data.*) as * 
| eval temp=1 | untable temp key value | sort 0 -value | head 10 | streamstats count | eval key=substr("00000000000".tostring(count),-3)."-".key | xyseries temp key value

 

Get Updates on the Splunk Community!

Security Highlights: September 2022 Newsletter

 September 2022 The Splunk App for Fraud Analytics (SFA) is now Splunk SupportedUse your existing Splunk ...

Platform Highlights | September 2022 Newsletter

 September 2022 What’s New in 9.0 and How to UpgradeGet a walk through of what is new Splunk Enterprise 9.0 ...

Observability Highlights | September 2022 Newsletter

 September 2022 Splunk Observability SuiteAccess to "Classic" SignalFx Interface Will be Removed on Sept 30, ...