Insights schema

The tables in this section provide detailed information about the BigFix 10 Insights schema.

Table 1. action_state_stringsThe action_state_strings table stores the string representation of various possible action states. datasource_action_results contains a tinyint state column. To get a human-readable form of the action result state, users can refer to dbo.action_state_strings.
Name Type Description
state_number int NOT NULL The state number that is stored in datasource_action_results
datasource_id int NOT NULL The id of the datasource from which the state_number - state_string pair came
state_string nvarchar(max) NOT NULL The human-readable version of the state
Table 2. content_resultsThe content_results table stores results for all datasources associated back to datasource_sites, datasource_devices, datasource_analyses and datasource_fixlets. Fixlet and Analysis relevance results are in this table.
Name Type Description
datasource_site_id bigint NOT NULL The id of dbo.datasource_sites
datasource_device_id bigint NOT NULL The id of dbo.datasource_devices
datasource_content_id bigint NOT NULL

The id of dbo.datasource_fixlet OR dbo.datasource_analysis

To check type column to determine which object result row refers to
type tinyint Type of fixlet.

Valid values:

0 - Fixlet

1 - Task

2 - Baseline

3 - Analysis

4 - Computer Group
relevant bit Whether this content is relevant (0/1)
applicable bit Whether the content relevance is applicable (0/1)
resolved bit Whether the action is resolved (0/1)
reverted bit Whether the action is reverted (0,1)
last_non_relevant datetime The last time the action was non-relevant
first_relevant datetime The first time the action was relevant
last_relevant datetime The last time the action was relevant
valid_from datetime The timestamp of the last ETL
valid_to datetime The timestamp indicating until when this row is valid
Table 3. custom_attributesThe custom_attributes table stores a set of key or value pairs that store additional data a user might need. These values are used in Tableau and PowerBI reports.
Name Type Description
id int NOT NULL The id of the name - value attribute pair
attribute_name nvarchar(128) NOT NULL The name of the attribute
category nvarchar(128) NOT NULL The value of the category
attribute_value nvarchar(128) NOT NULL The value of the attribute
last_updated datetime2 NOT NULL The timeline at which the attribute was last updated
Table 4. datasource_action_resultsThe datasource_action_results table stores the action results from all ETL'd datasources. Information is pulled from BFE actionresults table.
Name Type Description
datasource_device_id bigint NOT NULL

The id of the device to which the result belongs and which references dbo.datasource_devices.id
datasource_action_id bigint NOT NULL

The id of the action to the result belongs and which references dbo.datasource_actions.id
start_time datetime NULL Pulled from StartTime column in BFE ACTIONRESULTS
end_time datetime NULL Pulled from EndTime column in BFE ACTIONRESULTS
try_count smallint NOT NULL Pulled from TryCount column in BFE ACTIONRESULTS
retry_count smallint NOT NULL pulled from RetryCount column in BFE ACTIONRESULTS
line_number smallint NOT NULL Pulled from LineNumber column in BFE ACTIONRESULTS
state smallint NOT NULL Pulled from State column in BFE ACTIONRESULTS and contains a numerical representation. The human-readable format is stored in dbo.action_state_strings
report_number bigint NOT NULL Pulled from ReportNumner column in BFE ACTIONRESULTS
exit_code int NULL pulled from ExitCode column in BFE ACTIONRESULTS
valid_from datetime2(3) GENERATED ALWAYS AS ROW START NOT NULL Timestamp of the last ETL
valid_to datetime2(3) GENERATED ALWAYS AS ROW END NOT NULL Timestamp indicating until when the row is valid
Table 5. datasource_actionsThe datasource_actions table stores the actions for all ETL'd datasources,. The actions are pulled from BFE ACTIONS table.
Name Type Description
id bigint IDENTITY(1,1) NOT NULL The BigFix Insights db id of the action
remote_id bigint NOT NULL The id of the action object from BFE ACTIONS table
datasource_id int NOT NULL The id of the datasource the action was ETL'ed from, references dbo.datasource.id
datasource_site_id bigint NOT NULL The id of the site the action was ETL'ed from, references dbo.datasource_site.id
type int NOT NULL Type of the action
deleted tinyint NOT NULL Whether the action has been deleted or not
parent_id int NULL Pulled from ParentID column in BFE ACTIONS
is_subscription int NOT NULL Pulled from IsSubscription column in BFE ACTIONS
is_setting int NOT NULL Pulled from IsSetting column in BFE ACTIONS
inception_time datetime NULL Pulled from InceptionTime column in BFE ACTIONS
expiration_time datetime NULL Pulled from ExpirationTime column in BFE ACTIONS
stopped_at datetime NULL Pulled from StoppedAt column in BFE ACTIONS
stopped_by int NULL Pulled from StoppedBy column in BFE ACTIONS
name nvarchar(1024) NOT NULL Name of the action
state tinyint NOT NULL The state of the action, calculated by looking at the IsStopped and ExpirationTime columns from BFE ACTIONS:
  • 0 - Expired
  • 1 - Stopped
  • 2 - Open
