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
下表包含可能返回的结果示例。
country_or_region
count_country
state
count_state
principal_user
first_observed
last_observed
Netherlands
5
North Holland
5
admin@acme.com
2023-01-11 14:32:51 UTC
2023-01-11 14:32:51 UTC
Israel
1
Tel Aviv District
1
omri@acme.com
2023-01-11 10:09:32 UTC
2023-01-11 15:26:38 UTC
以下 SQL 查询演示了如何检测两个位置之间的距离。
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
[[["易于理解","easyToUnderstand","thumb-up"],["解决了我的问题","solvedMyProblem","thumb-up"],["其他","otherUp","thumb-up"]],[["很难理解","hardToUnderstand","thumb-down"],["信息或示例代码不正确","incorrectInformationOrSampleCode","thumb-down"],["没有我需要的信息/示例","missingTheInformationSamplesINeed","thumb-down"],["翻译问题","translationIssue","thumb-down"],["其他","otherDown","thumb-down"]],["最后更新时间 (UTC)：2025-08-21。"],[[["\u003cp\u003eGoogle Security Operations enhances security investigations by ingesting and analyzing contextual data from various sources.\u003c/p\u003e\n"],["\u003cp\u003eGeolocation-enriched data within UDM events is available for creating reports and provides additional context.\u003c/p\u003e\n"],["\u003cp\u003eAnalysts can use the Google Security Operations \u003ccode\u003eevents\u003c/code\u003e schema in BigQuery to query geolocation data for security events.\u003c/p\u003e\n"],["\u003cp\u003eBigQuery queries can be used to detect the distance between two locations or to identify impossible travel by comparing multiple geography values.\u003c/p\u003e\n"],["\u003cp\u003eDashboards can display geolocation-enriched UDM fields, offering a visual representation of the data, like the city of each UDM event.\u003c/p\u003e\n"]]],[],null,["# Use context-enriched data in reports\n====================================\n\nSupported in: \nGoogle secops [SIEM](/chronicle/docs/secops/google-secops-siem-toc)\n\nTo support security investigations, Google Security Operations ingests contextual\ndata from different sources, performs analysis on the ingested data, and\nprovides additional context about artifacts in a customer environment. This\ndocument provides examples of how analysts can use contextual enriched data\nin dashboards and in Google SecOps schemas in BigQuery.\n\nFor more information about data enrichment, see [How Google SecOps enriches event and entity data](/chronicle/docs/event-processing/data-enrichment).\n\nUse geolocation-enriched data\n-----------------------------\n\nUDM events may include geolocation-enriched data to provide additional context\nduring an investigation. When UDM events are exported to BigQuery,\nthese fields are also exported. This section explains how to use geolocation-enriched fields when creating reports.\n\n### Query data in the `events` schema\n\nGeolocation data can be queried using the Google SecOps `events` schema in BigQuery.\nThe following example is a SQL query that returns aggregate results for all\n`USER_LOGIN` events by user, country, and with the first and last observed times. \n\n SELECT\n ip_geo_artifact.location.country_or_region,\n COUNT(ip_geo_artifact.location.country_or_region) AS count_country,\n ip_geo_artifact.location.state,\n COUNT(ip_geo_artifact.location.state) AS count_state,\n target.user.email_addresses[ORDINAL(1)] AS principal_user,\n TIMESTAMP_SECONDS(MIN(metadata.event_timestamp.seconds)) AS first_observed,\n TIMESTAMP_SECONDS(MAX(metadata.event_timestamp.seconds)) AS last_observed,\n FROM `datalake.events`,\n UNNEST (principal.ip_geo_artifact) as ip_geo_artifact\n WHERE DATE(hour_time_bucket) = \"2023-01-11\"\n AND metadata.event_type = 15001\n AND metadata.vendor_name IN (\"Google Cloud Platform\",\"Google Workspace\")\n GROUP BY 1,3,5\n HAVING count_country \u003e 0\n ORDER BY count_country DESC\n\nThe following table contains an example of the results that might be returned. \n\nThe following SQL query illustrates how to detect the distance between two locations. \n\n SELECT\n DISTINCT principal_user,\n (ST_DISTANCE(north_pole,user_location)/1000) AS distance_to_north_pole_km\n FROM (\n SELECT\n ST_GeogPoint(135.00,90.00) AS north_pole,\n ST_GeogPoint(ip_geo_artifact.location.region_coordinates.longitude, ip_geo_artifact.location.region_coordinates.latitude) AS user_location,\n target.user.email_addresses[ORDINAL(1)] AS principal_user\n FROM `datalake.events`,\n UNNEST (principal.ip_geo_artifact) as ip_geo_artifact\n WHERE DATE(hour_time_bucket) = \"2023-01-11\"\n AND metadata.event_type = 15001\n AND metadata.vendor_name IN (\"Google Cloud Platform\",\"Google Workspace\")\n AND ip_geo_artifact.location.country_or_region != \"\"\n )\n ORDER BY 2 DESC\n\nThe following table contains an example of the results that might be returned. \n\nYou can achieve slightly more useful queries by leveraging area polygons to\ncalculate a reasonable area for travel from a location in a given interval.\nYou can also check whether multiple geography values match to identify impossible\ntravel detections. These solutions require having an accurate and consistent geolocation data source.\n\n### View enriched fields in dashboards\n\nYou can also build a dashboard using geolocation-enriched UDM fields. The chart\ndisplays the city of each UDM event. You can change the chart type to see the\ndata in a different format.\n\nWhat's next\n-----------\n\nFor information about how to use enriched data with other Google SecOps\nfeatures, see the following:\n\n- [Use context-enriched data in rules](/chronicle/docs/detection/use-enriched-data-in-rules).\n- [Use context-enriched data in UDM Search](/chronicle/docs/investigation/use-enriched-data-in-search).\n\n**Need more help?** [Get answers from Community members and Google SecOps professionals.](https://security.googlecloudcommunity.com/google-security-operations-2)"]]
