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?

Explorer
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

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

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

Explorer

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

Explorer

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
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!