topic Re: stats count by combine values in Splunk Search

How to create stats count by combine values?

mituw16 — Mon, 21 Aug 2023 19:52:27 GMT

I have a big query that produces output like this.

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.

Re: stats count by combine values

ITWhisperer — Mon, 21 Aug 2023 15:30:58 GMT

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,", ")

Re: stats count by combine values

yuanliu — Mon, 21 Aug 2023 23:06:50 GMT

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.

Re: stats count by combine values

mituw16 — Tue, 22 Aug 2023 12:21:57 GMT

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

Customer	Requests	IPAddresses
<guid here>	1000	192.168.0.1,192.168.0.2,192.168.0.3,192.168.0.4,...etc

Re: stats count by combine values

ITWhisperer — Tue, 22 Aug 2023 13:32:49 GMT

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

Re: stats count by combine values

mituw16 — Tue, 22 Aug 2023 13:34:10 GMT

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