How to find Okta users that logged in from rare countries?

[JasonPhang]

I want to search for Okta Logs to find users that logged in from rare countries. So typically, users who logged from USA, UK, Australia is considered BAU but those from Kuwait, Lesotho, etc are rare. So far, I have done this. 

index=* sourcetype="OktaIM2:log" eventType="user.session.start" outcome.result="success" client.geographicalContext.country!=null daysago=30
| stats values(user), values(client.ipAddress), values(actor.displayName) count by client.geographicalContext.country
| sort count
| where count < 20

It returned results like this which isnt that accurate. Like for the first row, it gives user 1 and user 2. My current search query gives total results of 20 logins from user 1, 2 and 3. So meaning user 1 could be 1 login, user 2 15 logins, user 3 5 logins. 

Uzbekistan	user1 user2 user3	3
Slovakia	user1	1

What i want is to have at least more than 5 logins and less than 20 for that particular user to show that there is some activity ongoing. So user 2 for example who had 15 logins from rare country will be displayed, but user 1 who only had a login from the rare country will not be displayed. How do I get to this? Thanks. 

[PickleRick]

Well, it is the result you asked for

| stats values(user), values(client.ipAddress), values(actor.displayName) count by client.geographicalContext.country

means

for each distinct value of client.geographicalContext.country give me:

list of distinct users found along with this country

list of distinct client.ipAddress values found with this country

list of distinct actor.displayName values found with this country

and the count of events with this value of client.geographicalContext.country.

So you have no knowledge whether the same user logged in from different countries, for example. Or how many times each user tried to log in from anywhere. Or even how many times a specific user tried to log in from a specific country.

If you want to differentiate by users as well as countries, you need to move the user from the aggregation part - values(users) - to the BY clause of the stats command.

So you want your stats command to be like

| stats count BY country user

[JasonPhang]

So when I tried that, it gave all results of other countries too that are not rare. Like Australia, UK which had more than 5 were also in the list. Is there a way to get the rare countries then extract out the users which have more than 5 counts for those rare countries? Something like grep. 

[PickleRick]

My stats just aggregate your data fro  which you can choose those fitting your criteria with "where" or "search" commands.

[yuanliu]

What @PickleRick meant was that the stats command should group by user in addition to country.  You still need to filer by count, like

| stats values(user), values(client.ipAddress), values(actor.displayName) count by user client.geographicalContext.country
| where count < 20
| sort count ``` sort after filter requires less compute ```

Your original code uses < 20 as criterium.  How is number 5 enter the picture?  If 5 is the criterium, you should use | where count < 6.

Also, you never showed any data, result, or illustrate what you actually get from which actual (anonymized) code.  It is very difficult for volunteers to speculate what "not rare", "Australia", "UK", etc., mean in this context.