Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Splunk Search

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Splunk Answers
- :
- Using Splunk
- :
- Splunk Search
- :
- How to find the sum of several transactions, inclu...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark Topic
- Subscribe to Topic
- Mute Topic
- Printer Friendly Page

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

dovelsh12223621

Path Finder

03-25-2015
06:08 AM

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.

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

dovelsh12223621

Path Finder

03-25-2015
06:57 AM

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.

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

aholzer

Motivator

03-25-2015
06:39 AM

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.

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

masonmorales

Influencer

03-25-2015
07:18 AM

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

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

masonmorales

Influencer

03-25-2015
07:25 AM

| stats sum(count) as Total

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

dovelsh12223621

Path Finder

03-25-2015
07:51 AM

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

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

aholzer

Motivator

03-25-2015
11:06 AM

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

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

dovelsh12223621

Path Finder

03-25-2015
06:25 PM

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.

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

dovelsh12223621

Path Finder

03-25-2015
07:47 AM

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

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

richgalloway

SplunkTrust

03-25-2015
06:35 AM

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.

If this reply helps you, Karma would be appreciated.

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

dovelsh12223621

Path Finder

03-25-2015
06:50 AM

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

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

masonmorales

Influencer

03-25-2015
06:32 AM

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

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

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

dovelsh12223621

Path Finder

03-25-2015
06:49 AM

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

Get Updates on the Splunk Community!

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

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

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