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 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 🙂
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
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.
Your data is being interpreted as a string and is sorted alphabetically.
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*
Sort according to the desired order by row. N
... | transpose 10 | sort "row 1"
RENAME the field ordering according to sort criteria.
... | eval i=1 | accum i | eval column="field".i | fields - i
Convert table to a single row
... | eval dummy=" " | xyseries dummy column "row 1" | eval dummy="A" | rename dummy AS Category
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.
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
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
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.
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.
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?