Dashboards & Visualizations

How can my search consider all days?

anooshac
Communicator

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?

Labels (3)
Tags (3)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

anooshac
Communicator

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

anooshac
Communicator

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

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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?

0 Karma

anooshac
Communicator

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?

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

anooshac
Communicator

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

isoutamo
SplunkTrust
SplunkTrust

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 

0 Karma

anooshac
Communicator

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

0 Karma

isoutamo
SplunkTrust
SplunkTrust

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

anooshac
Communicator

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.

 

0 Karma

isoutamo
SplunkTrust
SplunkTrust

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.

0 Karma

anooshac
Communicator

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?

0 Karma
Get Updates on the Splunk Community!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...