SQL HELP!!! Is it possible to list all computers and have the computers with a particular piece of software display something in a column indicating it is detected?
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
Answers (1)
Please log in to answer
Posted by:
chucksteel
9 years ago
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
Comments:
-
Thanks Chuck! This gave me just what I needed to be able to tweak the report to display the data being requested. - jparkins 9 years ago
