Symantec Management Platform (Notification Server)

When I run a lot of my reports, I get a lot of duplicate machine entries of the same system.

Here is the code:

SELECT Distinct  Inv_AeX_AC_Identification.Name, Inv_AeX_AC_Identification.[Domain], vUser.[Given Name], vUser.Surname, vUser.Name AS username,                   
                      Inv_AeX_OS_Add_Remove_Programs.Name AS [product name], Inv_AeX_OS_Add_Remove_Programs.Version                  
FROM         Inv_AeX_AC_Identification INNER JOIN                  
                      vUser ON Inv_AeX_AC_Identification.[Last Logon User] = vUser.Name INNER JOIN                  
                      Inv_AeX_OS_Add_Remove_Programs ON Inv_AeX_AC_Identification._ResourceGuid = Inv_AeX_OS_Add_Remove_Programs._ResourceGuid                  
WHERE     (Inv_AeX_OS_Add_Remove_Programs.Name LIKE '%Adobe Acrobat 8%')

I'm not proficient in SQL but can someone advise how to eliminate the duplicates from occuring?  

Thanks!

Kevin

your computers may have more than one entry in their add/remove programs that include "%Adobe Acrobat 8%".  If you change that I bet it will cut back on those.

What I found after my post was that the entries are not necessarily related to multiple add remove entries but over time the DB is holding on to both an initial install and any update. As in one example, the report indicates that X machine has Adobe Acrobat Standard 7.0.0 and also an instance for Adobe Acrobat Standard 7.1.1. The later is the latest from Adobe for this version but SQL reports both versions for the same machine. I guess in this instance I need to correct the SQL statement to account for the version I'm looking for. Assistance needed for targeting specific versions would be appreciated.

Try adding the version to your Where clause. For example:

WHERE (Inv_AeX_OS_Add_Remove_Programs.Name LIKE '%Adobe Acrobat%')

AND (Inv_AeX_OS_Add_Remove_Programs.Version LIKE '7.1%')

Handy SQL Queries

Besides the examples, Kyle's idea to use a parameter and a dropdown selector may be your best bet, if you want to reuse the query easily (for other apps).

So I entered AND (Inv_AeX_OS_Add_Remove_Programs.Version LIKE '7.1%') in my SQL.  

Works as I would expect but the screen shot below listed multiple machines with the same version installed but the product name entry is differnt, hence the duplicate entry.  Does this mean that I need to clean up an entry within the audit.ini file that is collecting the inventory data?

I think the report might be picking up updates or upgrades that fit your criteria, so it is reporting multiple installations. If you looked at Add/Remove Programs on one of those machines it might give you a better idea of what it is looking at.

Another way around the problem is to report on the Inv_AeX_SW_Audit_Software table instead of Inv_AeX_OS_Add_Remove_Programs and look for the specific executable file name and version: Acrobat.exe, version number.  Modifying overrides in  the auditpls.ini file can certainly help with inventory on executable files.

Hope this helps....

'Adobe Acrobat 7.1.0%'. There is nothing wrong with the data being collected, you have simply chosen a field (version) where everthing says 7.1.