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.

Collection with client Computers without ConfigMgr client

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.Client = 0 and SMS_G_System_OPERATING_SYSTEM.Caption not like “%Server%” or SMS_R_System.Client is null  and SMS_G_System_OPERATING_SYSTEM.Caption not like “%Server%”

Deploy Office 2007 SP2 with ConfigMgr 2007

As Microsoft recently released Service Pack 2 for Office 2007 suite, it is a good idea to add the update files to the package so it can be installed during the installation.

So, first of all we have to download the SP2 executable file from here: http://www.microsoft.com/downloads/details.aspx?FamilyID=b444bf18-79ea-46c6-8a81-9db49b4ab6e5&displaylang=en (see Visio and Project information at the end of this post).

Then, using a command prompt window, extract the content of the package to a folder you can browse to.

Accept EULA and click Continue.

Select a folder to extract the files to.

When the extraction is complete, you should have the following files:

At this point, you can delete the office2007sp2-kb953195-fullfile-en-us.exe file. Copy the other 9 files to “Updates” folder from your Office 2007 source folder.

Now you only have to update the distribution point/s and the next time Office 2007 will install, it will apply the SP2 update during Office 2007 installation.

Additional info:

The same steps are valid for Visio and Project 2007.

Microsoft Office Visio 2007 Service Pack 2 (SP2) can be downloaded from here: http://www.microsoft.com/downloads/details.aspx?familyid=78E36742-8BDA-471E-88E6-9B561BB06258&displaylang=en.
Microsoft Office Project 2007 Service Pack 2 (SP2) can be downloaded from here: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=c126fa4a-b43f-4f7e-a9d4-522e92a6cfee.