table sort columns numerically

[pethier]

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,

[rslama]

I had the same issue after chart command, I fixed it by adding leading 0 to my values:
search here
| eval data=if(len(data)=1,"0"+data,data)
| chart count by fielda data

[rslama]

another option works better:
base search
| chart dc(index) by fieldA fieldB useother=f limit=0
| transpose header_field=fieldA column_name=fieldB 100
| sort fieldB
| transpose column_name=fieldA header_field=fieldb 100

[yannK]

have you tried

sort num(number) 

or

sort str(number) 

see http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Sort

[monil286]

I am a noob at Splunk, but I worked on the solution as follows:

. . . | top limit=0 number | sort limit=0  number 

I had a similar problem (i tried to find the top 100 spenders on a sample file) and The above code worked for me.

[tawollen]

Interesting trick I did was to flip what you are sorting for, so instead of row at top and col down the side, you put row down the side and col at top then do a | transpose

I had to do this to get time correct, wanted a result | chart count by date_wday,date_hour limit=24, but sorted wrong.

doing this seemed to work
result | chart count by date_hour,date_wday useother=f | table sunday monday tuesday wednesday thursday friday saturday | transpose 24

not pretty, but worked.

[martin_mueller]

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?

[Gilberto_Castil · Accepted solution]

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.

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*

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.

[pethier]

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!

[Gilberto_Castil]

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.

[pethier]

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!

[pethier]

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,

[splunk24]

did you write custom command

[BernardEAI]

I have come across this problem numerous times, so a column sort command would be very valuable!

[Drainy]

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

[martin_mueller]

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