Yes. If the row exists but the value is null, then the join condition will be satisfied. But if the row doesn't exist at all, the join condition can't be satisfied; this is most likely what's going on.
J From: [email protected] [mailto:[email protected]] On Behalf Of Todd Hemsell Sent: Thursday, May 30, 2013 8:13 PM To: [email protected] Subject: Re: [mssms] RE: systems that can't run updates - inner join should still give results if null right? maybe it is empty and not null. Generally speaking it is easier to make a collection of all your managed systems. then a collection of all those with the correct version, then a collection of all systems not in the good collection and limit it to the all managed systems. CM12 makes that very easy to do. On Thu, May 30, 2013 at 12:05 PM, Jason Sandys <[email protected]<mailto:[email protected]>> wrote: Yes, this should work. To explain a bit, it's not that the Version value is null in the SMS_G_System_WINDOWSUPDATEAGENTVERSION class/table, it's that a row does not exist at all for that resource. An inner join will only return rows where the join condition is satisfied, ResourceID = ResourceID in this case but because no row exists in the second class/table, the join criteria is not satisfied and no row is returned at all. An outer join returns every row in the left class/table (for left outer joins and the right class/table for right outer joins) even if the join condition is not satisfied at all. Only doubt I have is whether you can have an outer join in WQL. You may need to translate this to SQL and run it from within SQL Management Studio or another tool to directly query SQL. J -----Original Message----- From: [email protected]<mailto:[email protected]> [mailto:[email protected]<mailto:[email protected]>] On Behalf Of William Jackson Sent: Thursday, May 30, 2013 10:42 AM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: systems that can't run updates - Change "inner join" to "left join". William From: [email protected]<mailto:[email protected]> on behalf of Burke, John Sent: Thursday, May 30, 2013 10:28 To: [email protected]<mailto:[email protected]> Subject: [mssms] systems that can't run updates - hi, a simple query. When I open up resource explorer some of the systems that can't get their updates via sccm have no "windows update agent version" in resource explorer. I wanted to get a quick count of how many systems are in that state. "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_WINDOWSUPDATEAGENTVERSION on SMS_G_System_WINDOWSUPDATEAGENTVERSION.ResourceID = SMS_R_System.ResourceId where SMS_G_System_WINDOWSUPDATEAGENTVERSION.Version is null " not sure why that is returning a count of 0. Something very strange.
