2018년 1월 26일 금요일

[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


[ G Suite 팁] 두개의 Google 스프레드시트 데이터 결합하기

스프레드시트를 사용하여 두개의 스프레드시트 데이터를 결합하여 단일 시트를 쉽게 만들 수 있습니다. 1 단계 : 결합하려는 스프레드시트를 확인합니다. 데이터를 가져올 두 개의 스프레드 시트를 가져옵니다. 원본 스프레드 시트 (예 : "S...