Now, what troubles most is how to find the sum of several transactions, including a zero result.
I want to run the following searches:
index=a | stats count as a
index=b | stats count as b
index=c | stats count as c (however, the result is 0)
Calculated as the sum of three transaction numbers.
My final search is like this:
A:
index=a | stats count as a |appendcols [search index=b | stats count as b] | appendcols [search index=c | stats count as c | fillnull value=0 ] | eval total=a+b+c
B:
index=a | stats count as a |appendcols [search index=b | stats count as b] | appendcols [search index=c | stats count as c | eval coalesce(c,0) ] | eval total=a+b+c
Unfortunately, the two searches have no results. What way should I try? When C is zero, I want the total to equal the sum of A plus B.
C'log like this:
aaaa count:100 bbbb
xxxxx count:200 zzzzz
wwww count:700 yyyyy
What I want to do like this:
Calculate the sum of all counts about C,When C is zero, is equal to the sum of A plus B.
AppendCols needs a column to join your data on. Since all three of your columns are different, there is nothing to join on. If you are just trying to get the count of everything you can do the following:
index=a OR index=b OR index=C | stats count as total
If you need the results separately first, then you can use append instead of appendcols.
index=a | eval label = a | stats count by label |append [search index=b | eval label = b| stats count by label] | append [search index=c | eval label = c| stats count by label | fillnull value=0 ]
This should result in a table like thus:
label count
a 5
b 2
c 6
Hope this helps
==== EDIT ====
My second search had a typo. The second "append" was typoed to "appendcols". Now it is correct, and both are appends.
If this solution doesn't work, we might need some sample data to help.
One thing to add, to get the total after the results table you would want to do:
| stats sum(count) as Total
C'log like this:
aaaa count:100 bbbb
xxxxx count:200 zzzzz
wwww count:700 yyyyy
A & B‘log like this:
qqqq
sssss
pppp
This should result in a table like thus:
label count
a 3
b 3
c 1000
total:1006
However,when c is zero, the search have no result.I want to calculate the sum like "6".
I had a typo in my original solution. Please try the new solution.
Just mentioning again: try using append
rather than appendcols
, and don't rename your count to something else. You can then use the pipe command that @masonmorales suggested to get the total of all three searches.
| stats sum(count) as Total
Hope this helps
I am sorry,I try to slove the question with the ways you given.But it still has no result .
My search like this:
index=bancs | stats count | append [search index=apache | stats sum(bytes) | fillnull value=0 ] | stats sum(count)
In a period of time,the search like: index=apache | stats sum(bytes),has no result, actually.
But the search like:index=bancs | stats count has result return.Finally,stats sum(count) has no result,however.I need the finally result like index=bancs | stats count
Please help me,thx.
My search like this:
index=a | stats count as a |appendcols [search index=b | stats count as b] | appendcols [search index=c sourcetype=cc "status=4" OR "status=5" OR "status=6" | stats sum (count) as c | fillnull value=0 ] | eval total=a+b+c
but ,it has no result,because c is zero.Why not total=A+B???
The search index=a | stats count as a | appendcols [search index=b | stats count as b] | appendcols [search index=c | stats count as c] | eval total=a+b+c
works correctly for me.
Perhaps something like this would work for you if the total is all you seek?
index=a OR index=b OR index=c | stats count as total
I am sorry,my search just a simple description of the problem.In fact,it including transctions and some limited conditions.
index=a | stats count as a |appendcols [search index=b | stats count as b] | appendcols [search index=c sourcetype=cc "status=4" OR "status=5" OR "status=6" | stats sum (count) as c | fillnull value=0 ] | eval total=a+b+c
Assuming your appendcols searches really are that simple, why not do...
index=a OR index=b OR index = C | stats count
I am sorry,my search just a simple description of the problem.In fact,it including transctions and some limited conditions.
index=a | stats count as a |appendcols [search index=b | stats count as b] | appendcols [search index=c sourcetype=cc "status=4" OR "status=5" OR "status=6" | stats sum (count) as c | fillnull value=0 ] | eval total=a+b+c