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

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

 

 

 

Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

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