Splunk Search

How to chart data by month with sorting order?

jeffbat
Path Finder

I am running into an issue when I am trying to get a chart to populate with the data as I am expecting.

I am running a search where the data is from IIS logs where it parsing out the referrer_stem  and then counting the total of each referrer_stem per month.  I am also splitting out the month field by both the shortname and numerical value (for testing each on the sort).

this is the end portion of my search:

 

 

| eval date_month=strftime(_time, "%b")
| eval number_month=strftime(_time, "%m")
| chart count BY referrer_stem, date_month
| sort 10 - count

 

 


The issue I am having is if I do this with date_month field then it shows columns or bars out of order (i.e. it shows as Feb Jan) where as if I do it by number_month it is correct (i.e. 01 02). 

I want it to show in the correct order but using the month's shortname.

I did try to use a case statement when using number_month but that doesn't work because after the chart command the field name seems to not exist (or I just don't know how to access the right name).

Any help  or insight on this would be greatly appreciated.

 

Labels (2)
Tags (1)
0 Karma
1 Solution

VatsalJagani
SplunkTrust
SplunkTrust

Try something like:

| eval number_month=strftime(_time, "%m")
| chart count BY referrer_stem, number_month
| sort 10 - count
| rename "01" as "Jan", "02" as "Feb", and so on...

VatsalJagani_0-1644991039591.png

FYI, your sort - count will not work because as you can see in the screenshot when you will use the chart command, you don't have a column called "count".

View solution in original post

0 Karma

jeffbat
Path Finder

Thank you everyone for all of the help.  This is the solution I ended up utilizes as it was pointed out to me that we would be showing some months from previous year and would want to have them ordered correctly so needed to add in the year.

| bin _time span=1mon 
| eval c_time=strftime(_time,"%Y-%m")
| chart count BY referrer_stem, c_time
| addtotals 
| rename "*-01" AS "*-Jan" "*-02" AS "*-Feb" "*-03" AS "*-Mar" "*-04" AS "*-Apr" "*-05" AS "*-May" "*-06" AS "*-Jun" "*-07" AS "*-Jul" "*-08" AS "*-Aug" "*-09" AS "*-Sep" "*-10" AS "*-Oct" "*-11" AS "*-Nov" "*-12" AS "*-Dec"
| sort 10 - Total
| fields - Total

I also solved the issue of the sorting by totaling the rows and using that column to do the sort (this ALSO solved the fact of making sure I had to top used referrer_stems) and then just removing the field after the sort

 

0 Karma

VatsalJagani
SplunkTrust
SplunkTrust

Try something like:

| eval number_month=strftime(_time, "%m")
| chart count BY referrer_stem, number_month
| sort 10 - count
| rename "01" as "Jan", "02" as "Feb", and so on...

VatsalJagani_0-1644991039591.png

FYI, your sort - count will not work because as you can see in the screenshot when you will use the chart command, you don't have a column called "count".

0 Karma

VatsalJagani
SplunkTrust
SplunkTrust

Other way is:

| eval date_month=strftime(_time, "%b")
| chart count BY referrer_stem, date_month
| table referrer_stem, Jan, Feb, Mar, .....
0 Karma

PickleRick
SplunkTrust
SplunkTrust

Working with time strings is tricky. How should splunk know that "feb" shoud be after "jan"? It's before in lexicographical order.

If you want to sort by time, leave it as is - as a unix timestamp. Just do a fieldformat so it's displayed the way you want. If you want only some part of the date (like the month in your case), do binning.

0 Karma

PickleRick
SplunkTrust
SplunkTrust

Ahh, I missed that it's about _column_order.

That's more tricky. If your column set is constant you simply add

| table columnA columnD columnC ...

at the end of your search and you have your order.

If you however want columns in a particular order but don't know the exact names, transposing, sorting and transposing back is the way to go.

0 Karma

VatsalJagani
SplunkTrust
SplunkTrust

Yup, I missed a simpler solution. The table should also solve.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...