ITNinja

This SQL report lists software not in a software list for machines that belongs to a label.

This SQL report can be used to find all computers belongs to a label that have software not in the approved software list.

To use this report, replace label_name with the group of machines label name, software_1, software_2, software_3, software_4 with list of approved software titles as displayed in Inventory | Software. E.g., 'Adobe Reader 9.1'.

% can be used as wild card. E.g., 'Adobe Reader%' includes all versions of Adobe Reader.

Duplicate the line and SOFTWARE.DISPLAY_NAME not like 'software_n' for additional software title.


SELECT
MACHINE.NAME AS SYSTEM_NAME, SYSTEM_DESCRIPTION, IP,
GROUP_CONCAT(DISTINCT SOFTWARE.DISPLAY_NAME SEPARATOR '\n') AS SOFTWARE_TITLE_NAME
from MACHINE LEFT JOIN MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID)
LEFT JOIN SOFTWARE ON (SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID)
WHERE
( (1 in (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 like 'label_name')) )
AND
(SOFTWARE.DISPLAY_NAME not like 'software_1'
and SOFTWARE.DISPLAY_NAME not like 'software_2'
and SOFTWARE.DISPLAY_NAME not like 'software_3'
and SOFTWARE.DISPLAY_NAME not like 'software_4'
and SOFTWARE.DISPLAY_NAME not like '%KB%')
GROUP BY MACHINE.ID
order by MACHINE.NAME,SOFTWARE.DISPLAY_NAME