Purpose
The Database Core Data Product is an inventory of your cloud database resources, such as managed relational databases and data warehouses. Each asset appears once, no matter how many feeds report it, and the table keeps both its current state and its history. Use it to see which databases exist, when
they first appeared and when they were closed, and to join back to OCSF activity and findings that involve those resources.
Entity Correlation
The Entity Correlator identifies a cloud asset by its unique ID (uid), for example an Amazon Resource Name. When several feeds report the same uid, they are merged into a single asset with a single id. If feeds disagree about a value, feed priority and recency decide which one is kept. A
resource reported without a unique ID, or without a type of database, databucket or table, is not tracked as an asset.
Understanding the Table
When a value on an asset changes, a new row is created, and active is set to false on the older rows, so each asset has exactly one row with active = TRUE.
An asset appears in this table when the feed that reports it classifies it as a database (type ID 1). Assets that a feed reports without a type are not placed in any of the cloud-asset tables.
The end_time field is populated when an asset is closed: either the source reports it as decommissioned, or no feed has reported it for the cloud-resource retention period (365 days by default, configurable). A closed asset has active = FALSE. If a closed asset is reported again, it is tracked as a new asset with a new id.
Joining to CDPs and OCSF
database.id identifies the asset across all of its history rows. OCSF event tables that reference cloud resources carry the asset in their resources array, and each element's id matches the CDP id. Join on that element to find the activity and findings that involve a database.
Schema
Example Queries
Find all active databases
SELECT * FROM database WHERE active=TRUE
Find the latest record for a given id
SELECT * FROM database WHERE active=TRUE AND id={id}
Find databases first seen in the last 30 days
SELECT id, name, uid, first_seen_time
FROM database
WHERE active=TRUE
AND first_seen_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
Find the largest active databases
SELECT id, name, uid, size
FROM database
WHERE active=TRUE
ORDER BY size DESC NULLS LAST
LIMIT 25
Joining back to OCSF
SELECT d.name, a.*
FROM OCSF.{activity} a,
LATERAL FLATTEN(input => a.resources) r,
CDP.database d
WHERE d.id = r.value:id::INT
AND d.active=TRUE