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