Ah - let me try that again. I'm not sure the query will be efficient, but it's below.
SELECT system.id AS `system.id`, system.icon AS `system.icon`, system.type AS `system.type`, system.name AS `system.name`, system.domain AS `system.domain`, system.ip AS `system.ip`, system.class AS `system.class`, system.os_family AS `system.os_family`, oa_org.name AS `oa_org.name`, windows.user_name AS `windows.user_name` FROM system LEFT JOIN oa_org ON (oa_org.id = system.org_id) LEFT JOIN windows ON (windows.system_id = system.id AND windows.current = 'y') LEFT JOIN oa_group_sys ON (oa_group_sys.system_id = system.id) WHERE oa_group_sys.group_id = @group AND system.id NOT IN (SELECT software.system_id FROM software WHERE software.name = 'KB4012212' AND current = 'y');
I just tested with the below query and it seemed to function as expected. I based it off the AntiVirus query.
SELECT system.id AS `system.id`, system.icon AS `system.icon`, system.type AS `system.type`, system.name AS `system.name`, system.domain AS `system.domain`, system.ip AS `system.ip`, system.class AS `system.class`, system.os_family AS `system.os_family`, oa_org.name AS `oa_org.name`, windows.user_name AS `windows.user_name`, software.name as `software.name`, software.version AS `software.version`, software.id as `software.id` FROM system LEFT JOIN software ON (software.system_id = system.id AND software.current = 'y' AND software.name LIKE '%KB4012212%') LEFT JOIN oa_org ON (oa_org.id = system.org_id) LEFT JOIN windows ON (windows.system_id = system.id AND windows.current = 'y') LEFT JOIN oa_group_sys ON (oa_group_sys.system_id = system.id) WHERE oa_group_sys.group_id = @group ORDER BY system.name, software.name
I replaced the Select statement from the Installed-AntiVirus query with the one provided but I am still getting a report with the Software Name column blank with all computers listed. In the AntiVirus query the Software Name column is a linked to /report/specific_software/$group_id and a column secondary equal to software.id. I tried it with column_type equal to text with the link and secondary blank but Software Name remains blank. I'm sure I am missing something basic.
I would like to display all devices that DO NOT have a value associated with it. An example of this would be to find all devices that do not have KB4012215 in their list of installed software.
I've looked at the Installed-AntiVirus query and I have attempted to search for the Microsoft update KB entries against the software.name. So I created a new queary based off Installed-AntiVirut with the only change I made was to substitute the '%virus%' etc. with '%KB4012212%' but nothing is found, the Software Name column is empty . Are the KB entries stored in a different table/field than software.name?