Appendix IV: Standard Data Transformation Output Schema

<< Click to Display Table of Contents >>

Raynet One Data Hub > 2026.2 > Administration and User Guide > Appendices 

Appendix IV: Standard Data Transformation Output Schema

The Standard Data Transformation task writes standardized output tables that can be used for reporting, integration, and analysis. This appendix documents the schema of each standard output table, including column names, data types, and nullability.

 

These result tables can be queried through the DataHub Data API. The schema documented here represents the default database output schema written by the Standard Data Transformation task.

 

Metadata Columns

When the metadata tracking feature is enabled on the tenant, the system automatically appends the following columns to every result table written by the Standard Data Transformation task.

 

Column Name

Data Type

Nullable

Description

__dh_created_at

DATETIME2

No

Timestamp (UTC) when the row was first written to the table. Defaults to the current UTC time at insert.

__dh_last_modified

DATETIME2

No

Timestamp (UTC) when the row was last updated. Defaults to the current UTC time at insert.

__dh_md5_checksum

VARCHAR(32)

Yes

MD5 checksum of the row data, used for change detection and delta tracking.

 

DataTransformation-result_devices

Purpose: Contains one row per inventoried device record after deduplication and normalization. Used as the primary device inventory output for reporting and integration.

 

Column Name

Data Type

Nullable

Description

bios

NVARCHAR(255)

Yes

BIOS identifier string of the device.

cpu_chip_count

FLOAT

Yes

Number of physical processor chips installed in the device.

cpu_core_count

FLOAT

Yes

Total number of CPU cores across all processor chips.

cpu_core_count_limited

FLOAT

Yes

Effective CPU core count used for licensing calculations, taking core count limitations into account.

cpu_speed

INT

Yes

Processor clock speed in MHz.

cpu_thread_count

FLOAT

Yes

Total number of logical processor threads.

department

NVARCHAR(255)

Yes

Department associated with the device.

device_key

NVARCHAR(255)

Yes

Unique identifier for the device record assigned during deduplication.

device_manufacturer

NVARCHAR(255)

Yes

Manufacturer of the device.

device_model

NVARCHAR(255)

Yes

Model designation of the device.

device_name

NVARCHAR(255)

Yes

Display name of the device.

fqdn

NVARCHAR(255)

Yes

Fully qualified domain name of the device.

graphics

NVARCHAR(255)

Yes

Graphics adapter information.

hostname

NVARCHAR(255)

Yes

Network hostname of the device.

hyper_threading_act

NVARCHAR(255)

Yes

Indicates whether hyper-threading is active on the processor.

import_cpu_type_id

NVARCHAR(255)

Yes

Reference to the CPU type in the source system.

import_data_source_id

NVARCHAR(255)

Yes

Reference to the data source that provided the record.

import_device_status_id

NVARCHAR(255)

Yes

Reference to the device status classification in the source system.

import_device_type_id

NVARCHAR(255)

Yes

Reference to the device type classification in the source system.

import_domain_id

NVARCHAR(255)

Yes

Reference to the domain in the source system.

import_id

NVARCHAR(255)

Yes

Device identifier from the source system.

import_location_id

NVARCHAR(255)

Yes

Reference to the location in the source system.

import_operating_system_id

NVARCHAR(255)

Yes

Reference to the operating system in the source system.

import_org_level_2_id

NVARCHAR(255)

Yes

Reference to the second-level organizational unit in the source system.

import_user_id

NVARCHAR(255)

Yes

Reference to the primary user of the device in the source system.

installation_date

NVARCHAR(255)

Yes

Date when the device was installed or commissioned.

inventory_date

NVARCHAR(255)

Yes

Date of the most recent inventory scan of the device.

inventory_number

NVARCHAR(255)

Yes

Asset inventory number assigned to the device.

ip_address

NVARCHAR(MAX)

Yes

IP address or addresses of the device.

last_logon_timestamp

NVARCHAR(255)

Yes

Timestamp of the last user logon on the device.

limit_cpu_core_count

BIT

Yes

Indicates whether the CPU core count is limited for licensing calculations.

mac_address

NVARCHAR(510)

Yes

MAC address or addresses of the device.

network

NVARCHAR(MAX)

