Stay organized with collections Save and categorize content based on your preferences.

Use GeoIP-enriched data in dashboards

Chronicle provides geolocation data enrichment (GeoIP data) for external IP addresses to provide additional context during an investigation. This document explains how you can use geolocation enriched fields when creating dashboards.

To learn more about:

Using enriched geolocation data in Chronicle Dashboards

UDM-enriched GeoIP data can be used through Chronicle's embedded Looker-powered Dashboards or the Looker Marketplace.

alt_text Example of GeoIP UDM enrichment

Using enriched geolocation data in Chronicle Data Lake (BigQuery)

UDM location data can be queried through Chronicle's Data Lake (BigQuery). The following example illustrates how to specify a SQL query to return aggregate results for all USER LOGIN events by user, by country, with the first and last observed times.

SELECT
 ip_location.country_or_region,
 COUNT(ip_location.country_or_region) AS count_country,
 ip_location.state,
 COUNT(ip_location.state) AS count_state,
 target.user.email_addresses[ORDINAL(1)] AS principal_user,
 TIMESTAMP_SECONDS(MIN(metadata.event_timestamp.seconds)) AS first_observed,
 TIMESTAMP_SECONDS(MAX(metadata.event_timestamp.seconds)) AS last_observed,
FROM `chronicle-coe.datalake.udm_events`,
UNNEST (principal.ip_location) as ip_location
WHERE DATE(_PARTITIONTIME) = "2022-07-04"
AND metadata.event_type = 15001
AND metadata.vendor_name IN ("Google Cloud Platform","Google Workspace")
GROUP BY 1,3,5
HAVING count_country > 0
ORDER BY count_country DESC

The following is the result:

country_or_region count_country state count_state principal_user first_observed last_observed
Nederland

5

Noord-Holland

5

admin@acme.com 2022-07-04T08:54:55Z 2022-07-04T19:24:55Z
Israel

1

מחוז תל אביב

1

omri@acme.com 2022-07-04T05:03:55Z 2022-07-04T05:03:55Z

The following BigQuery SQL illustrates how to detect the distance between two geographies:

SELECT
DISTINCT principal_user,
(ST_DISTANCE(north_pole,user_location)/1000) AS distance_to_north_pole_km
FROM (
  SELECT
    ST_GeogPoint(135.00,90.00) AS north_pole,
    ST_GeogPoint(ip_location.region_longitude, ip_location.region_latitude) AS user_location,
    target.user.email_addresses[ORDINAL(1)] AS principal_user
  FROM `chronicle-coe.datalake.udm_events`,
  UNNEST (principal.ip_location) as ip_location
  WHERE DATE(_PARTITIONTIME) = "2022-07-04"
  AND metadata.event_type = 15001
  AND metadata.vendor_name IN ("Google Cloud Platform","Google Workspace")
  AND ip_location.country_or_region != ""
)
ORDER BY 2 DESC

Answer the important questions, we now know which user is closest to the North pole.

principal_user distance_to_north_pole_km
omri@acme.com

6438.98507

admin@acme.com

4167.527018

However, you can achieve slightly more useful queries by leveraging area polygons, e.g, calculate a reasonable area for travel from a location in a given interval, and check if multiple geography values match, i.e., impossible travel detections, but with the caveat of having an accurate and consistent GeoIp source!