Splunk Search
Highlighted

How do you do a stats count by a specific field?

Path Finder

I'm working on an antivirus correlation rule, and I'm running into a few issues. I want to make sure dest, signature, filepath, and filehash are all in my notable event so I can call those variables in adaptive responses.

Below is the current search I have and it works very well as far as grouping multiple filepaths with the destination so when I call the variable, it shows them both. The issue I have is that the count always goes off of whatever the biggest field is in the row. I want to only show count for the risksignature field.

Please see screenshot for additional information.

index=security*sep sourcetype IN (symantec:ep:proactive:file, symantec:ep:risk:file) | stats count by dest, user, signature, file_path, file_hash | stats values(user) AS user_name, values(signature) AS risk_signature, values(file_path) AS full_path, values(file_hash) AS sha256 count by dest | where count>1

alt text

Edit 1:

The sum(count) by dest or by anything else changes some numbers but most stay the same. There are some weird entries per the screenshot below. The top one is the original search and the second one is the sum(count) search.

alt text

Edit 2:

I think I figured it out. If I do a dc(signature), I get a count and then I can just modify it where total_signatures > 1.

index=security*sep sourcetype IN (symantec:ep:proactive:file, symantec:ep:risk:file) | stats count by dest, signature, file_name, file_path, file_hash | stats dc(signature) AS total_signatures, values(file_name) as process, values(file_path) AS full_path, values(file_hash) AS sha256 count by dest | where total_signatures > 1
Tags (2)
0 Karma
Highlighted

Re: How do you do a stats count by a specific field?

SplunkTrust
SplunkTrust

Replace your final count with sum(count).

0 Karma
Highlighted

Re: How do you do a stats count by a specific field?

Path Finder

Martin - Thanks for the quick response. I'm not 100% sure what you are referencing. I tried changing my "sha256 count by dest" to "sha256 sum(count) by dest", but that didn't fix my issue.

0 Karma
Highlighted

Re: How do you do a stats count by a specific field?

SplunkTrust
SplunkTrust

Is there no increase compared to the original search?

0 Karma
Highlighted

Re: How do you do a stats count by a specific field?

Path Finder

Martin - Most stay the same but others are acting weird. I updated my original question with a new screenshot.

0 Karma
Highlighted

Re: How do you do a stats count by a specific field?

Explorer

index=security*sep sourcetype IN (symantec:ep:proactive:file, symantec:ep:risk:file) | stats count by dest, user, signature, filepath, filehash | stats count(user) AS username, count(signature) AS risksignature, count(filepath) AS fullpath, count(file_hash) AS sha256 by dest | where count>1

0 Karma
Highlighted

Re: How do you do a stats count by a specific field?

Path Finder

Thanks for the information. That doesn't specifically work for me since I need to have the file_path and hash in the logs so I can call those variables.

0 Karma
Highlighted

Re: How do you do a stats count by a specific field?

Esteemed Legend

Like this:

index=security*sep sourcetype IN (symantec:ep:proactive:file, symantec:ep:risk:file)
| stats count by dest, user, signature, file_path, file_hash
| eventstats sum(count) AS count_by_signature_dest BY signature dest
| stats values(user) AS user_name, values(signature) AS risk_signature, values(file_path) AS full_path, values(file_hash) AS sha256 count sum(count_by_signature_dest) AS signature_count BY dest
| where count>1
0 Karma
Highlighted

Re: How do you do a stats count by a specific field?

Path Finder

Unfortunately that doesn't work. The count still counts whichever field has the most entries in it and the signature_count does something crazy and makes the number really large.

There is one with 4 risksignatures and 10 fullpaths, and 6 sha256s. The signature_count it gives is 36 for some reason. There is another one with even less and the signature count is 147.

0 Karma
Highlighted

Re: How do you do a stats count by a specific field?

Path Finder

Putting an official answer on here for anyone else that is having issues with this. If I used dc on the signatures field and then modified the where clause to be total_signatures, it worked perfectly for me. I still have all of the variables that I need for adaptive response.

 index=security*sep sourcetype IN (symantec:ep:proactive:file, symantec:ep:risk:file) | stats count by dest, signature, file_name, file_path, file_hash | stats dc(signature) AS total_signatures, values(file_name) as process, values(file_path) AS full_path, values(file_hash) AS sha256 count by dest | where total_signatures > 1

View solution in original post

0 Karma