topic Re: How to count different fields from different hosts? in Splunk Search

How to count different fields from different hosts?

dwear — Fri, 29 Jan 2016 16:37:35 GMT

Pardon if this is easy, I just finished going through the Searching and Reporting class and am attempting to utilize what I learned in practice.

I'm attempting to correlate the number of malware events each endpoint on my network occurs over a given period. To do that, I need to count data from multiple hosts. The problem I'm running into, is the host identifies the endpoint in a different context in the log messages. In the logs, the victim endpoint might be identified as src=, dst= or dvc=. This is what I searched so far, but I don't know how to "count by" if the field is different. Thanks for any help.

(host="10.128.16.45" src=*) OR (host=”10.128.16.71” dst=*)|stats count by ??? |sort -count

Re: How to count different fields from different hosts?

richgalloway — Fri, 29 Jan 2016 17:54:49 GMT

Use the coalesce command to combine the different fields into a new field.

(host="10.128.16.45" src=*) OR (host=”10.128.16.71” dst=*)| eval newField = coalesce(src, dst, svc) | stats count by newField |sort -count

Re: How to count different fields from different hosts?

masonmorales — Fri, 29 Jan 2016 18:52:49 GMT

Could you post some sample data and perhaps a mock-up of what you want the results to look at?

Re: How to count different fields from different hosts?

dwear — Fri, 29 Jan 2016 19:05:42 GMT

Since I'm still new, I can't post a image of my logs but here is the generalization.

Appliance A: Malware Alert src=10.128.36.100 dst=96.127.180.106

Appliance B: Malware Alert src=96.127.180.106 dst=10.128.36.100

In this example I only care about listing the internal IP's or anything with 10.128.36.*

If I only use one appliance it works perfectly. I do host="Appliance A" src=* |stats count by src |sort -count

The issue is since Appliance A and Appliance B have the 10.128.36 network in different fields, how to I count both those fields, without counting ALL src and ALL dst?

Re: How to count different fields from different hosts?

dwear — Fri, 29 Jan 2016 19:11:23 GMT

Thanks! Since both src and dst are in almost all of my logs, wont coalesce collect ALL those values when I only really care about half of them? I only really care about whichever field contains the 10.128 network.

Re: How to count different fields from different hosts?

richgalloway — Fri, 29 Jan 2016 19:28:35 GMT

Yes, you are correct. That wasn't clear from your original posting. Here's another approach.

host=* | eval addr=case(cidrmatch("10.128.36/24",src),src,cidrmatch("10.128.36/24",dst),dst) | stats count by addr | sort - count

Re: How to count different fields from different hosts?

dwear — Mon, 01 Feb 2016 19:55:26 GMT

Awesome thanks Rich. Since I have numerous other, non relevant devices sending data to the same splunk instance, I had filtered it down some. It looks like its working correctly, can you just verify my syntax isn't over including or excluding anything?

host=10.128.16.71 OR host=10.128.16.45 | eval addr=case(cidrmatch("10.128/16",src),src,cidrmatch("10.128./16",dst),dst) | stats count by addr | sort - count

Re: How to count different fields from different hosts?

richgalloway — Tue, 02 Feb 2016 12:36:48 GMT

There's an extra '.' in your second CIDR. Otherwise, it looks fine. Of course, I don't know your network configuration so I don't know if your CIDRs are correct.