[G Suite for Enterprise 기능] BigQuery의 Gmail 로그 검색어 예

G Suite 제품에는 크게 3가지 종류가 있습니다.

• G Suite Basic - 1사용자/30GB/연간 50달러
• G Suite for Business - 1사용자/무제한용량/연간 120달러
• G Suite for Enterprise - 1사용자/무제한용량/연간 300달러

이 3가지 제품의 자세한 차이점을 상세히 비교한 자료는 https://goo.gl/LgD16G 를 참고하시기 바랍니다.

G Suite for Enterprise 의 핵심 기능중에 하나는 지메일 로그 대상으로 Google BigQuery를 적용할 수 있습니다. 

그동안 지메일 대상으로 BigQuery를 할 수 있는 예가 무엇이 있을까? 궁금했었습니다. 아래 샘플은 G Suite for Enterprise 의지메일 로그를 대상으로 할 수 있는 BigQuery 검색어 예입니다. 

BigQuery의 Gmail 로그 검색어 예

검색어 예

이 기능은 G Suite Enterprise에서만 제공됩니다.

다음은 BigQuery를 사용하는 Gmail 로그 검색어의 예입니다. 따로 언급되지 않은 경우 모든 예에서는 기존 SQL을 사용합니다. 

검색어는 다음 권장사항을 따르는 것이 좋습니다.

• 분석에 필요한 데이터만 검색합니다. 예에서는 1,000개 일치로 제한하지만, 제한을 직접 설정할 수 있습니다.
• 검색을 시간 내에 완료하도록 합니다. 하루가 일반적입니다.

제목 일치

지정한 제목과 일치하는 최대 1,000개 레코드의 메일 요약 보기:

SELECT FORMAT_UTC_USEC(event_info.timestamp_usec) as timestamp,        message_info.subject,        message_info.source.address,        message_info.destination.address,        message_info.rfc2822_message_id FROM [your_dataset_id.daily_YYYYMMDD] WHERE message_info.subject contains "test" LIMIT 1000

수신자 일치

지정한 수신자의 개별 메일 수:

SELECT EXACT_COUNT_DISTINCT(message_info.rfc2822_message_id) FROM [your_dataset_id.daily_YYYYMMDD] WHERE message_info.destination.address == "recipient@example.com"

처리 방법 및 수신자 일치

다음 두 가지 모두와 일치하는 최대 1,000개 레코드의 메일 요약 보기:

• 지정한 처리 방법(수정, 거부, 격리)
• 지정한 수신자

SELECT FORMAT_UTC_USEC(event_info.timestamp_usec) as timestamp,        message_info.subject,        message_info.source.address,        message_info.destination.address,        message_info.rfc2822_message_id FROM (FLATTEN([your_dataset_id.daily_YYYYMMDD], message_info.destination.address)) WHERE message_info.triggered_rule_info.consequence.action == 17       and message_info.destination.address == "recipient@example.com" LIMIT 1000

규칙 설명이 트리거됨

지정한 규칙 설명이 트리거되는 최대 1,000개 레코드의 메일 요약 보기:

SELECT FORMAT_UTC_USEC(event_info.timestamp_usec) as timestamp,        message_info.subject,        message_info.source.address,        message_info.destination.address,        message_info.rfc2822_message_id, FROM (FLATTEN([your_dataset_id.daily_YYYYMMDD], message_info.destination.address)) WHERE message_info.triggered_rule_info.consequence.reason contains "description" LIMIT 1000

스팸으로 표시됨

최대 1,000개 레코드의 메일 요약 보기: 

• 스팸으로 표시됨
• 지정한 수신자
• 모든 이유

SELECT FORMAT_UTC_USEC(event_info.timestamp_usec) as timestamp,        message_info.subject,        message_info.source.address,        message_info.destination.address,        message_info.rfc2822_message_id FROM [your_dataset_id.daily_YYYYMMDD] WHERE message_info.is_spam and       message_info.destination.address == "recipient@example.com" LIMIT 1000

암호화 프로토콜—암호화되지 않음

암호화 프로토콜—암호화되지 않음인 메일 요약 보기:

SELECT FORMAT_UTC_USEC(event_info.timestamp_usec) as timestamp,        message_info.subject,        message_info.source.address,        message_info.destination.address,        message_info.rfc2822_message_id FROM [your_dataset_id.daily_YYYYMMDD] WHERE message_info.connection_info.smtp_tls_state == 0 LIMIT 1000

암호화 프로토콜—TLS만

암호화 프로토콜—TLS만인 메일 요약 보기:

