Database API

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

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

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

  1. Разрешаем подключаться к postgresql с внешних ip.

Находим файл postgresql.conf (находится в папке /etc/postgresql/[ВЕРСИЯ]/main/) и правим следующую строчку:

listen_addresses = '*'

Обратите внимание, что нужно убрать перед директивой listen_addresses знак комментария #.

В файле pg_hba.conf (лежит в там же, где и postgresql.conf) добавляем следующую строчку:

hostssl  all  staffcop  0.0.0.0/0  md5

где:

hostssl - подключаемся через SSL
all - разрешаем подключение ко всем базам
staffcop - имя пользователя, которому разрешаем подключение
0.0.0.0/0 - маска сети с которой можно подключаться
md5 - способ шифрования пароля

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

service postgresql restart
  1. Получаем пароль для подключения к БД

Пароль находится в файле конфигурации, расположенном в /etc/staffcop/config:

DATABASES = {
    'default': {
        'NAME': 'staffcop',
        'USER': 'staffcop',
        'PASSWORD': 'password',
        'HOST': '',
        'PORT': '',
    }
}
  1. Попробуем подключиться с другого ПК

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

В pgadmin нужно щёлкнуть «Новое подключение» и в окне ввести следующие поля:

Имя - желаемое имя, например: my_server
Хост - ip-адрес сервера, например: 188.123.456.768
Порт - 5432 (по умолчанию)
Имя пользователя - staffcop
Пароль - пароль из файла конфигурации, полученный выше.

Остальные поля стоят по умолчанию. Нажимаете 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.description_cn::text, '') AS "agent_eventtype.description_cn",
    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.app_icon_id::text, '') AS "agent_application.app_icon_id",
    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.description_cn::text, '') AS "agent_feedbacktype.description_cn",
    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",
    COALESCE(agent_event.filters_data::text, '') AS "agent_event.filters_data"
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