Single-Row SQL Output for Multiple Software Installations

 💡 What This Query Does

 

 Retrieves all machines in a specific collection (CollectionID = ''`).

 Checks if Google Chrome or Microsoft Edge is installed.

 Displays the software version (or shows "Not Installed" if missing).

 Uses temporary tables to keep things clean and organized.

 🧾 SQL Query Breakdown

-- STEP 1: Chrome installations

SELECT DISTINCT

    sys1.ResourceID,

    sys1.Netbios_Name0 AS [Computer Name],

    arp.DisplayName0 AS [Software Name],

    arp.Version0 AS [Version]

INTO #TEMP_ENT8

FROM v_R_System AS sys1

INNER JOIN v_Add_Remove_Programs AS arp

    ON sys1.ResourceID = arp.ResourceID

WHERE arp.DisplayName0 LIKE 'Google Chrome';

 

-- STEP 2: Edge installations

SELECT DISTINCT

    sys1.ResourceID,

    sys1.Netbios_Name0 AS [Computer Name],

    arp.DisplayName0 AS [Software Name],

    arp.Version0 AS [Version]

INTO #TEMP_ENT9

FROM v_R_System AS sys1

INNER JOIN v_Add_Remove_Programs AS arp

    ON sys1.ResourceID = arp.ResourceID

WHERE arp.DisplayName0 LIKE 'Microsoft Edge';

  -- STEP 3: Final result with 'Not Installed' instead of NULL

SELECT DISTINCT

    COALESCE(ent8.[Computer Name], ent9.[Computer Name]) AS [Computer Name],

     ISNULL(ent8.[Software Name], 'Not Installed') AS [Google Chrome],

    ISNULL(ent8.Version, 'Not Installed') AS [Google Chrome Version],

     ISNULL(ent9.[Software Name], 'Not Installed') AS [Microsoft Edge],

    ISNULL(ent9.Version, 'Not Installed') AS [Microsoft Edge Version]

 FROM v_FullCollectionMembership AS fc

LEFT JOIN #TEMP_ENT8 AS ent8 ON ent8.ResourceID = fc.ResourceID

LEFT JOIN #TEMP_ENT9 AS ent9 ON ent9.ResourceID = fc.ResourceID

WHERE fc.CollectionID = 'CM400609'

  AND (ent8.ResourceID IS NOT NULL OR ent9.ResourceID IS NOT NULL);

 

-- STEP 4: Clean up temporary tables

DROP TABLE #TEMP_ENT8;

DROP TABLE #TEMP_ENT9;

✅ Sample Output

 

| Computer Name | Google Chrome | Chrome Version | Microsoft Edge | Edge Version  |

| ------------- | ------------- | -------------- | -------------- | ------------- |

| PC001         | Installed     | 117.0.5938.92  | Not Installed  | Not Installed |

| PC002         | Not Installed | Not Installed  | Installed      | 118.0.2088.69 |

 

 🧠 Final Notes

 

This script is a simple way to:

 

 Audit browser installations.

 Ensure compliance with your company’s software standards.

 Report on missing or outdated browsers.

 

You can modify the `CollectionID` or extend the query for other software like Firefox, Zoom, or antivirus tools.