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!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...