SELECT FORMAT_UTC_USEC(event_info.timestamp_usec) as timestamp,        message_info.subject,        message_info.source.address,        message_info.destination.address,        message_info.rfc2822_message_id FROM [your_dataset_id.daily_YYYYMMDD] WHERE message_info.connection_info.smtp_tls_state == 1 LIMIT 1000

메시지 ID 일치

지정한 메시지 ID의 메일 세부정보 보기(메시지 ID 주위에 '<>' 포함):

SELECT FORMAT_UTC_USEC(event_info.timestamp_usec) as timestamp,        event_info.success,        event_info.elapsed_time_usec,        message_info.subject,        message_info.source.address,        message_info.source.service,        message_info.source.selector,        message_info.destination.address,        message_info.destination.service,        message_info.destination.selector,        message_info.rfc2822_message_id,        message_info.payload_size,        message_info.num_message_attachments,        message_info.connection_info.smtp_tls_state,        message_info.description FROM [your_dataset_id.daily_YYYYMMDD] WHERE message_info.rfc2822_message_id == "" LIMIT 1000

처리 방법—메일 거부

메일 거부:

• 어떤 규칙으로 거부되었나요?

SELECT FORMAT_UTC_USEC(event_info.timestamp_usec) as timestamp,        message_info.subject,        message_info.source.address,        message_info.destination.address,        message_info.rfc2822_message_id,       message_info.triggered_rule_info.consequence.reason FROM (FLATTEN([your_dataset_id.daily_YYYYMMDD], message_info.destination.address)) WHERE message_info.rfc2822_message_id == "" and       message_info.triggered_rule_info.consequence.action == 17 LIMIT 1000

처리 방법—메일 수정

메일 수정: 

• 어떤 규칙으로 수정되었나요?
• 수정의 하위 카테고리는 무엇인가요(헤더, 제목 등)?
   

SELECT FORMAT_UTC_USEC(event_info.timestamp_usec) as timestamp,        message_info.subject,        message_info.source.address,        message_info.destination.address,        message_info.rfc2822_message_id,        message_info.triggered_rule_info.consequence.action,        message_info.triggered_rule_info.consequence.reason FROM (FLATTEN([your_dataset_id.daily_YYYYMMDD], message_info.destination.address)) WHERE message_info.rfc2822_message_id == "" and       (not message_info.triggered_rule_info.consequence.action is null and        message_info.triggered_rule_info.consequence.action != 0 and        message_info.triggered_rule_info.consequence.action != 17 and   message_info.triggered_rule_info.consequence.action != 3) LIMIT 1000

메일 격리: 

• 어떤 규칙으로 메일이 격리되었나요?

SELECT FORMAT_UTC_USEC(event_info.timestamp_usec) as timestamp,        message_info.subject,        message_info.source.address,        message_info.destination.address,        message_info.rfc2822_message_id,       message_info.triggered_rule_info.consequence.reason FROM (FLATTEN([your_dataset_id.daily_YYYYMMDD], message_info.destination.address)) WHERE message_info.rfc2822_message_id == "" and       message_info.triggered_rule_info.consequence.action == 3 LIMIT 1000

복합 검색어

지난 30일 동안 특정 규칙(규칙 설명)으로 필터링된 모든 메일 수

SELECT COUNT(message_info.rfc2822_message_id) as message_cnt FROM (TABLE_DATE_RANGE([your_dataset_id.daily_],       DATE_ADD(CURRENT_TIMESTAMP(), -30, 'DAY'),       CURRENT_TIMESTAMP())) Where message_info.triggered_rule_info.consequence.reason contains "rule description"

1일 이내에 TLS 암호화 없이 수신된 모든 메일 표시:

SELECT message_info.subject,        message_info.rfc2822_message_id FROM (TABLE_DATE_RANGE([your_dataset_id.daily_],       DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'),       CURRENT_TIMESTAMP())) Where message_info.connection_info.smtp_tls_state == 0

지난 30일 이내에 계정에서 메일을 교환한 상위 10개 도메인 표시:

SELECT   COUNT(message_info.rfc2822_message_id) as message_cnt,   CASE     WHEN message_info.is_policy_check_for_sender       THEN REGEXP_EXTRACT(message_info.source.address , "(@.*)")     ELSE REGEXP_EXTRACT(message_info.destination.address , "(@.*)")   END as domain FROM (TABLE_DATE_RANGE([your_dataset_id.daily_],       DATE_ADD(CURRENT_TIMESTAMP(), -30, 'DAY'),       CURRENT_TIMESTAMP())) GROUP BY domain ORDER BY message_cnt desc LIMIT 10

자세한 내용은 구글 도움말  참고

----------------------------------------

G Suite/Chromebook 전문 블로그 - charlychoi.blogspot.kr