A little more context to my question. I am trying to list all computer and then have some sort of identifier present in the report for those computers with the software 'Corp Detected'.

I think I am pretty close, but am stuck and hoping that someone can help.

Here is what I have for the sql so far:

SELECT 
    MACHINE.NAME AS SYSTEM_NAME,
    OS_NAME,
    MACHINE.USER_NAME,
    USER_FULLNAME,
    LAST_SYNC,
    A62.NAME AS 'Location'
FROM
    MACHINE
        LEFT JOIN
    ASSET ON ASSET.NAME = MACHINE.BIOS_SERIAL_NUMBER
        LEFT JOIN
    ASSET_ASSOCIATION J62 ON J62.ASSET_ID = ASSET.ID
        AND J62.ASSET_FIELD_ID = 62
        LEFT JOIN
    ASSET A62 ON A62.ID = J62.ASSOCIATED_ASSET_ID
        LEFT JOIN
    ASSET_DATA_1 AD62 ON AD62.ID = A62.ASSET_DATA_ID
WHERE
    (((EXISTS( SELECT 
            1
        FROM
            SOFTWARE,
            MACHINE_SOFTWARE_JT
        WHERE
            MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID
                AND SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID
                AND SOFTWARE.DISPLAY_NAME = 'Corp Detected')))
        AND ((EXISTS( SELECT 
            1
        FROM
            LABEL,
            MACHINE_LABEL_JT
        WHERE
            MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
                AND MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
                AND LABEL.TYPE <> 'hidden'
                AND (LABEL.NAME = 'Windows Workstations (All)')
                AND ((TIMESTAMP(LAST_SYNC) <= NOW()
                AND TIMESTAMP(LAST_SYNC) > DATE_SUB(NOW(), INTERVAL 14 DAY))))))) 
UNION SELECT 
    MACHINE.NAME AS SYSTEM_NAME,
    OS_NAME,
    MACHINE.USER_NAME,
    USER_FULLNAME,
    LAST_SYNC,
    A62.NAME AS 'Location'
FROM
    MACHINE
        LEFT JOIN
    ASSET ON ASSET.NAME = MACHINE.BIOS_SERIAL_NUMBER
        LEFT JOIN
    ASSET_ASSOCIATION J62 ON J62.ASSET_ID = ASSET.ID
        AND J62.ASSET_FIELD_ID = 62
        LEFT JOIN
    ASSET A62 ON A62.ID = J62.ASSOCIATED_ASSET_ID
        LEFT JOIN
    ASSET_DATA_1 AD62 ON AD62.ID = A62.ASSET_DATA_ID
WHERE
    (((NOT EXISTS( SELECT 
            1
        FROM
            SOFTWARE,
            MACHINE_SOFTWARE_JT
        WHERE
            MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID
                AND SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID
                AND SOFTWARE.DISPLAY_NAME = 'Corp Detected')))
        AND ((EXISTS( SELECT 
            1
        FROM
            LABEL,
            MACHINE_LABEL_JT
        WHERE
            MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
                AND MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
                AND LABEL.TYPE <> 'hidden'
                AND (LABEL.NAME = 'Windows Workstations (All)')
                AND ((TIMESTAMP(LAST_SYNC) <= NOW()
                AND TIMESTAMP(LAST_SYNC) > DATE_SUB(NOW(), INTERVAL 14 DAY)))))))
ORDER BY OS_NAME , SYSTEM_NAME
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0
Here is how I would do this:

SELECT M.NAME, M.IP, M.LAST_SYNC, 
CASE 
    WHEN GROUP_CONCAT(SOFTWARE.DISPLAY_NAME) like "%Adobe Acrobat XI Pro%" THEN "Yes"
END AS "Acrobat Pro Detected"
FROM ORG1.MACHINE M
JOIN MACHINE_SOFTWARE_JT on MACHINE_SOFTWARE_JT.MACHINE_ID = M.ID
LEFT JOIN SOFTWARE on SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID
GROUP BY M.NAME
Change the %Adobe Acrobat XI Pro% and the "Acrobat Pro Detected" appropriately for the software you are looking to find. Machines that don't have the title will have a Null in that column. If you want it to look nicer (Yes/No) then this would work:

SELECT M.NAME, M.IP, M.LAST_SYNC, 
CASE 
    WHEN GROUP_CONCAT(SOFTWARE.DISPLAY_NAME) like "%Adobe Acrobat XI Pro%" THEN "Yes"
    WHEN GROUP_CONCAT(SOFTWARE.DISPLAY_NAME) not like "%Adobe Acrobat XI Pro%" THEN "No"
END AS "Acrobat Pro Detected"
FROM ORG1.MACHINE M
JOIN MACHINE_SOFTWARE_JT on MACHINE_SOFTWARE_JT.MACHINE_ID = M.ID
LEFT JOIN SOFTWARE on SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID
GROUP BY M.NAME

Answered 04/10/2015 by: chucksteel
Red Belt

  • Thanks Chuck! This gave me just what I needed to be able to tweak the report to display the data being requested.
Please log in to comment
Answer this question or Comment on this question for clarity