Splunk Search

How to count event between different time

vgrand2
Explorer

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: 

Areaforecast_date
area 16/17/19
area 28/3/21
area 310/29/20
area 47/14/17
area 59/30/26
area 67/29/19
area 79/16/19
area 83/4/24
area 91/1/19
Labels (3)
0 Karma
1 Solution

inventsekar
Super Champion

 

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

 

 

 

View solution in original post

somesoni2
Revered Legend

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
Explorer

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

0 Karma

vgrand2
Explorer

@inventsekar 

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

0 Karma

inventsekar
Super Champion

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!

0 Karma

vgrand2
Explorer

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?

0 Karma

inventsekar
Super Champion

Please try this:

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

 

0 Karma

vgrand2
Explorer

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

Tags (1)
0 Karma

inventsekar
Super Champion

please try streamstats:

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



0 Karma

vgrand2
Explorer

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>

0 Karma

inventsekar
Super Champion

 

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

 

 

 

View solution in original post

.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!