actions_summary_daily.sqlx

/*
 * Copyright 2024 Google LLC
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

config {
  type: "incremental",
  // tags: ["summary", "daily"],
  uniqueKey: audit_logs.adminActionsUniqueColumns,
  bigquery: {
    partitionBy: "day",
    updatePartitionFilter:
      "day >= time_window_checkpoint"
  }
}

pre_operations {
  DECLARE time_window_checkpoint DEFAULT (
    ${when(incremental(),
    `SELECT MAX(day) FROM ${self()}`,
    `SELECT DATE("2000-01-01")`)}
  )
}

SELECT
  ${ref("stringify_log_entry")}(${audit_logs.adminActionsUniqueColumns}, counter) AS content,
  *
FROM (
  SELECT
    EXTRACT(DATE FROM timestamp) AS day,
    IFNULL(proto_payload.audit_log.authentication_info.principal_email, "unknown") as principal_email,
    IFNULL(proto_payload.audit_log.method_name, "unknown") as action,
    IFNULL(resource.type, "unknown") as resource_type,
    ${ref("get_resource_id")}(resource.type, resource.labels) AS resource_id,
    -- proto_payload.audit_log.resource_name as resource_name,
    SPLIT(log_name, '/')[SAFE_OFFSET(0)] as container_type,
    SPLIT(log_name, '/')[SAFE_OFFSET(1)] as container_id,
    ${ref("get_channel_type")}(proto_payload.audit_log.request_metadata.caller_supplied_user_agent) AS channel,
    IFNULL(proto_payload.audit_log.request_metadata.caller_ip, "unknown") as ip,
    -- ANY_VALUE(resource) as resource,           -- for debugging
    -- ANY_VALUE(proto_payload) as proto_payload, -- for debugging
    COUNT(*) counter
  FROM ${ref("_AllLogs")}
  WHERE
    ${ when(incremental(), `timestamp >= CAST(time_window_checkpoint AS TIMESTAMP) AND`) }
    timestamp >=  TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL ${dataform.projectConfig.vars.summary_lookback_days} DAY)
    ${ when(dataform.projectConfig.vars.admin_activity_only === "true",
    `AND log_id = "cloudaudit.googleapis.com/activity"`) }
  GROUP BY
    ${audit_logs.adminActionsUniqueColumns}
  ORDER BY
    day DESC, principal_email, action
)