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
Revered Legend

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
Revered Legend

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!

Enterprise Security Content Update (ESCU) | New Releases

In September, the Splunk Threat Research Team had two releases of new security content via the Enterprise ...

New in Observability - Improvements to Custom Metrics SLOs, Log Observer Connect & ...

The latest enhancements to the Splunk observability portfolio deliver improved SLO management accuracy, better ...

Improve Data Pipelines Using Splunk Data Management

  Register Now   This Tech Talk will explore the pipeline management offerings Edge Processor and Ingest ...