topic Re: How to find the sum of several transactions, including a zero result? in Splunk Search

How to find the sum of several transactions, including a zero result?

dovelsh12223621 — Wed, 25 Mar 2015 13:08:47 GMT

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

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.

Re: How to find the sum of several transactions, including a zero result?

masonmorales — Wed, 25 Mar 2015 13:32:16 GMT

Assuming your appendcols searches really are that simple, why not do...

index=a OR index=b OR index = C | stats count

Re: How to find the sum of several transactions, including a zero result?

richgalloway — Wed, 25 Mar 2015 13:35:16 GMT

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

Re: How to find the sum of several transactions, including a zero result?

aholzer — Wed, 25 Mar 2015 13:39:57 GMT

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.

Re: How to find the sum of several transactions, including a zero result?

dovelsh12223621 — Wed, 25 Mar 2015 13:49:31 GMT

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

Re: How to find the sum of several transactions, including a zero result?

dovelsh12223621 — Wed, 25 Mar 2015 13:50:24 GMT

Re: How to find the sum of several transactions, including a zero result?

dovelsh12223621 — Wed, 25 Mar 2015 13:57:17 GMT

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.

Re: How to find the sum of several transactions, including a zero result?

masonmorales — Wed, 25 Mar 2015 14:18:40 GMT

If this solution doesn't work, we might need some sample data to help.

Re: How to find the sum of several transactions, including a zero result?

masonmorales — Wed, 25 Mar 2015 14:25:17 GMT

One thing to add, to get the total after the results table you would want to do:
| stats sum(count) as Total

Re: How to find the sum of several transactions, including a zero result?

dovelsh12223621 — Wed, 25 Mar 2015 14:47:35 GMT

Re: How to find the sum of several transactions, including a zero result?

dovelsh12223621 — Wed, 25 Mar 2015 14:51:18 GMT

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".

Re: How to find the sum of several transactions, including a zero result?

aholzer — Wed, 25 Mar 2015 18:06:03 GMT

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

Re: How to find the sum of several transactions, including a zero result?

dovelsh12223621 — Thu, 26 Mar 2015 01:25:58 GMT

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.