Query of Systems and MAC Addresses shows many duplicates

[dverbern]

Hello,

 

Out of curiousity, I have created a Query in SCCM 2012 SP1 Admin Console that lists all systems and their MAC addresses.

The query is not collection limited.

 

The query delivers many results, but I'm concerned that there are many instances of duplication, where a given machine with a certain MAC address is listed multiple times.

 

Here is the query syntax I'm using:

 

select
SMS_G_System_NETWORK_ADAPTER.MACAddress,

SMS_R_System.Name

from

SMS_R_System

inner join

SMS_G_System_NETWORK_ADAPTER

on SMS_G_System_NETWORK_ADAPTER.ResourceId = SMS_R_System.ResourceId

 

I'm concerned that our process of building and rebuilding machines is causing duplication, triplication, etc of system records.

 

Currently, we advertise our bare metal Task Sequence to unknown computers or computers in a specific collection. Once those machines are built, we move the computer object out of that build collection.

When we rebuild a machine, we sometimes delete the computer object completely and import it afresh, entering computer name and MAC address and choosing a destination build collection.

 

I don't know enough about how SCCM and SQL work together to handle records to know whether SCCM is capable of managing old records or whether our process needs attention.

 

I've attached an example duplication of a laptop machine, found in my query.

 

Any help much appreciated.

 

Daniel

Melbourne, Victoria, Australia

[DancingFerret]

MAC Addresses are unique so rebuilding a machie makes no difference to the MAC address as its a physical hardware address that doesn't change.

The fact you have multiple MAC address for a laptop shown in your properties page means there are mulltiple network devices, eg Virtual NIC, WIFI, 3G card etc anything really that needs an IP Address so to speak.

I'm not on a sql box now but try using a distinct statement in your query, and also start leaning to use abreviations in your queuries as well, will save you a lot of time, ABreviation is whatever you want it to be, I've used SYS and NDAP as an example below.

If the query is shown stale records, you could add in where "client =1" (no sql access at the mo so can't write and test it)

 

OLD:

 

select
SMS_G_System_NETWORK_ADAPTER.MACAddress,

SMS_R_System.Name

from

SMS_R_System

inner join

SMS_G_System_NETWORK_ADAPTER

on SMS_G_System_NETWORK_ADAPTER.ResourceId = SMS_R_System.ResourceId

 

NEW

 

select distinct
NDAP.MACAddress,

SYS.Name

from

SMS_R_System SYS ( example ,not part of the statement ignore text , here you define the abreviation for R_System as SYS)

inner join

SMS_G_System_NETWORK_ADAPTER as NDAP (example, not part of the statement ignore text , here you define the abreviation for xxx_Adapter as NDAP)

on NDAP.ResourceId = SYS.ResourceId

[GarthMJ]

I would check for duplicate PCs within CM, You likely have two named 7YVW-908KLV1, One is like to be marked as inactive/obsolete.

[dverbern]

Thank you for your suggestions - yes, I will have to start using abbreviations in my queries.