Returnes Duplicates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Returnes Duplicates

Author

Topic

hh1234
Starting Member

30 Posts

Posted - 2011-09-23 : 13:14:59

I have this code which works.

SELECT Sys.Netbios_Name0 as 'Computer Name',IPAddr.IP_Addresses0 as 'IP Address',Sys.User_Domain0 as 'User Domain', Sys.User_Name0 as 'UserName', Comp.Manufacturer0 as 'Make', Comp.Model0 as 'Model', Process.Name0 as 'Processor', BIOS.SerialNumber0 as 'Serial#', Memory.TotalPhysicalMemory0/1000 as 'RAM (MB)', Sys.Operating_System_Name_and0 as 'Operating System', OS.CSDVersion0 as 'Service Pack', Status.LastHWScan as 'Last H/W Scan', OS.InstallDate0 as 'Install_Date', OS.LastBootUpTime0 as 'Last Bootup', Sys.AD_SITE_NAME0 as 'Location'
FROM v_R_System Sys
LEFT JOIN v_GS_COMPUTER_SYSTEM Comp on Sys.ResourceID = Comp.ResourceID
LEFT JOIN v_GS_PC_BIOS BIOS on Sys.ResourceID = BIOS.ResourceID
LEFT JOIN v_GS_X86_PC_MEMORY Memory on Sys.ResourceID = Memory.ResourceID
LEFT JOIN v_GS_PROCESSOR Process on Sys.ResourceID = Process.ResourceID
LEFT JOIN v_GS_WORKSTATION_STATUS Status on Sys.ResourceID = Status.ResourceID
LEFT JOIN v_GS_OPERATING_SYSTEM OS on Sys.ResourceID = OS.ResourceID
LEFT JOIN dbo.v_RA_System_IPAddresses IPAddr ON SYS.ResourceID = IPAddr.ResourceID
WHERE OS.LastBootUpTime0 <> ' ' and (Sys.Name0 like '0___0-[PT]C-%' or Sys.Name0 like '63___-[PT]C-%')
ORDER BY Sys.Netbios_Name0

But when run it returns the IP Address and Mac Address for each machine so we have duplicates. How do I make so only the IPAddress is returned?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 13:39:42

I cant see Mac address in select list. Is the value contained inside IP address field itself?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hh1234
Starting Member

30 Posts

Posted - 2011-09-23 : 15:19:34

Yes, when you run the report on the SQL server you get the following:

Computer Name: IP Address
00020-LT-0004 10.10.10.10
00020-LT-0004 fe80::f4ce:3158:55be:697

And that repeats over and over.........

What Mods do I need to make to eliminate the duplicates?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 23:22:09

so you want only one out of above two? and that should the IPAddress? ie xx.xx.xx.xx format?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources