안녕하세요. 오늘은 베스핀글로벌 DevOps실 이주웅님이 작성해 주신 Bigquery Audit Log를 구성하고 사용자/시간대 별 비용 확인하기에 대해 알아보겠습니다. 궁금하신 부분이 있으시면 댓글을 달아주세요 🙂
1. 감사로그 설정 방법
Stackdriver의 로그 기록을 이용하여 Bigquery 감사로그를 BigQuery에 내보낼 수 있습니다.
감사로그는 데이터에 대한 읽기 및 변경과 관련된 DML성 data_access 로그와 Table 및 Dataset 생성과 관련된 DDL성 activity 로그로 Dataset 내 Table로 구분되어 저장됩니다.
[note] 작업 했던 시점차이로 현재는 테이블이 만료되어 삭제될때에 대한 로그가 system_event에 추가로 기록되는 것 같네요. Bigquery는 Table 및 Partition에 만료기한을 설정하여 데이터에 대한 Retention Policy 관리가 가능합니다. https://cloud.google.com/bigquery/docs/managing-partitioned-tables?hl=ko |
StackDriver > 로그기록 > 내보내기 > 상단에 내보내기 만들기
![](https://i0.wp.com/bespin-wordpress-bucket.s3.ap-northeast-2.amazonaws.com/wp-content/uploads/2023/05/image-61-1024x227.png?resize=1024%2C227&ssl=1)
- 싱크이름 : 싱크 명
- 싱크서비스 : BigQuery, Colud Storage, Colud pub/Sub 중에 선택가능
- 싱크 대상 위치 : BigQuery를 선택했으면 Dataset 이름을 넣어준다.
![](https://i0.wp.com/bespin-wordpress-bucket.s3.ap-northeast-2.amazonaws.com/wp-content/uploads/2023/03/1-33.png?resize=532%2C568&ssl=1)
싱크 생성이 완료되면 BigQuery에 감사로그 테이블을 만들어야 하는데 싱크 서비스 계정이 BigQuery에 테이블 생성 권한이 없어 수동으로 구성이 필요합니다.
![](https://i0.wp.com/bespin-wordpress-bucket.s3.ap-northeast-2.amazonaws.com/wp-content/uploads/2023/03/1-34.png?resize=543%2C283&ssl=1)
작성자 ID를 복사하여 IAM 및 관리자에서 위 계정을 IAM에 BigQuery 쓰기 권한을 줘서 등록 해야합니다.
![](https://i0.wp.com/bespin-wordpress-bucket.s3.ap-northeast-2.amazonaws.com/wp-content/uploads/2023/05/image-62-1024x560.png?resize=1024%2C560&ssl=1)
이제 BiGquery에 DDL, DML 작업이 실행되면 Dataset에 아래 이름으로 테이블이 생성됩니다.
- cloudaudit_googleapis_com_activity_[date]
- cloudaudit_googleapis_com_data_access_[date]
![](https://i0.wp.com/bespin-wordpress-bucket.s3.ap-northeast-2.amazonaws.com/wp-content/uploads/2023/03/1-36.png?resize=447%2C450&ssl=1)
2. 시간대/사용자 별 비용 확인하기
A. 시간대 별 비용
#standardSQL SELECT TIMESTAMP_TRUNC(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.endTime, HOUR) as time_window, FORMAT(‘%9.2f’,5.0 * (SUM(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes)/POWER(2, 40))) as Estimated_USD_Cost FROM `AuditLogsDataSet.cloudaudit_googleapis_com_data_access_YYYYMMDD` WHERE protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.eventName = ‘query_job_completed’ GROUP BY time_window ORDER BY time_window DESC |
![](https://i0.wp.com/bespin-wordpress-bucket.s3.ap-northeast-2.amazonaws.com/wp-content/uploads/2023/03/1-38.png?resize=356%2C234&ssl=1)
B. 사용자 별 비용
#standardSQL WITH data as ( SELECT protopayload_auditlog.authenticationInfo.principalEmail as principalEmail, protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent as jobCompletedEvent FROM `AuditLogsDataSet.cloudaudit_googleapis_com_data_access_YYYYMMDD` ) SELECT principalEmail, FORMAT(‘%9.2f’,5.0 * (SUM(jobCompletedEvent.job.jobStatistics.totalBilledBytes)/POWER(2, 40))) as Estimated_USD_Cost FROM data WHERE jobCompletedEvent.eventName = ‘query_job_completed’ GROUP BY principalEmail ORDER BY Estimated_USD_Cost DESC |
![](https://i0.wp.com/bespin-wordpress-bucket.s3.ap-northeast-2.amazonaws.com/wp-content/uploads/2023/03/1-39.png?resize=421%2C211&ssl=1)
3. 주요 컬럼 정보
![](https://i0.wp.com/bespin-wordpress-bucket.s3.ap-northeast-2.amazonaws.com/wp-content/uploads/2023/03/1-40-1024x222.png?resize=1152%2C250&ssl=1)
![](https://i0.wp.com/bespin-wordpress-bucket.s3.ap-northeast-2.amazonaws.com/wp-content/uploads/2023/03/1-41-1024x263.png?resize=1154%2C296&ssl=1)
4. 참고 자료
- BigQuery를 이용한 감사 로그 분석 설정 방법
https://cloud.google.com/bigquery/audit-logs - Destination permissions 설정 방법
https://cloud.google.com/logging/docs/export/configure_export_v2 - BigQuery 가격 정보
https://cloud.google.com/bigquery/pricing
감사합니다~ 🙂
![](https://i0.wp.com/bespin-wordpress-bucket.s3.ap-northeast-2.amazonaws.com/wp-content/uploads/2023/03/%EB%A7%88%EB%AC%B4%EB%A6%AC-3.png?resize=504%2C109&ssl=1)
문의: info@bespinglobal.com | 대표번호: 02-1668-1280