Splunk Search

filter results on number of events by field

markwymer
Path Finder

In my defense - it's been a really long day and I apologies if this is the most simple question......

I have a search, a transaction and a few field renames (for readability) and a table. e.g.

<search> | transaction Session_ID |eval LogonTime=strftime(_time,"%Y/%m/%d - %H:%M:%S") | rename Logon_Username AS "Account Name", ip_address AS "Source IP", mac_address AS "Network MAC" | table LogonTime,  "Account Name", "Source IP", "Network MAC"

The problem that I'm struggling with is that I want to filter the search/results so that it only returns results where the count of logon_username > 3 ( or any other number!) and I just can't figure out the way to count and filter the events and keep all the other fields for my table.

Any help will be gratefully appreciated and will help me sleep tonight. 🙂 🙂
Mark.

0 Karma
1 Solution

woodcock
Esteemed Legend

Ditch transaction and try this:

<search> | stats count(Logon_Username) AS AccountNameCount dc(Logon_Username) AS AccountNameDC values(*) AS * BY Session_ID
| rename Logon_Username AS "Account Name", ip_address AS "Source IP", mac_address AS "Network MAC"
| table Session_ID LogonTime,  "Account Name", "Source IP", "Network MAC"
| where AccountNameDC > 3

Note: Maybe you need AccountNameCount>3 instead (are you counting logs or are you counting users)?

View solution in original post

0 Karma

woodcock
Esteemed Legend

Ditch transaction and try this:

<search> | stats count(Logon_Username) AS AccountNameCount dc(Logon_Username) AS AccountNameDC values(*) AS * BY Session_ID
| rename Logon_Username AS "Account Name", ip_address AS "Source IP", mac_address AS "Network MAC"
| table Session_ID LogonTime,  "Account Name", "Source IP", "Network MAC"
| where AccountNameDC > 3

Note: Maybe you need AccountNameCount>3 instead (are you counting logs or are you counting users)?

0 Karma

markwymer
Path Finder

Thanks - worked perfectly.

( I always forget about "values(*) AS *" !!! )

Cheers, Mark.

0 Karma

javiergn
SplunkTrust
SplunkTrust

Because you've used a transaction your Logon_Username field is probably a multivalued one (it depends on whether you specified mvlist or not, see documentation here).

Anyway, if your Logon_Username is a multivalued field you can then use the following syntax:

| eval Logon_Username_Count=mvcount(Logon_Username)
| where Logon_Username_Count > 3

Hope that helps.

Thanks,
J

0 Karma
Get Updates on the Splunk Community!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...