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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...