Data model

Staffcop Enterprise is based on the OLAP technology.

OLAP-structure, created from working data is called OLAP-cube. A cube is created from merging of tables with the usage of the star schema.

In the centre of the start a table of facts will appear, containing the key facts the reqeuests are made for.

multiple tables with dimensions are bound to the facts table. These tables demonstrate the ways aggregated relational data can be analized.

Table of facts

The main table of facts is agent_event containing export keys for dimension tables

Column	Type	Purpose
id	integer	Initial key
event_type_id	integer	dimension - event type
agent_id	integer	Dimension - Computer
account_id	integer	Dimension - User
time_id	integer	Dimension - Time
application_id	integer	Dimension - Application
web_data_id	integer	Dimension - Website
net_data_id	integer	Dimension - Network activity
file_operation_id	integer	Dimension - File operation
file_path_id	integer	Dimension - File
attached_file_id	integer	Dimension - Intercepted file
dialog_id	integer	Dimension - Dialog
app_installation_id	integer	Dimension - Application installation
device_id	integer	Dimension - Device
feedback_id	integer	Dimension - Alerts
call_duration	integer	Measure - Call duration
activity_time	bigint	Measure - Activity time in the FILETIME format
idle_time	bigint	Measure - Downtime in the FILETIME format
time_registered	timestamp with time zone	Time of fact registration at the server
local_time	timestamp with time zone	Agent local time
window_title	text	Window title
text_data	text	Text data
parent_id	integer	Parental fact
children_count	integer	Number of subfacts
filters_data	text	Data on triggered filters

Dimension Agent

Dimension Agent is found in the agent_agent table.

Column	Purpose
computer_name	Compute
last_report	Status
last_ip	IP addres
agent_version	Agent version
os	Os version
id	ID
guid	HWID
label	Group

Dimension User

Dimension User is found in the agent_account table.

Column	Purpose
id	ID
guid	SID
user_name	User
full_name	Full name
office	Department
company	Organization
post	Title
phone	Phone
mail	E-Mail
comment	Commentary
user_domain	Domain
last_report_time	Last report time
last_activity_time	Last activity time

Dimension Application

Dimension Application is found in the agent_application table.

Column	Purpose
app_name	Name
exe_path	Full path
app_description	Description
window_title	Window title

Dimension Website

Dimension Website is found in the agent_web table.

Column	Purpose
site	Website
domain	Domain
url	URL
protocol	Protocol
mime	Content type

Dimension Network activity

Dimension Network activity is found in the agent_networkconnection table.

Column	Purpose
remote_address	IP addres
remote_port	Network port

Dimension File

Dimension File is found in the agent_attachedfile table.

Column	Purpose
file_name	File name
file_ext	Extention
file_path	Path
mime	Content type
data	Url
drive_type	Disk type
sha1	File hash
guid	GUID
id	ID
extracted	Extraction status
file_operation	Operation

Dimension Device

Dimension Device is found in the agent_device table.

Column	Purpose
manufacturer	Device type
description	Device
hwid	Device ID
drive_type	Disk type
device_class	Device class

Dimension Dialog

Dimension Dialog is found in the agent_dialog table.

Column	Purpose
sender	Sender
contact	Recipient
recipients	All recipients
sender_domain	Sender domain
domain	Recipient domain
direction	Direction
dialog_type	Communication channel
message_format	Message format
members	Chats

Dimension Date

Dimension Date is in the agent_time table.

Column	Purpose
year	Year
month	Month
day	Day
hour	Hour
Quarter	Четверть часа
minute	Minute
weekday	Day of the week
time_zone	Time zone

Dimension Installations

Dimension Installations is found in the agent_appinstallation table.

Column	Purpose
install_event	Operation
product_name	Product
publisher	Vendor
version	Version

Dimension Triggered filters

Dimension Triggered filters is found in the analytics_filter table.

Column	Purpose
name	Name
category	Category
syscat	System category