SCCM collection query for NIC’s with an error

So here’s the scenario, you’ve been told that there are machines showing a network adapter with an exclamation and error in windows device manager. The task is to get a list of the machines using ConfigMgr 2012 which are affected by this and then you can work on narrowing down the problem. It may be a particular machine model, a bad driver you have, who knows. Until you can identify the machines with the issue you’re going to struggle to find the root cause.

So, this is where the power of ConfigMgr comes into play.

Firstly you need to extend the hardware inventory of your ConfigMgr clients to include an additional WMI property. To do this from your ConfigMgr 2012 console go to:

Administration>Client Settings, hit properties from the ribbon and select Hardware Inventory.
Now expand Network Adapter and select the ‘Config Manager Error Code’. Click OK.

NIC_ConfigMgrErrorCode

If you want some further info on the Win32_NetworkAdapter WMI class then go here.

This will now ensure that your ConfigMgr clients pull back the error information for their network cards. There is no need to deploy anything further, the info will start collecting in your SQL database. It is NOT supported or recommended to meddle with your ConfigMgr database but if you do wish to check (entirely at your own risk) then you might want to run something like this:

SELECT [ResourceID]
,[GroupID]
,[RevisionID]
,[AgentID]
,[TimeStamp]
,[AdapterType0]
,[AutoSense0]
,[Availability0]
,[Caption0]
,[ConfigManagerErrorCode0]
,[ConfigManagerUserConfig0]
,[Description0]
,[DeviceID0]
,[ErrorCleared0]
,[ErrorDescription0]
,[Index0]
,[InstallDate0]
,[Installed0]
,[LastErrorCode0]
,[MACAddress0]
,[Manufacturer0]
,[MaxNumberControlled0]
,[MaxSpeed0]
,[Name0]
,[NetworkAddresses0]
,[PermanentAddress0]
,[PNPDeviceID0]
,[PowerManagementCapabilities0]
,[PowerManagementSupported0]
,[ProductName0]
,[ServiceName0]
,[Speed0]
,[Status0]
,[StatusInfo0]
,[SystemName0]
,[TimeOfLastReset0]
FROM [CM_TST].[dbo].[v_GS_NETWORK_ADAPTER]
WHERE ConfigManagerErrorCode0 != ” AND Description0 = ‘Microsoft Teredo Tunneling Adapter’

That query will essentially show you any record that has an error code value of anything other than 0 (zero) for a ‘Microsoft Teredo Tunneling Adapter’. If you comment out the WHERE line (with — before it) you can browse the available network card descriptions for your estate. Don’t forget to change the CM_TST value to represent your site name.

Now you can create your ConfigMgr collection query in a very similar way. To do this I’ll assume you’re proficient in creating collections:

Go to properties of your collection, select the Membership Rules tab and select Add rule, query rule.
Give your query a name and select Edit Query Statement.
Select the Show Query Language button and paste in the following:

select SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.Name, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Client from  SMS_R_System inner join SMS_G_System_NETWORK_ADAPTER on SMS_G_System_NETWORK_ADAPTER.ResourceID = SMS_R_System.ResourceId where SMS_G_System_NETWORK_ADAPTER.ConfigManagerErrorCode != 0 and SMS_G_System_NETWORK_ADAPTER.Description = “Microsoft Teredo Tunneling Adapter”

Click OK until you’re back to the ConfigMgr console.

You can change the SMS_G_System_NETWORK_ADAPTER.ConfigManagerErrorCode != 0 section to represent a specific error value if you prefer, the error values are in here if you need them. Don’t forget to change your != to = though.
You can also change the SMS_G_System_NETWORK_ADAPTER.Description = “Microsoft Teredo Tunneling Adapter” to a value that is more appropriate for you.

Now all that is left for you to do is check your collection for the results. You may also want to add in further columns to aid your view, but be aware at the moment (as far as I’m aware) you cannot add in a column for the make and model.
If you decided to run the SQL query above your results should mirror between ConfigMgr and SQL, you may need to run an update membership on your collection though.

Let me know if you have any further suggestions for this, I’d be glad to get your comments.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s