My installed software query that I have been running for over a year is no longer working correctly. It is ran off an asset group and only shows results for two thirds of the assets that are in the asset group. When I try to test the query on one of the missing devices, the report is blank and only shows the headers. Query is pasted below. Can you tell what the issue is? Thanks!
SELECT dsi.name AS “Site Name”, da.ip_address AS “IP Address”, da.host_name AS “Host Name”, dos.description AS “Operating System”,
dht.description AS “Host Type”, ds.vendor AS “Software Vendor”, ds.name AS “Software Name”, ds.version AS “Software Version”
FROM dim_asset da
JOIN dim_operating_system dos USING (operating_system_id)
JOIN dim_host_type dht USING (host_type_id)
JOIN dim_asset_software das USING (asset_id)
JOIN dim_software ds USING (software_id)
JOIN dim_site_asset dsa USING (asset_id)
JOIN dim_site dsi USING (site_id)
ORDER BY da.ip_address, ds.vendor, ds.name
Depending on the info we have for those assets it could be an issue with the dim_operating_system table or the dim_host_type tables. If we don’t have info to populate those fields in those tables the lines would not appear with a typical JOIN because you’re telling it to only JOIN when you have data. Try using a LEFT JOIN on those two tables and re-run.
Thanks John! I added the left to those 2 tables below
LEFT JOIN dim_operating_system dos USING (operating_system_id)
LEFT JOIN dim_host_type dht USING (host_type_id)
It caught around 100 more assets than the report did that didn’t have the “LEFT”, but I am still missing over 1000. should I add LEFT to more of the tables, or do you have any recommendations for a new query instead? I tried to create a new one but kept getting an error. Thanks in advance!
probably the two software tables as well (sorry I should have said that the first time). Everything should be contained within a site so those two last tables are fine. But yeah if we didn’t have an authenticated scan or an agent present on the machine then we most likely don’t have any info for the other tables.
SELECT dsi.name AS “Site Name”, da.ip_address AS “IP Address”, da.host_name AS “Host Name”, dos.description AS “Operating System”,
dht.description AS “Host Type”, ds.vendor AS “Software Vendor”, ds.name AS “Software Name”, ds.version AS “Software Version”
FROM dim_asset da
LEFT JOIN dim_operating_system dos USING (operating_system_id)
LEFT JOIN dim_host_type dht USING (host_type_id)
LEFT JOIN dim_asset_software das USING (asset_id)
LEFT JOIN dim_software ds USING (software_id)
JOIN dim_site_asset dsa USING (asset_id)
JOIN dim_site dsi USING (site_id)
ORDER BY da.ip_address, ds.vendor, ds.name