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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...