Use context-enriched data in reports

To support security investigations, Chronicle ingests contextual data from different sources, performs analysis on the ingested data, and provides additional context about artifacts in a customer environment. This document provides examples of how analysts can use contextual enriched data in dashboards and in Chronicle schemas in BigQuery.

For more information about data enrichment, see How Chronicle enriches event and entity data.

Use geolocation-enriched data

UDM events may include geolocation-enriched data to provide additional context during an investigation. When UDM events are exported to BigQuery, these fields are also exported. This section explains how to use geolocation-enriched fields when creating reports.

Query data in the events schema

Geolocation data can be queried using the Chronicle events schema in BigQuery. The following example is a SQL query that returns aggregate results for all USER_LOGIN events by user, country, and with the first and last observed times.

SELECT
 ip_geo_artifact.location.country_or_region,
 COUNT(ip_geo_artifact.location.country_or_region) AS count_country,
 ip_geo_artifact.location.state,
 COUNT(ip_geo_artifact.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 `datalake.events`,
UNNEST (principal.ip_geo_artifact) as ip_geo_artifact
WHERE DATE(hour_time_bucket) = "2023-01-11"
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 table contains an example of the results that might be returned.

country_or_region	count_country	state	count_state	principal_user	first_observed	last_observed
Netherlands	5	North Holland	5	[e-mail]	2023-01-11 14:32:51 UTC	2023-01-11 14:32:51 UTC
Israel	1	Tel Aviv District	1	[e-mail]	2023-01-11 10:09:32 UTC	2023-01-11 15:26:38 UTC

The following SQL query illustrates how to detect the distance between two locations.

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_geo_artifact.location.region_coordinates.longitude, ip_geo_artifact.location.region_coordinates.latitude) AS user_location,
    target.user.email_addresses[ORDINAL(1)] AS principal_user
  FROM `datalake.events`,
  UNNEST (principal.ip_geo_artifact) as ip_geo_artifact
  WHERE DATE(hour_time_bucket) = "2023-01-11"
  AND metadata.event_type = 15001
  AND metadata.vendor_name IN ("Google Cloud Platform","Google Workspace")
  AND ip_geo_artifact.location.country_or_region != ""
)
ORDER BY 2 DESC

The following table contains an example of the results that might be returned.

principal_user	distance_to_north_pole_km
[e-mail]	6438.98507
[e-mail]	4167.527018

You can achieve slightly more useful queries by leveraging area polygons to calculate a reasonable area for travel from a location in a given interval. You can also check whether multiple geography values match to identify impossible travel detections. These solutions require having an accurate and consistent geolocation data source.

View enriched fields in dashboards

You can also build a dashboard using geolocation-enriched UDM fields. The following image is one example.

Example dashboard using enriched data

What's next

For information about how to use enriched data with other Chronicle features, see the following:

• Use context-enriched data in rules.
• Use context-enriched data in UDM Search.