Can anyone tell me why this query is failing in InsightVM? Purpose: Identify installed software whose install location/path suggests "per-user" deployment (e.g., HKCU-style installs often land under C:\Users\\AppData or similar user paths).
SELECT
da.asset_id AS "Asset ID",
da.ip_address AS "IP Address",
da.host_name AS "Host Name",
da.os_name AS "OS",
ds.name AS "Software Name",
ds.version AS "Software Version",
dis.install_location AS "Install Location",
dis.install_path AS "Install Path",
dis.publisher AS "Publisher"
FROM dim_asset da
JOIN dim_installed_software dis
ON dis.asset_id = da.asset_id
JOIN dim_software ds
ON ds.software_id = dis.software_id
WHERE
/* Common user-profile locations */
COALESCE(dis.install_location, '') ILIKE 'C:\Users\%' ESCAPE '\'
OR COALESCE(dis.install_path, '') ILIKE 'C:\Users\%' ESCAPE '\'
OR COALESCE(dis.install_location, '') ILIKE '%\AppData\%' ESCAPE '\'
OR COALESCE(dis.install_path, '') ILIKE '%\AppData\%' ESCAPE '\'
OR COALESCE(dis.install_location, '') ILIKE '%\LocalAppData\%' ESCAPE '\'
OR COALESCE(dis.install_path, '') ILIKE '%\LocalAppData\%' ESCAPE '\'
OR COALESCE(dis.install_location, '') ILIKE '%\Roaming\%' ESCAPE '\'
OR COALESCE(dis.install_path, '') ILIKE '%\Roaming\%' ESCAPE '\'
OR COALESCE(dis.install_location, '') ILIKE '%\Temp\%' ESCAPE '\'
OR COALESCE(dis.install_path, '') ILIKE '%\Temp\%' ESCAPE '\'
ORDER BY
da.host_name, ds.name, ds.version;