I need to see the location of the ip for each log
This is giving me the count of logins per location. I need all of this information except the count. How do I have the output also show the location in for each of the logs?
_sourceCategory = gsuite/* ipAddress email login "\"type\": \"login\""
| json "actor", "id", "ipAddress", "events" nodrop
| json field=actor "email", "profileId"
| json field=id "applicationName"
| json "events[0].type" as eventType nodrop | json "events[0].name" as eventName nodrop
| where eventType="login" and applicationName="login"
| count by ipAddress
| lookup latitude, longitude, country_code, country_name, region, city, postal_code from geo://location on ip = ipAddress
| where !isNull(latitude)
| sum(_count) as _count by latitude, longitude, country_code, country_name, region, city, postal_code
| sort by _count
-
Hi Aaronisa,
you can remove a column from the final table with:
| fields -_countFor your second question you have some options there:
1. you can do a lookup by ipaddress but not do a | count by ipaddress. This will just add the geo lookup fields to the raw unaggregated mesages.
2 You could combine a summary table with more fields say like this:
| count by ipAddress,eventtype,eventname. //etc any other fields include here
| lookup latitude, longitude, country_code, country_name, region, city, postal_code from geo://location on ip = ipAddress
| where !isNull(latitude)
| sum(_count) as _count by latitude, longitude, country_code, country_name, region, city, postal_code,eventtype,eventname // etc add same fields here you added earlier
| sort by _count | fields -_count
Please sign in to leave a comment.
Comments
1 comment