Splunk Search

table sort columns numerically

pethier
Explorer

I would like to be able to sort table columns numerically. Right now it sorts based on 1 11 111 2, but I want 1 2 11 111. I do not believe there is a feature in Splunk right not to handle this, and am considering writing my own.

I've tried transposing, sorting, and transposing back, but it appears transpose is not a true linear algebraic transpose.

Any other thoughts before I write my own command | sortcols type=numeric or something like that?

Thanks,

1 Solution

Gilberto_Castil
Splunk Employee
Splunk Employee

It looks like your field is not being sorted numerically. For instance, if we create the minimal data set for testing, sorting by the number will order the results by 1, 2, 11 and 111. Here is the data:

1 This is one line
11 This is the third line
111 This is the fourth line
2 This is the second line

This is the search for the data:

sourcetype="answers_q_1369765528" | rex field=_raw "^(?<number>\d+)\s+(?<message>.+?)$" | sort number | table number message

alt text



On the other hand, if we alter the data so that the value is considered a string, then the results are different. Here is the search:

sourcetype="answers_q_1369765528" | rex field=_raw "^(?<number>\d+)\s+(?<message>.+?)$" | eval number="a-".number | sort number | table number message

Here are the results. Note that the order reflects your current condition.

alt text





Your data is being interpreted as a string and is sorted alphabetically.

PART 2


Given the explanation about sorting the column values in a table, here is a mechanical way to provide a sort using transpose and xyseries. Given the following data set:

A 1 11 111 2 22 222 4

We extract the fields and present the primary data set. Of course there are more elegant ways to do this but this used for testing, not real life 🙂

sourcetype="answers_2" | rex field=_raw "(?<Category>\w+)\s+(?<field1>\d+)\s+(?<field2>\d+)\s+(?<field3>\d+)\s+(?<field4>\d+)\s+(?<field5>\d+)\s+(?<field6>\d+)\s+(?<field7>\d+)" | table field*

alt text

Sort according to the desired order by row. N

... | transpose 10 | sort "row 1" 

alt text

RENAME the field ordering according to sort criteria.

... | eval i=1 | accum i | eval column="field".i | fields - i 

alt text

Convert table to a single row

... | eval dummy=" " | xyseries dummy column "row 1" | eval dummy="A" | rename dummy AS Category

alt text

Now, this not very useful for multi-row tables. I'd have to think about that one. If you do not care about the field naming, this may help. Otherwise, this may confirm the need for a special sort command.

My challenge with a special sort for column values is not the logic but the nature of tables in the Splunk UI. By default the fields are ordered alpha-numerically and field values do not override that default ordering.

View solution in original post

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...