Splunk Search

Need to run the below query for a month

pradeepk50
Observer

Need to run the below query for a month

If i run the below query i will get results for the yesterday AVG count.

sourcetype="X"| bin _time span=1s | stats count by logtime | stats avg(count) | eval date=strftime(now()-86400, "%d-%m-%Y") | table date avg(count) | eval 'avg(count)'=round('avg(count)',0) | fields - avg(count)

Output:

date 'avg(count)'
12-05-2020 11
But i want to get the AVG count for a month or 6 months.

Please suggest me to update this query to get the results as expected for 6 months , each day AVG count i would need.

Thanks
Pradeep

Tags (2)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

The example query computes a single average count over the entire time window and then hard-codes the date to be yesterday. The result says it's for yesterday, but it could be 6 months.

Try timechart, instead.

sourcetype="X" earliest=-6mon@mon 
| bucket span=1s _time
| stats count by _time
| timechart span=1d avg(count) as AvgCount
| eval AvgCount = round(AvgCount, 0)
| table _time AvgCount
---
If this reply helps you, Karma would be appreciated.

View solution in original post

0 Karma

richgalloway
SplunkTrust
SplunkTrust

The example query computes a single average count over the entire time window and then hard-codes the date to be yesterday. The result says it's for yesterday, but it could be 6 months.

Try timechart, instead.

sourcetype="X" earliest=-6mon@mon 
| bucket span=1s _time
| stats count by _time
| timechart span=1d avg(count) as AvgCount
| eval AvgCount = round(AvgCount, 0)
| table _time AvgCount
---
If this reply helps you, Karma would be appreciated.
0 Karma

pradeepk50
Observer

Thank you , but the AvgCount is not displayed after using the provided query.

Only _time havintg the dates . under AvgCount its blank

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Dumb error on my part. You didn't have count field so timechart had nothing to average. Try the modified answer.

---
If this reply helps you, Karma would be appreciated.
0 Karma

pradeepk50
Observer

now hte AvgCount shwing value but its not correct.
as if run the query for one day the AVGCount will give as 10-14

but here i can see the number above 1k.

example:
_time AvgCount
2020-04-01 782757
2020-04-02 849822

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Adjust the span in the bucket command to change how many events get counted and how many counts are averaged.

---
If this reply helps you, Karma would be appreciated.
0 Karma

pradeepk50
Observer

Thank yoo much , got the results as requried.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

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 ...