Hi,

I have a number of machines that have duplicate guids for their McAfee Agent. I've made a custom inventory rule but I want to group the guid and count them up so I can find the duplicate guids and run a script fix on those machines. Is report that can do this? I'm trying to figure out how to do this but I'm grasping at straws at the moment.

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

2

I've gotten the code to generate the report however it shows groups that have one etnry, I'd like to get rid of those and only show groups that have more then one entry. Here's my sql

 

SELECT IP,MAC,MACHINE.NAME AS SYSTEM_NAME,(SELECT MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE FROM MACHINE_CUSTOM_INVENTORY WHERE MACHINE_CUSTOM_INVENTORY.ID=MACHINE.ID AND MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID=44226) AS MACHINE_CUSTOM_INVENTORY_0_44226,(SELECT MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE FROM MACHINE_CUSTOM_INVENTORY WHERE MACHINE_CUSTOM_INVENTORY.ID=MACHINE.ID AND MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID=43613) AS MACHINE_CUSTOM_INVENTORY_0_43613 FROM MACHINE    WHERE ((1  in (select 1 from MACHINE_CUSTOM_INVENTORY where MACHINE.ID = MACHINE_CUSTOM_INVENTORY.ID and MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID = 44226 and MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE is not null)) )  ORDER BY MACHINE_CUSTOM_INVENTORY_0_44226 desc

Answered 11/01/2012 by: ms01ak
Tenth Degree Black Belt

  • It's hard for me to test this without setting something like this up myself. Can you try adding the "HAVING COUNT" line to the end of this query (2nd to the last line)?

    SELECT IP,MAC,MACHINE.NAME AS SYSTEM_NAME,
    (SELECT MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE FROM MACHINE_CUSTOM_INVENTORY WHERE MACHINE_CUSTOM_INVENTORY.ID=MACHINE.ID
    AND MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID=44226) AS MACHINE_CUSTOM_INVENTORY_0_44226,
    (SELECT MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE FROM MACHINE_CUSTOM_INVENTORY
    WHERE MACHINE_CUSTOM_INVENTORY.ID=MACHINE.ID AND MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID=43613) AS MACHINE_CUSTOM_INVENTORY_0_43613
    FROM MACHINE
    WHERE ((1 in (select 1 from MACHINE_CUSTOM_INVENTORY where MACHINE.ID = MACHINE_CUSTOM_INVENTORY.ID
    and MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID = 44226 and MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE is not null)) )
    HAVING COUNT(MACHINE_CUSTOM_INVENTORY_0_43613) > 1
    ORDER BY MACHINE_CUSTOM_INVENTORY_0_44226 desc
    • I just ran this query below on one of my custom inventories. I have a script that returns the Sophos update paths. On our older machines there is just one. On our Win 7 machines there are two update paths. I took out the "is not NULL" to return all results.

      SELECT IP,MAC,M.NAME AS SYSTEM_NAME,
      (SELECT MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE FROM MACHINE_CUSTOM_INVENTORY
      WHERE MACHINE_CUSTOM_INVENTORY.ID=M.ID AND MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID=94141) AS SOPHOS
      FROM MACHINE M
      GROUP BY M.NAME
      HAVING COUNT(SOPHOS) > 1
      ORDER BY M.NAME desc

      This returned back all results that had SOPHOS populated. When I changed the HAVING COUNT to "<" it returned all NULL results. You might have better luck, but it looks like it's seeing anything in that field as one result.
Please log in to comment

Answers

1

Something like that can be done using the GUI in Kace. Especially since your custom ID will be different. Try it through there first. If it needs more customization post the SQL code that it generated.

Answered 11/01/2012 by: dugullett
Red Belt

Please log in to comment
-1

I am assuning the guid is in the registry.  If so create a custom software inventory label using RegistryValueEquals(registryPath, valueName, value) and run a report on that label.

Answered 11/02/2012 by: SMal.tmcc
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity

Share