Details for creating your own visualization in data analysis tools

Read technical information necessary for integrating Safetica with Power BI or other data analysis tools.

Information in this article applies to Safetica 10 or older.

In this article, you will find information about the following views:

[pbi].[user_structure_view]
[pbi].[apps_webs_view]
[pbi].[apps_webs_activities]
[pbi].[user_activity_by_day_view]
[pbi].[user_activity_by_day_category_view]
[pbi].[data_security_view]

Exposed views

These views can be used as a base for various dashboards and they provide different levels of detail. You can find more info about individual views below:

[pbi].[user_structure_view]

This view provides insight into the user structure. It uses data from the user tree. However, you can import your own user structure, e.g. an XLS sheet assigning particular employees to particular affiliates.

Column name	Type	Content
[id]	bigint	Internal ID of a user. Can be used to distinguish two users of a same name.
[canonical_name]	nvarchar(300)	Name of the user.
[parent_id]	bigint	ID of an immediate parent group.
[parent]	nvarchar(300)	Name of an immediate parent group.
[grandparent_id]	bigint	ID of a grandparent group.
[grandparent]	nvarchar(300)	Name of a grandparent group.

[pbi].[apps_webs_view]

This table contains raw logs of user activities involving applications and websites. For aggregated information, see the other views.

Column name	Type
[id]	bigint	Unique identifier of a log.
[id_user]	bigint	Internal ID of a user. Can be used to distinguish two users of a same name.
[id_pc]	bigint	Internal ID of a computer.
[user_name]	nvarchar(300)	Name of a user who performed the activity.
[pc_name]	nvarchar(300)	Name of a computer where the action was performed.
[date_time]	datetime	Date and time when the action was performed.
[duration]	int	Duration of the action.
[module]	int	Origin of the log (used for internal purposes).
[productivity_level]	int	Productivity of the action -1 = Not set 0 = Neutral 1 = Productive 2 = Nonproductive 3 = Critical
[category]	bigint	Unique identifier of an application or web category. 110 = Business 120 = File hosting 130 = Financial 140 = Games 150 = Government and religion 160 = Health 170 = Illegal 180 = IT 190 = Job search 200 = Leisure 210 = Malware 220 = Multimedia and art 230 = News 240 = Pornography 250 = Science and education 260 = Search engines 270 = Shopping 280 = Social networks 290 = Sport 300 = Web mails 310 = Web portals 320 = CRM 330 = Intranet 340 = ERP 350 = Proxy web 360 = Custom 370 = Instant Messaging Web Applications 1000 = 3D design software 2000 = Antivirus 3000 = Archiving software 4000 = CAD software 5000 = Database 6000 = Disc authoring and virtual drive 7000 = Email client 8000 = File manager 9000 = File sharing 10000 = File synchronization 11000 = FTP clients 13000 = Image viewers and editors 14000 = Instant messaging and VOIP software 15000 = Keylogger 16000 = Media software 17000 = Office suite 18000 = Page layout software 19000 = PDF reader
[application_name]	nvarchar(300)	Name of the application (including a process name).
[activity_type]	int	Type of an activity. 0 = Web 1 = Application
[web_domain]	nvarchar(300)	Domain of the web visited. NULL for application activities.
[web_title]	nvarchar(1024)	Title of the web visited. NULL for application activities.
[web_url]	nvarchar(2348)	URL of the web visited. NULL for application activities.
[id_application]	bigint	Unique identifier of an application involved in the action.
[id_web]	bigint	Unique identifier of a web involved in the action.
[activity]	nvarchar(300)	Either application name or website domain.

[pbi].[apps_webs_activities]

This table is basically the previous table converted to a human-readable form (e.g. the enums are translated to strings, duration is available in different formats, etc.).

Column name	Type	Content
[id_user]	bigint	Internal ID of a user. Can be used to distinguish two users of a same name.
[id_pc]	bigint	Internal ID of a computer.
[user_name]	nvarchar(300)	Name of a user who performed the activity.
[pc_name]	nvarchar(300)	Name of a computer where the action was performed.
[activity]	nvarchar(300)	Either application name or website domain.
[date_time]	datetime	Date and time when the action was performed.
[duration]	int	Duration of the action.
[durationHMS]	time(7)	Duration of the action in the time format.
[date]	date	Date of the action.
[time]	time(7)	Time of the day when the action was performed.
[durationH]	int	Hours part of the duration.
[durationM]	int	Minutes part of the duration.
[durationS]	int	Seconds part of the duration.
[durationFloat]	float	Duration as a float number, e.g. 1h30min will be 1,5.
[category]	nvarchar(max)	Application or web category of the action.
[application_name]	nvarchar(300)	Name of the application (including a process name).
[activity_type]	nvarchar(50)	Application or Web
[web_domain]	nvarchar(300)	Domain of the web visited. NULL for application activities.
[web_title]	nvarchar(1024)	Title of the web visited. NULL for application activities.
[web_url]	nvarchar(2348)	URL of the web visited. NULL for application activities.
[productivity]	nvarchar(50)	Productivity level of the activity. Can be any of Not set, Neutral, Productive, Nonproductive or Critical.

[pbi].[user_activity_by_day_view]

This view provides aggregated information on how users spend their working time on a daily basis. You can use this view e.g. as a replacement for attendance systems for users working from home.

