topic Re: How to count event between different time in Splunk Search

How to count event between different time

vgrand2 — Thu, 15 Oct 2020 21:45:13 GMT

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

Re: How to count event between different time

inventsekar — Thu, 15 Oct 2020 22:31:13 GMT

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!

Re: How to count event between different time

vgrand2 — Thu, 15 Oct 2020 22:51:34 GMT

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?

Re: How to count event between different time

inventsekar — Thu, 15 Oct 2020 23:08:39 GMT

Please try this:

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

Re: How to count event between different time

vgrand2 — Thu, 15 Oct 2020 23:18:11 GMT

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

Re: How to count event between different time

inventsekar — Thu, 15 Oct 2020 23:32:36 GMT

please try streamstats:

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

Re: How to count event between different time

vgrand2 — Fri, 16 Oct 2020 07:38:50 GMT

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>

Re: How to count event between different time

inventsekar — Fri, 16 Oct 2020 08:38:42 GMT

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

Re: How to count event between different time

vgrand2 — Fri, 16 Oct 2020 20:14:39 GMT

@inventsekar

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

Re: How to count event between different time

vgrand2 — Fri, 16 Oct 2020 20:20:08 GMT

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

Re: How to count event between different time

somesoni2 — Fri, 16 Oct 2020 20:22:53 GMT

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