Splunk Search

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
1 Solution

Esteemed Legend

Like this:

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

View solution in original post

0 Karma

New Member

Perhaps something like this?

sourcetype="IDS" | transaction id type | stats sum(id2) by id as counts | table id type counts | sort -counts
0 Karma

Esteemed Legend

Like this:

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

View solution in original post

0 Karma

Motivator

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

0 Karma

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

Esteemed Legend

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

0 Karma

Esteemed Legend

Like this:

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

Motivator

Got it! It's

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

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

Motivator

Sorry, that doesn't work - take this data

type,id2
20,118881686
20,118881686
20,118881686
20,118881686
20,118873014
20,118873014
20,118873014
20,118873014

Using count(id2) as Legs by id2 gives Legs=4, count=2, whereas BY type gives Legs=8, count=1

Also, for me, type does not have to be the same for each set of id2.

Thanks for your help.

0 Karma

Motivator

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

0 Karma

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