Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Splunk Search

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Community
- :
- Splunk Answers
- :
- Using Splunk
- :
- Splunk Search
- :
- table sort columns numerically

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

pethier

Explorer

05-27-2013
10:38 PM

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Re: table sort columns numerically

martin_mueller

SplunkTrust

05-27-2013
11:56 PM

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Re: table sort columns numerically

Drainy

Champion

05-28-2013
12:17 AM

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Re: table sort columns numerically

pethier

Explorer

05-28-2013
08:02 AM

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,

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Re: table sort columns numerically

splunk24

Path Finder

12-11-2015
08:12 AM

did you write custom command

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Gilberto_Castil

Splunk Employee

05-28-2013
11:26 AM

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.

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Re: table sort columns numerically

pethier

Explorer

05-28-2013
11:31 AM

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!

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Re: table sort columns numerically

Gilberto_Castil

Splunk Employee

05-28-2013
12:30 PM

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.

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Re: table sort columns numerically

pethier

Explorer

05-28-2013
12:48 PM

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!

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Re: table sort columns numerically

martin_mueller

SplunkTrust

05-28-2013
12:27 PM

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?