Примеры запросов для журналов отчетов в BigQuery

Эта функция доступна в версии Cloud Identity Premium. Сравнение версий 

В этой статье приведены примеры запросов для создания отчетов в BigQuery. В примерах используется прежняя версия SQL. Замените api_project_name.dataset_name названиями своего проекта и набора данных.

Подробнее об извлечении данных BigQuery…

Поля журнала Gmail и их значения описаны в схеме для журналов активности Gmail в BigQuery.

Примеры запросов

Аккаунты

Количество аккаунтов администратора и делегированных аккаунтов, а также отключенных, заблокированных и приостановленных до определенной даты аккаунтов

SELECT date,
accounts.num_locked_users,
accounts.num_disabled_accounts,
accounts.num_delegated_admins,
accounts.num_super_admins,
accounts.num_suspended_users,
accounts.num_users
FROM api_project_name.dataset_name.usage
WHERE accounts.num_users IS NOT NULL
ORDER BY date ASC;

Администраторы

Наиболее частые события, выполняемые администратором

SELECT count(*) as admin_actions, event_name
FROM api_project_name.dataset_name.activity
WHERE email IN (
  SELECT user_email
  FROM api_project_name.dataset_name.usage
  WHERE accounts.is_super_admin = TRUE
)
GROUP BY 2
ORDER BY 1 DESC;

Количество суперадминистраторов в определенном домене

SELECT COUNT(DISTINCT user_email) as number_of_super_admins, date
FROM api_project_name.dataset_name.usage
WHERE accounts.is_super_admin = TRUE
GROUP BY 2
ORDER BY 2 DESC;

Календарь

Только стандартный SQL

Отношение количества активных пользователей Google Календаря за день к числу его активных пользователей за последние 30 дней. В этом примере запрашиваются данные из нескольких таблиц.

Активные пользователи за день

SELECT date, calendar.num_1day_active_users
FROM api_project_name.dataset_name.usage
WHERE calendar.num_1day_active_users IS NOT NULL
ORDER BY date DESC

Активные пользователи за 30 дней

SELECT date, calendar.num_30day_active_users
FROM api_project_name.dataset_name.usage
WHERE calendar.num_30day_active_users IS NOT NULL
ORDER BY date DESC;

Количество мероприятий календаря по типу

SELECT COUNT(DISTINCT calendar.calendar_id) AS count, event_name
FROM api_project_name.dataset_name.activity
WHERE calendar.calendar_id IS NOT NULL
GROUP BY 2 ORDER BY 1 DESC;

Диск

Количество объектов на Google Диске, к которым предоставлен доступ, сгруппированных по способу предоставления доступа

SELECT COUNT(DISTINCT drive.doc_id) AS count, drive.visibility
FROM api_project_name.dataset_name.activity
WHERE drive.doc_id IS NOT NULL
GROUP BY 2 ORDER BY 1 DESC;

Идентификатор, название, владелец и тип файла. Файлы, доступные за пределами домена на определенный период времени.

SELECT TIMESTAMP_MICROS(time_usec) AS date, drive.doc_id, drive.doc_title,
drive.owner, drive.doc_type
FROM api_project_name.dataset_name.activity
WHERE drive.visibility = "shared_externally"
ORDER BY 1 DESC
LIMIT 100;

Изменения разрешений на предоставление доступа и их результат. Эти данные позволяют узнать, какие изменения разрешений привели к изменению доступности файлов.

SELECT TIMESTAMP_MICROS(time_usec) AS date, drive.doc_title,
drive.visibility_change,drive.old_visibility, drive.visibility,
FROM api_project_name.dataset_name.activity
WHERE record_type = "drive"
AND drive.old_visibility IS NOT NULL
AND drive.old_visibility != "unknown";

Типы событий, сгруппированные по типам файлов. Эти данные полезны для создания отчетов об уровне внедрения по типам файлов.

SELECT drive.doc_type, event_type, count(*) 
FROM  api_project_name.dataset_name.activity
WHERE record_type = "DRIVE"
GROUP by 1,2 ORDER BY 3 desc;

Тип и название события для каждого общего диска