creator_id int NOT NULL Pulled from CreatorID column in BFE ACTIONS
creation_time datetime NOT NULL Pulled from CreationTime column in BFE ACTIONS
targeting_method tinyint NOT NULL Pulled from TargetingMethod column in BFE ACTIONS
targeting_relevance nvarchar(max) NULL Pulled from TargetingRelevance column in BFE ACTIONS
targeting_properties nvarchar(max) NULL Pulled from TargetingProperties column in BFE ACTIONS
source_site_url nvarchar(1024) NULL Pulled from SourceSiteURL column in BFE ACTIONS
source_site_id bigint NULL Pulled from SourceSiteID column in BFE ACTIONS
source_content_id int NULL Pulled from SourceContentID column in BFE ACTIONS
source_site_name nvarchar(1024) NULL Pulled from SourceSiteName column in BFE ACTIONS
Table 6. datasource_actionsite_propertiesThe datasource_properties table contains all properties that are pulled from the BES.properties table that exist in the actionsite. This is a temporary table. The properties are used within the ETL to populate the datasource_property_map table which can be used to generate dimensions tables.
Name Type Description
id bigint NOT NULL IDENTITY(1, 1) Datasource property id
datasource_id int NOT NULL dbo.datasources id
remote_content_id bigint NOT NULL remote content id
remote_site_id bigint NOT NULL remote site id
remote_property_id tinyint NOT NULL remote property id
name nvarchar(512) NOT NULL property name
Table 7. datasource_analysesThe datasource_analyses table contains external and custom analyses from ETL'd datasources.
Name Type Description
id bigint NOT NULL IDENTITY(1, 1) The datasource analysis id
remote_id bigint NOT NULL The remote analysis id
datasource_id int NOT NULL The dbo.datasources id
datasource_site_id bigint NOT NULL The dbo.datasource_sites id
name nvarchar(255) NOT NULL The name of the analysis
deleted bit NOT NULL Whether the content is deleted
valid_from datetime2 (2) GENERATED ALWAYS AS ROW START NOT NULL The timestamp from the last ETL
valid_to datetime2 (2) GENERATED ALWAYS AS ROW END NOT NULL The timestamp indicating until when the row is valid
Table 8. datasource_analysis_propertiesThe datasource_analysis_properties table stores property data associated with dbo.datasource_analyses.
Name Type Description
id bigint NOT NULL IDENTITY(1, 1), The datasource analysis property ID
remote_id bigint NOT NULL The remote content id
datasource_analysis_id bigint NOT NULL The dbo.datasource_analyses id
name nvarchar(512) NOT NULL The name of the property
deleted bit NOT NULL Whether the content is deleted
valid_from datetime2 (2) GENERATED ALWAYS AS ROW START NOT NULL The timestamp from the last ETL
valid_to datetime2 (2) GENERATED ALWAYS AS ROW END NOT NULL The timestamp indicating until when the row is valid
Table 9. datasource_computer_groupsThe datasource_computer_groups table provides relationship of computers to groups. It displays all computer groups memberships across all datasource devices. This is more like a flattened computer group membership results that is not linked and there is no global representation within this table.
Name Type Description
id bigint NOT NULL IDENTITY(1, 1) The datasource computer group ID
datasource_id int NOT NULL The datasources id
datasource_site_id bigint NOT NULL The dbo.datasource_sites id
datasource_group_id bigint NOT NULL The dbo.datasource_groups id
datasource_device_id bigint NOT NULL The dbo.datasource_devices id
version varbinary The group version
is_member tinyint Whether the computer is a member of the group
valid_from AS ROW START NOT NULL The timestamp from the last ETL
valid_to AS ROW END NOT NULL The timestamp indicating until when the row is valid
Table 10. datasource_databasesThe datasource_databases table contains datasource definitions and parameters of how to target and authenticate to the datasource.
Name Type Description
id int NOT NULL IDENTITY(1, 1) The datasource database id
host nvarchar(512) NOT NULL The hostname or IP address
database nvarchar(128) NOT NULL The database name
type nvarchar(8) NOT NULL CONSTRAINT "DF_ca681866723fdb25046ba6b637c" DEFAULT 0
The type of datasource
  • BFE - BigFix Enterprise
  • BFI - BigFix Inventory
  • SCA - BigFix Software and Computer Analytics
