安全湖查詢 AWS 資料來源版本 2 (OCSF1.1.0)

您可以查詢安全湖泊存儲在其中的數據 AWS Lake Formation 資料庫和資料表。您也可以在 Security Lake 主控台中建立第三方訂閱者API，或 AWS CLI。 第三方訂閱者也可以從您指定的來源查詢 Lake Formation 資料。

Lake Formation 資料湖管理員必須將相關資料庫和資料表的SELECT權限授與查詢資料的IAM身分。訂閱者也必須先在安全湖中建立，才能查詢資料。如需如何建立具有查詢存取權的訂閱者的詳細資訊，請參閱管理安全湖訂戶的查詢存取權。

以下部分提供從 Security Lake 查詢資料的指引，並包含一些原生支援的查詢範例 AWS 來源。這些查詢旨在檢索特定的數據 AWS 區域。 這些範例使用 us-east-1 (美國東部 (維吉尼亞北部))。此外，範例查詢會使用LIMIT 25參數，該參數最多可傳回 25 筆記錄。您可以省略此參數，也可以根據自己的偏好進行調整。如需更多範例，請參閱 Amazon 安全湖OCSF查詢 GitHub 目錄。

記錄來源表格

當您查詢 Security Lake 資料時，您必須包含資料所在的 Lake Formation 資料表的名稱。

SELECT *
FROM "amazon_security_lake_glue_db_DB_Region"."amazon_security_lake_table_DB_Region_SECURITY_LAKE_TABLE"
WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP
LIMIT 25      

記錄來源資料表的一般值包括下列項目：

• cloud_trail_mgmt_2_0 – AWS CloudTrail 管理事件

• lambda_execution_2_0— Lambda 的 CloudTrail 資料事件

• s3_data_2_0— S3 的 CloudTrail 資料事件

• route53_2_0— Amazon 路線 53 解析器查詢日誌

• sh_findings_2_0 – AWS Security Hub 問題清單

• vpc_flow_2_0— Amazon Virtual Private Cloud（AmazonVPC）流程日誌

• eks_audit_2_0— Amazon Elastic Kubernetes Service (AmazonEKS) 審計日誌

• waf_2_0 – AWS WAF 2 日誌

範例：us-east-1 區域中資料表sh_findings_2_0中的所有 Security Hub 發現項目

SELECT *
    FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_sh_findings_2_0"
    WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP
LIMIT 25

資料庫區域

當您查詢 Security Lake 資料時，您必須包含要從中查詢資料的資料庫區域名稱。如需目前提供安全湖泊的完整資料庫區域清單，請參閱 Amazon 安全湖端點。

範例：列出來自來源 IP 的 Amazon Virtual Private Cloud 活動

下面的例子列出了來自源 IP 的所有 Amazon VPC 活動 192.0.2.1 之後被記錄 20230301 (二零二三年三月一日)，列於表中 vpc_flow_2_0 從 us-west-2 DB_Region.

SELECT * 
    FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_vpc_flow_2_0"
    WHERE time_dt > TIMESTAMP '2023-03-01' 
    AND src_endpoint.ip = '192.0.2.1'
    ORDER BY time_dt desc
LIMIT 25          

分割區日期

透過分割資料，您可以限制每個查詢掃描的資料量，進而改善效能並降低成本。與安全湖 1.0 相比，安全湖 2.0 中的分區工作方式略有不同。安全湖現在實作透過time_dtregion、和分割accountid。然而，安全湖 1.0 實現了通過 eventDayregion，和accountid參數進行分區。

查詢time_dt將自動從 S3 產生日期分區，並且可以像在 Athena 中任何基於時間的欄位一樣進行查詢。

以下是使用 time_dt分割區在 2023 年 3 月 1 日之後查詢記錄檔的範例查詢：

SELECT *
    FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_vpc_flow_2_0"
WHERE time_dt > TIMESTAMP '2023-03-01'
AND src_endpoint.ip = '192.0.2.1'
ORDER BY time desc
LIMIT 25            

的一般值time_dt包括下列項目：

過去 1 年發生的事件

WHERE time_dt > CURRENT_TIMESTAMP - INTERVAL '1' YEAR

最近 1 個月發生的事件

WHERE time_dt > CURRENT_TIMESTAMP - INTERVAL '1' MONTH

過去 30 天內發生的事件

WHERE time_dt > CURRENT_TIMESTAMP - INTERVAL '30' DAY

