Data model

StaffCop Enterprise is based on the OLAP technology.

OLAP-structure created from the working data is called OLAP-cube. The cube is created by connecting the tables into the star schema.

In the center of the star schema there is a facts table containing the key facts used for making queries.

Multiple tables with dimensions are attached to the facts table. These tables show the way the aggregated relational data are analyzed.

Facts table

The main table is the facts table called agent_event containing external keys to dimensions tables

Column	Type	Purpose
id	integer	Primary key
event_type_id	integer	Dimension - Event type
agent_id	integer	Dimension - Computer
account_id	integer	Dimension - Account
time_id	integer	Dimension - Date and time
application_id	integer	Dimension - Application
web_data_id	integer	Dimension - Website
net_data_id	integer	Dimension - Network connection
file_operation_id	integer	Dimension - File operation
file_path_id	integer	Dimension - File
attached_file_id	integer	Dimension - Intercepted file
dialog_id	integer	Dimension - Messaging
app_installation_id	integer	Dimension - 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 regidtration on server
local_time	timestamp with time zone	Local agent time
window_title	text	Window title
text_data	text	Text data
parent_id	integer	Parent fact
children_count	integer	Number of children facts
filters_data	text	Data on triggered filters

Dimension Computer

Dimension Computer is found in the table agent_agent.

Column	Purpose
computer_name	Computer
last_report	Status
last_ip	IP address
agent_version	Agent version
os	OS version
id	ID
guid	HWID
label	Group

Dimension Account

Dimension Account is found in the table agent_account.

Column	Purpose
id	ID
guid	SID
user_name	User name
full_name	Full name
office	Office
company	Company
post	Title
phone	Phone
mail	Mail
comment	Comment
user_domain	Domain
last_report_time	Last report time
last_activity_time	Last activity time

Dimension Application

Dimension Application is found in the table agent_application.

Column	Purpose
app_name	Executable
exe_path	Path
app_description	Description
window_title	Window title

Dimension Website

Dimension Website is found in the table agent_web.

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

Dimension Network connection

Dimension Network connection is found in the table agent_networkconnection.

Column	Purpose
remote_address	IP address
remote_port	Port

Dimension File

Dimension File is found in the table agent_attachedfile.

Column	Purpose
file_name	File name
file_ext	Extention
file_path	File path
mime	Content type
data	Url
drive_type	Drive type
sha1	File hash
guid	GUID
id	ID
extracted	Text extracted
file_operation	File operation

Dimension Device

Dimension Device is found in the table agent_device.

Column	Purpose
manufacturer	Drive type
description	Description
hwid	HWID
drive_type	Drive type
device_class	Device class

Dimension Messaging

Dimension Messaging is found in the table agent_dialog.

Column	Purpose
sender	Sender
contact	Recipient
recipients	All recipients
sender_domain	Sender domain
domain	Recipient domain
direction	Direction
dialog_type	Channel
message_format	Message format
members	Threads

Dimension Date and time

Dimension Date and time is found in the table agent_time.

Column	Purpose
year	Year
month	Month
day	Day
hour	Hour
quarter	Quarter
minute	Minute
weekday	Weekday
time_zone	Time Zone

Dimension Installation

Dimension Installation is found in the table agent_appinstallation.

Column	Purpose
install_event	Operation
product_name	Product
publisher	Publisher
version	Version

Dimension Triggered filter

Dimension Triggered filter is found in the table analytics_filter.

Column	Purpose
name	Name
category	Category
syscat	System category