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!
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")
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")
It worked!!
Thanks a lot for your help and also for the advise about the rename!
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
Hi @rakshithreddy,
Thanks for your reply.
I am using a timepkr that is why I can't use "earliest".
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
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"
What was the search you ran? I'm assuming you substituted where I indicated <search>
?
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"
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"
The data do have a time log - 09/01/2018 11:04:52.000
and the functions earliest /latest does work.