I am trying to calculate the average number of errors by calculating events(with error)/total events.
Here is my query
...| stats count(_raw) as Total| appendcols[search .... error|rex "(?i)^[^\\.]*\\.\\w+:\\s+(?P.+)"|stats count as errors by FIELDNAME ]|eval average = errors/Total|sort -errors
FIELDNAME | errors | Total| average ================================ abc 10 def 2 ghi 2 30 0.0666 jkl 1 mno 1
FIELDNAME errors Total average ================================ abc 10 30 3.3 def 2 30 0.66 ghi 2 30 0.0666 jkl 1 30 0.33 mno 1 30 0.33
my question is why total is not calculated for all the events? what logic I am missing here.
Thank you so much.
<your search> .... error|rex "(?i)^[^\\.]*\\.\\w+:\\s+(?P.+)"|stats count AS errors by FIELDNAME | join [ ...| stats count(_raw) as Total ] | eval average = errors/Total|sort -errors
Note: joins are expensive.
Can you point out the mistake in my query and the thought process that went when writing yours.
I just went by your requirement.
Just read up the documentation about appendcols and join. Specifically, appendcols synopsis states that it "Appends the fields of the subsearch results to current results, first results to first result, second to second, etc." In your case, the subsearch returns just one event (the total stats count), and that is why it was getting appended to only one event from your main search. In case of join, all events are combined based on common field (if none specified)...
Hope this helps.