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]

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"?

[somesoni2 · Accepted solution]

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]

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))"

[Earenhart]

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))"