I need to see the location of the ip for each log

Follow

Aaronisa Barnes

• April 03, 2020 19:21

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

0

• 

  Rick Jury

  • April 05, 2020 21:09

  Hi Aaronisa,

  you can remove a column from the final table with:
  | fields -_count

   

  For 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

  0

  Comment actions Permalink

Please sign in to leave a comment.