Splunk Search

How to count daily events with specific time?

auaave
Communicator

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!

0 Karma
1 Solution

micahkemp
Champion

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

View solution in original post

micahkemp
Champion

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

auaave
Communicator

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

0 Karma

rakshithreddy
Explorer

Hi

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

0 Karma

auaave
Communicator

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

0 Karma

micahkemp
Champion

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

auaave
Communicator

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"
0 Karma

micahkemp
Champion

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

0 Karma

auaave
Communicator

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"

0 Karma

micahkemp
Champion

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"
0 Karma

auaave
Communicator

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

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...