Having problems creating a sub-select query.
I'm trying to create a query that shows all systems without .NET 3.5 SP1 and it shows an error by the query that reads "[*The ConfigMgr Provider reported an error.*]" Here's the code: select distinct SMS_G_System_SYSTEM.Name, SMS_R_System.LastLogonUserName from SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM.Name not in (select distinct SMS_G_System_SYSTEM.Name, SMS_R_System.LastLogonUserName from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%microsoft .NET framework 3.5 sp1%") Here's one for Adobe 9 that does work: select distinct SMS_G_System_SYSTEM.Name from SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM.Name not in (select SMS_G_System_SYSTEM.Name from SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceId = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%Adobe Reader 9%")
December 28th, 2009 6:36pm

Take this out: , SMS_R_System.LastLogonUserName subselects just don't like trying to "select" multiple things. Freaks 'em out.select distinct SMS_G_System_SYSTEM.Name from SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM.Name not in (select distinct SMS_G_System_SYSTEM.Name from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%microsoft .NET framework 3.5 sp1%")fyi... slightly unrelated, but I'm going to say it anyway. There's a general aversion to most of us query-builders to use .name or netbios_name0 when building a query for a collection. For the visual query, sure. But resourceID is just plain better. If it were me, I'd use something like this:select distinct SMS_G_System_SYSTEM.Name from SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM.resourceid not in (select SMS_G_System_SYSTEM.Resourceid from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%microsoft .NET framework 3.5 sp1%")Standardize. Simplify. Automate.
Free Windows Admin Tool Kit Click here and download it now
December 28th, 2009 6:51pm

No, you didn't take out LastLogonUserName. It's still there. select distinct SMS_G_System_SYSTEM.Name from SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM.Name not in (select distinct SMS_G_System_SYSTEM.Name, SMS_R_System.LastLogonUserName from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%microsoft .NET framework 3.5 sp1%")Standardize. Simplify. Automate.
December 28th, 2009 7:54pm

Sherry: I am confused. Here's my script now: select distinct SMS_G_System_SYSTEM.Name, SMS_R_System.LastLogonUserName from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%microsoft .NET framework 3.5 sp1%" and SMS_R_System.OperatingSystemNameandVersion not like "%Microsoft Windows NT Advanced Server 5.2%" and SMS_R_System.OperatingSystemNameandVersion not like "Microsoft Windows NT Server 5.2" Notice that I have the LLUN in Bold, but I removed it from that other spot. Why did that work at that time? Here's what I'm using now to include two AD OU's: select distinct SMS_G_System_SYSTEM.Name from SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM.Name not in (select distinct SMS_G_System_SYSTEM.Name from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%microsoft .NET framework 3.5 sp1%") and (SMS_R_System.SystemOUName = "int.chickasaw.net/CNHQ/Youth & Family Division/Child Support Enforcement" or SMS_R_System.SystemOUName = "int.chickasaw.net/CNHQ/Education Division") Does this look right?
Free Windows Admin Tool Kit Click here and download it now
December 28th, 2009 8:53pm

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

Other recent topics Other recent topics