How can I adjust the query to include total connections that are initiated out of the country?

[Network506]

Good Morning,

I have a query that I'd like to refine. I'm new to Splunk.

So the current query that I'm running is used to identify when people outside of the country connect to our VPN.

index=company_logs "Client Type: Cisco AnyConnect VPN Agent" | iplocation src | stats dc(src) by Country

So this works fine for giving us a tally as to how many total connections are initiated out of the country.

I'd like to get more granular and have a breakdown by username. This is the field that contains the username: Cisco_ASA_user.

How can I adjust the query to include that data?

Thanks!

[woodcock]

index=company_logs "Client Type: Cisco AnyConnect VPN Agent"
| stats count src Cisco_ASA_user
| iplocation src
| search NOT Country="United States"
| stats sum(count) AS count dc(src) AS dc_src BY Country user
| stats list(*) AS * BY user

[Network506]

Thanks. The first query works great. May I ask another question? How would I modify it to exclude the USA in the results?

[johnhuang]

Add this after the ip location line:

| search NOT Country="United States"

If want to exclude a list of Countries:

| search NOT Country IN ("United States", "United Kingdom", "France")

[johnhuang]

There's a few ways to break it down by users. You can try:

index=company_logs "Client Type: Cisco AnyConnect VPN Agent" 
| iplocation src 
| stats dc(src) BY Country Cisco_ASA_user

index=company_logs "Client Type: Cisco AnyConnect VPN Agent" 
| iplocation src 
| chart dc(src) BY Cisco_ASA_user Country 

index=company_logs "Client Type: Cisco AnyConnect VPN Agent" 
| iplocation src 
| stats dc(src) values(Cisco_ASA_user) AS Cisco_ASA_user BY Country