Query to find SP2 clients 2

[longlivegoku]

I'd like to be able to track our SP2 deployment progress. I have created a sub-collection called SP2 but have no idea how to setup a membership rule or query to get clients that have sp2 into it. Can someone help with this?

Thanks,

Ed

 

[crobin1]

Here's one I recently created:
[tt]
select SMS_R_System.Name, SMS_R_System.SMSAssignedSites,
SMS_R_System.IPAddresses, SMS_R_System.IPSubnets,
SMS_R_System.OperatingSystemNameandVersion,
SMS_G_System_OPERATING_SYSTEM.CSDVersion,
SMS_R_System.ResourceDomainORWorkgroup,
SMS_R_System.LastLogonUserDomain,
SMS_R_System.LastLogonUserName,
SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceId,
SMS_R_System.NetbiosName from SMS_R_System inner join
SMS_G_System_OPERATING_SYSTEM on
SMS_G_System_OPERATING_SYSTEM.ResourceID =
SMS_R_System.ResourceId where
SMS_R_System.OperatingSystemNameandVersion like "%Workstation 5.1%" and
SMS_G_System_OPERATING_SYSTEM.CSDVersion = "Service Pack 2"
[/tt]

 

[longlivegoku]

I'm assuming this is a query lol. I don't do much, or haven't done much, with them. Can you explain how to use this? I don't do much with SQL or anything like that so haven't been exposed thus far.

Thanks.

 

[crobin1]

[ul]
[li]Go to the properties of the collection[/li]
[li]Click on the "Membership Rules" tab[/li]
[li]Click on the "New Query" button (yellow cylinder)[/li]
[li]Name the query[/li]
[li]Click on the "Edit Query Statement" button[/li]
[li]Click on the "Show Query Language" button[/li]
[li]Copy and paste the following code in to the "Query statement" box:
[tt]
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_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId
where SMS_R_System.OperatingSystemNameandVersion like "%Workstation 5.1%" and SMS_G_System_OPERATING_SYSTEM.CSDVersion = "Service Pack 2"
[/tt][/li]
[li]Click "OK"[/li]
[li]Click "OK"[/li]
[li]If you want the collection updated automatically be sure to use "Update this collection on a schedule"[/li]
[/ul]

My first post was from a query I created (copied from a similar query), and the code above was from a collection I created, copied from a similar collection. The SQL is a little easier to copy and paste rather than going through all of the steps to select each attribute you want and the particular criteria.

 

[longlivegoku]

Thanks very much for taking time for the SMS newbies. Much appreciated!

Ed

 

[SLMHC]

Thanks also for the post. worked like a charm!

thanks,

Dave

 

[crobin1]

I still consider myself a newbie in SMS. Looking at the built-in collections/queries helps a lot.