Splunk Search

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

dovelsh12223621
Path Finder

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.

Tags (3)
0 Karma

dovelsh12223621
Path Finder

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.

0 Karma

aholzer
Motivator

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.

masonmorales
Influencer

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

0 Karma

masonmorales
Influencer

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

dovelsh12223621
Path Finder

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

0 Karma

aholzer
Motivator

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

0 Karma

dovelsh12223621
Path Finder

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.

0 Karma

dovelsh12223621
Path Finder

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???

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, Karma would be appreciated.
0 Karma

dovelsh12223621
Path Finder

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

0 Karma

masonmorales
Influencer

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

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

dovelsh12223621
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...