G Suite Update‎ > ‎

[G Suite Enterprise 기능] Gmail 대상 Google BigQuery 검색 예

게시자: Charly Choi, 2018. 1. 25. 오후 5:18   [ 2018. 1. 28. 오후 4:27에 업데이트됨 ]

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

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

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


G Suite 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 == "<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 == "<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 == "<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 == "<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


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

Comments