Is there a way to find devices by exception?

 
1
0
-1

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.

  1. Rick Wegner

    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?

CommentAdd your comment...

2 answers

  1.  
    1
    0
    -1

    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');
      CommentAdd your comment...
    1.  
      1
      0
      -1

      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
      1. Rick Wegner

        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.

      CommentAdd your comment...