topic Re: How to Chart data by month with sorting order in Splunk Search

How to chart data by month with sorting order?

jeffbat — Wed, 16 Feb 2022 17:42:35 GMT

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.

Re: Trying to Chart data by month with sorting order

PickleRick — Tue, 15 Feb 2022 19:55:25 GMT

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.

Re: How to Chart data by month with sorting order

VatsalJagani — Wed, 16 Feb 2022 05:58:24 GMT

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...

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".

Re: Trying to Chart data by month with sorting order

PickleRick — Wed, 16 Feb 2022 06:20:06 GMT

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.

Re: Trying to Chart data by month with sorting order

VatsalJagani — Wed, 16 Feb 2022 06:22:51 GMT

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

Re: How to Chart data by month with sorting order

VatsalJagani — Wed, 16 Feb 2022 06:27:34 GMT

Other way is:

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

Re: How to Chart data by month with sorting order

jeffbat — Wed, 16 Feb 2022 16:26:57 GMT

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