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%"







April 27th, 2015 5:45pm

Change all your Inner joins to Left outer joins. that should solve your problem.
Free Windows Admin Tool Kit Click here and download it now
April 27th, 2015 7:07pm

I tried all 4 type of joins, same result (around 1000 rows) As soon I remove Model and Serial, I have 2000 rows!
April 27th, 2015 8:06pm

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 otuer 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%"

Free Windows Admin Tool Kit Click here and download it now
April 27th, 2015 8:10pm

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

OK this is what I was trying to get:

PC1 10.225.88.116
PC2 10.106.66.91 Hewlett-Packard HP Compaq 6200 Pro SFF PC MXL2020KSV
PC3 10.225.82.72
PC4 10.106.49.128 Hewlett-Packard HP Compaq 6200 Pro SFF PC MXL2020KT3

wherever it can't get the info, just leave the cell blank (do not duplicate the row), but it's filtering the rows without model and serial. I merged both results using Excel and got something like above.

Then I noticed that I need to change the SMS_R_System.IPAddresses to SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.IPAddress.

Now I have duplicated rows for each PC with all joins as Inner. One row with IP address another one without IP.

pc1               10.0.0.1         Lenovo 

pc1                                    Lenovo

April 27th, 2015 9:16pm

I just looked at the query and 'outer' is spelled wrong..
Free Windows Admin Tool Kit Click here and download it now
April 27th, 2015 11:11pm

Without your query that you are referencing it is hard to answer your question.

What exactly do you have I your where clause now?

April 27th, 2015 11:14pm

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

Other recent topics Other recent topics