topic Re: Addtotals possible on stats list/count search? in Splunk Search

Addtotals possible on stats list/count search?

jwalzerpitt — Fri, 29 Apr 2016 17:38:51 GMT

I have the following search results and would like to add the count totals up. If I add the following line:

|addtotals fieldname=Blocks

I see the total, but the issue is for items with only one line, their count is doubled (for example, the second line with a count of 2899 has a total of 5798. How do I make sure single counts in the list don't get doubled when adding the totals?

Thx

Broken scenario using addtotals command that doubles the count for IPs that have only one domain listed:

src_ip     Domain                   count     Blocks
0.0.0.0    fox169.tlscdn.com         2899       5798

1.1.1.1    video.cynogage.com        3643       3886
           serve.blutonic-ads.com     160
           conf.wsm.360.cn             71
           sconf.f.360.cn              11
           quri.f.360.cn                1

2.2.2.2    video.cynogage.com        2301       2305
           rdcdn.com                    4

3.3.3.3    video.cynogage.com         594       1188

4.4.4.4    video.cynogage.com         576       1152

5.5.5.5    video.cynogage.com         837        873
           serve.blutonic-ads.com      36

6.6.6.6    video.cynogage.com         663        705
           serve.blutonic-ads.com      42

7.7.7.7    video.cynogage.com         563        566
           serve.blutonic-ads.com       3

8.8.8.8    lb.cdn.e-adguardian.com    111        517
           lb.cdn.d-adguardian.com    110
           lb.cdn.a-adguardian.com    109
           lb.cdn.c-adguardian.com    102
           lb.mspt4-01.com             79
           lb.msps4-01.com              6

9.9.9.9    video.cynogage.com         506        516
           dopefile.com                 6
           us-nj-e3.rtbauction.com      4

Using the search of:

index=indexname
| stats count BY domain, src_ip
| sort 0 -count 
| stats list(domain) AS Domain, list(count) AS count, sum(count) AS total BY src_ip
| sort 0 -total | head 10
| fields - total
| addtotals fieldname=Blocks

I get the following:

Domain                   count     Blocks
video.cynogage.com        3643
serve.blutonic-ads.com     160
conf.wsm.360.cn             71
sconf.f.360.cn              11
quri.f.360.cn                1

fox169.tlscdn.com         2899       2899

video.cynogage.com        2301
rdcdn.com                    4

video.cynogage.com         837
serve.blutonic-ads.com      36

video.cynogage.com         663
serve.blutonic-ads.com      42

video.cynogage.com         594        594

video.cynogage.com         576        576

video.cynogage.com         563
serve.blutonic-ads.com       3

video.cynogage.com         506
dopefile.com                 6
us-nj-e3.rtbauction.com      4

lb.cdn.e-adguardian.com    111
lb.cdn.d-adguardian.com    110
lb.cdn.a-adguardian.com    109
lb.cdn.c-adguardian.com    102
lb.mspt4-01.com             79
lb.msps4-01.com              6

Re: Addtotals possible on stats list/count search?

woodcock — Fri, 29 Apr 2016 17:50:44 GMT

I completely do not understand your scenario; show us working and broken examples.

Re: Addtotals possible on stats list/count search?

jwalzerpitt — Fri, 29 Apr 2016 18:07:28 GMT

Added broken scenario - thx

Re: Addtotals possible on stats list/count search?

woodcock — Fri, 29 Apr 2016 18:29:22 GMT

Based on the image, we need to see more of your search, too.

Re: Addtotals possible on stats list/count search?

jwalzerpitt — Tue, 29 Sep 2020 09:34:57 GMT

Search is as follows:

Re: Addtotals possible on stats list/count search?

woodcock — Fri, 24 Jun 2016 05:06:59 GMT

OK, first let's spoof the data:

| noop
| stats count as trash
| eval trash="0.0.0.0,fox169.tlscdn.com,2899::1.1.1.1,video.cynogage.com,3643::1.1.1.1,serve.blutonic-ads.com,160::1.1.1.1,conf.wsm.360.cn,71::1.1.1.1,sconf.f.360.cn,11::1.1.1.1,quri.f.360.cn,1::2.2.2.2,video.cynogage.com,2301::2.2.2.2,rdcdn.com,4::3.3.3.3,video.cynogage.com,594::4.4.4.4,video.cynogage.com,576::5.5.5.5,video.cynogage.com,837::5.5.5.5,serve.blutonic-ads.com,36::6.6.6.6,video.cynogage.com,663::6.6.6.6,serve.blutonic-ads.com,42::7.7.7.7,video.cynogage.com,563::7.7.7.7,serve.blutonic-ads.com,3::8.8.8.8,lb.cdn.e-adguardian.com,111::8.8.8.8,lb.cdn.d-adguardian.com,110::8.8.8.8,lb.cdn.a-adguardian.com,109::8.8.8.8,lb.cdn.c-adguardian.com,102::8.8.8.8,lb.mspt4-01.com,79::8.8.8.8,lb.msps4-01.com,6::9.9.9.9,video.cynogage.com,506::9.9.9.9,dopefile.com,6::9.9.9.9,us-nj-e3.rtbauction.com,4"
| makemv delim="::" trash
| mvexpand trash
| rex field=trash "(?<src_ip>[^,]+),(?<domain>[^,]+),(?<count>[^,]+)"
| fields - trash

Now, these events are pre-counted so this next line will be different than yours (keep yours the same):

| stats sum(count) AS count BY domain, src_ip

Finally, here is the adjusted/repaired remainder:

| sort 0 -count 
| stats list(domain) AS Domain, list(count) AS count, sum(count) AS total BY src_ip
| sort 10 -total
| rename Total AS Blocks

Re: Addtotals possible on stats list/count search?

jwalzerpitt — Tue, 05 Jul 2016 15:08:08 GMT

Thx - query works great

Re: Addtotals possible on stats list/count search?

jam00 — Tue, 30 Jul 2019 11:06:19 GMT

Hello @woodcock. I'm not sure I understand the logic behind |stats count as trash.
With stats count you get a 0 result. If you rename that with "trash" you're only changing the name. But without that initial statement, the query doesn't work. So, what is the key to understand your first query? Because, you are creating a field "trash" with eval command from scratch. It's true that the field "trash" already exists from stats and you are feeding it with the eval command but I don't understand why is necessary. I'm not getting the pre-counted concept you comment, probably.

Thanks in advance.

Re: Addtotals possible on stats list/count search?

woodcock — Tue, 30 Jul 2019 14:58:24 GMT

Before makeresults existed, we used to use | noop | stats count to create an event out of nothing.

Re: Addtotals possible on stats list/count search?

jam00 — Tue, 30 Jul 2019 16:14:23 GMT

Ok. Thank you for your explanation.