Database API¶
StaffCop uses relational DBMS - PostgreSQL.
Direct connection to the database is the most efficient way of extracting data.
How to configure remote connection¶
Allow connecting to postgresql from external ip.
Find the postgresql.conf file (in the folder /etc/postgresql/[VERSION]/main/) and edit the following line:
listen_addresses = '*'
Keep in mind that you should remove the commenting character “#” before the listen_addresses directory.
Open file pg_hba.conf (located in the same folder as postgresql.conf) and add the following line:
hostssl all staffcop 0.0.0.0/0 md5
where:
hostssl - connect with SSL
all - allow connection to all databases
staffcop - the name of the user with connection rights
0.0.0.0/0 - network mask for connection
md5 - password encryption method
Restart postqresql with the command:
service postgresql restart
Receive the password for connection to the database
The password is located in the configuration file found at /etc/staffcop/config:
DATABASES = {
'default': {
'NAME': 'staffcop',
'USER': 'staffcop',
'PASSWORD': '3a7e...418e',
'HOST': '',
'PORT': '',
}
}
Try connecting from another PC
Use some client application to test the connection, e.g. pgadmin.
Click “New connection” in the interface of pgadmin and fill in the following fields:
Name - desired name, e.g.: my_server
Host - ip-address of the server, e.g. : 188.123.456.768
Port - 5432 (default)
Username - staffcop
Password - Password from the configuration file received earlier.
All other fields are left wit the default settings. Click OK. If everything works then it means that the remote connection to postgresql is configured.
Data export¶
To export all data in the denormalized way (events 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