Retrieving application versions via OR grouped by version
Published: June 14, 2024 - 3:25 PM
Good morning,
Here is a query that retrieves:
- the list of applications
- grouped by version number
- indicating which workstations have this version
- of a specific OU
Here is a query that retrieves:
- the list of applications
- grouped by version number
- indicating which workstations have this version
- of a specific OU
Code: Select all
SELECT
hs.name AS Software_Name,
hs.version AS Software_Version,
COUNT(DISTINCT h.computer_fqdn) AS NbMachines,
STRING_AGG(DISTINCT LOWER(h.computer_fqdn), ', ') AS Computer_FQDNs
FROM
hosts h
JOIN hostsoftwares hs ON h.computer_fqdn = hs.host_id
WHERE
h.computer_ad_ou LIKE '%%OU%%'
GROUP BY
hs.name, hs.version
ORDER BY
hs.name, hs.version;