topic Re: How to count daily events with specific time? in Splunk Search

How to count daily events with specific time?

auaave — Tue, 09 Jan 2018 00:02:35 GMT

Hi guys,

I need to count number of events daily starting from 9 am to 12 midnight. Currently I have "earliest=@d+9h latest=now" on my search.
This works well if I select "Today" on the timepckr. However, if I select yesterday, it is still counting the events from today.

how can I fix this?

Thanks a lot!

Re: How to count daily events with specific time?

micahkemp — Tue, 29 Sep 2020 17:35:17 GMT

From the other question you posted about this:

<your search> date_hour>=9
| timechart span=1h count

Splunk parses out the timestamp components (date_month, date_mday, date_hour, etc) for each event, so these fields are available to be a part of your base search.
Or if you want the total count per day:

<your search> date_hour>=9
| timechart span=1d count

Re: How to count daily events with specific time?

rakshithreddy — Tue, 09 Jan 2018 00:58:20 GMT

Use this for yesterday - 9 am to 12 midnight
earliest=-1d@d+9h latest=-1d@d+24h

& this for day before yesterday - 9 am to 12 midnight
earliest=-2d@d+9h latest=-2d@d+24h

Thanks

Re: How to count daily events with specific time?

auaave — Tue, 09 Jan 2018 00:58:35 GMT

Hi @micahkemp,

Thanks for your reply.

I need the per hour count and the total count per day.

I used the below query for the per hour count but it's not working.

<search> date_hour>=9
| timechart span=1h count(EVENT) as "Pallet Quantity"

Re: How to count daily events with specific time?

auaave — Tue, 09 Jan 2018 01:16:19 GMT

Hi @rakshithreddy,
Thanks for your reply.
I am using a timepkr that is why I can't use "earliest".

Re: How to count daily events with specific time?

micahkemp — Tue, 09 Jan 2018 01:53:25 GMT

What was the search you ran? I'm assuming you substituted where I indicated <search>?

Re: How to count daily events with specific time?

auaave — Tue, 29 Sep 2020 17:31:06 GMT

Yes I did! 🙂
Here is the complete query, it works when I remove date_hour>=9

index=bpi_sql sourcetype=DM_H_OUTBOUND_PALLET_CREATED_R date_hour>=9
| dedup TSUID
| timechart span=1H count as IDEVENT
| rename IDEVENT AS " PALLET QUANTITY"

Re: How to count daily events with specific time?

micahkemp — Tue, 09 Jan 2018 02:08:37 GMT

I certainly don't understand what your data looks like, which may be impacting my ability to come up with a solution. A run-anywhere example of the concept is:

index=_internal date_hour>=9
| timechart span=1h count AS IDEVENT
| rename IDEVENT AS "PALLET QUANTITY"

Re: How to count daily events with specific time?

auaave — Tue, 09 Jan 2018 02:18:50 GMT

The data do have a time log - 09/01/2018 11:04:52.000
and the functions earliest /latest does work.

Re: How to count daily events with specific time?

micahkemp — Tue, 09 Jan 2018 02:25:09 GMT

This should work, though I can't explain why date_hour didn't:

index=bpi_sql sourcetype=DM_H_OUTBOUND_PALLET_CREATED_R
| timechart span=1h count AS IDEVENT
| rename IDEVENT AS "PALLET QUANTITY"
| eval hour=strftime(_time, "%H")
| search hour>=9

By the way, you shouldn't need to use rename, you can just pick the name you want in your timechart command:

index=bpi_sql sourcetype=DM_H_OUTBOUND_PALLET_CREATED_R
| timechart span=1h count AS "PALLET QUANTITY"
| eval hour=strftime(_time, "%H")
| search hour>=9

And then if you want the total for the day:

index=bpi_sql sourcetype=DM_H_OUTBOUND_PALLET_CREATED_R
| timechart span=1h count AS "PALLET QUANTITY"
| eval hour=strftime(_time, "%H")
| search hour>=9
| stats sum("PALLET QUANTITY")

Re: How to count daily events with specific time?

auaave — Tue, 09 Jan 2018 02:35:10 GMT

It worked!!
Thanks a lot for your help and also for the advise about the rename!