Splunk Search

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

ericl42
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, 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

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
1 Solution

ericl42
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

ericl42
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
0 Karma

woodcock
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

ericl42
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 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.

0 Karma

tsaikumar009
Explorer

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

0 Karma

ericl42
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

martin_mueller
SplunkTrust
SplunkTrust

Replace your final count with sum(count).

0 Karma

ericl42
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

martin_mueller
SplunkTrust
SplunkTrust

Is there no increase compared to the original search?

0 Karma

ericl42
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

Routing logs with Splunk OTel Collector for Kubernetes

The Splunk Distribution of the OpenTelemetry (OTel) Collector is a product that provides a way to ingest ...

Welcome to the Splunk Community!

(view in My Videos) We're so glad you're here! The Splunk Community is place to connect, learn, give back, and ...

Tech Talk | Elevating Digital Service Excellence: The Synergy of Splunk RUM & APM

Elevating Digital Service Excellence: The Synergy of Real User Monitoring and Application Performance ...