Database API¶

Staffcop использует реляционную базу данных PostgreSQL.

Прямое подключение к базе данных – наиболее эффективный способ извлечения данных.

Настройка удаленного подключения¶

Шаг 1. Разрешите подключение к PostgreSQL с внешних IP¶

Найдите файл postgresql.conf в папке /etc/postgresql/[ВЕРСИЯ]/main/ и откройте его для редактирования.
Измените параметр:

listen_addresses = 'localhost,00.00.00.000'             # what IP address(es) to listen on
Убедитесь, что перед этой строкой нет символа комментария #.

Замените 00.00.00.00 на имя вашего сервера, на котором установлена база данных.

В этой же директории найдите файл pg_hba.conf и добавьте строку:

host    staffcop        staffcop        00.00.00.00/32         md5
где:

host – тип подключения;

staffcop – база данных, к которой будет доступ;

staffcop – имя пользователя, которому разрешено подключение;

00.00.00.00/32 – замените на имя вашего ПК и маску сети для подключения;

md5 – способ шифрования пароля.

Перезапустите PostgreSQL командой:

service postgresql restart

Шаг 2. Получите пароль для подключения к БД¶

Откройте файл /etc/staffcop/config и найдите пароль:

DATABASES = {
    'default': {
         'NAME': 'staffcop',
        'USER': 'staffcop',
         'PASSWORD': 'password',
         'HOST': '',
         'PORT': '',
     }
 }

Шаг 3. Подключитесь с другого ПК¶

Используйте клиент для проверки подключения, например pgAdmin.

В pgAdmin нажмите Новое подключение и заполните поля:

Имя – имя, например: my_server;

Хост – IP-адрес сервера, например: 188.123.456.768;

Порт – 5432 (по умолчанию);

Имя пользователя – staffcop;

Пароль – пароль, полученный из файла конфигурации на Шаге 2.
Остальные поля стоят по умолчанию.

Нажимите OK. Если всё заработало – удалённое подключение к PostgreSQL настроено.

Выгрузка данных¶

Для выгрузки всех данных в денормализованном виде (факты и измерения) используйте SQL-запрос:

