Splunk Search

How to return full count of field1, and a TRUE/FALSE field if 1 or more of the results in field1 matches a specific criteria?

Earenhart
Path Finder
eventtype=X | iplocation ClientIP  | where Country!="United States" | eval bad=if(match(Country,"Brazil|China|Vietnam|India|Thailand|Nigeria|South\sSudan|Russia|Ukraine|Turkey"), "TRUE","FALSE") | rex field=UserId "(?[\w\d]+(?=\@email))" | stats dc(Country) as Country_Count by Account, bad | sort - count

With this search, I am attempting to find all users who are logging in from countries outside of the US, count how many of those countries are seen in those logins, and return a simple TRUE or FALSE if any 1 of those countries match the named countries.

My issue is that each account is returning 2 separate stats results if anything matches both TRUE/FALSE, so for example if account=abc is seen logging in from 10 different countries, but 1 of them is Nigeria, it will return a stat showing 9 "FALSE", and a stat with 1 "TRUE". How can I modify this search to make it so account=abc would show up with a country count of 10, and the "bad" field will just show "TRUE"?

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Try like this

eventtype=X | iplocation ClientIP | where Country!="United States" | eval bad=if(match(Country,"Brazil|China|Vietnam|India|Thailand|Nigeria|South\sSudan|Russia|Ukraine|Turkey"), "TRUE","FALSE") | rex field=UserId "(?[\w\d]+(?=\@email))" | stats values(bad) as bad dc(Country) as Country_Count by Account  | eval bad=mvindex(bad,-1) | sort - count

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

Try like this

eventtype=X | iplocation ClientIP | where Country!="United States" | eval bad=if(match(Country,"Brazil|China|Vietnam|India|Thailand|Nigeria|South\sSudan|Russia|Ukraine|Turkey"), "TRUE","FALSE") | rex field=UserId "(?[\w\d]+(?=\@email))" | stats values(bad) as bad dc(Country) as Country_Count by Account  | eval bad=mvindex(bad,-1) | sort - count

Earenhart
Path Finder

Thank you somesoni2! This worked after I corrected my rex field lol. Not sure how I managed to copy/paste it without the "Account" part.

Correction: rex field=UserId "(?[\w\d]+(?=\@email))"

0 Karma

Earenhart
Path Finder

What the heck... this site is apparently omitting part of this regex.

rex field=UserId "(?(In this spot here, there is supposed to be "Account" within these brackets <>)[\w\d]+(?=\@email))"

rex field=UserId "(?[\w\d]+(?=\@email))"

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