Getting Data In

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

AruBhende
Explorer

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

 

 

Labels (2)
Tags (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
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

View solution in original post

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

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

AruBhende
Explorer

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

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

AruBhende
Explorer

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

0 Karma

AruBhende
Explorer

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
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
0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...