username nvarchar(128) NOT NULL The database username (encrypted)
password nvarchar(1024) The database password (encrypted)
port int The database port
domain nvarchar(512) The database Windows domain
instance_name nvarchar(512) The database SQL server instance name
approved bit NOT NULL default 0 State:
  • 0 – Not Approved if public_key is NULL, Pending if public_key is not NULL
  • 1 – Approved
public_key nvarchar(1024) Public key saved for user
Table 11. datasource_devicesThe datasource_devices table stores all devices from all ETLed BES deployments.
Name Type Description
id bigint NOT NULL IDENTITY(1, 1) The datasource device id
datasource_id int NOT NULL The dbo.datasources id
remote_id bigint NOT NULL The remote device id
name nvarchar(512) The device name
report_number bigint NOT NULL The number of the last report received
last_report_time datetime The last time a report was received.

Format: unix epoch timestamp in UTC
last_report_sent datetime The last time a report was sent.

Format: unix epoch timestamp in UTC
locked bit NOT NULL CONSTRAINT

"DF_f7c2c0228e83297504c49ac4074" DEFAULT 0

 Whether the device is locked
deleted bit NOT NULL CONSTRAINT "DF_4079840396b41f9b717aed30ef9" DEFAULT 0 Whether the device is deleted
valid_from datetime2 (2) GENERATED ALWAYS AS ROW START NOT NULL The timestamp from last ETL
valid_to datetime2 (2) GENERATED ALWAYS AS ROW END NOT NULL The timestamp indicating until when the row is valid
Table 12. datasource_fixletsThe datasource_fixlets table displays all content across all datasources. These are flattened content objects that are not linked and there is no global representation within this table.
Name Type Description
id bigint NOT NULL IDENTITY(1, 1) The datasource content id
datasource_id int NOT NULL The dbo.datasources id
datasource_site_id bigint NOT NULL The dbo.datasource_sites id
remote_id bigint NOT NULL, The remote content id
name nvarchar(1024) NOT NULL The fixlet name
class nvarchar(32) The fixlet class name
category nvarchar(255) The fixlet category name
is_task tinyint NOT NULL Whether the fixlet is a task
severity nvarchar(1024) The fixlet severity
source nvarchar(255) The fixlet source
source_release_date nvarchar(32) The fixlet source release date
source_id nvarchar(1024) The source id for this fixlet
download_size bigint The download size for this fixlet
cve nvarchar(max) The CVE id list
cvss cvss nvarchar(127) The CVSS id list
sans nvarchar(max) The sans id list
hidden bit NOT NULL CONSTRAINT "DF_9da82af0b7111efbe9652888c6b" DEFAULT 0 Whether this fixlet is hidden
deleted bit NOT NULL CONSTRAINT "DF_fc594b434464ca05741816b75fe" DEFAULT 0 Whether this fixlet is deleted
creation_time datetime Creation time of the fixlet
user_remote_id int The user remote id
last_modification_time datetime The last modification time of the fixlet
valid_from datetime2 (2) GENERATED ALWAYS AS ROW START NOT NULL The timestamp from last ETL
valid_to datetime2 (2) GENERATED ALWAYS AS ROW END NOT NULL The timestamp indicating until when the row is valid
Table 13. datasource_groupsThe datasource_groups table has a listing of all datasources computer groups. These are flattened content results that is not linked. There is no global representation within this table.
Name Type Description
id bigint NOT NULL IDENTITY(1, 1) The datasource group id
datasource_id int NOT NULL The dbo.datasources id
datasource_site_id bigint NOT NULL The dbo.datasource_sites id
remote_id bigint NOT NULL The remote group id
name nvarchar(512) NOT NULL The group name
type tinyint NOT NULL