Yes

Network adapter information.

ram

INT

Yes

Total physical memory in MB.

serial_number

NVARCHAR(255)

Yes

Hardware serial number of the device.

source

NVARCHAR(255)

Yes

Origin/source system or connector from which the record was derived.

source_table

NVARCHAR(255)

Yes

Original source table or input table from which the record was derived.

storage

BIGINT

Yes

Total disk storage capacity in MB.

uuid

NVARCHAR(255)

Yes

Universally unique identifier of the device.

 

DataTransformation-result_software

Purpose: Contains one row per installed software record per device after deduplication and normalization. Used as the primary software inventory output for reporting and integration.

 

Column Name

Data Type

Nullable

Description

description

NVARCHAR(255)

Yes

Software description.

edition

NVARCHAR(255)

Yes

Edition of the software product.

file_name

NVARCHAR(255)

Yes

Name of the primary executable or installation file.

file_path

NVARCHAR(255)

Yes

Path to the primary executable or installation file.

import_account_id

NVARCHAR(255)

Yes

Reference to the account associated with the installation in the source system.

import_data_source_id

NVARCHAR(255)

Yes

Reference to the data source that provided the record.

import_device_id

NVARCHAR(255)

Yes

Reference to the device on which the software is installed, in the source system.

installation_date

NVARCHAR(255)

Yes

Date when the software was installed.

last_usage

NVARCHAR(255)

Yes

Timestamp of the most recent usage of the software.

os

NVARCHAR(255)

Yes

Operating system on which the software is installed.

product

NVARCHAR(MAX)

Yes

Product name of the software.

product_code

NVARCHAR(255)

Yes

Installation code or GUID of the software product.

product_version

NVARCHAR(256)

Yes

Version string of the software product.

publisher

NVARCHAR(1024)

Yes

Name of the software publisher.

source

NVARCHAR(255)

Yes

Origin/source system or connector from which the record was derived.

source_table

NVARCHAR(510)

Yes

Original source table or input table from which the record was derived.

usage_frequency_month

INT

Yes

Number of times the software was used in the last calendar month.

usage_time

INT

Yes

Total accumulated usage time in minutes.

 

DataTransformation-result_software_summary

Purpose: Contains one row per unique software title across all devices. Provides a summary view of installed software, including the total number of installations per title.

 

Column Name

Data Type

Nullable

Description

description

NVARCHAR(255)

Yes

Software description.

edition

NVARCHAR(255)

Yes

Edition of the software product.

file_name

NVARCHAR(255)

Yes

Name of the primary executable or installation file.

file_path

NVARCHAR(255)

Yes

Path to the primary executable or installation file.

id

UNIQUEIDENTIFIER

Yes

Internal identifier for the software summary record.

import_data_source_id

NVARCHAR(255)

Yes

Reference to the data source that provided the record.

installedon

INT

Yes

Number of devices on which this software title is installed.

os

NVARCHAR(255)

Yes

Operating system on which the software is installed.

product

NVARCHAR(MAX)

Yes

Product name of the software.

product_code

NVARCHAR(255)

Yes

Installation code or GUID of the software product.

product_version

NVARCHAR(256)

Yes

Version string of the software product.

publisher

NVARCHAR(1024)

Yes

Name of the software publisher.

source

NVARCHAR(255)

Yes

Origin/source system or connector from which the record was derived.

source_table

NVARCHAR(510)

Yes

Original source table or input table from which the record was derived.

 

DataTransformation-result_users

Purpose: Contains one row per user record after deduplication and normalization. Used as the primary user inventory output for reporting and integration.

 

Column Name

Data Type

Nullable

Description

email

NVARCHAR(255)

Yes

Email address of the user.

first_name

NVARCHAR(255)

Yes

First name of the user.

import_data_source_id

NVARCHAR(255)

Yes

Reference to the data source that provided the record.

import_id

NVARCHAR(255)

Yes

User identifier from the source system.

import_level_2_id

NVARCHAR(255)

Yes

Reference to the second-level organizational unit in the source system.

import_location_id

NVARCHAR(255)

Yes

Reference to the location in the source system.

is_active

BIT

Yes

Indicates whether the user account is active.

last_name

