Display the entire inventory, showing for each machine whether a package is affected or not

[filou07]

Good morning,
I created this reporting query, but it only displays machines where the MON_PAQUET package is assigned. How can I modify it so that 100% of machines appear: those that have it and those that don't?

My request:

Code: Select all

SELECT
    hosts.Description,
    hosts.uuid as Machine,
    hosts.last_logged_on_user,
    hostpackagesstatus.install_date,
    hostpackagesstatus.install_status
FROM hosts, hostpackagesstatus
WHERE hostpackagesstatus.name ilike '%%MON_PAQUET%%'
    AND hosts.uuid ilike '%%pc%%'
    AND hosts.uuid=hostpackagesstatus.host_id
ORDER BY hostpackagesstatus.name ASC

Thanks in advance !

[jarnaud]

Good morning,

Can you try this query?

Code: Select all

SELECT
    hosts.description,
    hosts.uuid AS machine,
    hosts.last_logged_on_user,
    hostpackagesstatus.install_date,
    hostpackagesstatus.install_status,
    hostpackagesstatus.version,
    CASE
        WHEN hostpackagesstatus.host_id IS NOT NULL THEN 'Présent'
        ELSE 'Absent'
    END AS statut
FROM hosts
LEFT JOIN hostpackagesstatus
    ON hosts.uuid = hostpackagesstatus.host_id
    AND hostpackagesstatus.package = 'tis-7zip'
WHERE hosts.uuid ilike '%%pc%%'
ORDER BY hostpackagesstatus.name ASC;

Does that answer your question?

[dcardon]

Hi Philippe,

did Jordan's SQL query meet your needs?

Best regards,

Denis