SELECT drive.shared_drive_id, event_type, event_name, record_type,
count(distinct drive.doc_id) AS count
FROM api_project_name.dataset_name.activity
WHERE record_type = "drive"
AND drive.shared_drive_id IS NOT NULL
GROUP BY 1,2,3,4 ORDER BY 5 DESC;

Информация о пользователях за пределами домена

SELECT email, event_name, count(*) AS count
FROM api_project_name.dataset_name.activity
WHERE email != ""
AND email NOT LIKE "%mydomain.com%"
GROUP BY 1,2 ORDER BY 3 DESC;

Сведения о том, какие разрешения предоставлялись пользователям за пределами домена и когда это происходило

SELECT drive.target_user, event_name, count(*) AS count
FROM api_project_name.dataset_name.activity
WHERE drive.target_user IS NOT NULL
AND drive.target_user NOT LIKE "%mydomain.com%"
GROUP BY 1,2 ORDER BY 3 DESC;

Информация о мониторинге хранилища

Эти данные полезны для создания отчетов о пользователях, потребляющих более чем Х пространства в хранилище, с заданным пороговым значением (определенным с помощью условия AND accounts.drive_used_quota_in_mb > 0).

Этот запрос может быть определен как плановый запрос или вызываться периодически с помощью API.

SELECT date,
user_email,
accounts.drive_used_quota_in_mb,
FROM api_project_name.dataset_name.usage
WHERE accounts.drive_used_quota_in_mb IS NOT NULL
AND accounts.drive_used_quota_in_mb > 0
AND user_email != ""
AND date = CAST(DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AS STRING)
ORDER BY 3,1 DESC;

Примечания

• Это значение можно изменить так, чтобы оно соответствовало настроенному клиентом фильтру. Например, более 15 ГБ: AND accounts.drive_used_quota_in_mb > 15000
• Использование CAST(DATE_SUB(CURRENT_DATE(), INTERVAL x DAY) AS STRING) позволяет сравнивать даты с доступным форматом из значения date.

• Этот запрос также применим к Gmail, где есть аналогичное значение: accounts.gmail_used_quota_in_mb

Gmail

Рекомендации по работе с запросами для журналов Gmail в BigQuery

• Запрашивайте только те данные, которые требуются для анализа. Приведенные примеры запросов позволяют извлечь не более 1000 записей, но вы можете настроить собственное ограничение.
• Ограничивайте время работы запроса. Обычно достаточно одного дня.

Соответствие темы
Сводка сообщений, тема которых соответствует указанной (не более 1000 записей).

SELECT TIMESTAMP_MICROS(gmail.event_info.timestamp_usec) as timestamp,
     gmail.message_info.subject,
     gmail.message_info.source.address,
     gmail.message_info.rfc2822_message_id
FROM your_dataset_id.activity
WHERE gmail.message_info.subject LIKE "%test%"
LIMIT 1000

Соответствие получателя
Количество разных сообщений для указанного получателя.

SELECT COUNT(DISTINCT gmail.message_info.rfc2822_message_id)
FROM your_dataset_id.activity d
WHERE
  EXISTS(
   SELECT 1 FROM d.gmail.message_info.destination WHERE destination.address = "[e-mail]")

Соответствие правила и получателя

Сводка сообщений, которые соответствуют обоим следующим параметрам (не более 1000 записей):

• указанному правилу ("Modify (Изменить)", "Reject (Отклонить)", "Quarantine (Поместить в карантин)");
• заданному получателю.

SELECT TIMESTAMP_MICROS(gmail.event_info.timestamp_usec) as timestamp,
     gmail.message_info.subject,
     gmail.message_info.source.address as source,
     destination.address as destination,
     gmail.message_info.rfc2822_message_id
FROM your_dataset_id.activity d, d.gmail.message_info.destination
WHERE
     destination.address = "[e-mail]" AND
     EXISTS(SELECT 1 FROM d.gmail.message_info.triggered_rule_info ri, ri.consequence
          WHERE consequence.action = 17)
LIMIT 1000

Активировано правило с указанным описанием
Сводка сообщений, для которых сработало правило с указанным описанием (не более 1000 записей).