過去 12 小時內發生的事件

WHERE time_dt > CURRENT_TIMESTAMP - INTERVAL '12' HOUR

最近 5 分鐘內發生的事件

WHERE time_dt > CURRENT_TIMESTAMP - INTERVAL '5' MINUTE

7-14 天前發生的事件

WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '14' DAY AND CURRENT_TIMESTAMP - INTERVAL '7' DAY

在特定日期或之後發生的事件

WHERE time_dt >= TIMESTAMP '2023-03-01'

範例：表格中 2023 年 3 月 1 192.0.2.1 日或之後來源 IP 的所有 CloudTrail 活動清單 cloud_trail_mgmt_1_0

SELECT *
    FROM amazon_security_lake_glue_db_us_east_1.amazon_security_lake_table_us_east_1_cloud_trail_mgmt_1_0
    WHERE eventDay >= '20230301'
    AND src_endpoint.ip = '192.0.2.1'
    ORDER BY time desc
    LIMIT 25       

範例：表格中過去 30 天來源 IP 192.0.2.1 的所有 CloudTrail 活動清單 cloud_trail_mgmt_1_0

SELECT *
    FROM amazon_security_lake_glue_db_us_east_1.amazon_security_lake_table_us_east_1_cloud_trail_mgmt_1_0
    WHERE eventDay > cast(date_format(current_timestamp - INTERVAL '30' day, '%Y%m%d%H') as varchar) 
    AND src_endpoint.ip = '192.0.2.1'
    ORDER BY time desc
    LIMIT 25       

查詢安全湖觀察

可觀測值是安全湖 2.0 現在可用的新功能。可觀察到的對象是一個樞紐分析元素，其中包含在事件中的許多地方發現的相關信息。查詢可觀測項允許使用者從他們的資料集衍生出高層級的安全見解。

通過查詢 observables 中的特定元素，你可以限制數據集的東西，如特定的用戶名，資源 UIDsIPs，哈希和其他IOC類型的信息

這是使用可觀測數組查詢的示例查詢跨VPC流程和 Route53 表（包含 IP 值「172.01.02.03」）的日誌

WITH a AS 
    (SELECT 
    time_dt,
    observable.name,
    observable.value
    FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_vpc_flow_2_0",
    UNNEST(observables) AS t(observable)
    WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP
    AND observable.value='172.01.02.03'
    AND observable.name='src_endpoint.ip'),
b as 
    (SELECT 
    time_dt,
    observable.name,
    observable.value
    FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_route53_2_0",
    UNNEST(observables) AS t(observable)
    WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP
    AND observable.value='172.01.02.03'
    AND observable.name='src_endpoint.ip')
SELECT * FROM a
LEFT JOIN b ON a.value=b.value and a.name=b.name
LIMIT 25   

CloudTrail 資料查詢

AWS CloudTrail 跟踪用戶活動和API使用 AWS 服務。 訂閱者可以查詢資 CloudTrail 料以瞭解下列類型的資訊：

以下是 CloudTrail 數據的一些示例查詢：

未經授權的嘗試 AWS 服務 在過去 7 天

SELECT
    time_dt, 
    api.service.name, 
    api.operation, 
    api.response.error, 
    api.response.message, 
    api.response.data, 
    cloud.region, 
    actor.user.uid, 
    src_endpoint.ip, 
    http_request.user_agent
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_cloud_trail_mgmt_2_0"
WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP 
AND api.response.error in (
    'Client.UnauthorizedOperation',
    'Client.InvalidPermission.NotFound',
    'Client.OperationNotPermitted',
    'AccessDenied')
ORDER BY time desc
LIMIT 25

過去 7 天來源 IP 192.0.2.1 的所有 CloudTrail 活動清單

SELECT
    api.request.uid, 
    time_dt, 
    api.service.name, 
    api.operation, 
    cloud.region, 
    actor.user.uid, 
    src_endpoint.ip, 
    http_request.user_agent
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_cloud_trail_mgmt_2_0"
WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP
AND src_endpoint.ip = '192.0.2.1.'
ORDER BY time desc
LIMIT 25

最近 7 天內所有IAM活動的清單

SELECT *
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_cloud_trail_mgmt_2_0"
WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP
AND api.service.name = 'iam.amazonaws.com'
ORDER BY time desc
LIMIT 25

過去 7 天內使用認證AIDACKCEVSQ6C2EXAMPLE的執行個體

