Splunk Search
Highlighted

table sort columns numerically

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,

Highlighted

Re: table sort columns numerically

SplunkTrust
SplunkTrust

Don't forget to publish an add-on once you've finished writing the command 🙂

0 Karma
Highlighted

Re: table sort columns numerically

Champion

Ignore any answer notifications, its far too early and I'm not reading questions properly 🙂

0 Karma
Highlighted

Re: table sort columns numerically

Explorer

thanks for the responses! and I'm happy to upload a custom command after I write it, however I'd first like to confirm that it's actually needed 🙂

cheers,

0 Karma
Highlighted

Re: table sort columns numerically

Path Finder

did you write custom command

Highlighted

Re: table sort columns numerically

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

Highlighted

Re: table sort columns numerically

Explorer

Sorry, but that's not what I am asking. I really do appreciate the help! Here's some more information:

The columns/fields of tables, not the rows, cannot be sorted numerically.

I end up with tables that look like this:

A 1 11 111 2 22 222 3

a 0 1 2 3 4 5 6

etc

I know it's kind of hard to show without a screen shot, but it's field names as numbers, for example, after doing a | stats by

Thanks!

0 Karma
Highlighted

Re: table sort columns numerically

Splunk Employee
Splunk Employee

The problem is not the sorting of the values for each field. Tables columns are normally ordered in alphabetical order such that field1 will always be before field2.

I am updating the answer with a technical note which may help illustrate that point. If you do not care about field names shifting, then this may help a bit. Otherwise, you may have confirmation of the need for a special sort for column values in a table.

0 Karma
Highlighted

Re: table sort columns numerically

Explorer

yes that is correct. the field names / column headers are sorted alphabetically. While the responses here are encouraging, I think the only solution might be to write a custom colsort command.

thanks!

0 Karma
Highlighted

Re: table sort columns numerically

SplunkTrust
SplunkTrust

This may be a workaround, but I have no access to a splunk instance to verify right now... convert the numeric field to a string and prepend the correct number of zeroes to make them all the same length. The default string sort for field order should then sort them correctly. Afterwards, use rename to get rid of zeroes if necessary.

Off the top of my head, it might look something like this:

... | eventstats max(field) as max_field | eval field = substr("000", 1, length(tostring(max_field))-length(tostring(max_field))).tostring(field) | stats ... | rename 00* as * | rename 0* as *

If you expect more than four digits you'd need to add zeroes... negative numbers or non-integers may fail... not sure about the number zero itself, best to properly test this. Additionally, a colsort command may still be nice - do you have the means to write one?