Request to retrieve Software and Package by OR

[Gaetan]

Hello everyone, here are two requests:

@Template Computer with SOFTWARE not installed by OU

Code: Select all

SELECT
    h.computer_name
FROM
    hosts h
WHERE
    h.computer_ad_ou LIKE '%%OU%%'
    AND NOT EXISTS (
        SELECT 1
        FROM hostsoftwares hs
        WHERE hs.host_id = h.uuid
        AND hs.name ilike '%%SOFTWARE%%'
    );

@Template computer with PACKAGE not installed by OU

Code: Select all

SELECT
    h.computer_name,last_seen_on,last_logged_on_user
FROM
    hosts h
WHERE
    h.computer_ad_ou LIKE '%%OU%%'
    AND h.uuid NOT IN (
        SELECT
            hs.host_id
        FROM
            hostpackagesstatus hs
        INNER JOIN
            packages p ON hs.package_uuid = p.package_uuid
        WHERE
            p.package LIKE 'PACKAGE'
    );