About Geolocation usage
Hi,
I want to take the public IP address from the log when the users who connects through VPN. Here if the user account is compromised the chance would be there to use those compromised user account from various places. So in this scenario I'm able to find it using two queries one to identify the usernames from multiple locations and the other one used to list out the places from which this account is accessed. Is there a way to put it all in single query so that I can see all users access from different places.
Query1 : | parse "IP <*>" as address | parse "User <*>" as user
| lookup latitude, longitude, country_code, country_name, city, postal_code from geo://location on ip = address | count_distinct(longitude,latitude) group by user | where _count_distinct > 1
Query2: | parse "IP <*>" as address | parse "User <*>" as user
| lookup latitude, longitude, country_code, country_name, city, postal_code from geo://location on ip = address | count by user, country_name, city | where user matches "<username>"
Thanks,
Chaitanya Swamy K
-
Hi Chaitanya,
You can achieve the above with a JOIN query.. for example, join the first condition with the second by user name.
| parse "IP <*>" as address | parse "User <*>" as user
| lookup latitude, longitude, country_code, country_name, city, postal_code from geo://location on ip = address| join (count_distinct(longitude,latitude) group by user | where _count_distinct > 1) as a,
(count by user, country_name, city) as b on a.user=b.user| fields b_user, b_country_name, b_city, b__count
You can also use the filter operator, like this..
| parse "IP <*>" as address | parse "User <*>" as user
| lookup latitude, longitude, country_code, country_name, city, postal_code from geo://location on ip = address| count by user, country_name, city, longitude, latitude
| filter user in (count_distinct(longitude,latitude) group by user | where _count_distinct > 1)Hope these helps,
Monty
Please sign in to leave a comment.
Comments
1 comment