Device

Purpose  

The Device CDP contains information on all devices and assets within an organization. The Device CDP is ideal for answering questions about the current and historical status of the organization's asset inventory and can be joined to OCSF event tables for further historical trend analysis.

Entity Correlation

The Device CDP leverages the Entity Correlator to effectively track and manage the distinct assets that DataBee has identified within the organization. The Entity Correlator utilizes several key fields to identify and differentiate users, listed below in their rank order of importance:

• Serial Number: this unique identifier is the primary field used to distinguish each asset within the system

• IMEI: the IMEI of the device serves as the secondary piece of information to identify a device

• MAC: the MAC address provides an additional layer of identification

• Hostname: hostname, or the unique name of the device, is used in conjunction with the above attributes to identify a device

• IP: the IP address is the last used piece of information as it can easily change

Understanding the Table

The Device CDP is a Type 2 Slowly Changing table. This classification signifies that any modifications to an entity will result in the creation of new rows for that device ID, capturing the updated fields. The active column for the older row is set to False, ensuring that historical data remains unaltered and providing a clear distinction between current and previous states. Consequently, users can trace the evolution of the data over time, making it easier to perform audits and historical analyses.

End_time is populated only if a device has been merged into another device by the Entity Correlator. In such cases, a new row is created to reflect the merged asset, and the end_time field is populated for all previous rows associated with the device. This ensures that there is a clear distinction between the historical data and the current state of the Entity Correlator. By tracking the end_time, users can easily identify when a particular device entity was last active before being merged.

On the other hand, if a device’s attributes are merely updated without merging, the end_time will not be populated for the older row. Instead, the active field will be set to False. This indicates that the older information is no longer current but still retains its historical significance.

Joining to CDPs and OCSF

The device.id column is the primary identifier used for all joins within the Core Data Products. This column ensures consistency and reliability when linking different data sets within the CDPs. If the security hygiene entitlement feature is enabled, the device.id can also be utilized to join data back to the Open Cybersecurity Schema Framework (OCSF) tables, providing a seamless connection between the two data architectures.

In scenarios where the security hygiene entitlement is not enabled, alternative identifiers must be used. These alternatives include device.hardware_info_serial_number, device.mac, or device.hostname.

Schema

Example Queries

Find all active devices

SELECT * FROM device where active=TRUE 

Find the latest record for a given id

SELECT * FROM device where active=TRUE and id={id} 

Find all active entities and their latest row

SELECT * FROM device where active=TRUE

How to join back to OCSF

SELECT * FROM CDP.device left join OCSF.{activity} on CDP.device.id = OCSF.{activity}.device_id 

How to join back to OCSF without security hygiene

SELECT * FROM CDP.device left join OCSF.{activity} on CDP.device.mac = OCSF.{activity}.device.mac