SELECT
DATE("local_time"),
COALESCE(agent_event.id, 0) AS "agent_event.id",
COALESCE(agent_event.activity_time, 0) AS "agent_event.activity_time",
COALESCE(agent_event.idle_time, 0) AS "agent_event.idle_time",
COALESCE(agent_event.call_duration, 0) AS "agent_event.call_duration",
replace(replace(array(select filter_id from analytics_filter_events where event_id=agent_event.id)::text, '{', '['), '}', ']') AS "agent_event.filters",
COALESCE(agent_time.id, 0) AS "agent_time.id",
COALESCE(agent_time.year, 0) AS "agent_time.year",
COALESCE(agent_time.month, 0) AS "agent_time.month",
COALESCE(agent_time.day, 0) AS "agent_time.day",
COALESCE(agent_time.hour, 0) AS "agent_time.hour",
COALESCE(agent_time.quarter, 0) AS "agent_time.quarter",
COALESCE(agent_time.minute, 0) AS "agent_time.minute",
COALESCE(agent_time.weekday, 0) AS "agent_time.weekday",
COALESCE(agent_time.time_zone::text, '') AS "agent_time.time_zone",
COALESCE(agent_eventtype.id, 0) AS "agent_eventtype.id",
COALESCE(agent_eventtype.description_ru::text, '') AS "agent_eventtype.description_ru",
COALESCE(agent_eventtype.description_en::text, '') AS "agent_eventtype.description_en",
COALESCE(agent_eventtype.name::text, '') AS "agent_eventtype.name",
COALESCE(agent_agent.id, 0) AS "agent_agent.id",
COALESCE(agent_agent.guid::text, '') AS "agent_agent.guid",
COALESCE(agent_agent.computer_name::text, '') AS "agent_agent.computer_name",
COALESCE(agent_agent.last_ip::text, '') AS "agent_agent.last_ip",
COALESCE(agent_agent.agent_version::text, '') AS "agent_agent.agent_version",
COALESCE(agent_agent.os::text, '') AS "agent_agent.os",
COALESCE(agent_agent.last_report_time::text, '') AS "agent_agent.last_report_time",
COALESCE(agent_agent.last_activity_time::text, '') AS "agent_agent.last_activity_time",
COALESCE(agent_agent.label::text, '') AS "agent_agent.label",
COALESCE(agent_agent.last_report::text, '') AS "agent_agent.last_report",
COALESCE(agent_account.id, 0) AS "agent_account.id",
COALESCE(agent_account.guid::text, '') AS "agent_account.guid",
COALESCE(agent_account.user_name::text, '') AS "agent_account.user_name",
COALESCE(agent_account.last_report_time::text, '') AS "agent_account.last_report_time",
COALESCE(agent_account.last_activity_time::text, '') AS "agent_account.last_activity_time",
COALESCE(agent_account.full_name::text, '') AS "agent_account.full_name",
COALESCE(agent_account.office::text, '') AS "agent_account.office",
COALESCE(agent_account.company::text, '') AS "agent_account.company",
COALESCE(agent_account.post::text, '') AS "agent_account.post",
COALESCE(agent_account.phone::text, '') AS "agent_account.phone",
COALESCE(agent_account.mail::text, '') AS "agent_account.mail",
COALESCE(agent_account.comment::text, '') AS "agent_account.comment",
COALESCE(agent_account.user_domain::text, '') AS "agent_account.user_domain",
COALESCE(agent_application.id, 0) AS "agent_application.id",
COALESCE(agent_application.app_name::text, '') AS "agent_application.app_name",
COALESCE(agent_application.exe_path::text, '') AS "agent_application.exe_path",
COALESCE(agent_application.app_description::text, '') AS "agent_application.app_description",
COALESCE(agent_event.window_title::text, '') AS "agent_event.window_title",
COALESCE(agent_application.icon::text, '') AS "agent_application.icon",
COALESCE(agent_web.site::text, '') AS "agent_web.site",
COALESCE(agent_web.domain::text, '') AS "agent_web.domain",
COALESCE(agent_web.url::text, '') AS "agent_web.url",
COALESCE(agent_web.id, 0) AS "agent_web.id",
COALESCE(agent_web.protocol::text, '') AS "agent_web.protocol",
COALESCE(agent_web.mime::text, '') AS "agent_web.mime",
COALESCE(agent_networkconnection.id, 0) AS "agent_networkconnection.id",
COALESCE(agent_networkconnection.remote_address::text, '') AS "agent_networkconnection.remote_address",
COALESCE(agent_networkconnection.remote_port::text, '') AS "agent_networkconnection.remote_port",
COALESCE(agent_filepath.name::text, '') AS "agent_filepath.name",
COALESCE(agent_filepath.ext::text, '') AS "agent_filepath.ext",
COALESCE(agent_filepath.path::text, '') AS "agent_filepath.path",
COALESCE(agent_attachedfile.mime::text, '') AS "agent_attachedfile.mime",
COALESCE(agent_attachedfile.data::text, '') AS "agent_attachedfile.data",
COALESCE(agent_device.drive_type::text, '') AS "agent_device.drive_type",
COALESCE(agent_attachedfile.sha1::text, '') AS "agent_attachedfile.sha1",
COALESCE(agent_attachedfile.guid::text, '') AS "agent_attachedfile.guid",
COALESCE(agent_attachedfile.id, 0) AS "agent_attachedfile.id",
COALESCE(agent_attachedfile.extracted::text, '') AS "agent_attachedfile.extracted",
COALESCE(agent_fileoperationtype.description_en::text, '') AS "agent_fileoperationtype.description_en",
COALESCE(agent_fileoperationtype.description_ru::text, '') AS "agent_fileoperationtype.description_ru",
COALESCE(agent_device.manufacturer::text, '') AS "agent_device.manufacturer",
COALESCE(agent_device.description::text, '') AS "agent_device.description",
COALESCE(agent_device.hwid::text, '') AS "agent_device.hwid",
COALESCE(agent_device.device_class::text, '') AS "agent_device.device_class",
COALESCE(agent_dialog.sender::text, '') AS "agent_dialog.sender",
COALESCE(agent_dialog.recipients::text, '') AS "agent_dialog.recipients",
COALESCE(agent_dialog.sender_domain::text, '') AS "agent_dialog.sender_domain",
COALESCE(agent_dialog.direction::text, '') AS "agent_dialog.direction",
COALESCE(agent_dialog.dialog_type::text, '') AS "agent_dialog.dialog_type",
COALESCE(agent_dialog.message_format::text, '') AS "agent_dialog.message_format",
COALESCE(agent_dialog.members::text, '') AS "agent_dialog.members",
COALESCE(agent_appinstallation.install_event::text, '') AS "agent_appinstallation.install_event",
COALESCE(agent_appinstallation.product_name::text, '') AS "agent_appinstallation.product_name",
COALESCE(agent_appinstallation.publisher::text, '') AS "agent_appinstallation.publisher",
COALESCE(agent_appinstallation.version::text, '') AS "agent_appinstallation.version",
COALESCE(agent_event.text_data::text, '') AS "agent_event.text_data",
COALESCE(agent_feedbacktype.id, 0) AS "agent_feedbacktype.id",
COALESCE(agent_feedbacktype.description_ru::text, '') AS "agent_feedbacktype.description_ru",
COALESCE(agent_feedbacktype.description_en::text, '') AS "agent_feedbacktype.description_en",
COALESCE(agent_feedbacktype.name::text, '') AS "agent_feedbacktype.name",
COALESCE(agent_event.logon_event::text, '') AS "agent_event.logon_event",
DATE_TRUNC('second', agent_event.local_time::TIMESTAMP WITHOUT TIME ZONE) AS "agent_event.local_time",
COALESCE(agent_event.time_registered::text, '') AS "agent_event.time_registered",
COALESCE(agent_event.parent_id::text, '') AS "agent_event.parent_id",
COALESCE(agent_event.children_count::text, '') AS "agent_event.children_count"

FROM agent_event
LEFT JOIN agent_feedbacktype ON agent_feedbacktype.id = agent_event.feedback_id
LEFT JOIN agent_dialog ON agent_dialog.id = agent_event.dialog_id
LEFT JOIN agent_time ON agent_time.id = agent_event.time_id
LEFT JOIN agent_application ON agent_application.id = agent_event.application_id
LEFT JOIN agent_filepath ON agent_filepath.id = agent_event.file_path_id
LEFT JOIN agent_eventtype ON agent_eventtype.id = agent_event.event_type_id
LEFT JOIN agent_agent ON agent_agent.id = agent_event.agent_id
LEFT JOIN agent_web ON agent_web.id = agent_event.web_data_id
LEFT JOIN agent_fileoperationtype ON agent_fileoperationtype.id = agent_event.file_operation_id
LEFT JOIN agent_attachedfile ON agent_attachedfile.id = agent_event.attached_file_id
LEFT JOIN agent_appinstallation ON agent_appinstallation.id = agent_event.app_installation_id
LEFT JOIN agent_networkconnection ON agent_networkconnection.id = agent_event.net_data_id
LEFT JOIN agent_device ON agent_device.id = agent_event.device_id
LEFT JOIN agent_account ON agent_account.id = agent_event.account_id