Splunk Search
Highlighted

How to sum the count of FieldA where FieldB is the same?

Motivator

I have data like:

id,type,id2
1,a,100
2,a,100
3,c,
4,a,101
5,a,101
6,b,102
7,b,102
8,b,102
9,b,103
10,b,103
11,b,103
12,d,104
13,d,104
14,d,104
15,d,104
16,e,
17,c,105
18,c,105

What I am trying to do is to get the output:

a=2
b=2
c=2
d=1
e=1

So, this is essentially a count of the number of 'type' where id2 are the same. In the above data,

Count of (a=2) is
id 1 and 2 are type 'a' and have id2=100
id 4 and 5 are type 'a' and have id2=101

Count of (b=2) is
id 6,7 and 8 are type 'b' and have id2=102
id 9,10 and 11 are type 'b' and have id2=103

Count of (c=2) is
id 3 is type 'c' and has no id2
id 17 and 18 are type 'c' and have id2=105

Count of (d=1) is
id 12,13,14 and 15 are type 'd' and have id2=104

Count of (e=1) is
id 16 is type 'e' and has no id2

and a final total of 8, which is 2+2+2+1+1

I can't figure out the search required. Note that all types may or may not have id2 and the duplication of id2 can be up to 20 instances of the same id2.

Tags (3)
0 Karma
Highlighted

Re: How to sum the count of FieldA where FieldB is the same?

Esteemed Legend

Like this:

...| stats dc(id2) by type | addtotals col=t row=f | fillnull value="TOTAL"

View solution in original post

0 Karma
Highlighted

Re: How to sum the count of FieldA where FieldB is the same?

Motivator

Argh! I can't believe it was so simple - thanks so much. My head got warped trying to overthink it!

0 Karma
Highlighted

Re: How to sum the count of FieldA where FieldB is the same?

Motivator

One other query I'm now trying to get from that is to count the frequency of the repeat of a particular id2, i.e.

common_count=frequency

1=2 - count of id2 being empty (row 3,16)
2=3 - count of id2 having 2 common values (rows 1,2 - 4,5 and 17,18 - values 100,101,105)
3=2 - count of id2 having 3 common values (rows 6,7,8 and 9,10,11- values 102,103)
4=1 - count of id2 having 4 common values (rows 12,13,14,15 - values 104)
5=0
etc.

0 Karma
Highlighted

Re: How to sum the count of FieldA where FieldB is the same?

Esteemed Legend

That's crazy, somebody just asked this same question last week. Hang on for a link...

0 Karma
Highlighted

Re: How to sum the count of FieldA where FieldB is the same?

Esteemed Legend

Like this:

...  | stats dc(id2) as dc BY type | stats count BY dc
0 Karma
Highlighted

Re: How to sum the count of FieldA where FieldB is the same?

Motivator

That's not quite right. Using that for types 'a' and 'c' would give

stats dc(id2) as dc by 'a' = 2
stats dc(id2) as dc by 'c' = 1

but these should both be summed to a count of 3 by '2' where 2 is the common number of occurrences of id2. In your example, you would get 1=1 and 2=1 because there is only a single count of dc=2 for 'a' and a single count of dc=1 for 'c'.

I have, for example in my test data, 36 rows, with dc(id2) = 9 and type=20, and there are always 4 rows per id2. What I want is 4=9

0 Karma
Highlighted

Re: How to sum the count of FieldA where FieldB is the same?

Motivator

type can't be used, as the answer is needed for the question 'how many rows does each unique id2 have'?

0 Karma
Highlighted

Re: How to sum the count of FieldA where FieldB is the same?

Motivator

Got it! It's

stats count(id2) as Legs by id2 | stats count by Legs
0 Karma
Highlighted

Re: How to sum the count of FieldA where FieldB is the same?

Esteemed Legend

I think not. See my revised answer above. Using your nomenclature, it should be this:

... | stats count(id2) AS Legs BY type | stats count BY Legs

In other words, switch out id2 | for type |.

0 Karma