SELECT 
      actor.user.uid, 
      actor.user.uid_alt, 
      actor.user.account.uid, 
      cloud.region
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_cloud_trail_mgmt_2_0"
WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP
AND actor.user.credential_uid = 'AIDACKCEVSQ6C2EXAMPLE'
LIMIT 25

過去 7 天失敗的 CloudTrail 記錄清單

SELECT 
      actor.user.uid, 
      actor.user.uid_alt, 
      actor.user.account.uid, 
      cloud.region
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_cloud_trail_mgmt_2_0"
WHERE status='failed' and time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP
ORDER BY time DESC
LIMIT 25

查詢 Route 53 解析程式查詢記錄

Amazon Route 53 解析器查詢日誌跟踪 DNS Amazon 內部資源進行的查詢。VPC訂閱者可以查詢 Route 53 解析器查詢日誌，以了解以下類型的信息：

以下是 Route 53 儲存器查詢記錄檔的一些範例查詢：

過去 7 天內的DNS查詢清單 CloudTrail

SELECT 
    time_dt,
    src_endpoint.instance_uid,
    src_endpoint.ip,
    src_endpoint.port,
    query.hostname,
    rcode
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_route53_2_0"
WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP 
ORDER BY time DESC
LIMIT 25

過去 7 天內符合s3.amazonaws.com的DNS查詢清單

SELECT 
    time_dt,
    src_endpoint.instance_uid,
    src_endpoint.ip,
    src_endpoint.port,
    query.hostname,
    rcode,
    answers
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_route53_2_0"
WHERE query.hostname LIKE 's3.amazonaws.com.' and time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP
ORDER BY time DESC
LIMIT 25

過去 7 天未解決的DNS查詢清單

SELECT 
    time_dt,
    src_endpoint.instance_uid, 
    src_endpoint.ip, 
    src_endpoint.port, 
    query.hostname, 
    rcode, 
    answers
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_route53_2_0"
WHERE cardinality(answers) = 0 and time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP
LIMIT 25

過去 7 天內解析為192.0.2.1的DNS查詢清單

SELECT 
    time_dt,
    src_endpoint.instance_uid, 
    src_endpoint.ip, 
    src_endpoint.port, 
    query.hostname, 
    rcode, 
    answer.rdata
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_route53_2_0",
UNNEST(answers) as st(answer)
WHERE answer.rdata='192.0.2.1' 
AND time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP
LIMIT 25

Security Hub 發現項目的查詢

安全中心為您提供安全性狀態的全面檢視 AWS 並協助您根據安全性產業標準和最佳實務來檢查您的環境。Security Hub 會產生安全性檢查結果，並從協力廠商服務接收發現項目。

以下是 Security Hub 發現的一些示例查詢：

過去 7 天內嚴重性大於或等MEDIUM於的新發現項目

SELECT
    time_dt,
    finding_info,
    severity_id,
    status
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_sh_findings_2_0"
WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP 
    AND severity_id >= 3
    AND status = 'New'
ORDER BY time DESC
LIMIT 25

過去 7 天內重複的發現項目

SELECT 
    finding_info.uid,
    MAX(time_dt) AS time,
    ARBITRARY(region) AS region,
    ARBITRARY(accountid) AS accountid,
    ARBITRARY(finding_info) AS finding,
    ARBITRARY(vulnerabilities) AS vulnerabilities
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_sh_findings_2_0"
WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP
GROUP BY finding_info.uid
LIMIT 25

過去 7 天內所有非資訊性發現

SELECT 
    time_dt,
    finding_info.title,
    finding_info,
    severity
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_sh_findings_2_0"
WHERE severity != 'Informational' and time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP
LIMIT 25

資源為 Amazon S3 儲存貯體的發現項目 (無時間限制)

SELECT *
   FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_sh_findings_2_0"
WHERE any_match(resources, element -> element.type = 'amzn-s3-demo-bucket')
LIMIT 25

常見弱點評分系統 (CVSS) 分數大於 1 (無時間限制) 的發現項目

SELECT
    DISTINCT finding_info.uid
    time_dt,
    metadata,
    finding_info,
    vulnerabilities,
    resource
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_sh_findings_2_0",
UNNEST(vulnerabilities) AS t(vulnerability),
UNNEST(vulnerability.cve.cvss) AS t(cvs)
WHERE cvs.base_score > 1.0
AND vulnerabilities is NOT NULL
LIMIT 25

