How to Avoid alphabetical sorting on xyseries command?

[maria2691]

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

[elliotproebstel · Accepted solution]

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

[maria2691]

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

[josephro]

I have a similar issue..
base search | stats count by Month,date_year,date_month, SLAMet, ReportNamewithextn | sort date_year date_month | fields Month ReportNamewithextn count | xyseries ReportNamewithextn Month count | fillnull value=0 | rename ReportNamewithextn as "ReportName"

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

[elliotproebstel]

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

[maria2691]

Thanks a lot @elliotproebstel. It worked 🙂

[elliotproebstel]

Great! Glad you got it working.

[josephro]

I have a similar issue..
base search | stats count by Month,date_year,date_month, SLAMet, ReportNamewithextn | sort date_year date_month | fields Month ReportNamewithextn count | xyseries ReportNamewithextn Month count | fillnull value=0 | rename ReportNamewithextn as "ReportName"

Result:

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