Report Services - Duplicate Systems

Hello I am using the following query, listed below;    it seems to be duplicating some of my systems, not all but some...

The only thing I notice is, the duplicate systems have different IP addresses.

Thank you for your help...

SELECT DISTINCT
 v_GS_COMPUTER_SYSTEM.Name0,'Chassis'=CASE 
 WHEN v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 in ('3','4','6','7','15') THEN 'Desktop'
 WHEN v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 in ('8','9','10','21') THEN 'Laptop'
 END,
      v_GS_PC_BIOS.SerialNumber0, v_GS_COMPUTER_SYSTEM.Manufacturer0, v_GS_COMPUTER_SYSTEM.Model0,
                      v_GS_OPERATING_SYSTEM.Caption0, v_GS_OPERATING_SYSTEM.CSDVersion0, v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0,
                      v_R_System.User_Name0 AS [Last Logged], v_GS_PC_BIOS.ReleaseDate0, v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0,
                      v_GS_NETWORK_ADAPTER_CONFIGUR.DefaultIPGateway0
FROM         v_GS_PC_BIOS INNER JOIN
                      v_GS_COMPUTER_SYSTEM ON v_GS_PC_BIOS.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID INNER JOIN
                      v_GS_NETWORK_ADAPTER_CONFIGUR ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_NETWORK_ADAPTER_CONFIGUR.ResourceID INNER JOIN
                      v_GS_OPERATING_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID INNER JOIN
                      v_R_System ON v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceID LEFT OUTER JOIN
                      v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID INNER JOIN
                      v_GS_SYSTEM_ENCLOSURE on v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_SYSTEM_ENCLOSURE.ResourceID
WHERE     (v_GS_NETWORK_ADAPTER_CONFIGUR.IPEnabled0 = 1) AND (NOT (v_GS_NETWORK_ADAPTER_CONFIGUR.DefaultIPGateway0 IS NULL)) AND
                      (v_R_System.Operating_System_Name_and0 LIKE '%Workstation%')
ORDER BY v_GS_COMPUTER_SYSTEM.Name0

January 9th, 2014 5:21pm

Duplicates will show up because of systems having multiple IPs. You can solve it like so: http://myitforum.com/cs2/blogs/jnelson/archive/2007/11/21/108484.aspx
Free Windows Admin Tool Kit Click here and download it now
January 9th, 2014 5:42pm

Hi,

In addition, the blog below could be helpful.

SCCM Guru Webcast Q&A #2-Multiple Rows in Reports

(Note: Microsoft provides third-party contact information to help you find technical support. This contact information may change without notice. Microsoft does not guarantee the accuracy of this third-party contact information.)

Best Regards,

Joyce Li

January 12th, 2014 9:05pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics