Need to run the below query for a month

[pradeepk50]

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

[richgalloway · Accepted solution]

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

[pradeepk50]

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

Only _time havintg the dates . under AvgCount its blank

[richgalloway]

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

[pradeepk50]

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

[richgalloway]

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

[pradeepk50]

Thank yoo much , got the results as requried.