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
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
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
thanks a million Mr. Woodcock.
Absolutely the result i was looking for 🙂
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