SMS Query not working
I am using sms 2003, and have this query, but it is not working. All I really need are all the WinXP SP1 machines in my domain, but I am running into endless problems. Apperantly there are many SP2 machines being reported as SP1, so I am trying to filter them out with sort against the explorer.exe size, but it is still getting me no where. The following Query is returning machines that I know have SP2, and have the file size 1,032,192. Please help.:(
select SMS_R_System.Name,
SMS_R_System.SMSInstalledSites,
SMS_G_System_OPERATING_SYSTEM.Caption,
SMS_G_System_OPERATING_SYSTEM.CSDVersion,
SMS_G_System_LOGICAL_DISK.FreeSpace,
SMS_R_System.SystemOUName from SMS_R_System inner join SMS_G_System_LOGICAL_DISK on SMS_G_System_LOGICAL_DISK.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_LOGICAL_DISK.FreeSpace >= 1500 and SMS_G_System_LOGICAL_DISK.DeviceID = "C:" and SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Workstation 5.1" and SMS_G_System_OPERATING_SYSTEM.CSDVersion = "Service Pack 1" and SMS_G_System_SoftwareFile.FileName = "Explorer.exe" and SMS_G_System_SoftwareFile.FileSize != 1032192 and not SMS_R_System.SystemOUName like "%DOMAIN.EDU/HUMAN RESOURCES%"
[:@]
select SMS_R_System.Name,
SMS_R_System.SMSInstalledSites,
SMS_G_System_OPERATING_SYSTEM.Caption,
SMS_G_System_OPERATING_SYSTEM.CSDVersion,
SMS_G_System_LOGICAL_DISK.FreeSpace,
SMS_R_System.SystemOUName from SMS_R_System inner join SMS_G_System_LOGICAL_DISK on SMS_G_System_LOGICAL_DISK.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_LOGICAL_DISK.FreeSpace >= 1500 and SMS_G_System_LOGICAL_DISK.DeviceID = "C:" and SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Workstation 5.1" and SMS_G_System_OPERATING_SYSTEM.CSDVersion = "Service Pack 1" and SMS_G_System_SoftwareFile.FileName = "Explorer.exe" and SMS_G_System_SoftwareFile.FileSize != 1032192 and not SMS_R_System.SystemOUName like "%DOMAIN.EDU/HUMAN RESOURCES%"
[:@]
0 Comments
[ + ] Show comments
Answers (5)
Please log in to answer
Posted by:
Bladerun
19 years ago
Have you tried using the OS Version attribute?
I can give you more specifics on monday when I'm at work to mess around with it.
I can give you more specifics on monday when I'm at work to mess around with it.
Posted by:
WiseUser
19 years ago
Unless I'm mistaken, SMS will return all copies of "explorer.exe" - not just the copy in "%SystemRoot%\System32\". Is it possible that some machines have several copies of "explorer.exe" (maybe a backup copy)?
Are you sure the inventory has been completely updated since you're machines have received SP2?
Are you sure the inventory has been completely updated since you're machines have received SP2?
Posted by:
blankmonkey
19 years ago
Bladerun,
That was the first thing I tried, but it returns a bunch of machines that I know have SP2, but SMS tells me has SP1. Interesting part is if I sort by (first tab, "General") OS CSD version, the SP is reported differantly (not sure if correctly, but it looks that way). Same statement in the second tab ("Criteria") returns incorrect information.
WiseUser,
I am not sure, i will have to be more specific in my query of were explorer.exe is located. What I really want though is only SP1 machines, so I can push SP2 to them. I know, MS has a file that I can up load into SMS that will write the query for me, I did that. That query is the original one that is retuning SP2 machines and calling them SP1, in addition, it also returns SP2 machines (reported correctly) that I have sorted out, i think.
That was the first thing I tried, but it returns a bunch of machines that I know have SP2, but SMS tells me has SP1. Interesting part is if I sort by (first tab, "General") OS CSD version, the SP is reported differantly (not sure if correctly, but it looks that way). Same statement in the second tab ("Criteria") returns incorrect information.
WiseUser,
I am not sure, i will have to be more specific in my query of were explorer.exe is located. What I really want though is only SP1 machines, so I can push SP2 to them. I know, MS has a file that I can up load into SMS that will write the query for me, I did that. That query is the original one that is retuning SP2 machines and calling them SP1, in addition, it also returns SP2 machines (reported correctly) that I have sorted out, i think.
Posted by:
blankmonkey
19 years ago
Thought you all would like to know the solution to this. I found out that there we obsolete records in the all systems list. when we checked the box to remove obsolete records, the query cleaned right up. Just an FYI follow up.
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.