topic Re: How to get the month name for selecting previous month via date_month? in Splunk Search

How to get the month name for selecting previous month via date_month?

aaron_sakovich — Thu, 05 Apr 2018 21:53:31 GMT

We've got the following search:

tag=PeopleCounters earliest=-13mon@mon latest=@mon date_month=March
| chart sum(count) as Traffic by date_year

This winds up looking great, giving us two bars -- one each for a month, year over year (e.g., March 2017 vs. March 2018).

    date_year  Traffic
    2017       109113
    2018       126805

However, we want the date_month in the search to be variable so that we can put this in a dashboard panel, so the search only pulls data that happened in March (or next month, April).
Suggestions on how to evaluate what the name of the previous month was and embed that into the search?

Re: How to get the month name for selecting previous month via date_month?

sravankaripe — Tue, 29 Sep 2020 18:53:26 GMT

tag=PeopleCounters earliest=-13mon@mon latest=@mon date_month=March
| chart sum(count) as Traffic by date_year | where date_year>2016

Re: How to get the month name for selecting previous month via date_month?

aaron_sakovich — Thu, 05 Apr 2018 22:17:32 GMT

Nice try, but I've already limited the range of years with the -13mon@mon. Instead, I need a variable with the month name so that I can search for date_month=$lastmonth$.
Hope that makes more sense.
Thanks.

Re: How to get the month name for selecting previous month via date_month?

DalJeanis — Fri, 06 Apr 2018 01:56:54 GMT

Okay, so you have two different items here - the search time range, and the year-over-year comparison.

This gets you all the data for the last 24 months.

<search id="monthbase">
   <query>
   tag=PeopleCounters 
   | eval month_num=strftime(_time,"%m")
   | stats  max(date_month) as date_month sum(count) as Traffic by date_year month_num 
   </query>
   <earliest>-24mon@mon</earliest> 
   <latest>@mon</latest>
</search>

The above search gives you a nice little table that has this layout. | table date_year month_num date_month Traffic You do not need the table code, it's just to show you the record format at that point. Put the above in a base search in your dashboard, and let them choose the month from a dropdown. Populate the dropdown with that same base search after passing it through this...

<input type="dropdown" token="month_nbr">
   <search base="monthbase">
   <query>
    | stats max(date_month) as date_month by month_num
   </query>
</search>
<fieldForLabel>date_month</fieldForLabel>
<fieldForValue>month_num</fieldForValue>
</input>

The above dropdown will set the token $month_nbr$ to the selected value for month_num. (We only changed the name so you can see which is which.)

Finally, in the dashboard panel, use a postprocess query/filter, with the same base search and a filter on the token selected from the dropdown to select only the month they want, which will give you exactly two values, just like your original search.

   <search base="monthbase">
     <query>
     | where month_num = $month_nbr$
     | chart sum(Traffic) as Traffic by date_year
     </query>    
   </search>

Re: How to get the month name for selecting previous month via date_month?

DalJeanis — Fri, 06 Apr 2018 02:06:11 GMT

If you wanted, you could set a token off the base search in order to set a default for the dropdown. You'd do this by grabbing the last record.

<search base="monthbase">
  <query>
  | tail 1
  | table month_num 
  </query>    
  <done>
        <set token="month_nbr">$result.month_num$</set>
   </done>
</search>

Re: How to get the month name for selecting previous month via date_month?

aaron_sakovich — Fri, 06 Apr 2018 16:49:03 GMT

Nope. Still way too complex. The users don't need to be able to select a month, we just need to be able to show them last month's data over the same month a year ago. We have a very seasonal business, and we want them to see how things have changed year over year for the most recent month.
That's why I need to be able to limit the search to just the specified month name/number. If this were June of 2018, I want to display the sum of the counts for May of 2018 AND May of 2017.
Accordingly, I went on a dig and read a lot more documentation and examples, and came up with this:

tag=PeopleCounters earliest=-13mon@mon latest=@mon 
 | eval lastMonth=strftime(relative_time(now(),"-1mon@mon"),"%B")
 | where match(date_month,lower(lastMonth))
 | chart sum(count) as Traffic by date_year

This does what I wanted. Thanks for trying!

Re: How to get the month name for selecting previous month via date_month?

DalJeanis — Sun, 08 Apr 2018 18:07:25 GMT

Sure. So, you really have users who never ever need to see the prior month? Interesting.

Glad you got what you needed.

Re: How to get the month name for selecting previous month via date_month?

aaron_sakovich — Mon, 09 Apr 2018 13:09:31 GMT

Oh, sure they do, but that’s easy, and in a different panel on their dashboard; they get to see the whole of the previous 3 years with an annual timewrap. This single month over month is just a single panel on that dashboard that they’re used to seeing from our old manually collected and processed stats report.

Re: How to get the month name for selecting previous month via date_month?

niketn — Tue, 29 Sep 2020 18:57:20 GMT

@aaron_sakovich, try the following dashboard. It uses a dummy search to set the previous month as token to be used in the base search of your query. This way you will be filtering date_month as previous month in your base search itself. This would run faster as in your current search you are pulling last 13 months data and then filtering 2 months data afterward.

<dashboard>
  <label>Filter records for previous month over year</label>
  <search>
    <query>| makeresults
      | eval prevMonth=lower(strftime(relative_time(_time,"-1mon@d"),"%B"))
      | fields - _time
    </query>
    <progress>
      <set token="tokPrevMonth">$result.prevMonth$</set>
    </progress>
  </search>
  <row>
    <panel>
      <title>Previous Month: $tokPrevMonth$</title>
      <table>
        <search>
          <query>tag=PeopleCounters  date_month="$tokPrevMonth$"
| chart count as Traffic by date_year</query>
          <earliest>-13mon@mon</earliest>
          <latest>@mon</latest>
        </search>
        <option name="refresh.display">progressbar</option>
      </table>
    </panel>
  </row>
</dashboard>

Please try out and confirm.