SELECT TIMESTAMP_MICROS(gmail.event_info.timestamp_usec) as timestamp,
     gmail.message_info.subject,
     gmail.message_info.source.address as source,
     destination.address as destination,
     gmail.message_info.rfc2822_message_id
FROM your_dataset_id.activity d, d.gmail.message_info.destination
WHERE
    EXISTS(SELECT 1 FROM d.gmail.message_info.triggered_rule_info ri, ri.consequence
       WHERE consequence.reason LIKE '%description%')
LIMIT 1000

Отмечено как спам
Сводка сообщений (не более 1000 записей):

• отмечено как спам;
• для указанного получателя;
• с любой причиной пометки.

SELECT TIMESTAMP_MICROS(gmail.event_info.timestamp_usec) as timestamp,
     gmail.message_info.subject,
     gmail.message_info.source.address as source,
     destination.address as destination,
     gmail.message_info.rfc2822_message_id
FROM your_dataset_id.activity d, d.gmail.message_info.destination
WHERE gmail.message_info.is_spam AND
          destination.address = "[e-mail]"
LIMIT 1000

Для протокола шифрования указано значение not encrypted (не зашифровано)
Сводка сообщений, у которых для параметра протокола шифрования указано значение not encrypted (не зашифровано).

SELECT TIMESTAMP_MICROS(gmail.event_info.timestamp_usec) as timestamp,
     gmail.message_info.subject,       
     gmail.message_info.source.address as source,
     destination.address as destination,
     gmail.message_info.rfc2822_message_id
FROM your_dataset_id.activity d, d.gmail.message_info.destination
WHERE gmail.message_info.connection_info.smtp_tls_state = 0
LIMIT 1000

Для протокола шифрования указано значение TLS only (только TLS)
Сводка сообщений, у которых для параметра протокола шифрования указано значение TLS only (только TLS).

SELECT TIMESTAMP_MICROS(gmail.event_info.timestamp_usec) as timestamp,
     gmail.message_info.subject,
     gmail.message_info.source.address as source,
     destination.address as destination,
     gmail.message_info.rfc2822_message_id
FROM your_dataset_id.activity d, d.gmail.message_info.destination
WHERE gmail.message_info.connection_info.smtp_tls_state = 1
LIMIT 1000

Соответствие идентификатора сообщения
Подробные сведения о сообщении с указанным идентификатором (включая окружающие его угловые скобки <>).

SELECT TIMESTAMP_MICROS(gmail.event_info.timestamp_usec) as timestamp,
     gmail.event_info.success,
     gmail.event_info.elapsed_time_usec,
     gmail.message_info.subject,
     gmail.message_info.source.address as source,
     gmail.message_info.source.service as source_service,
     gmail.message_info.source.selector as source_selector,
     destination.address as destination,
     destination.service,
     destination.selector as destination_selector,
     gmail.message_info.rfc2822_message_id,
     gmail.message_info.payload_size,
     gmail.message_info.num_message_attachments,
     gmail.message_info.connection_info.smtp_tls_state,
     gmail.message_info.description
FROM your_dataset_id.activity d, d.gmail.message_info.destination
WHERE gmail.message_info.rfc2822_message_id = ""
LIMIT 1000

Отклонение сообщения: причина
Отклонение сообщения:

• Согласно какому правилу это сделано?

SELECT TIMESTAMP_MICROS(gmail.event_info.timestamp_usec) as timestamp,
     gmail.message_info.subject,
     gmail.message_info.source.address as source,
     destination.address as destination,
     gmail.message_info.rfc2822_message_id,
     (SELECT ARRAY_AGG(consequence.reason)
     FROM d.gmail.message_info.triggered_rule_info ri, ri.consequence)
FROM your_dataset_id.activity d, d.gmail.message_info.destination
WHERE gmail.message_info.rfc2822_message_id = "<message id>" AND
     EXISTS(SELECT 1 FROM d.gmail.message_info.triggered_rule_info ri, ri.consequence
        WHERE consequence.action = 17)
LIMIT 1000