The group type. Values:

0 - manual

1 - automatic
is_client_evaluated bit NOT NULL CONSTRAINT "DF_9878d124eced91430318722648b" DEFAULT 0 Whether the client evaluates that it is a member of this group. This is always 1 for automatic groups. For manual groups, this is 1 when the group is "Determined by client settings"
creator_id int NOT NULL The user that created this group, if any.
creation_time datetime2 The creation time. Format: Unix epoch timestamp in UTC
deleted bit NOT NULL CONSTRAINT "DF_c3db464e009de0643d2455c0f99" DEFAULT 0 Whether the content is deleted
valid_from datetime2 (2) GENERATED ALWAYS AS ROW START NOT NULL The timestamp from the last ETL
valid_to datetime2 (2) GENERATED ALWAYS AS ROW END NOT NULL The timestamp indicating until when the row is valid
Table 14. datasource_property_mapThe datasource_property_map table includes all properties across all deployments. Data is pulled from [BFInsights].datasource_actionsite_properties, [BFInsights].datasource_analysis_properties, [BFInsights].datasource_analyses. This is a temporary table and is used to generate the dimension table [BFInsights].[device_dimensions].
Name Type Description
id bigint NOT NULL IDENTITY(1, 1) The datasource property map id
datasource_id int NOT NULL The dbo.datasources id
datasource_site_id bigint NOT NULL The dbo.datasource_sites id
remote_content_id bigint NOT NULL The remote content id
remote_property_id bigint NOT NULL The remote property id
name nvarchar(512) NOT NULL The name of the property
type tinyint

The property type. Following are the values:

0 - Reserved. This is a read-only predefined property.

1 - Default. This is a predefined property that can be edited.

2 - Custom. This is a user created property.

Relevance: custom flag, default flag, reserved flag
deleted bit NOT NULL Whether the property is deleted
valid_from datetime2 (2) GENERATED ALWAYS AS ROW START NOT NULL The timestamp from the last ETL
valid_to datetime2 (2) GENERATED ALWAYS AS ROW END NOT NULL The timestamp indicating until when the row is valid
Table 15. datasource_property_resultsThe datasource_property_results table contains full list of property values across all deployments. This is a temporary table and is used in the webui to generate the [BFInsights].device_dimensions table.
Note: The values are pulled from QUESTIONRESULTS and LONGQUESTIONRESULTS, but only where:
  • A valid WebUISiteID exists (NOT NULL)
  • The property type is either Default (0) Reserved (1)
Name Type Description
id bigint NOT NULL IDENTITY(1, 1) The datasource property id
datasource_id int NOT NULL The dbo.datasources id
remote_device_id bigint NOT NULL The remote device id
remote_site_id bigint NOT NULL The remote site id
remote_content_id bigint NOT NULL The remote content id
remote_property_id bigint NOT NULL The remote property id
value nvarchar(4000)

The property value.

