Splunk Search

How to count how many times the same result set from stats(values) or stats(list) is repeated.

loeweps
Explorer

I have the following data. Each one has a different date entry.


DATE,ACCOUNT_NUMBER, SOLUTION, FINDING
1-1-2015,1, Replaced, WIRE
1-1-2015,2, Repaired, BOX
1-1-2015,3, Repaired, BOX
1-2-2015,1, Repaired, WIRE
1-2-2015,2, Replaced, BOX
1-3-2015,3, Replaced, BOX

I am using a subsearch to remove results where only a single result exists but it is not required.


index=data [ search index=data | stats count by ACCOUNT_NUMBER | where count>1 | fields ACCOUNT_NUMBER ]
| stats values(SOLUTION) as SOL, values(FINDING) by FND by ACCOUNT_NUMBER

I get the following result:


Account_Number, SOL, FND
1, Replaced, WIRE
Repaired, WIRE
2, Repaired, BOX
Replaced, BOX
3, Repaired, BOX
Replaced, BOX

I want to count how many times the same set of results appears.

SOL, FND, Count 
Replaced, Wire, 1
Replaced, Wire
Repaired, BOX,  2
Replaced, BOX

I have tried various methods of counting the result sets but haven't been able to get it to work.

Appreciate any help that can be provided. Thank you for taking the time to respond.

Tags (4)
0 Karma

lguinn2
Legend

Wow - I think you are doing this the hard way! Try the following - it should be faster as well as easier.

index=data
| stats count by ACCOUNT_NUMBER PROBLEM FINDING

If you want to eliminate accounts with only 1 entry across all values of problem and finding, do this

index=data
| stats count by ACCOUNT_NUMBER PROBLEM FINDING
| eventstats sum(count) as accountTotal by ACCOUNT_NUMBER
| where accountTotal > 1
| fields - accountTotal

BTW, you didn't use the field names PROBLEM , SOLUTION and FINDING the same way in your table and your example, so I hope I got it right.

0 Karma

loeweps
Explorer

Thank you for taking the time to help. Sorry for the confusion. I am hoping there may be an easier way to do this as it's been painful so far. Searched around for hours looking instances were the stats (values) or stasts(list) result is grouped and counted but haven't had any luck.

I attempted to use this solution. I am still counting by Account number.

I want to count how many times the same result set(group of Problem/Finding) appears.

So if there are five different instances of Repaired, Replaced, Box, Box. I want to count these without using account number.

I want to have it look like this:


SOL,FND, Count
Replaced, Wire, 1
Replaced, Wire
Repaired, BOX, 5
Replaced, BOX

I want to count each time the same set of results appears. If there are 20 instances where that combination of results appears I want to count that. Every time I try to use stats or eval on the SOL, FIND group it breaks them up and counts the individually again.

I am not familiar with html so trying to add the pre/code tag here. I also fixed the original question so that I was using Solution throughout the example.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...