topic Re: How to Avoid alphabetical sorting on xyseries command? in Splunk Search

How to Avoid alphabetical sorting on xyseries command?

maria2691 — Fri, 09 Mar 2018 14:58:20 GMT

Hello Everyone

Below is my search query:

base search  | fillnull TimesRan value=1 
| bucket span=1mon _time 
| stats sum(TimesRan) as timesran by source _time 
| sort by _time asc 
| eval _time=strftime(_time,"%b - %Y") 
| xyseries source, _time, timesran 
| fillnull value=0 
| rename source as "Process"

Now the results are like,

Process Aug - 2017 Dec - 2017 Feb - 2018 Jan - 2018
hdjdd 21 16 15 15

hsfjd 0 172 143 164
hdjd 0 0 2 0

jhdjdk 0 39 54 59

Even though I have sorted the months before using xyseries, the command is again sorting the months by Alphabetical order. How do I avoid it so that the months are shown in a proper order.

Thanks
Maria Arokiaraj

Re: How to Avoid alphabetical sorting on xyseries command?

elliotproebstel — Fri, 09 Mar 2018 15:26:22 GMT

There might be a cleaner way to do this, but this should work:

base search  
| fillnull TimesRan value=1 
| bucket span=1mon _time 
| stats sum(TimesRan) as timesran by source _time 
| xyseries source, _time, timesran 
| fillnull value=0 
| rename source as "Process"
| transpose
| eval column=if(column!="Process", strftime(column,"%b - %Y"), column) 
| transpose header_field=column 
| fields - column

Re: How to Avoid alphabetical sorting on xyseries command?

maria2691 — Fri, 09 Mar 2018 15:30:11 GMT

Hello @elliotproebstel

I have tried using Transpose earlier. However it is not showing the complete results. Some of the sources and months are missing in the final result and that is the reason I went for xyseries.
Using Transpose, I get only 4 months and 5 processes which should be more than 10 each.

Thanks

Re: How to Avoid alphabetical sorting on xyseries command?

elliotproebstel — Fri, 09 Mar 2018 15:46:40 GMT

Ah, sure! The transpose command defaults to only 5 rows. Try this:

base search  
| fillnull TimesRan value=1 
| bucket span=1mon _time 
| stats sum(TimesRan) as timesran by source _time 
| xyseries source, _time, timesran 
| fillnull value=0 
| rename source as "Process"
| transpose 0
| eval column=if(column!="Process", strftime(column,"%b - %Y"), column) 
| transpose 0 header_field=column 
| fields - column

Re: How to Avoid alphabetical sorting on xyseries command?

maria2691 — Fri, 09 Mar 2018 15:49:08 GMT

Thanks a lot @elliotproebstel. It worked 🙂

Re: How to Avoid alphabetical sorting on xyseries command?

elliotproebstel — Fri, 09 Mar 2018 15:51:03 GMT

Great! Glad you got it working.

Re: How to Avoid alphabetical sorting on xyseries command?

josephro — Tue, 29 Sep 2020 22:48:38 GMT

Result:

Report Name Apr 2018 Aug 2018 Dec 2018 Feb 2018
aaaaaaaaa 3 5 3 2

It needs to be ordered by Mon Year chronologically. I tried above solution, but it doesn't work. Can you please help

Re: How to Avoid alphabetical sorting on xyseries command?

josephro — Tue, 29 Sep 2020 22:48:40 GMT

Result:

Report Name Apr 2018 Aug 2018 Dec 2018 Feb 2018
aaaaaaaaa 3 5 3 2