Note: This value is nullable.
Table 16. datasource_sequencesThe datasource_sequences table tracks sequence ranges for every ETL'd table. Once the ETL is started, you record the current latest sequence from BigFix Enterprise DB and record it here and next time the ETL is ran, it would begin sequence in ETL.
Name Type Description
datasource_id int NOT NULL The dbo.datasources id
entity_name nvarchar(128) NOT NULL The name of the entity being tracked
last_sequence binary(8) NOT NULL CONSTRAINT "DF_1dbb74c62a71e48bed0b4d1eb9e" DEFAULT 0 The last sequence pulled for entity
Table 17. datasource_sitesThe datasource_sites table holds the list of sites ETL'd from each datasource. The data comes from dbo.SITES table in BigFix Enterprise.
Name Type Description
id bigint NOT NULL IDENTITY(1, 1) The datasource site id, primary key, used to identify site within the Insights DB.
datasource_id int NOT NULL The dbo.datasources id, datasource id from which the site comes from.
remote_id bigint NOT NULL The remote site id, references SiteID within the BigFix Enterprise DB.
name nvarchar(512) NOT NULL The name of the site, references Name column within the BigFix Enterprise DB.
display_name nvarchar(512) The display name of the site, option, display name of the site
url nvarchar(1024) NOT NULL The gather URL of the site.
version int NOT NULL The version of the site.
type tinyint NOT NULL

The site type. Following are the values:

0 - External

1 - ActionSite

2 - Custom

3 - Operator

Relevance: type
is_excluded bit NOT NULL CONSTRAINT "DF_10df8b1811fbcc64562a31ae28d" DEFAULT 0 Whether the site is excluded from ETL, set from etl_sites is_excluded column
deleted bit NOT NULL CONSTRAINT "DF_042faf76cefda84e084362635e2" DEFAULT 0 Whether the site is deleted
valid_from datetime2 (2) GENERATED ALWAYS AS ROW START NOT NULL The timestamp from the last ETL
valid_to datetime2 (2) GENERATED ALWAYS AS ROW END NOT NULL The timestamp indicating until when the row is valid
Table 18. datasourcesThe datasources table represents all datasources added by the user when configuring ETLs.
Name Type Description
id int NOT NULL IDENTITY(1, 1) The datasource id.
name uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID() The uuid for the datasource
excluded bit NOT NULL CONSTRAINT "DF_8baa2c7f1a5bacdff54a25e37fb" DEFAULT 0 Whether the datasource is excluded from ETLs. Format: Boolean.
version nvarchar(32) Version of the datasource.
user_alias nvarchar(128) The datasource alias, assigned by the user.
last_modified datetime2 NOT NULL CONSTRAINT "DF_749648a827211eafdf548bfcc6a" DEFAULT GETUTCDATE() The last time the datasource was modified.
bes_database_id int NOT NULL The bes database_datasource id.
Table 19. etl_metricsThe etl_metrics table gives you the basic metrics information about ETLs that transpire. The purpose: Front-end WebUI app exposes data to user.
Note: Timestamps are generated from Insights SQL server via dbo.get_time().
Name Type Description
id bigint NOT NULL IDENTITY(1,1) The unique id
datasource_id int NOT NULL the id of ETL'ed datasource
start_time datetime

the start time of ETL

Note: This is approximate and differs slightly from what you find in ETL_TIMES.
end_time datetime The end time of ETL
duration_ms bigint DEFAULT 0 The duration in milliseconds of ETL
status tinint

The status of ETL. Following are the values:

0 - RUNNING

1 - SUCCESS

2 - FAILED
preflights varchar(max) the preflight data collected prior to runs of the etl
Table 20. etl_sitesThe etl_sites table contains all sites from all datasources and acts as a state manager for the webui front-end to inform on whether a given site is "excluded" and/or "primary" based on a user selection.

Definitions:

* "excluded": A BigFix site is considered excluded if it has made the appropriate selection witihin the WebUI frontend. Content from a site that is excluded will no longer be drawn and therefore any existing content within the database will remain stale. That is if you decide to exclude a site after having run an ETL, future updates to content within that site will not be pulled and you will end up seeing stale content.

* "primary": A BigFix site is considered primary if a user selects a site to be primary, or if it is the first time this site has been imported via the WebUI datasource import process. The primary flag is set at datasource import time. If there exists more than one datasource within BFInsights, then a primary site is used to inform [BFInsights].Global entities (global_fixlets, global_analyses, global_site) when these entities are updated after an ETL.

