How to add machine name to this? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

How to add machine name to this?

Author

Topic

mqh7
Yak Posting Veteran

58 Posts

Posted - 2012-10-24 : 17:44:17

This code works. When run it will return a list of Serial Numbers and the Count of machines that share that serial number.

SELECT v_GS_PC_BIOS.SerialNumber0, Count(v_R_System.Netbios_Name0) AS SystemCount
FROM dbo.v_R_System RIGHT OUTER JOIN v_GS_PC_BIOS
ON dbo.v_R_System.ResourceID = v_GS_PC_BIOS .ResourceID
GROUP BY v_GS_PC_BIOS.SerialNumber0
having(Count(v_GS_PC_BIOS.SerialNumber0)>1)
ORDER BY SystemCount DESC

This is used in Microsoft's SCCM 2007 tool. When you re-image a PC the old data is kept in the SCCM DB so you can have 3 or 4 or more computer names associated with the same serial number (or Mac Address for that matter)

Here is some of the output from this code.

SerialNumber0 SystemCount
L3F6502 5
2UA0180YFD 3
JK9RSM1 3
2UA1241V0L 3
L32X4A4 3
2UA101025R 3

What I'd like to also see is the NetBIOS name for each serial number. So for the first serial number I'd like to see all 5 computer names that share that serial number.

v_R_System.Netbios_Name0 is the computer name but how do I get the data I want?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-10-24 : 18:17:16

select netbios_name from v_R_System where serialnumber0 = 'L3F6502'

But maybe I'm not understanding the question.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

mqh7
Yak Posting Veteran

58 Posts

Posted - 2012-10-24 : 18:24:55

What I want to do is run the code I have that's working but have it return a list of NetBIOS names. I have about 250 serial numbers that have more than one NetBIOS name associated with them. So I want something like this.

SerNumber SystemCount NetBIOS
L3F6502 5 ABC-PC-0001
L3F6502 5 ABC-PC-0002
L3F6502 5 ABC-PC-0003
L3F6502 5 ABC-PC-0004
L3F6502 5 ABC-PC-0005

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-25 : 02:32:54

Here am assuming that NetBIOS column is available in v_GS_PC_BIOS table....

SELECT t.SerNumber, t.SystemCount, v.NetBIOS
FROM v_GS_PC_BIOS v
JOIN
(SELECT v_GS_PC_BIOS.SerialNumber0 AS SerialNumber0, Count(v_R_System.Netbios_Name0) AS SystemCount
FROM dbo.v_R_System RIGHT OUTER JOIN v_GS_PC_BIOS
ON dbo.v_R_System.ResourceID = v_GS_PC_BIOS .ResourceID
GROUP BY v_GS_PC_BIOS.SerialNumber0
having(Count(v_GS_PC_BIOS.SerialNumber0)>1)) t ON v.SerialNumber0 = t.SerialNumber0
ORDER BY SystemCount DESC

--
Chandu

mqh7
Yak Posting Veteran

58 Posts

Posted - 2012-10-25 : 10:02:08

I had to modify this code.

SELECT t.SerialNumber0, v.Netbios_Name0
FROM v_R_System v
JOIN
(SELECT v_GS_PC_BIOS.SerialNumber0 AS SerialNumber0, Count(v_R_System.Netbios_Name0) AS SystemCount
FROM dbo.v_R_System RIGHT OUTER JOIN v_GS_PC_BIOS ON dbo.v_R_System.ResourceID = v_R_System.ResourceID
GROUP BY v_GS_PC_BIOS.SerialNumber0
having(Count(v_GS_PC_BIOS.SerialNumber0)>1))
t ON t.SerialNumber0 = t.SerialNumber0

ORDER BY SystemCount DESC

-- v_R_System has the NetBIOS_Name0
-- v_GS_BIOS has the SerialNumber0
-- SystemCount is not data in any column. that was defined AS. Example: Count(v_R_System.Netbios_Name0) AS 'SystemCount'

When I run the code Bandi provided it returns hundreds of thousands rows of data. It also associated a serial number with hundreds of NetBIOS names.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources