How to count event between different time

[vgrand2]

Hi Splunk community,

How to count number of "area" between time range to show results like these:

Between 1/1/19 to 6/30/19, there are 2 areas

Between 7/1/19 to 12/31/19, there are 2

Between 1/1/20 to 6/30/20, there are 0

Between 7/1/20 to 12/31/20, there is 1

Between 1/1/21 to 12/31/21, there is 1

After 1/1/22, there are 2

=> Raw data like this: 

Area	forecast_date
area 1	6/17/19
area 2	8/3/21
area 3	10/29/20
area 4	7/14/17
area 5	9/30/26
area 6	7/29/19
area 7	9/16/19
area 8	3/4/24
area 9	1/1/19

[somesoni2]

Try something like this

base search | eval _time=strptime(forecast_date, "%m/%d/%y")
| timechart span=6mon count as areas
| eval period="Between "+ strftime(_time,"%m/%d/%y")+ " and " + strftime(relative_time(_time,"+6mon-1d"),"%m/%d/%y")
| table period areas

[vgrand2]

@inventsekar did you mean easiest and latest are not the Splunk value? 

[vgrand2]

@inventsekar 

why don't we have both token with time rage not related to each other? I really don't get it.

[inventsekar]

Hi @vgrand2  you have to convert the date to epoch and then easily count the areas between two epoch values. 

base search | eval epochdates=strptime(forecast_date, "%m/%d/%y")
| chart count over Area BY epochdates 

there may be some modifications required, let us know how this above one goes, so that we can fine-tune the search query. thanks. 

~ Happy Splunking ~ Karma points are appreciated!

[vgrand2]

Thanks @inventsekar , but it did not group date together by time range, instead, it counts Area separately. Can you give me other solution to count and group areas together by time range?

[inventsekar]

Please try this:

base search | eval epochdates=strptime(forecast_date, "%m/%d/%y")
| chart count(Area) BY epochdates 

[vgrand2]

That one did not work.

The previous one worked but it gave the result separately area by area, not group some areas together.

| chart count over Area BY epochdates 

I tried to use between time but it did not show anything at all.

earliest=10/1/2020:00:00:00 latest=01/1/2021:23:59:59

[inventsekar]

please try streamstats:

base search | eval epochdates=strptime(forecast_date, "%m/%d/%y")
| streamstats count(Area) by epochdates
| delta count as diff_count

[vgrand2]

It did not work since it didnt group different areas from date range.

I am thinking about using Dropdown menu, but I don't know how to connect the token into the search query

<input type="dropdown" token="time_token" searchWhenChanged="true">
<label>Select any Time Period</label>
<choice value="earliest=-720d@d latest=now">Today</choice>
<choice value="earliest=now latest=+@90d@d">Next 3 months</choice>
<choice value="earliest=now latest=+@180d@d">Next 6 months</choice>
<choice value="earliest=now latest=+@270d@d">Next 9 months</choice>
<choice value="earliest=now latest=+@360d@d">Next 12 months</choice>
<choice value="earliest=now latest=+540d@d">Next 18 months</choice>
<choice value="earliest=now latest=+730d@d">Next 24 months</choice>
<default>earliest=-720d@d latest=now</default>

[inventsekar · Accepted solution]

>>> I am thinking about using Dropdown menu, but I don't know how to connect the token into the search query<<<

on the search query, you need to specify: base search earliest="$earliestToken" latest="$latestToken$"

<form>
  <label>test-token</label>
  <fieldset submitButton="false">
    <input type="dropdown" token="earliestToken" searchWhenChanged="true">
      <label>earliestToken</label>
      <choice value="-30m">30m ago</choice>
      <choice value="-60m">60m ago</choice>
    </input>
    <input type="dropdown" token="latestToken" searchWhenChanged="true">
      <label>latestToken</label>
      <choice value="now">now</choice>
      <choice value="-15m">15m ago</choice>
    </input>
  </fieldset>
  <row>
    <panel>
      <title>token</title>
      <event>
        <search>
          <query>index="_internal" earliest="$earliestToken$" latest="$latestToken$"</query>
          <earliest>-15m</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>