Here's a cool tip from Frank Rojas, ConfigMgr 2007 Support Engineer out in North Carolina.  If you're seeing duplicate records in your reports and want to get rid of them then check this out:

========

Problem:  When running reports in SMS 2003 or ConfigMgr 2007, duplicate records may show up in some of your reports. For example, when running the report "Computers with a specific product", a PC may show up more than once in the report as having the product.

Resolution: Technically this behavior is by design, however if you want to filter out the duplicates from the report follow these steps:

1. Create a duplicate of the Report by right clicking on it and choosing Clone
2. Give the cloned report a new name to distinguish it from the original report
3. Right click on the newly created report and choose Properties
4. Click on the Edit SQL Statement... button
5. Under SQL statement, add DISTINCT after the SELECT statement
6. Click OK, then OK again.

In our previous example, the "Computers with a specific product" Report would change from:

Select SYS.Netbios_Name0, SYS.User_Name0, SP.ProductName, SP.CompanyName,
SP.ProductVersion
FROM v_GS_SoftwareProduct SP
JOIN v_R_System SYS on SP.ResourceID = SYS.ResourceID
WHERE SP.ProductName = @variable
Order by SP.ProductName, SP.ProductVersion

to

Select Distinct SYS.Netbios_Name0, SYS.User_Name0, SP.ProductName, SP.CompanyName,
SP.ProductVersion
FROM v_GS_SoftwareProduct SP
JOIN v_R_System SYS on SP.ResourceID = SYS.ResourceID
WHERE SP.ProductName = @variable
Order by SP.ProductName, SP.ProductVersion

==========

Thanks Frank!

J.C. Hornbeck | Manageability Knowledge Engineer