Find all Pre-SP2 XP systems in organization

[colt1911carry]

I need to find a way to find all of my pre-sp2 XP machines so I can then do an upgrade to these. I am pretty new to SMS (we installed it mainly as a remote tools item, but now are looking to maximize it functionality). Any help is greatly appreciated.

 

[tbrennans]

(SP2 = SMS or XP)?

 

[colt1911carry]

SP2 for XP

 

[tbrennans]

This would be a query based collection

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 >= "% Workstation
5.1.2600" and SMS_G_System_OPERATING_SYSTEM.CSDVersion < "Service Pack 2"

 

[colt1911carry]

How do I do a query based collection?

 

[tbrennans]

Probably could change it to
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 >= "% Workstation
5.1%" and SMS_G_System_OPERATING_SYSTEM.CSDVersion < "Service Pack 2"

to be a wee bit more accurate, but both worked for me

(I changed the workstation from "%workstation 5.1.2600" to %workstation 5.1%") probably not a huge deal

 

[colt1911carry]

I got a syntax error on trying to run this.

 

[colt1911carry]

It seems to have run the query anyway (with the syntax error) but I get a list that includes several of my Windows 2003 Servers. Is there a way to filter those out?

 

[colt1911carry]

I figures that part out.

select
SMS_G_System_COMPUTER_SYSTEM.Name, SMS_G_System_OPERATING_SYSTEM.CSDVersion, SMS_G_System_OPERATING_SYSTEM.Caption 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_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.CSDVersion = "Service Pack 1" and SMS_G_System_OPERATING_SYSTEM.Caption = "Microsoft Windows XP Professional"

Thanks for the help guys. Is there somewhere to get more information on doing these types of things with SMS?

 

[MexiCant]

Anyone willing to post that same query in SQL statment code that I can use in a SMS 2k3 report?

 

[GSC]

Colt,
With your query you'll only get the Win XP SP1 systems and not Win XP without SP. For that you'll need to use something like this:

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 = "Microsoft Windows NT Workstation 5.1" and SMS_G_System_OPERATING_SYSTEM.CSDVersion <= "Service Pack 1"

MexiCant;
Here is a similar query that will show all Win 2003 systems without SP1:

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 "%Server 5.2%" and SMS_G_System_OPERATING_SYSTEM.CSDVersion is NULL



I hope you find this post helpful.

Regards,
GSC