Query Results: Don't filter a row cause it doesn't have anything in a colmun

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
April 27th, 2015 9:43pm

Looks like SCCM didn't like the query above. Getting error of either syntax or language feature is not supported.

Hello,

I do believe that Garth's query will give you the correct information. However, in addition to correct the "outer", you may also need to remove all of the line breaks as WMI does not like them.

Free Windows Admin Tool Kit Click here and download it now
April 28th, 2015 6:44am

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 12 hours 26 minutes ago
April 28th, 2015 3:08pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics