VPN users (same user login from 2 different locations in a specific time range)

[aliroumani]

i'm search for vpn users that might login into two different locations in a specific time-range and im getting the following result:

index=cisco_acs src="*" AND src!=10.0.*.* | iplocation src 
| stats count(src_ip) AS ipCount by user, src_ip, _time, Country | where ipCount >=1 
| table _time, user, src_ip, Country
| rename values(_time) as _time values(src_ip) as src_ip values(Country) as Country 
| eval _time=strftime(_time, "%Y-%d-%m %H:%M")
| stats values(_time) values(src_ip), values(Country), count by user 
| rename values(_time) as time values(src_ip) as src_ip values(Country) as country

Results:

user        _time            country
user1      20/07/2016        united kingdom
user2      21/07/2016        germany, usa
user3      23/07/2016        India, China
user4      25/07/2017        france
....etc

My question is how to make the search shows only the results with 2 countries or more and ignore results with 1 country.
and with time range between the 2 logins less than 4 hrs.

regards

[woodcock · Accepted solution]

Try this:

 index=cisco_acs src="" AND src!=10.0..* | iplocation src 
| stats count(src_ip) AS ipCount by user, src_ip, _time, Country | where ipCount >=1 
| table _time, user, src_ip, Country
| rename values(_time) as _time values(src_ip) as src_ip values(Country) as Country 
| eval _time=strftime(_time, "%Y-%d-%m %H:%M")
| stats values(_time) values(src_ip), values(Country), count dc(Country) AS numCountries by user 
| rename values(_time) as time values(src_ip) as src_ip values(Country) as country
| search numCountries>1

[aliroumani]

thanks a million Mr. Woodcock.
Absolutely the result i was looking for 🙂

[rashid47010]

whats about below:
index=cisco_acs src="" AND src!=10.0..* | iplocation src
| stats values(_time) values(src_ip), values(Country),dc(Country) AS numCountries by user
| search numCountries>1

user _time
coxxx 1470040453 1xx.xx1.xxx.x3 Greece 2
1470047874 xx.xx.xx2.1x9 United States
1470048082
1470048127
1470048144
1470048178

I am only facing with time. it is not showing proper time format