Name Type Description
id bigint not null identity(1, 1) The ETL site id.
datasource_id int not null The dbo.datasources id.
remote_site_id bigint not null The remote site id.
name nvarchar(512) not null The masthead name of the site
display_name nvarchar(512) The propagated display name of the site.
type tinyint not null

The site type. Following are the values:

0 - External

1 - ActionSite

2 - Custom

3 - Operator

Relevance: type
url navchar(512) The gather URL for the site.
device_count int not null The number of devices subscribed to the site.
content_count int not null The number of content associated with the site.
is_excluded bit not null constraint "DF_cbe84844dc63d312aac5d151072" default 0 Whether the site is excluded from ETL.
is_primary bit not null Whether the site is the primary site for all similar sites from all datasources.
deleted bit not null constraint "DF_2a71f0da409e899e54c3c7dd3cf" default 0 Whether the site is deleted.
link_user_alias nvarchar(128) When the site is the primary site, alias for the group of all linked items.
link_updated datetime When the site is the primary site, last time the metadata for the linked item group has changed.
version int not null Site version from datasource
Table 21. etl_timesThe etl_sites table tracks the ETL Start time and validity for SCD. Internal tables should not be used for reporting; pleae use time_dimensions table.
Name Type Description
start_time datetime2 NOT NULL The start time of the ETL
datasource_id int NOT NULL The datasource id.
valid_to datetime2 NOT NULL The name of the site.
Table 22. global_analysesThe global_analyses table lists the global representation of an analysis object.
Name Type Description
id bigint IDENTITY(1,1) NOT NULL The global analysis id
datasource_analyses_remote_id bigint NOT NULL The remote_id from dbo.datasource_analyses
global_site_id bigint NOT NULL The dbo.global_sites id
datasource_analyses_id bigint NOT NULL The dbo.datasource_analyses id
name nvarchar(512) NOT NULL The name of the global analysis
user modified bit NOT NULL CONSTRAINT "DF_d0c00b9a184f80a8ae481c357cd" DEFAULT 0 Whether the analysis has been modified by the user
deleted bit NOT NULL CONSTRAINT "DF_9c98cb597a5aade71b3f2ee5983" DEFAULT 0 Whether the analysis has been deleted
Table 23. global_analyses_associationsThe global_analyses_associations maps dbo.global_analyses and dbo.datasource_analyses objects together. One global analysis could have multiple datasource analyses associated with it.
Name Type Description
global_analyses_id bigint NOT NULL References dbo.global_analyses.id
datasource_analyses_id bigint NOT NULL References dbo.datasource_analyses.id
datasource_id int NOT NULL The id of the datasource to which the datasource_analysis belongs, for ease of joining with specific datasources
deleted bit NOT NULL Whether the datasource_analysis or global_analysis object has been deleted
Table 24. global_fixlet_associationsThe global_fixlet_associations table tracks mapping between datasource fixlets and global fixltes table. One global fixlet can have mulitple datasource fixlets associated with it based on the datasource fixlet `id` which reference PK in dbo.datasource_fixlets table. Datasource fixlet `remote_id` columns reference ContentID columns in BigFix Enterprise database in external_fixlets and custom_fixlets tables
Name Type Description
global_fixlet_id bigint not null The id of a global fixlet; references dbo.global_fixlets ID column
datasource_fixlet_id bigint not null The id of datasource fixlet; references dbo.datasource_fixlets ID column
datasource_id int not null The id of the datasource to which the global content belongs, for ease of joining with specific datasource
deleted bit not null Whether the datasource_fixlet or global_fixlet object has been deleted
Table 25. global_site_associationsThe global site association table tracks mapping between datasource sites and global sites. One global site can have mulitple datasource sites associated with it based on datasource site `id` which reference PK in Datasource site table. Datasource site remote_id columns reference SiteID columns in BigFix Enterprise database.
Name Type Description
global_site_id bigint not null The id of the global site; references dbo.global_sites table ID column
datasource_site_id bigint not null The datasource Site ID; references datasource_sites table
datasource_id int not null The id of the datasource
deleted bit not null Whether the given global site is deleted
Table 26. global_site_metadataThe global_site_metadata table allows user-configured fields to be associated with the global sites. global_site_metadata references dbo.global_sites through global_site_id. All fields are user modifiable.
Name Type Description
global_site_id bigint not null The global site id; has one to one relationship with the global site
configuration nvarchar(128) null Custom field; user-defined
release_version nvarchar(128) null Custom field; user-defined
vendor nvarchar(128) null Custom field; user-defined
description nvarchar(512) null Custom field; user-defined
updated_at datetime2(0) not null Has trigger on update , set sbo.get_date() function
Table 27. global_sitesThe Global Representation of Sites, Global site represent external site object which can combine external sites from multiple datasources for reporting purposes. The linkage works through dbo.global_site_associations table. The key is datasource_sites 'remote_id' columns which link datasource sites to BigFix Enterprise site object, every external site has the same `remote_id`.
Name Type Description
id bigint NOT NULL IDENTITY(1, 1) The global site id, primary keym which identifies global site with in the deployment.
datasource_site_remote_id bigint NOT NULL The dbo.datasource_sites remote site id, references dbo.datasource_sites `remote_id` column.
name nvarchar(512) NOT NULL The name of the site, populated from datasource_sites name colums which is marked as master.
display_name nvarchar(512) The display name of the site, populated from datasource_sites name colums which is marked as master.
url nvarchar(1024) NOT NULL The gather url of the site, populated from datasource_sites name colums which is marked as master.
version int NOT NULL The version of the site.
type tinyint NOT NULL The site type. Values:

