How to detect outliers if there are also periods with no events

[rrovers]

I use timechart to count the events per month by department

| timechart span=1mon count AS Aantal by department

after that I want to calculate the average for each department:

this works when I do this for each department seperately.

| eventstats avg(dep1) avg(dep2)

But I want to do that with 1 command. Something like:

| eventstats avg(Aantal) by department

This doesn't work because after the timechart there is no field Aantal or department.

The reason I use timechart in stead of stats is that there are also months without values and this causes the averages are not calculated correctly. The following step after this is to detect the outliers.

Is there another way to do this?

[bowesmana]

You can do

| eventstats avg(*) as avg_*
| foreach avg_* [ eval <<FIELD>>=round(<<FIELD>>, 2) ]

which will add new fields with an avg_ prefix for each department rounded to 2 decimal places, but that will add a new field for each department with an average for that department for the whole timechart. Is that what you want?

[rrovers]

It's a step forward but not the entire solution because I want to calculate with the results.

I have this for example:

| foreach avg_*
[ eval '<<FIELD>>'=round('<<FIELD>>', 2) ]
| foreach stdev_*
[ eval '<<FIELD>>'=round('<<FIELD>>', 2) ]

I want to calculate an lowerBound and upperBound for each department with the results.

My original search was this:

| eval lowerBound=(avg-stdev*exact(3)), upperBound=(avg+stdev*exact(3))

But this isn't working for all the departments in 1 search. I don't know how to calculate with the results of your search.

[bowesmana]

There is another element you can use with foreach - <<MATCHSTR>> which is the part that matches the wildcard in the foreach statement, so you can do this - from the eventstats statement onwards

| eventstats avg(*) as avg_* stdev(*) as stdev_*
| foreach avg_* [ eval <<FIELD>>=round('<<FIELD>>', 2) ]
| foreach stdev_* [ eval <<FIELD>>=round('<<FIELD>>', 2) ]
| foreach avg_*
  [ eval "lowerBound_<<MATCHSTR>>"='<<FIELD>>'-'stdev_<<MATCHSTR>>'*exact(3),
         "upperBound_<<MATCHSTR>>"='<<FIELD>>'+'stdev_<<MATCHSTR>>'*exact(3)  ]

Note the important wrapping with single quotes of the field usage on the right hand side of the equals sign - left hand side should not have the single quote characters, but may have double quotes if your department names have characters that need escaping.

In the final foreach, the use of <<FIELD>> could be replaced with avg_<<MATCHSTR>> which would be the same thing in this case.

Hope this helps

One other powerful thing with foreach is MATCHSEGx where x is 1, 2, 3 etc and will match the xth wildcard in the foreach, e.g. 

| makeresults
| eval x_a_b_4=1, x_j_k_7=2
| foreach x_*_*_* [ eval x<<MATCHSEG1>><<MATCHSEG2>><<MATCHSEG3>>='<<FIELD>>'*<<MATCHSEG3>> ]

[yuanliu]

Filling null periods is a frequent challenge.  @woodcock helped my case way back, and posted a fairly general one recently: https://community.splunk.com/t5/Splunk-Search/How-to-fill-empty-field-values-to-0-in-Splunk/m-p/4984....  The latest posted solution on this was from @bowesmana that uses a different approach: https://community.splunk.com/t5/Splunk-Search/Need-to-generate-0-results-in-case-of-no-data-availabl...