From Run or Command Prompt window: gpedit.msc /gpcomputer: Computername
Collection with Windows 7 computers without SP1
First, if you want your query to display the right information, you must have a collection with all Windows 7 computers, regardless of the service pack version installed.
Here is the simplest query to create “All Windows 7 computers” collection (please make this collection “Not collection limited”):
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_G_System_OPERATING_SYSTEM.Caption like “%Windows 7%”
And here is the query to create “Windows 7 computers without SP1”, you must limit this collection to “All Windows 7 computers” collection created earlier:
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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Name not in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name 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.Caption like “%Windows 7%” and SMS_G_System_OPERATING_SYSTEM.CSDVersion = “Service Pack 1”)
Find OS Architecture using Win32_OperatingSystem class
WMI queries to find 64 and 32 bit computers:
SELECT * FROM Win32_OperatingSystem WHERE OSArchitecture = “64-bit”
SELECT * FROM Win32_OperatingSystem WHERE OSArchitecture = “32-bit”
Computers without Java 6 Update 30
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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.Version < “6.0.300” and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like “Java(TM) 6%”
Count Adobe Reader versions
SELECT DISTINCT TOP (100) PERCENT DisplayName0, Version0, COUNT(Version0) AS Total
FROM dbo.v_GS_ADD_REMOVE_PROGRAMS
GROUP BY DisplayName0, Version0
HAVING (DisplayName0 LIKE ‘%Adobe Reader%’)
ORDER BY Total DESC, DisplayName0
Collections with computers without Forefront Endpoint Protection 2010 client
A customer asked me today to help him with creating some collections with client computers that do not have FEP 2010 installed so he can advertise the antivirus client to those computers. You can find the queries bellow.
All Windows 7 without Forefront Endpoint Protection 2010
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_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_COMPUTER_SYSTEM.Name not in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name 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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = “Microsoft Forefront Endpoint Protection 2010”) and SMS_R_System.Client = 1 and SMS_G_System_OPERATING_SYSTEM.Caption like “%Microsoft Windows 7%”
All Windows Vista without Forefront Endpoint Protection 2010
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_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_COMPUTER_SYSTEM.Name not in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name 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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = “Microsoft Forefront Endpoint Protection 2010”) and SMS_R_System.Client = 1 and SMS_G_System_OPERATING_SYSTEM.Caption like “%Microsoft® Windows Vista%”
Windows XP Professional SP2 without Forefront Endpoint Protection 2010
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_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_COMPUTER_SYSTEM.Name not in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name 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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = “Microsoft Forefront Endpoint Protection 2010”) and SMS_R_System.Client = 1 and SMS_G_System_OPERATING_SYSTEM.CSDVersion like “%Service Pack 2%” and SMS_G_System_OPERATING_SYSTEM.Caption = “Microsoft Windows XP Professional”
Windows XP Professional SP3 without Forefront Endpoint Protection 2010
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_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_COMPUTER_SYSTEM.Name not in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name 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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = “Microsoft Forefront Endpoint Protection 2010”) and SMS_R_System.Client = 1 and SMS_G_System_OPERATING_SYSTEM.CSDVersion like “%Service Pack 3%” and SMS_G_System_OPERATING_SYSTEM.Caption = “Microsoft Windows XP Professional”
Create a report to count all computer models and manufacturer for computers inside a specific collection
SELECT dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0 AS Manufacturer, dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model,
COUNT(dbo.v_GS_COMPUTER_SYSTEM.Model0) AS Total
FROM dbo.v_GS_COMPUTER_SYSTEM INNER JOIN
dbo.v_FullCollectionMembership ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_FullCollectionMembership.ResourceID
GROUP BY dbo.v_GS_COMPUTER_SYSTEM.Model0, dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0, dbo.v_FullCollectionMembership.CollectionID
HAVING (dbo.v_FullCollectionMembership.CollectionID = ‘CollectionID’)
ORDER BY Total DESC, Manufacturer, Model
I’ve been at Tech-Ed today…
Actually I just attended a local event organized by Microsoft Romania and the last session was Useful Hacker Techniques: Which Part of Hackers’ Knowledge Will Help You in Efficient IT Administration held by polish Enterprise Security MVP – Paula Januszkiewicz.
Geez… this girl presented some things that, I can bet, made half of all admins go home/work and revise their server’s security and configuration.
After seeing this presentation and demos, I will repeat what I said a couple of years ago: Romanian MVP’s still have to learn from the foreign ones. A lot!
Installing Forefront Endpoint Protection 2010 RC – prerequisite error
While installing the latest “Forefront Client Security” product named Forefront Endpoint Protection 2010 (RC version), the prerequisites verification step displayed an error at the “Verifying restart is not required” step. And the error details: “An earlier software installation still has outstanding file rename operations pending. Before Setup can continue, you must restart the computer.”
The more stranger thing happens after you restart your computer and start the Setup again. The same is displayed. Why? Well, maybe because on the FEP 2010 requirement is to have a working Configuration Manager 2007 infrastructure and almost always ConfigMgr will have open files and read/write operations on the computer you’re trying to install FEP 2010.
So, to workaround this problem you have to modify your registry (do it on your own as I did). Open PendingFileRenameOperations key from HKLM\SYSTEM\CurrentControlSet\Control\Session Manager and delete or copy its content to a notepad. Save the key and start Setup now, it should work, at least it for me.
Thanks to Evgeny-D who posted this on Microsoft’s forum. I’m glad that I did not forget Russian language 🙂
Install SQL Server on a 64 bit Windows Server–missing SQLncli_x64.msi
I had to install a copy of SQL Server 2005 on a Windows Server 2003 R2 SP2 x64. During installation I got a strange error saying that SQLncli_x64.msi file is missing. I was pretty sure that I have the same ISO file as I did while installing SQL so many times before and without any errors… Searching the net, the solution from Microsoft forum was the right one.
Before installing SQL Server, you have to download and install Microsoft SQL Server Native Client x64 file (sqlncli_x64.msi) from Microsoft Download Center.
Note: If you’re going to install Analysis Services too, then make sure to download and install Microsoft SQL Server 2005 Management Objects Collection x64 file (SQLServer2005_XMO_x64.msi) also.