0 - External

1 - ActionSite

2 - Custom

3 - Operator

Relevance: type
user_modified bit NOT NULL CONSTRAINT "DF_e9fc5a1422e7419b6127e015541" DEFAULT 0 Whether the site has been modified by the user, if site is marked as user modified it's not going to particiapte in ETL and should be updated manually.
deleted bit NOT NULL CONSTRAINT "DF_1777d4164442db2017ef27482e9" DEFAULT 0 Whether the site is deleted, can be deleted only by a user.
updated_at datetime2 (0) The timestamp from last updated date.
Table 28. staging_fixlet_fieldsThe staging_fixlet_fields table dumps the raw data from BFE external_fixlet_fields and custom_fixlet_fields. As a staging table, the data in dbo.staging_fixlet_fields is used by Insights later on and not intended for user interaction.
Name Type Description
datasource_fixlet_id bigint NOT NULL Fixlet of the field object, references dbo.datasource_fixlet.id
datasource_id int NOT NULL Datasource of the fixlet, references dbo.datasource.id
name nvarchar(1024) NOT NULL Name of the field.
value nvarchar(max) NOT NULL Value of the field.
deleted big NOT NULL Whether the field has been deleted or not.
Table 29. staging_fixlet_resultsThe staging_fixlet_results table is a list of all fixlet results for all ETLed BES databases pulled from the BFE FIXLETRESULTS table. As a staging table, the data is meant to be consumed by other Insights tables and not intended for users.
Name Type Description
datasource_id int NOT NULL The dbo.datasources id.
datasource_site_remote_id bigint NOT NULL The remote site id pulled from BFE FIXLETRESULTS WebuiSiteID column , can be used to reference dbo.datasource_site.remote_id.
datasource_device_remote_id bigint NOT NULL The remote device id pulled from BFE FIXLETRESULTS ComputerID column, can be used to reference dbo.datasource_device.remote_id.
datasource_content_remote_id bigint NOT NULL The remote content id pulled from BFE FIXLETRESULTS ID column. Can be used to reference dbo.datasource_fixlets.remote_id OR datasource_analyses.remote_id. Need to check type column to determine the object result.
relevant bit NOT NULL To check whether fixlet is relevant.
type tinyint NOT NULL This indicates which type of fixlet:

0 - Fixlet

1 - Task

2 - Baseline

3 - Analysis