符合常見弱點和曝光 (CVE) 的發現項目 CVE-0000-0000 (無時間限制)

SELECT *
    FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_sh_findings_2_0"
WHERE any_match(vulnerabilities, element -> element.cve.uid = 'CVE-0000-0000')
LIMIT 25

過去 7 天內從 Security Hub 傳送發現項目的產品計數

SELECT 
    metadata.product.name,
    count(*)
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_sh_findings_2_0"
WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP
GROUP BY metadata.product.name
ORDER BY metadata.product.name DESC
LIMIT 25

過去 7 天內發現項目中的資源類型計數

SELECT
    count(*) AS "Total",
    resource.type
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_sh_findings_2_0"
WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP
GROUP BY resource.type
ORDER BY count(*) DESC
LIMIT 25

過去 7 天內發現的易受攻擊的套件

SELECT 
    vulnerabilities
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_sh_findings_2_0"
WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP
AND vulnerabilities is NOT NULL
LIMIT 25

過去 7 天內已變更的發現項目

SELECT 
    status,
    finding_info.title,
    finding_info.created_time_dt,
    finding_info,
    finding_info.uid,
    finding_info.first_seen_time_dt,
    finding_info.last_seen_time_dt,
    finding_info.modified_time_dt
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_sh_findings_2_0"
WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP
LIMIT 25

查詢 Amazon VPC 流程日誌

Amazon Virtual Private Cloud (AmazonVPC) 提供有關進出網路界面的 IP 流量的詳細資訊VPC。

以下是 Amazon VPC 流量日誌的一些查詢範例：

特定交通 AWS 區域 在過去 7 天

SELECT *
    FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_vpc_flow_2_0"
WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP 
AND region in ('us-east-1','us-east-2','us-west-2')
LIMIT 25

過去 7 天來自來源 IP 192.0.2.1 和來源通訊埠22的活動清單

SELECT *
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_vpc_flow_2_0"
WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP 
AND src_endpoint.ip = '192.0.2.1'
AND src_endpoint.port = 22
LIMIT 25

過去 7 天內不同目的地 IP 位址的計數

SELECT 
    COUNT(DISTINCT dst_endpoint.ip) AS "Total"
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_vpc_flow_2_0"
WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP 
LIMIT 25

過去七天內自 198.51.100.0/24 之間的流量

SELECT *
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_vpc_flow_2_0"
WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP 
AND split_part(src_endpoint.ip,'.', 1)='198'AND split_part(src_endpoint.ip,'.', 2)='51'
LIMIT 25

過去 7 天內的所有HTTPS流量

SELECT 
    dst_endpoint.ip as dst, 
    src_endpoint.ip as src, 
    traffic.packets
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_vpc_flow_2_0"
WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP 
AND dst_endpoint.port = 443
GROUP BY 
    dst_endpoint.ip, 
    traffic.packets, 
    src_endpoint.ip 
ORDER BY traffic.packets DESC 
LIMIT 25

過去 7 天443內目的地連線的封包數量排序

SELECT 
    traffic.packets,
    dst_endpoint.ip
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_vpc_flow_2_0"
WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP 
AND dst_endpoint.port = 443 
GROUP BY 
    traffic.packets,
    dst_endpoint.ip
ORDER BY traffic.packets DESC
LIMIT 25

IP 192.0.2.1 與過去 7 天之192.0.2.2間的所有流量

SELECT 
    start_time_dt, 
    end_time_dt, 
    src_endpoint.interface_uid, 
    connection_info.direction,
    src_endpoint.ip,
    dst_endpoint.ip,
    src_endpoint.port,
    dst_endpoint.port,
    traffic.packets,
    traffic.bytes
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_vpc_flow_2_0"
WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP 
AND(
    src_endpoint.ip = '192.0.2.1'
AND dst_endpoint.ip = '192.0.2.2')
OR (
    src_endpoint.ip = '192.0.2.2'
AND dst_endpoint.ip = '192.0.2.1')
ORDER BY start_time_dt ASC
LIMIT 25

過去 7 天內的所有入站流量

SELECT *
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_vpc_flow_2_0"
WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP 
AND connection_info.direction = 'Inbound'
LIMIT 25

過去 7 天內的所有輸出流量

SELECT *
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_vpc_flow_2_0"
WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP 
AND connection_info.direction = 'Outbound'
LIMIT 25

過去 7 天內所有拒絕的流量

