How to sort as numerals the X axis of a histogram

[yugin]

Hi,

I'm trying to plot a histogram of transaction durations. The durations range from 0s to 60s. My search string is something like this:

source=blah | transaction thread | chart count by duration span=1s

The resulting histogram look strange because the X-Axis is ordered:

0 1 10 11 12 13 14 15 16 17 18 19 2 20
21 ...

instead of:

0 1 2 3 4 5 6 7 8 9 10 11 12 ...

I suspect that the chart command is ordering the X-Axis as a strings instead of numerals. Is there a way to change this?

Any help is appreciated. Thanks.

[yugin · Accepted solution]

With the help of [martin_mueller][1] I've found appending

| sort + duration

to the search corrects the problem. Now the X-axis is sorted correctly. The entire search to produce a histogram looks like this:

source=blah | transaction thread | chart count by duration span=1s | sort + duration

and I selected a column chart.

Thanks to everyone for your inputs and kind help.

[yugin]

Hi Martin,

When I appended "| sort + duration" to the search, the X-axis was sorted correctly. Thanks for your help!

[dwaddle]

Yugin,

I tend to agree with you, the chart's x axis is being sorted lexicographically not numerically. One workaround might be to "zero-fill" such that 0 -> 00, 1 -> 01, etc -- then the numeric and lexicographic sorts are the same.

I would also suggest a support case to see if they have any additional ideas.

[martin_mueller]

Okay, what happens if you append this to the search?

... | sort + duration

[yugin]

Hi Martin,
Your search still appears wrong to me. The x-axis reads
0 1 11 14 15 16 18 19 2 20 21 ......

[martin_mueller]

Does my search appear sorted correctly or incorrectly for you?

[yugin]

Hi Martin,
I'm not doing anything special. The problem occurs when the number of x-axis values is greater than 10.

Hi Aholzer,
You are right. I've tried this:
"source=blah | transaction thread |bucket duration span=1s | stats count by duration"
which outputs to the statistics tab in the wrong sort order. From there I could click the duration header to get the sorting right (which proves that bucket is outputing 'duration' as numbers) but when I click on visualization, the x-axis of the chart is still sorted wrongly.

[aholzer]

My guess is that you are using a "string" field rather than a "numerical" on your X axis. This would mean that it would sort the values 0-11 based on "alphabetical" order. Hence getting 0 1 10 11 2 3 4...

Convert your X axis field to a numerical value before using it in your chart command.

Hope this helps

[martin_mueller]

I've modified your search to be runnable everywhere:

index=_internal component=* | transaction maxspan=60s component | chart count by duration span=1s

and charted that as a column chart without doing anything special... and sorting works as expected, 2 comes after 1.

Are you doing anything special not mentioned here?