topic Re: stats avg() does not consider all days. in Dashboards & Visualizations

How can my search consider all days?

anooshac — Tue, 18 Oct 2022 13:54:25 GMT

Hi all, i am finding average per day for some of the data. My search looks like this.

| bucket _time span=1d | stats distinct_count(Task) as counts by _time Group| stats avg(counts) as AverageCountPerDay by Group

I was able to get the results but the problem is the stats avg does not consider the day in which there is no "Task". I want my search to consider all the days. How can i achieve this?

Re: stats avg() does not consider all days.

isoutamo — Thu, 13 Oct 2022 08:13:48 GMT

you can try this

If needed you could drop first fill null away if you don't need it.

r. Ismo

Re: stats avg() does not consider all days.

anooshac — Thu, 13 Oct 2022 08:30:53 GMT

Hi @isoutamo , tried it but the result i am getting is same.. It does not consider the day in which there is no "Task".

Re: stats avg() does not consider all days.

isoutamo — Thu, 13 Oct 2022 09:02:42 GMT

Sorry my misunderstanding. Maybe this is what you are looking for

Re: stats avg() does not consider all days.

anooshac — Thu, 13 Oct 2022 09:28:12 GMT

Hi @isoutamo , still it is giving different values.

For finding out the average separately i use the following query. This is finding out the average separately for the "Group".

|timechart span=1d distinct_count(Task) as Count| stats avg(Count) as Average

My data has different "Group", so i want to create a table for the Group and the averages. But the timechart is not working if used for the groups.

Re: stats avg() does not consider all days.

isoutamo — Thu, 13 Oct 2022 10:53:46 GMT

Why you don't use

| timechart span=1d distinct_count(Task) as Count by Group | stats avg(*) as Average_* | foreach * [ eval <<FIELD>> = round('<<FIELD>>', 2)]

With my test data I get same results with this and my previous example. Please check that those field names are correct as I tested it with different events.

Re: stats avg() does not consider all days.

ITWhisperer — Thu, 13 Oct 2022 11:13:19 GMT

Try something like this

Re: stats avg() does not consider all days.

anooshac — Tue, 18 Oct 2022 10:52:16 GMT

hi, @ITWhisperer i tried and the table only has averages for few Groups. It doesn't show averages for all the groups.

Re: stats avg() does not consider all days.

anooshac — Tue, 18 Oct 2022 11:04:30 GMT

Hi , @isoutamo tried this and i am not able to produce the average for all the Groups. Also is there a way to put these values as Group and Average as columns in a table?

Re: stats avg() does not consider all days.

ITWhisperer — Tue, 18 Oct 2022 20:39:24 GMT

Can you share you full search? (In a code block </> preferably)

Re: stats avg() does not consider all days.

anooshac — Wed, 19 Oct 2022 08:01:48 GMT

Hi @ITWhisperer , This is my sample data. This is only for one Task_no. There are many Tasks under a Group, likewise there are many Groups also.

"Task_no":"5",

"Group": "G1",

"EXECUTION_DATE":1648081994535,

"STATUS":"FAILURE",

"DURATION":1951628

And this is the search i'm using,

| bucket _time span=1d
| stats distinct_count(Task) as counts by _time Group| stats avg(counts) as AverageCountPerDay by Group

Actually i have table which combines all some of the calculated data using JOIN. The query is too big so only posted the search for the average. I have been trying to calculate average and trying to combine the data to my table. But the query which i used does not give the values correctly. I find the averages separately for a Group like this,

|timechart span=1d distinct_count(Task) as Count| stats avg(Count) as Average

Re: stats avg() does not consider all days.

ITWhisperer — Wed, 19 Oct 2022 09:15:50 GMT

Timechart will fill in the blanks in your time range so you could end up with zeroes for some days. This will bring the average down, when compared to taking the average of the non-zero counts that stats is giving you. Is this the reason for the "incorrect" averages?

Re: stats avg() does not consider all days.

anooshac — Tue, 25 Oct 2022 04:40:06 GMT

Yes. I guess that is the reason for the incorrect values. Any idea in how to resolve this? Or is it possible for to find averages for all the Groups together by any other way?

Re: stats avg() does not consider all days.

ITWhisperer — Tue, 25 Oct 2022 05:31:52 GMT

I am not sure what you are trying to resolve - do you want the average from stats (without the days with zeroes being included) or the average from timechart (with the days with zeroes being included)? (The answer is in the question! 😀)

Re: stats avg() does not consider all days.

anooshac — Tue, 25 Oct 2022 08:22:47 GMT

I want to find the averages together with the days with zeroes being included..

Re: stats avg() does not consider all days.

ITWhisperer — Tue, 25 Oct 2022 08:34:24 GMT

Try this