How to count daily events with specific time?

[auaave]

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!

[micahkemp · Accepted solution]

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]

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

[rakshithreddy]

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

[auaave]

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

[micahkemp]

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

[auaave]

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"

[micahkemp]

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

[auaave]

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"

[micahkemp]

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"

[auaave]

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