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