Retrieving application versions via OR grouped by version

[Gaetan]

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

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;