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
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share