SELECT DISTINCT ds.name AS site_name, da.ip_address, da.host_name, da.mac_address, dos.description AS operating_system, dht.description, dos.asset_type, da.last_assessed_for_vulnerabilities, aggregated_credential_status_description,
das.port
FROM dim_asset da
JOIN dim_operating_system dos USING (operating_system_id)
JOIN dim_host_type dht USING (host_type_id)
JOIN dim_site_asset dsa USING (asset_id)
JOIN dim_site ds USING (site_id)
JOIN fact_asset_scan_service fass using (asset_id)
JOIN dim_credential_status dcs using (credential_status_id)
JOIN dim_credential_status USING(credential_status_id)
JOIN fact_asset fa USING (asset_id)
JOIN dim_aggregated_credential_status USING(aggregated_credential_status_id)
JOIN dim_asset_service das USING (asset_id)
ORDER BY site_name, da.ip_address
Please See Query Above:
I would like to add the “owner tag” to the above SQL Query. I have tried all the connections with the Data map from Rapid7 but cannot get it to work. Any help would be appreciated
@jacob_horning Since you can have multiple owner tags associated with a single asset, the best way to do this is to create a list of owner tags first. Something like:
WITH
owner_tags AS (
SELECT asset_id, array_to_string(array_agg(distinct tag_name),'|') AS owner_tags
FROM dim_tag
JOIN dim_tag_asset USING (tag_id)
WHERE tag_type = 'OWNER'
GROUP BY asset_id
)
SELECT DISTINCT ds.name AS site_name, da.ip_address, da.host_name, da.mac_address, dos.description AS operating_system, dht.description, dos.asset_type, da.last_assessed_for_vulnerabilities, aggregated_credential_status_description,
das.port, ot.owner_tags
FROM dim_asset da
JOIN dim_operating_system dos USING (operating_system_id)
JOIN dim_host_type dht USING (host_type_id)
JOIN dim_site_asset dsa USING (asset_id)
JOIN dim_site ds USING (site_id)
JOIN fact_asset_scan_service fass using (asset_id)
JOIN dim_credential_status dcs using (credential_status_id)
JOIN dim_credential_status USING(credential_status_id)
JOIN fact_asset fa USING (asset_id)
JOIN dim_aggregated_credential_status USING(aggregated_credential_status_id)
JOIN dim_asset_service das USING (asset_id)
LEFT JOIN owner_tags AS ot ON (ot.asset_id = fa.asset_id)
ORDER BY site_name, da.ip_address