topic Re: Sorting X axis by month in Splunk Search

Sorting X axis by month

mcwomble — Wed, 21 Jul 2010 17:36:18 GMT

I have the following search which displays amounts of records by month (X-axis).

index="billing" suspededrecords | eval monthnum = if(month="JAN",1,if(month="FEB",2,if(month="MAR",3,if(month="APR",4,if(month="MAY",5,if(month="JUN",6,if(month="JUL",7,if(month="AUG",8,if(month="SEP",9,if(month="OCT",10,if(month="NOV",11,if(month="DEC",0,0)))))))))))) |sort num(monthnum) | chart max(monthsuspense) as "Suspense" by month

However the X-axis is always sorted month name alaphabetically which is confusing. I put in the the above eval and sort statements to try and create fields that would allow the months to be displayed in the correct order, but it doesn't work. I'm starting to think that I starting to complicate the issue and wondered if there is an easy way to achieve a correct listing of months.

Re: Sorting X axis by month

Paolo_Prigione — Wed, 21 Jul 2010 20:31:16 GMT

I'd suggest you to use timechart, as you are plotting against time, and its bucketing options, to compute the max() over the entire month:

timechart span="1mon" max(monthsuspense) as Suspense

After your comment, I'm editing the answer...

I've noticed that you issued the chart command using "month" and not "monthnum" as X-axis. Was this intentional?

If noy, you might then want to:

replace the monthnumber generation with something more compact;
use the chart "over monthnum"

So that would end up as:

index="billing" suspededrecords | eval monthnum=strftime(strptime("2010/" + month + "/01", "%Y/%b/%d"),"%m") | chart max(monthsuspense) over monthnum span=1mon

Re: Sorting X axis by month

mcwomble — Thu, 22 Jul 2010 02:46:05 GMT

Unfortunately it doesn't seem to be as simple as creating a time chart. The data that generates this graph comes in once a day from a dbquery which has the amounts defined by month and is effectively a snapshot at one moment in time. If reprocessing takes place the historical amount per month can change. So really the month on the x-axis is just a field value rather than a distinct time event.

Re: Sorting X axis by month

Jason — Wed, 04 Aug 2010 23:47:18 GMT

I wrestled with this for a bit when trying to aggregate a particular day of the week's values over a month, and display the "average week" for the month. I could count by date_wday, but it the x axis would always be sorted lexigraphically. Very confusing.

Here's my solution: use the eval to make a field with both a number for ordering as well as the name for easy viewing:

search ..... | eval Day = if(date_wday="sunday","1. Sunday",if(date_wday="monday","2. Monday",if(date_wday="tuesday","3. Tuesday",if(date_wday="wednesday","4. Wednesday",if(date_wday="thursday","5. Thursday",if(date_wday="friday","6. Friday",if(date_wday="saturday","7. Saturday",0)))))))| chart count over Day

Re: Sorting X axis by month

assaphmehr — Thu, 31 May 2012 23:44:03 GMT

Jason,

Here's a cleaner eval that does the same:

... | eval Day=strftime(_time, "%u. %A") | ...

Hope this helps makes it more readable 🙂