BigQuery-LookerSutdio Audit Log SQL

• LookerStudio Pro 사용시 Audit Log To BigQuery Export 에서 Workspace 목록 추출

SELECT
  FORMAT_TIMESTAMP(('%Y-%m-%d %H:%M:%S'), TIMESTAMP_MICROS(log.time_usec), 'Asia/Seoul') AS inq_dttm,
  gwslog.event_name,
  gwslog.email,
  gwslog.event_type,
  gwslog.data_studio.asset_id,
  gwslog.data_studio.asset_name,
  gwslog.data_studio.parent_workspace_id,
  rs.asset_name latest_asset_name,
  gwslog.data_studio.owner_email,
  gwslog.data_studio.visibility,
  rs.owner_email latest_owner_email,
  rs.visibility latest_visibility,
  rs.parent_workspace_id latest_parent_workspace_id
FROM
  `[Project_ID].[DataSet].activity` gwslog 
LEFT JOIN ( -- 가장 최근에 변경된 보고서명을 가져오기 위한 서브쿼리 조인
  SELECT
    data_studio.asset_id,
    data_studio.asset_name,
    data_studio.visibility,
    data_studio.owner_email,
    data_studio.parent_workspace_id,
    ROW_NUMBER() OVER (PARTITION BY data_studio.asset_id ORDER BY time_usec DESC) AS rn
  FROM
    `[Project_ID].[DataSet].activity`
  WHERE
    record_type ='data_studio'
    AND data_studio.asset_type='WORKSPACE'
  QUALIFY
    rn=1 ) rs 
ON
  gwslog.data_studio.asset_id = rs.asset_id
WHERE
  gwslog.record_type = 'data_studio'
  AND event_name='CREATE' -- 생성보고서 기준으로 필터링 조건
  AND data_studio.asset_type='WORKSPACE' -- LookerStudio Pro에서 Workspace 생성 목록 필터링 조건 
  AND NOT EXISTS ( -- 현재 삭제된 Workspace는 제외
  SELECT
    x.data_studio.asset_id
  FROM
    `[Project_ID].[DataSet].activity` x
  WHERE
    1=1
    AND event_name='TRASH' -- LookerStudio Pro에서 삭제된 리스트 필터링 조건
    AND x.data_studio.asset_id = gwslog.data_studio.asset_id
    AND data_studio.asset_type='WORKSPACE'
    AND x.data_studio.asset_id = gwslog.data_studio.asset_id)