Изменение сообщения: причина и подробности
Изменение сообщения: 

• Согласно какому правилу это сделано?
• Какой подкатегории (например, заголовки, тема) коснулись изменения?

SELECT TIMESTAMP_MICROS(gmail.event_info.timestamp_usec) as timestamp,
     gmail.message_info.subject,
     gmail.message_info.source.address as source,
     destination.address as destination,
     gmail.message_info.rfc2822_message_id,
     (SELECT ARRAY_AGG((consequence.action, consequence.reason))
     FROM d.gmail.message_info.triggered_rule_info ri, ri.consequence)
FROM your_dataset_id.activity d, d.gmail.message_info.destination
WHERE gmail.message_info.rfc2822_message_id = "<message id>" AND
   EXISTS(SELECT 1 FROM d.gmail.message_info.triggered_rule_info ri, ri.consequence
       WHERE consequence.action NOT IN (0, 17, 3))
LIMIT 1000

Сообщение помещено в карантин
Согласно какому правилу сообщение помещено в карантин?

SELECT TIMESTAMP_MICROS(gmail.event_info.timestamp_usec) as timestamp,
     gmail.message_info.subject,
     gmail.message_info.source.address as source,
     destination.address as destination,
     gmail.message_info.rfc2822_message_id,
     (SELECT ARRAY_AGG(consequence.reason)
     FROM d.gmail.message_info.triggered_rule_info ri, ri.consequence)
FROM your_dataset_id.activity d, d.gmail.message_info.destination
WHERE gmail.message_info.rfc2822_message_id = "<message id>" AND
     EXISTS(SELECT 1 FROM d.gmail.message_info.triggered_rule_info ri, ri.consequence
        WHERE consequence.action = 3)
LIMIT 1000

Сложные запросы
Количество сообщений, для которых правило с указанным описанием ("rule description") сработало в течение последних 30 дней:

SELECT
  COUNT(gmail.message_info.rfc2822_message_id) AS message_cnt
FROM
  `your_dataset_id.activity`,
UNNEST (gmail.message_info.triggered_rule_info) AS triggered_rule
WHERE
  _PARTITIONTIME >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND triggered_rule.rule_name LIKE "rule description"

Список всех сообщений, полученных без шифрования TLS за последний день:

SELECT gmail.message_info.subject,
    gmail.message_info.rfc2822_message_id
FROM `your_dataset_id.activity`
WHERE
    _PARTITIONTIME >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY) AND
    gmail.message_info.connection_info.smtp_tls_state = 0

Список из 10 доменов, с которыми ваш аккаунт чаще всего обменивался почтой в течение последних 30 дней:

SELECT
  COUNT(DISTINCT gmail.message_info.rfc2822_message_id) as message_cnt,
  IF(gmail.message_info.is_policy_check_for_sender, 
       REGEXP_EXTRACT(gmail.message_info.source.address , "(@.*)"),
       REGEXP_EXTRACT(destination.address , "(@.*)")) AS domain
FROM `your_dataset_id.activity` d, d.gmail.message_info.destination
WHERE 
    _PARTITIONTIME >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY domain
ORDER BY message_cnt desc
LIMIT 10

Отношение количества активных пользователей Gmail за день к количеству пользователей, проявлявших активность за последние 30 дней

Активные пользователи за день:

SELECT date,
gmail.num_1day_active_users
FROM api_project_name.dataset_name.usage
WHERE gmail.num_1day_active_users > 0
ORDER BY 1 DESC;

Активные пользователи за 7 дней:

SELECT date,
gmail.num_7day_active_users
FROM api_project_name.dataset_name.usage
WHERE gmail.num_7day_active_users > 0
ORDER BY 1 DESC;

Активные пользователи за 30 дней:

SELECT date,
gmail.num_30day_active_users
FROM api_project_name.dataset_name.usage
WHERE gmail.num_30day_active_users > 0
ORDER BY 1 DESC;

Группы

Изменение количества участников группы Google и их действия

SELECT TIMESTAMP_MICROS(time_usec) AS date,
  event_name,
  admin.group_email,
  event_type,
  email,
  record_type,
  admin.user_email,
  admin.new_value,
  admin.old_value,
  admin.setting_name