Column name	Type	Content
[id_user]	bigint	Internal ID of a user. Can be used to distinguish two users of a same name.
[canonical_name]	nvarchar(300)	Name of a user who performed the activity.
[date]	date	Particular date
[day_start]	time(0)	Time when the user started working that day.
[day_end]	time(0)	Time when the user stopped working that day.
[total_work_time]	int	Number of seconds between day_start and day_end, i.e. total working time of a user that day.
[total_active_time_seconds]	bigint	Number of seconds the user spent actively working with PC that day.
[durationFloat]	float	Actively spent time for particular day in hours as a float number.
[productive_time_seconds]	bigint	Number of seconds the user spent productively (in applications and on websites) that day.
[nonproductive_active_time_seconds]	bigint	Number of seconds the user spent non-productively (in applications and on websites) that day.
[productive_web_time_seconds]	bigint	Number of seconds the user spent productively on websites that day.
[nonproductive_web_time_seconds]	bigint	Number of seconds the user spent non-productively on websites that day.
[productive_app_time_seconds]	bigint	Number of seconds the user spent productively in applications that day.
[nonproductive_app_time_seconds]	bigint	Number of seconds the user spent non-productively in applications that day.
[total_work_time_HMS]	time(7)	Total working time in a time format.
[total_active_time_HMS]	time(7)	Total active time in a time format.
[total_work_time_H_float]	float	Total working time in hours as a float number.
[total_active_time_H_float]	float	Total active time in hours as a float number.

[pbi].[user_activity_by_day_category_view]

This view can be used to track individual application and web category usage. This way, the company can e.g. track the usage of particular software and ensure that expensive licenses are used properly. Another usage includes tracking how much time the employees of a particular department spent using specific categories. This can help pinpoint inefficient workload structure, e.g. when a developer spends 80 % of their time responding the emails instead of using development tools. The company can then review internal processes to increase overall efficiency.

Column name	Type	Content
[id_user]	bigint	Internal ID of a user. Can be used to distinguish two users of a same name.
[canonical_name]	nvarchar(300)	Name of a user who performed the activity.
[activity]	nvarchar(300)	Either application name or website domain.
[activity_type]	int	Type of an activity. 0 = Web 1 = Application
[category_id]	bigint	Unique identifier of an application or web category. 110 = Business 120 = File hosting 130 = Financial 140 = Games 150 = Government and religion 160 = Health 170 = Illegal 180 = IT 190 = Job search 200 = Leisure 210 = Malware 220 = Multimedia and art 230 = News 240 = Pornography 250 = Science and education 260 = Search engines 270 = Shopping 280 = Social networks 290 = Sport 300 = Web mails 310 = Web portals 320 = CRM 330 = Intranet 340 = ERP 350 = Proxy web 360 = Custom 370 = Instant Messaging Web Applications 1000 = 3D design software 2000 = Antivirus 3000 = Archiving software 4000 = CAD software 5000 = Database 6000 = Disc authoring and virtual drive 7000 = Email client 8000 = File manager 9000 = File sharing 10000 = File synchronization 11000 = FTP clients 13000 = Image viewers and editors 14000 = Instant messaging and VOIP software 15000 = Keylogger 16000 = Media software 17000 = Office suite 18000 = Page layout software 19000 = PDF reader
[category]	nvarchar(max)	Human readable name of the category.
[date]	date	Date of the activity.
[total_time_seconds]	bigint	Total number of seconds the user spent on the activity that day.
[total_time_H_float]	float	Time spent in hours as a float number.

[pbi].[data_security_view]

This view provides an overview of outgoing file operations and their security aspects. It can be used to identify channels via which files leave the company, so you can take appropriate measures. Moreover, it can be used e.g. to track network shares containing sensitive data etc.

Column name	Type	Content
[id]	bigint	Unique identifier of a log.
[id_user]	bigint	Internal ID of a user. Can be used to distinguish two users of a same name.
[id_pc]	bigint	Internal ID of a computer.
[id_application]	bigint	Internal ID of an application.
[operation]	nvarchar(50)	Type of the operation performed (e.g. Copy file, Web upload).
[action]	nvarchar(50)	The resulting action of the operation (e.g. Notified, Denied).
[date_time]	datetime	Date and time when the action was performed.
[file_name]	nvarchar(300)	Name of the file transferred.
[file_extension]	nvarchar(300)	Extension of the file transferred.
[file_type]	nvarchar(max)	Type of the file transferred (e.g. Image Files, Spreadsheet Files).
[file_size]	bigint	Size of the file in bytes.
[source_type]	nvarchar(50)	Type of the source location (e.g. Local path, Network share).
[destination_type]	nvarchar(50)	Type of the destination (e.g. Local path, Network share).
[source_path]	nvarchar(300)	Source path of the file.
[destination_path]	nvarchar(300)	Destination path/URL/IM application.
[data_categories]	nvarchar(300)	Data categories assigned to a file.
[is_tagged]	int	If the file has any data category assigned, the value is 1. Otherwise, it is 0.
[sensitive_content]	int	If the file contained any sensitive content, the value is 1. Otherwise, it is 0.
[user_name]	nvarchar(300)	Name of a user who performed the activity.
[pc_name]	nvarchar(300)	Name of a computer where the action was performed.
[application_name]	nvarchar(300)	Name of the application used to perform the file transfer.
[category_extension]	bigint	ID of the extension category (mapped to human readable file_type column).
[to_safe_zone]	tinying	If the destination lies in the safe zone, the value is 1. Otherwise, it is 0.
[id_policy]	bigint	ID of a DLP policy involved.
[policy_bound]	tinyint	If the operation was controlled by any policy, the value is 1. Otherwise, it is 0.
[module]	int	Origin of the log (used for internal purposes).
[policy_restriction]	int	Restriction mode implied by the DLP policy. 1 = Controlled but allowed 2 = Testing mode 3 = Denied 4 = Not controlled by DLP policy
[policy_name]	nvarchar(300)	Name of the policy involved.
[suspicous]	int	The result of the internal threat identification. 0 = Not suspicious 1 = Suspicious