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!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...