I’m trying to create SQL query that list Operating Systems including the VMware ESXi server and also contains the ‘OWNER’ tag. But the problem is that it doesn’t out VMware esxi server in my sql query. But if I use the query builder and export to csv then shows VMware ESXI. Looking for some eagle eye out there if the query below needs more input?
SELECT
t.tag_name AS “Asset Owner”,
da.host_name,
da.ip_address,
dos.description AS “Operating System”,
dos.version AS “OS Version”,
dos.family AS “OS Family”
FROM
dim_asset da
LEFT JOIN dim_operating_system dos
ON da.operating_system_id = dos.operating_system_id
LEFT JOIN dim_tag_asset dta
ON da.asset_id = dta.asset_id
LEFT JOIN dim_tag t
ON dta.tag_id = t.tag_id AND t.tag_type = ‘OWNER’
ORDER BY
t.tag_name, dos.family, dos.version, da.ip_address;
dim_asset.operating_system_id often misses OSes detected only during scan (especially ESXi).
dim_asset_operating_system contains “all detected fingerprints”, including multiple OSes per asset with certainty ratings.
Looks like your old query relies on a static mapping via da.operating_system_id, which won’t catch VMware ESXi if it was discovered dynamically
try this and let me know.
WITH asset_owners AS (
SELECT dta.asset_id, dt.tag_name AS owner
FROM dim_tag_asset dta
JOIN dim_tag dt ON dta.tag_id = dt.tag_id
WHERE dt.tag_type = 'OWNER'
)
SELECT
ao.owner AS "Asset Owner",
da.host_name AS "Hostname",
da.ip_address AS "IP Address",
dos.description AS "Operating System",
dos.version AS "OS Version",
dos.family AS "OS Family",
dao.certainty AS "Certainty"
FROM dim_asset da
-- Get OS fingerprints (including ESXi) with certainty
JOIN dim_asset_operating_system dao ON da.asset_id = dao.asset_id
JOIN dim_operating_system dos ON dao.operating_system_id = dos.operating_system_id
-- Bring in tag-based ownership
LEFT JOIN asset_owners ao ON ao.asset_id = da.asset_id
ORDER BY ao.owner, dos.family, dos.version, da.ip_address;