4 - ComputerGroup
first_relevant datetime The first time the action was non-relevant.
last_relevant datetime The last time the action was relevant.
last_non_relevant datetime The last time the action was non-relevant.
updated_at datetime2(2) NOT NULL CONSTRAINT "DF_62411c85e6ff6c0c6ed7a96e3b4" DEFAULT GETUTCDATE() The timestamp when ETL last updated this row.
Table 30. time_dimensionsThe time_dimensions table tracks all imports that have been ran on BigFix Enterprise database. This table helps to build a temporal view of the data over time. In order to see you data at specific time in the past, time_dimensions id column needs to be used with `as of <data>` query.
Name Type Description
datetime2(2) NOT NULL ID as timestamp, a time of ETL(Import) was ran, every successful import should have a record in this table.
datasource_id int NOT NULL dbo.datasources id, ID of the datasource for which import was ran.
day_of_week tinyint NOT NULL Indicates day of the week of timeslice. This column allow an easy filtering for specific time frame.
day_of_month tinyint NOT NULL Indicates day of the month of timeslice.
week_of_year tinyint NOT NULL Indicates week of year of timeslice.
month_of_year tinyint NOT NULL Indicates month of year of timeslice.
quarter tinyint NOT NULL Indicates quarter of timeslice.
year smallint NOT NULL Indicates year of timeslice.
Table 31. activity_historyThe activity_history table is used to track ongoing status, events, and information from the activities panel.
Name Type Description
id bigint NOT NULL IDENTITY(1, 1) The activity ID.
user_ID int NOT NULL The user ID.
user_name nvarchar(128) NOT NULL The user name.
method nvarchar(128) NOT NULL Action taken. Values:

create

update

delete
parent_id nvarchar(128) The parent ID.
entity_alias nvarchar(128) The datasource/server alias.
entity_type nvarchar(36) NOT NULL The entity type. Values
  • server
  • datasource
  • schedule
  • site
entity_id int NOT NULL The entity ID.
description nvarchar(128) The description of the activity.
Table 32. webui.etl_schedulesThe webui.etl_schedules table is used to track ongoing ETLs schedules from datasources.
Name Type Description
id bigint NOT NULL IDENTITY(1, 1) The activity ID.
datasource_database_id int NOT NULL The dbo.datasource_database ID.
time_type nvarchar(128) NOT NULL The schedule period. Values:
  • Daily
  • Weekly
  • Monthly
day_of_week nvarchar(128) The day of the week. Values: MONDAY - FRIDAY
week_of_month nvarchar(128) The week of the month. Values: 1st-31st
day_after int The number of days after day_of_week.
hour int The hour.
minute int The minute.
next_etl nvarchar(36) The next etl time.
etl_status nvarchar(36) The etl status. Values:
  • succeeded
  • failed
  • running
Table 33. device_dimensionsThe device_dimensions table lists the detailed attributes for scanned devices in the BigFix domain.
Name Type Description
datasource_device_id bigint not null the device id – references datasource_devices
computer_name nvarchar(512) the name of the computer
locked bit not null

the locked status

1 - locked

0 - not locked

os nvarchar(512) the operating system
cpu nvarchar(512) the cpu
relay nvarchar(512) the relay
dns_name nvarchar(4000) the DNS name
active_directory_path nvarchar(4000) the AD path
ip_address nvarchar(4000) the IP address(es)
ipv6_address nvarchar(4000) the IPV6 address(es)
agent_version nvarchar(128) the agent version
device_type nvarchar(128) the device type
computer_type nvarchar(128) the computer type
user_name nvarchar(512) the username
ram nvarchar(512) the RAM
subnet_address nvarchar(4000) the subnet address
valid_from datetime2 the valid_from date
valid_to datetime2 the valid to date
agent_type nvarchar(128) The agent type
mac_address nvarchar(512) The mac address
Table 34. insights_table_metricsThe insights_table_metrics table lists the detailed attributes of ETL load procedures into the Insights database.
Name Type Description
etl_metric_id bigint not null the unique id of associated metric. References etl_metrics
datasource_id int not null the datasource id
table_name nvarchar(128) the name of the table
schema_name nvarchar(128) the name of the schema
rows bigint not null the number of rows
total_space_mb decimal(36,2) not null the total space in megabytes
used_space_mb decimal(36,2) not null the used space in megabytes
unused_space_mb decimal(36,2) no null the unused space in megabytes