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!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...