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!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...