Database API¶
StaffCop uses object-relational database PostgreSQL
Direct connection to the database is the most efficient way of receiving data.
Configure remote connection¶
- Allow connection to postgresql from external ip.
Find file postgresql.conf (located in teh folder /etc/postgresql/[VERSION]/main/) and make correction of the line:
listen_addresses = '*'
Pay attention that you should delete the commentary character # before listen_addresses
In file pg_hba.conf (has the same location as postgresql.conf) add the following line:
hostssl all staffcop 0.0.0.0/0 md5
where:
hostssl - подключаемся через SSL
all - разрешаем подключение ко всем базам
staffcop - имя пользователя, которому разрешаем подключение
0.0.0.0/0 - маска сети с которой можно подключаться
md5 - способ шифрования пароля
Restart postqresql with the command:
service postgresql restart
- Receive the password for connection to DB
The password is located in the configuration file in /etc/staffcop/config:
DATABASES = {
'default': {
'NAME': 'staffcop',
'USER': 'staffcop',
'PASSWORD': '3a7e...418e',
'HOST': '',
'PORT': '',
}
}
- Try connection from another PC
Use a client to check connection, for example pgadmin.
In pgadmin you should click the “New connection” and fill in the following fields:
Имя - желаемое имя, например: my_server
Хост - ip-адрес сервера, например: 188.123.456.768
Порт - 5432 (по умолчанию)
Имя пользователя - staffcop
Пароль - пароль из файла конфигурации, полученный выше.
The other fields are left by default. Click OK.
Export data¶
To export all the events in a denormalized view (facts and dimensions)you can use the following SQL request:
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