Splunk Search

How to create stats count by combine values?

mituw16
Explorer

I have a big query that produces output like this. splunk.png

Those rows are guid id, count of occurrences, then ip addresses (they're stored in csv like that in raw data). What I'm attempting to do is basically combine instances of the same guid, sum all occurrences, and then have a column that would be a big csv of ALL ip addresses for the guid. I've tried many things, but not  having any luck. 

Labels (2)
Tags (3)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

Split your ip addresses into a multi-value field, gather then up and sum your counts by guid, then join the ip addresses up again into a single string

| eval ip=split(ip,", ")
| stats values(ip) as ip sum(count) as count by guid
| eval ip=mvjoin(ip,", ")

View solution in original post

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Split your ip addresses into a multi-value field, gather then up and sum your counts by guid, then join the ip addresses up again into a single string

| eval ip=split(ip,", ")
| stats values(ip) as ip sum(count) as count by guid
| eval ip=mvjoin(ip,", ")
0 Karma

yuanliu
SplunkTrust
SplunkTrust

This said, I have a suspicion that the "big query" itself uses stats to get that "ip1, ip2, ip3" pattern.  If so, you should examine that "big query" and do proper stats from there.

0 Karma

mituw16
Explorer

Unfortunately the IPAddresses are logged in that manner (2 addresses with a comma) in the applications themselves. My query didn't combine them like that.

That said, I ended up figuring it out. Used this. 

 

 

prequery

| stats count(Customer) as CustomerRequests values(IPAddresses) as IPAddresses by Customer

| eval IPAddresses = mvjoin(IPAddresses, ",")

| table Customer, CustomerRequests, IPAddresses

| sort -CustomerRequests

 

 

This produced the desired output of 

 CustomerRequestsIPAddresses
<guid here>1000192.168.0.1,192.168.0.2,192.168.0.3,192.168.0.4,...etc

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

If your ip addresses appear in more than one list, they get duplicated unless you do the split as I suggested.

mituw16
Explorer

oh good point! I hadn't noticed that possibility. Thanks! 

0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In the last month, the Splunk Threat Research Team (STRT) has had 2 releases of new security content via the ...

Announcing the 1st Round Champion’s Tribute Winners of the Great Resilience Quest

We are happy to announce the 20 lucky questers who are selected to be the first round of Champion's Tribute ...

We’ve Got Education Validation!

Are you feeling it? All the career-boosting benefits of up-skilling with Splunk? It’s not just a feeling, it's ...