Security

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

aliroumani
Explorer

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

Tags (1)
0 Karma
1 Solution

woodcock
Esteemed Legend

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

View solution in original post

0 Karma

woodcock
Esteemed Legend

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
0 Karma

aliroumani
Explorer

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

0 Karma

rashid47010
Communicator

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

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to May Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars this month. This ...

They're back! Join the SplunkTrust and MVP at .conf24

With our highly anticipated annual conference, .conf, comes the fez-wearers you can trust! The SplunkTrust, as ...

Enterprise Security Content Update (ESCU) | New Releases

Last month, the Splunk Threat Research Team had two releases of new security content via the Enterprise ...