NVARCHAR(255)

Yes

Last name of the user.

login

NVARCHAR(255)

Yes

Login name of the user.

source

NVARCHAR(255)

Yes

Origin/source system or connector from which the record was derived.

source_table

NVARCHAR(255)

Yes

Original source table or input table from which the record was derived.

user_key

NVARCHAR(255)

Yes

Unique identifier for the user record assigned during deduplication.

userPrincipalName

NVARCHAR(255)

Yes

User Principal Name (UPN) of the user account.

 

DataTransformation-result_user_device_relations

Purpose: Contains one row per user-device assignment, recording which users are associated with which devices and whether each assignment is the primary assignment.

 

Column Name

Data Type

Nullable

Description

creation_time

NVARCHAR(255)

Yes

Timestamp when the user-device relation was created in the source system.

import_data_source_id

NVARCHAR(255)

Yes

Reference to the data source that provided the record.

import_device_id

NVARCHAR(255)

Yes

Reference to the device in the source system.

import_id

NVARCHAR(255)

Yes

Relation identifier from the source system.

import_user_id

NVARCHAR(255)

Yes

Reference to the user in the source system.

is_active

BIT

Yes

Indicates whether the user-device relation is currently active.

is_primary

BIT

Yes

Indicates whether this is the primary device assignment for the user.

source

NVARCHAR(255)

Yes

Origin/source system or connector from which the record was derived.

source_table

NVARCHAR(255)

Yes

Original source table or input table from which the record was derived.

 

DataTransformation-result_accounts

Purpose: Contains one row per account record, linking user accounts to their source system identifiers. Used for account-level reporting and cross-referencing with user and device data.

 

Column Name

Data Type

Nullable

Description

account

NVARCHAR(MAX)

—

Account name or login identifier.

import_data_source_id

NVARCHAR(MAX)

—

Reference to the data source that provided the record.

import_id

NVARCHAR(MAX)

—

Account identifier from the source system.

import_provider_instance_id

NVARCHAR(MAX)

—

Reference to the provider instance in the source system.

import_user_id

NVARCHAR(MAX)

—

Reference to the user record associated with this account in the source system.

source

NVARCHAR(MAX)

—

Origin/source system or connector from which the record was derived.

source_table

NVARCHAR(MAX)

—

Original source table or input table from which the record was derived.

userPrincipalName

NVARCHAR(MAX)

—

User Principal Name (UPN) of the account.

Note: Nullability for this table could not be confirmed from the available source code. Column names and data types are sourced from the TableSchemaExport.xml schema definition.

 

DataTransformation-result_device_relations

Purpose: Contains one row per device-to-device relationship record, such as virtualization host/guest relationships. Used for topology reporting and license optimization scenarios.

 

Column Name

Data Type

Nullable

Description

import_data_source_id

NVARCHAR(255)

Yes

Reference to the data source that provided the record.

import_device_rel_type_id

NVARCHAR(255)

Yes

Reference to the device relation type in the source system.

import_from_device_id

NVARCHAR(255)

Yes

Source device identifier in the relation.

import_id

NVARCHAR(510)

Yes

Relation identifier from the source system.

import_to_device_host_id

NVARCHAR(255)

Yes

Host device identifier on the target side of the relation.

import_to_device_id

NVARCHAR(255)

Yes

Target device identifier in the relation.

powerState

NVARCHAR(255)

Yes

Power state of the device, relevant in virtualization scenarios.

source

NVARCHAR(255)

Yes

Origin/source system or connector from which the record was derived.

source_table

NVARCHAR(255)

Yes

Original source table or input table from which the record was derived.

 

DataTransformation-result_hardware_summary

Purpose: Contains one row per unique hardware configuration (manufacturer and model combination). Used for hardware asset reporting and capacity planning.

 

Column Name

Data Type

Nullable

Description

Id

UNIQUEIDENTIFIER

Yes

Internal identifier for the hardware summary record.

manufacturer

NVARCHAR(255)

Yes

Hardware manufacturer name.

model

NVARCHAR(MAX)

Yes

Hardware model designation.

type_hint

TINYINT

Yes

Numeric hint for the device type classification.

used_by

INT

Yes

Number of devices using this hardware configuration.