How can my search consider all days?

[anooshac]

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?

[ITWhisperer]

Try something like this

| bucket _time span=1d
| chart dc(Task) as counts by _time Group
| eventstats min(_time) as start
| eval bin=floor((_time-start)/(60*60*24))
| makecontinuous bin
| filldown start
| eval _time=start+(bin*60*60*24)
| fillnull value=0
| fields - bin start
| untable _time Group counts
| stats avg(counts) as AverageCountPerDay by Group

[anooshac]

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

[ITWhisperer]

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

[anooshac]

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

[ITWhisperer]

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?

[anooshac]

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?

[ITWhisperer]

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! 😀)

[anooshac]

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

[ITWhisperer]

Try this

| bucket _time span=1d
| chart dc(Task) as counts useother=f limit=0 by _time Group
| eventstats min(_time) as start
| eval bin=floor((_time-start)/(60*60*24))
| makecontinuous bin
| filldown start
| eval _time=start+(bin*60*60*24)
| fillnull value=0
| fields - bin start
| untable _time Group counts
| stats avg(counts) as AverageCountPerDay by Group

[isoutamo]

Hi

you can try this

| bucket _time span=1d
| stats distinct_count(Task) as counts by _time Group
| makecontinuous _time span=1d
| fillnull value=NULL Group
| fillnull value=0 counts
| stats avg(counts) as AverageCountPerDay by Group

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

r. Ismo 

[anooshac]

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

[isoutamo]

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

| bucket _time span=1d
| stats distinct_count(Task) as counts by _time Group
| makecontinuous _time span=1d
| eventstats values(Group) as grp
| eval Group=coalesce(Group, grp)
| fields - grp
| fillnull value=0 counts
| mvexpand Group
| stats avg(counts) as AverageCountPerDay by Group

[anooshac]

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.

[isoutamo]

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.

[anooshac]

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?