I need to get a list of PC name, IP, Model, and Serial Number based on the IP address or hostname.

On results pane of a query, when I only choose Name and IP, it'll give me all alive IP addresses with host name. But if I choose Model and/or Serial Number, the results list will shrink! and will only show the ones with Model and serial Number and will filter the ones without these info!

1. Is there any way to force it to list Model and Serial for all? (I guess not easy)

2. more importantly: How to list all IP addresses and Names, and for the ones that SCCM can't get the Model and Serial Number, just leave the field blank, not filter it the entire row?

select SMS_R_System.Name, SMS_R_System.IPAddresses, SMS_G_System_COMPUTER_SYSTEM.Model, SMS_G_System_PC_BIOS.SerialNumber from  SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_PC_BIOS on SMS_G_System_PC_BIOS.ResourceID = SMS_R_System.ResourceId where SMS_R_System.IPAddresses like "10.106.%" or SMS_R_System.NetbiosName like "ITD%"

• Edited by Ray Darvi Monday, April 27, 2015 9:50 PM

I tried all 4 type of joins, same result (around 1000 rows) As soon I remove Model and Serial, I have 2000 rows!

You will have to post a screenshot of some of the missing rows.

Your query should look something like this. (I wrote this freehand so watch for typos)

select 
	SMS_R_System.Name, 
	SMS_R_System.IPAddresses, 
	SMS_G_System_COMPUTER_SYSTEM.Model, 
	SMS_G_System_PC_BIOS.SerialNumber 
from  
	SMS_R_System 
	left outer join SMS_G_System_COMPUTER_SYSTEM on SMS_R_System.ResourceId  = SMS_G_System_COMPUTER_SYSTEM.ResourceID 
	left outer join SMS_G_System_PC_BIOS on SMS_R_System.ResourceId = SMS_G_System_PC_BIOS.ResourceID
where 
	SMS_R_System.IPAddresses like "10.106.%" 
	or SMS_R_System.NetbiosName like "ITD%"

OK with this query:

select 
	SMS_R_System.Name, 
	SMS_R_System.IPAddresses 
from  
	SMS_R_System 
where 
	SMS_R_System.IPAddresses like "10.106.69.%"

This is what I get.

But when I add another property to results, it'll filter and only show the rows with content in additional column. a query like this:

select 
	SMS_R_System.Name, 
	SMS_R_System.IPAddresses, 
	SMS_G_System_PC_BIOS.SerialNumber 
from  
	SMS_R_System 
	inner join SMS_G_System_PC_BIOS on SMS_G_System_PC_BIOS.ResourceId = SMS_R_System.ResourceId 
where 
	SMS_R_System.IPAddresses like "10.106.69.%"

will give me this:

What happened to the other ones? I think SCCM can't get the WMI value for them.

First question was, is there anyway to get the proper value on all of them clients? some thing like run winrm quickconfig on a give list of computers remotely.

if not, OK (not exactly), but at least show them on the results list, even if there's nothing to show for that field.

I want SCCM to leave the cell blank for the ones that it can't get the value, not to filter the entire row; and already tried all kind of joins.

• Edited by Ray Darvi Tuesday, April 28, 2015 7:19 PM

Yes, I know this is an old post, but Im trying to clean them up. Did you solve this problem, if so what was the solution?

The other ones are filter out because they dont have a BIOS Serial number. They is why you need to use outer joins, not inner joins. What this is also telling me is that most of your clients dont have the CM!2 agent installed on them.