How to search for Count by day by hour or half hour?

[AruBhende]

I need to get count of events by day by hour or half-hour using a field in splunk log which is a string whose value is date - e.g. eventPublishTime: 2022-05-05T02:20:40.994Z

I tried some variations of below query, but it doesn't work.  How should I formulate my query?

index=our-applications  env=prod
| eval publishTime=strptime(eventPublishTime, "%Y-%m-%dT%H:%M:%SZ")
| convert timeformat="%H:%M" ctime(publishTime) AS PublishHrMin
| convert timeformat="%Y-%m-%d" ctime(_time) AS ReceiptDate | stats c(ReceiptDate) AS ReceiptDateCount by ReceiptDate, parentEventName,, PublishHrMin

Thank you

[ITWhisperer]

You have the wrong time format string for parsing eventPublishTime. Try this

| eval publishTime = strptime(eventPublishTime, "%Y-%m-%dT%H:%M:%S.%3N%Z")

[AruBhende]

Thanks.  But how will I do a count by hour by day?

[ITWhisperer]

What you have should work with the change to parsing

index=our-applications  env=prod
| eval publishTime=strptime(eventPublishTime, "%Y-%m-%dT%H:%M:%S.3N%Z")
| convert timeformat="%H:%M" ctime(publishTime) AS PublishHrMin
| convert timeformat="%Y-%m-%d" ctime(_time) AS ReceiptDate
| stats c(ReceiptDate) AS ReceiptDateCount by ReceiptDate, parentEventName, PublishHrMin

[AruBhende]

This is not returning any data - I also tried changing the _time to publish date like below

index=our-applications env=prod
| eval publishTime=strptime(eventPublishTime, "%Y-%m-%dT%H:%M:%S.3N%Z") 
| bin span=30m publishTime 
| convert timeformat="%H:%M" ctime(publishTime) AS PublishHrMin 
| convert timeformat="%Y-%m-%d" ctime(publishTime) AS PublishDate 
| stats c(PublishHrMin) AS PublishHrMinCount by PublishDate, parentEventName, PublishHrMinMinCount by PublishDate, parentEventName, PublishHrMin

Am I missing something or doing something wrong ?

Thanks

[AruBhende]

Thanks @ITWhisperer .  After your earlier suggestion for correcting the format, I got the query to show me the hour / minute.  But I want to count by that hour / minute in half hour range.

i.e. if PublishHrMin is between say 10:30 and 11:00 - then they should be counted together and be able to display the range as 10:30-11:00

Is it possible to do it that way?  My current count by _time  per day

Thanks again

[ITWhisperer · Accepted solution]

index=our-applications  env=prod
| eval publishTime=strptime(eventPublishTime, "%Y-%m-%dT%H:%M:%S.3N%Z")
| bin span=30m publishTime
| convert timeformat="%H:%M" ctime(publishTime) AS PublishHrMin
| convert timeformat="%Y-%m-%d" ctime(_time) AS ReceiptDate
| stats c(ReceiptDate) AS ReceiptDateCount by ReceiptDate, parentEventName, PublishHrMin