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 |
>>> 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>
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
@inventsekar did you mean easiest and latest are not the Splunk value?
why don't we have both token with time rage not related to each other? I really don't get it.
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!
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?
Please try this:
base search | eval epochdates=strptime(forecast_date, "%m/%d/%y")
| chart count(Area) BY epochdates
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
please try streamstats:
base search | eval epochdates=strptime(forecast_date, "%m/%d/%y")
| streamstats count(Area) by epochdates
| delta count as diff_count
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>
>>> 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>