FROM project_name.dataset_name.activity
WHERE `admin`.group_email IS NOT NULL
AND CONCAT(TIMESTAMP_MICROS(time_usec)) LIKE "%YYYY-MM-DD%"
ORDER BY 1 DESC
LIMIT
  1000

Если вы хотите использовать временную метку в формате ГГГГ-ММ-ДД, первое выражение SELECT можно заменить следующим:
EXTRACT(DATE FROM TIMESTAMP_MICROS(time_usec)) AS date,

Даты в условии WHERE можно отфильтровать любым из следующих способов:

SELECT TIMESTAMP_MICROS(time_usec) AS date,
  event_name,
  admin.group_email,
  event_type,
  email,
  record_type,
  admin.user_email,
  admin.new_value,
  admin.old_value,
  admin.setting_name
FROM project_name.dataset_name.activity
WHERE `admin`.group_email IS NOT NULL
AND EXTRACT(DATE FROM TIMESTAMP_MICROS(time_usec)) > "2020-06-30"
AND EXTRACT(DATE FROM TIMESTAMP_MICROS(time_usec)) < "2020-08-31"
ORDER BY 1 DESC
LIMIT
  1000

SELECT TIMESTAMP_MICROS(time_usec) AS date,
  event_name,
  admin.group_email,
  event_type,
  email,
  record_type,
  admin.user_email,
  admin.new_value,
  admin.old_value,
  admin.setting_name
FROM project_name.dataset_name.activity
WHERE `admin`.group_email IS NOT NULL
AND TIMESTAMP_MICROS(time_usec) > TIMESTAMP("2020-07-21")
AND TIMESTAMP_MICROS(time_usec) < TIMESTAMP("2020-07-23")
ORDER BY 1 DESC
LIMIT
  1000

Google Meet

Количество звонков и общее число минут по дате

SELECT date, meet.num_calls, meet.total_call_minutes
FROM `api_project_name.dataset_name.usage`
WHERE meet.num_calls IS NOT NULL
ORDER BY date ASC

Активные пользователи за день

SELECT date, meet.num_1day_active_users
FROM `api_project_name.dataset_name.usage`
WHERE meet.num_1day_active_users IS NOT NULL
ORDER BY date DESC

Активные пользователи за 30 дней

SELECT date, meet.num_30day_active_users
FROM `api_project_name.dataset_name.usage`
WHERE meet.num_30day_active_users IS NOT NULL
ORDER BY date DESC

Правила

Активированные правила DLP по названию, соответствующему приложению и действиям

SELECT TIMESTAMP_MICROS(time_usec) AS date, rules.rule_name, rules.application,
rules.resource_title, rules.actions, rules.resource_owner_email,
rules.data_source, rules.matched_trigger
FROM api_project_name.dataset_name.activity
WHERE rules.rule_name IS NOT NULL
ORDER BY 1 DESC LIMIT 1000;

Токены

Количество случаев предоставления доступа к Google Диску сторонним приложениям

SELECT token.client_id, scope, token.app_name, count(*) AS count
FROM api_project_name.dataset_name.activity
LEFT JOIN UNNEST(token.scope) AS scope
WHERE scope LIKE "%drive%"
GROUP BY 1,2,3 ORDER BY 4 DESC;

Попытки входа в консоль администратора

Подробные сведения о неудачных попытках входа в консоль администратора Google

SELECT TIMESTAMP_MICROS(time_usec) AS date, email, ip_address,
event_name, login.login_type, login.login_failure_type
FROM api_project_name.dataset_name.activity
WHERE login.login_type IS NOT NULL
AND login.login_failure_type IS NOT NULL
AND event_type = "login"
ORDER BY date DESC;

Набор атрибутов для таблиц использования

Этот набор атрибутов может изменяться. Обновленный полный список параметров и полей доступен в документации по Reports API.

Как фильтровать данные по дате

Вы можете фильтровать данные по дате при запросе данных из таблиц активности или использования. Они имеют разные форматы отображения даты:

• Таблица активности хранит временные метки в микросекундах Unix. Это целое значение (число), которое можно преобразовать в дату с помощью функции TIMESTAMP_MICROS().
• Таблица использования содержит значения дат в формате дат, поэтому преобразование не требуется.

Для каждой из таблиц вы можете выбрать фильтрацию по конкретной дате (или диапазону дат) одним из описанных далее способов.

Таблица активности

Для фильтрации по конкретной дате с помощью структуры Unix Micros (в таблице активности) можно определить оператор WHERE и функцию TIMESTAMP() для простого сравнения с использованием операторов "больше чем" (>) и "меньше чем" (<):

SELECT TIMESTAMP_MICROS(time_usec) as date, record_type
FROM api_project_name.dataset_name.activity
WHERE TIMESTAMP_MICROS(time_usec) > TIMESTAMP("2020-07-01")
AND TIMESTAMP_MICROS(time_usec) < TIMESTAMP("2020-07-07")
ORDER BY 1 DESC LIMIT 1000

Смысл в том, чтобы установить ограничения на входное значение time_usec, сравнивая возвращаемое значение функции TIMESTAMP_MICROS() с возвращаемым значением функции TIMESTAMP(), где дата добавлена в виде строкового параметра. Этот метод соответствует стандарту, определенному в функциях временных меток в стандартном SQL, и использует операторы простого сравнения (>) и (<), а также расширение AND в условии WHERE, в частности для закрытия временного окна.

Таблица использования

SELECT date, meet.num_calls,
FROM api_project_name.dataset_name.usage
WHERE meet.num_calls IS NOT NULL
AND TIMESTAMP(date) > TIMESTAMP("2020-07-01")
AND TIMESTAMP(date) < TIMESTAMP("2020-07-07")
ORDER BY date DESC;

Мы можем передать строковое значение даты, сохраненное в таблице, в функцию TIMESTAMP() и применить операторы сравнения (>) и (<) так же, как в первом примере.

 

Фильтрация по псевдонимам домена и субдоменам: исключение и включение

Чтобы исключить или включить определенные домены, можно применить фильтрацию адресов электронной почты в условии WHERE с помощью подстановочных знаков (%) для фильтрации доменов.

Использование операторов AND или OR зависит от того, что вам нужно сделать: отфильтровать (исключить) или включить определенные результаты.

Как исключить определенные домены из результатов

WHERE email NOT LIKE ("%@sub.%")
AND email NOT LIKE ("%@test.%")

Как включить в результаты только определенные домены

WHERE email LIKE ("%@sub.%")
OR email LIKE ("%@test.%")

Журналы аудита правил

Этот запрос используется для отслеживания попыток пользователей передать конфиденциальную информацию:

SELECT TIMESTAMP_MICROS(time_usec) AS Date,
rules.resource_owner_email AS User,
rules.rule_name AS ruleName,
rules.rule_type AS ruleType,
rules.rule_resource_name AS ruleResourceName,
rules.resource_id AS resourceId,
rules.resource_title AS resourceTitle,
rules.resource_type AS resourceType,
rules.resource_owner_email AS resourceOwner,
CAST(recipients AS STRING) AS Recipients,
rules.data_source AS dataSource,
rules.actor_ip_address AS actorIpAddress,
rules.severity AS severity,
rules.scan_type AS scanType,
rules.matched_trigger AS matchedTriggers,
detect.display_name AS matchedDetectorsName,
detect.detector_id AS matchedDetectorsId,
detect.detector_type AS matchedDetectorsType,
triggers.action_type AS triggeredActions,
suppressors.action_type AS suppressedActions,
FROM api_project_name.dataset_name.activity
LEFT JOIN UNNEST(rules.resource_recipients) as recipients
LEFT JOIN UNNEST(rules.matched_detectors) as detect
LEFT JOIN UNNEST(rules.triggered_actions) as triggers
LEFT JOIN UNNEST(rules.suppressed_actions) as suppressors
WHERE rules.rule_name IS NOT NULL
AND triggers.action_type != "ALERT"
ORDER BY 1 DESC
LIMIT 1000;

Статьи по теме

• Документация по BigQuery