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

[ericl42]

I'm working on an antivirus correlation rule, and I'm running into a few issues. I want to make sure dest, signature, file_path, and file_hash 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 file_paths 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 risk_signature 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

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.

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

[ericl42 · Accepted solution]

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

[woodcock]

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

[ericl42]

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 risk_signatures and 10 full_paths, 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.

[tsaikumar009]

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

[ericl42]

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.

[martin_mueller]

Replace your final count with sum(count).

[ericl42]

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.

[martin_mueller]

Is there no increase compared to the original search?

[ericl42]

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