建立和查詢警示日誌的資料表 - Amazon Athena
查詢範例

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

建立和查詢警示日誌的資料表

  1. 修改下列範例DDL陳述式以符合警示日誌的結構。您可能需要更新陳述式，以加入最新版本日誌的資料欄。如需詳細資訊，請參閱《AWS Network Firewall 開發人員指南》中的防火牆日誌內容。

    CREATE EXTERNAL TABLE network_firewall_alert_logs (
  firewall_name string,
  availability_zone string,
  event_timestamp string,
  event struct<
    timestamp:string,
    flow_id:bigint,
    event_type:string,
    src_ip:string,
    src_port:int,
    dest_ip:string,
    dest_port:int,
    proto:string,
    app_proto:string,
    sni:string,
    tls_inspected:boolean,
    tls_error:struct<
      error_message:string>,
    revocation_check:struct<
      leaf_cert_fpr:string,
      status:string,
      action:string>,
    alert:struct<
      alert_id:string,
      alert_type:string,
      action:string,
      signature_id:int,
      rev:int,
      signature:string,
      category:string,
      severity:int,
      rule_name:string,
      alert_name:string,
      alert_severity:string,
      alert_description:string,
      file_name:string,
      file_hash:string,
      packet_capture:string,
      reference_links:array<string>
    >, 
    src_country:string, 
    dest_country:string, 
    src_hostname:string, 
    dest_hostname:string, 
    user_agent:string, 
    url:string
   >
)
 ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
 LOCATION 's3://amzn-s3-demo-bucket/path_to_alert_logs_folder/';

  2. 修改 LOCATION子句以指定 Amazon S3 中日誌的資料夾。

  3. 在 Athena CREATE TABLE查詢編輯器中執行查詢。查詢完成後，Athena 會註冊network_firewall_alert_logs資料表，讓其指向的資料準備好進行查詢。

查詢範例

本節中的範例警示日誌查詢會針對執行TLS檢查的事件進行篩選，其中的警示嚴重性等級為 2 或更高。

查詢使用別名來建立輸出資料欄標題，以顯示struct資料欄所屬的 。例如， event.alert.category 欄位的欄位標題event_alert_category不是只有 category。若要進一步自訂資料欄名稱，您可以修改別名以符合您的偏好設定。例如，您可以使用底線或其他分隔符號來分隔struct名稱和欄位名稱。

請記得根據您的資料表定義和您在查詢結果中想要的欄位修改資料欄名稱和struct參考。

SELECT 
  firewall_name,
  availability_zone,
  event_timestamp,
  event.timestamp AS event_timestamp,
  event.flow_id AS event_flow_id,
  event.event_type AS event_type,
  event.src_ip AS event_src_ip,
  event.src_port AS event_src_port,
  event.dest_ip AS event_dest_ip,
  event.dest_port AS event_dest_port,
  event.proto AS event_protol,
  event.app_proto AS event_app_proto,
  event.sni AS event_sni,
  event.tls_inspected AS event_tls_inspected,
  event.tls_error.error_message AS event_tls_error_message,
  event.revocation_check.leaf_cert-fpr AS event_revocation_leaf_cert,
  event.revocation_check.status AS event_revocation_check_status,
  event.revocation_check.action AS event_revocation_check_action,
  event.alert.alert_id AS event_alert_alert_id,
  event.alert.alert_type AS event_alert_alert_type,
  event.alert.action AS event_alert_action,
  event.alert.signature_id AS event_alert_signature_id,
  event.alert.rev AS event_alert_rev,
  event.alert.signature AS event_alert_signature,
  event.alert.category AS event_alert_category,
  event.alert.severity AS event_alert_severity,
  event.alert.rule_name AS event_alert_rule_name,
  event.alert.alert_name AS event_alert_alert_name,
  event.alert.alert_severity AS event_alert_alert_severity,
  event.alert.alert_description AS event_alert_alert_description,
  event.alert.file_name AS event_alert_file_name,
  event.alert.file_hash AS event_alert_file_hash,
  event.alert.packet_capture AS event_alert_packet_capture,
  event.alert.reference_links AS event_alert_reference_links,
  event.src_country AS event_src_country,
  event.dest_country AS event_dest_country,
  event.src_hostname AS event_src_hostname,
  event.dest_hostname AS event_dest_hostname,
  event.user_agent AS event_user_agent,
  event.url AS event_url
FROM 
  network_firewall_alert_logs 
WHERE 
  event.alert.severity >= 2
  AND event.tls_inspected = true 
LIMIT 10;