topic Re: Why are the numbers not sorted as expected in my chart? in Splunk Search

Why are the numbers not sorted as expected in my chart?

changux — Tue, 24 May 2016 16:52:26 GMT

Hi all.

I have this search:

index="bucle_cm" sourcetype="cierres-pendientes" "Tipo Actuacion"="*" "Tipo Actuacion"!=Bajas EMPRES="*" | sort num("DIA CIERRE") | chart count by "Tipo Actuacion","DIA CIERRE" limit=0 | addcoltotals  | addtotals fieldname=Total

Where "DIA CIERRE" is a number field with values like 1,2,3,4,10,11..etc.

The result set looks like this:

Why are the numbers not sorted?

Thanks!

Re: Why are the numbers not sorted as expected in my chart?

somesoni2 — Tue, 24 May 2016 16:57:50 GMT

The field names in Splunk are treated as alphanumeric string, not number, hence the column ordering you see here. The sort command before the chart will not have any effect on the output of the chart command.

Re: Why are the numbers not sorted as expected in my chart?

changux — Tue, 24 May 2016 17:02:59 GMT

Thanks. Putting the sort after the chart command give the same ordering issue.

Re: Why are the numbers not sorted as expected in my chart?

somesoni2 — Tue, 24 May 2016 17:06:09 GMT

There is no good way to sort columns with numbers in it. Do you know if "DIA CIERRE" has fixed number of values??

Re: Why are the numbers not sorted as expected in my chart?

changux — Tue, 24 May 2016 17:09:27 GMT

"DIA CIERRE" is a day in a month, so, in the worst case, you have 31 as fixed value.

Re: Why are the numbers not sorted as expected in my chart?

somesoni2 — Tue, 24 May 2016 18:00:14 GMT

Does it's value depends upon the time range you run the query for? In other words, is there a way I can find out the max value of "DIA CIERRE" which your search is going to return? I've a workaround but I need to know the max value (and the start value).

Re: Why are the numbers not sorted as expected in my chart?

changux — Tue, 24 May 2016 18:36:31 GMT

With a subsearch like:

| stats max("DIA CIERRE")

We can obtain this one. In the last case is 11.

Re: Why are the numbers not sorted as expected in my chart?

sundareshr — Tue, 24 May 2016 18:48:32 GMT

how about something like this

....| eval "DIA CIERRE"=if(len("DIA CIERRE")=1, "0".'DIA CIERRE', 'DIA CIERRE') | chart count over "TIPO"  by "DIA CIERRE"

Then max value wouldn't matter
Full search

index="bucle_cm" sourcetype="cierres-pendientes" "Tipo Actuacion"="*" "Tipo Actuacion"!=Bajas EMPRES="*" .| eval "DIA CIERRE"=if(len("DIA CIERRE")=1, "0".'DIA CIERRE', 'DIA CIERRE') | chart count over "TIPO"  by "DIA CIERRE" | addcoltotals  | addtotals fieldname=Total

Re: Why are the numbers not sorted as expected in my chart?

changux — Tue, 24 May 2016 18:59:16 GMT

Hi.
I tried first:

| eval "DIA CIERRE"=if(len('DIA CIERRE')=1, "0".y, y) | table "DIA CIERRE"

And not results. Can you explain please your idea?

Re: Why are the numbers not sorted as expected in my chart?

somesoni2 — Tue, 24 May 2016 19:20:35 GMT

Give this a try (assuming "DIA CIERRE" always starts with 1.

index="bucle_cm" sourcetype="cierres-pendientes" "Tipo Actuacion"="*" "Tipo Actuacion"!=Bajas EMPRES="*" | chart count by "Tipo Actuacion","DIA CIERRE" limit=0 | addcoltotals  | addtotals fieldname=Total | table [search index="bucle_cm" sourcetype="cierres-pendientes" "Tipo Actuacion"="*" "Tipo Actuacion"!=Bajas EMPRES="*"  | stats max("DIA CIERRE") as t | eval search=mvrange(1,t,1) | stats list(search) as search delim="," | nomv search ]

Re: Why are the numbers not sorted as expected in my chart?

somesoni2 — Tue, 24 May 2016 19:21:07 GMT

Replace y with 'DIA CIERRE'.

Re: Why are the numbers not sorted as expected in my chart?

somesoni2 — Tue, 24 May 2016 19:24:24 GMT

This could work just fine...since max value is 31 (2 digit)
Much simpler than mine...

Re: Why are the numbers not sorted as expected in my chart?

changux — Tue, 24 May 2016 19:53:11 GMT

Works great! Awesome @somesoni2. Thanks a lot!!