I am trying to build a query in SCCM 2012 R2 to look for devices that is still running Windows XP Professional.  Below is the query statement that I used to ask SCCM to prompt for Operating System entry.  The query ran without prompting and return zero row of data.  Any idea why it does not prompt me to enter Operating System?

Thanks,

Brian.

Select SYS.Name0,
OS.Caption0
from v_R_System sys
JOIN V_GS_Operating_system os on SYS.ResourceID = OS.ResourceID
Where OS.Caption0 like '%Windows XP%'

This query won't prompt for the OS but will return all Windows XP systems.

You can change '%Windows XP%' for another OS.

Where OS.Caption0 like '%Windows XP%'

Yes, we can hard coded the OS version.  Why can't SMS_R_System.operatingSystem be prompted?  How can we tell which fields do not accept prompt?  

Thanks,

Brian

I ran it as a query. The built in report does not provide the additional information that I want.  I need the LastLogonUserName information along with the device ID so that we could contact the end user to schedule for OS upgrade.

So Now that I'm back in the office. I have looked at this and you problem is that you have a few typos in your query.

There are no column called:

• SMS_R_System.operatingSystem
• SMS_R_System.operatingSystemVersion 

Therefore the queried failed.

This query will work, but remember to add the "%" to the prompted test for the like to work correctly.

select 
	SMS_R_System.NetbiosName, 
	SMS_R_System.LastLogonUserName, 
	SMS_R_System.OperatingSystemNameandVersion 
from  
	SMS_R_System 
where 
	SMS_R_System.OperatingSystemNameandVersion like ##PRM:SMS_R_System.OperatingSystemNameandVersion##

Yes, we can hard coded the OS version.  Why can't SMS_R_System.operatingSystem be prompted?  How can we tell which fields do not accept prompt?  

Thanks,

Brian

Did you run this query as query or a collection query?

Btw there is a built in report for this data.

I ran it as a query. The built in report does not provide the additional information that I want.  I need the LastLogonUserName information along with the device ID so that we could contact the end user to schedule for OS upgrade.

Thanks,

Brian.

I cut and paste your query then entered %Windows XP% when prompted, it returned "No items found.&qu

It works!

Thanks,

Brian.