SELECT *
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_vpc_flow_2_0"
WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP 
AND action = 'Denied'
LIMIT 25

查詢 Amazon EKS 稽核日誌

Amazon EKS 日誌追蹤控制平面活動可將稽核和診斷日誌直接從 Amazon EKS 控制平面提供到您帳戶中的 CloudWatch 日誌。這些日誌可讓您輕鬆執行叢集並確保叢集的安全。訂閱者可以查詢EKS記錄以瞭解下列類型的資訊。

以下是 Amazon EKS 稽核日誌的一些查詢範例：

最近 7 天內對特定URL的請求

SELECT 
    time_dt,
    actor.user.name,
    http_request.url.path,
    activity_name
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_eks_audit_2_0" 
WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP 
AND activity_name = 'get'
and http_request.url.path = '/apis/coordination.k8s.io/v1/'
LIMIT 25                      

在過去 7 天內，來自「10.0.97.167」的更新要求

SELECT 
    activity_name,
    time_dt,
    api.request,
    http_request.url.path,
    src_endpoint.ip,
    resources
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_eks_audit_2_0" 
WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP 
AND src_endpoint.ip = '10.0.97.167'
AND activity_name = 'Update'
LIMIT 25

過去 7 天內與資源 'kube-controller-manager' 相關聯的要求和回應

SELECT 
    activity_name,
    time_dt,
    api.request,
    api.response,
    resource.name
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_eks_audit_2_0",
UNNEST(resources) AS t(resource)
WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP 
AND resource.name = 'kube-controller-manager'
LIMIT 25

查詢 AWS WAF第 2 版日誌

AWS WAF 是一種 Web 應用程式防火牆，可用來監控使用者傳送至應用程式的 Web 要求，以及控制對內容的存取。

以下是一些查詢示例 AWS WAF第 2 版記錄檔：

在過去 7 天內張貼來自特定來源 IP 的要求

SELECT 
    time_dt,
    activity_name,
    src_endpoint.ip,
    http_request.url.path,
    http_request.url.hostname,
    http_request.http_method,
    http_request.http_headers
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_waf_2_0" 
WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP
AND src_endpoint.ip = '100.123.123.123'
AND activity_name = 'Post'
LIMIT 25

過去 7 天內符合防火牆類型 MANAGED RULE _ GROUP 的要求

SELECT 
    time_dt,
    activity_name,
    src_endpoint.ip,
    http_request.url.path,
    http_request.url.hostname,
    http_request.http_method,
    firewall_rule.uid,
    firewall_rule.type,
    firewall_rule.condition,
    firewall_rule.match_location,
    firewall_rule.match_details,
    firewall_rule.rate_limit
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_waf_2_0" 
WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP
AND firewall_rule.type = 'MANAGED_RULE_GROUP'
LIMIT 25

過去 7 天REGEX內符合防火牆規則中的要求

SELECT 
    time_dt,
    activity_name,
    src_endpoint.ip,
    http_request.url.path,
    http_request.url.hostname,
    http_request.http_method,
    firewall_rule.uid,
    firewall_rule.type,
    firewall_rule.condition,
    firewall_rule.match_location,
    firewall_rule.match_details,
    firewall_rule.rate_limit
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_waf_2_0" 
WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP
AND firewall_rule.condition = 'REGEX'
LIMIT 25

拒絕取得的要求 AWS 觸發的憑據 AWS WAF 過去 7 天的規則

SELECT 
    time_dt,
    activity_name,
    action,
    src_endpoint.ip,
    http_request.url.path,
    http_request.url.hostname,
    http_request.http_method,
    firewall_rule.uid,
    firewall_rule.type
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_waf_2_0" 
WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP
AND http_request.url.path = '/.aws/credentials'
AND action = 'Denied'
LIMIT 25

取得的要求 AWS 過去 7 天內依國家/地區分組的認證

SELECT count(*) as Total,
    src_endpoint.location.country AS Country,
    activity_name,
    action,
    src_endpoint.ip,
    http_request.url.path,
    http_request.url.hostname,
    http_request.http_method
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_waf_2_0"
WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY
    AND CURRENT_TIMESTAMP
    AND activity_name = 'Get'
    AND http_request.url.path = '/.aws/credentials'
GROUP BY src_endpoint.location.country,
    activity_name,
    action,
    src_endpoint.ip,
    http_request.url.path,
    http_request.url.hostname,
    http_request.http_method