Splunk Search

How to Avoid alphabetical sorting on xyseries command?

maria2691
Path Finder

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

0 Karma
1 Solution

elliotproebstel
Champion

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

View solution in original post

elliotproebstel
Champion

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
Path Finder

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

0 Karma

josephro
Observer

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

0 Karma

elliotproebstel
Champion

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
Path Finder

Thanks a lot @elliotproebstel. It worked 🙂

0 Karma

elliotproebstel
Champion

Great! Glad you got it working.

josephro
Observer

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

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud's AI Assistant in Action Series: Observability as Code

This is the sixth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Answers Content Calendar, July Edition I

Hello Community! Welcome to another month of Community Content Calendar series! For the month of July, we will ...

Secure Your Future: Mastering Upgrade Readiness for Splunk 10

Spotlight: The Splunk Health Assistant Add-On  The Splunk Health Assistant Add-On is your ultimate companion ...