How to find which computers do not contain Forefront endpoint protetion (msseces.exe file)

[mercy92]

Good afternoon,

 

I have problem with creating something like "subselected query".

I need to find which computers do not have installed Forefront Endpoint Protection.

The best solution seems to be find on computer msseces.exe, because Add or remove programs was not successful for me.

 

I created query, which show me, which computers contain msseces.exe:

select SMS_R_System.NetbiosName, SMS_R_System.LastLogonUserName, SMS_G_System_SYSTEM_CONSOLE_USER.LastConsoleUse, SMS_G_System_OPERATING_SYSTEM.Version, SMS_R_System.ResourceId from SMS_R_System inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SYSTEM_CONSOLE_USER on SMS_G_System_SYSTEM_CONSOLE_USER.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_SoftwareFile.FileName = "msseces.exe"

 

Than I created subselected query, which should show me computers without "msseces.exe":

select SMS_R_System.LastLogonUserName, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.NetbiosName from SMS_R_System where SMS_R_System.ResourceId not in (select SMS_R_System.NetbiosName, SMS_R_System.LastLogonUserName, SMS_G_System_SYSTEM_CONSOLE_USER.LastConsoleUse, SMS_G_System_OPERATING_SYSTEM.Version, SMS_R_System.ResourceId from SMS_R_System inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SYSTEM_CONSOLE_USER on SMS_G_System_SYSTEM_CONSOLE_USER.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_SoftwareFile.FileName = "msseces.exe")

 

But it do not works for me..

 

Could anyone help me please?

 

Thank you very much,

Lucas

[GarthMJ]

Your subselect has too many columns, you can only have 1 column with a subselect query.

[mercy92]

Thank you GarthMJ for your reply.

Please, what you think I should edit?

Columns in first query or some columns in subselect query?

[mercy92]

Now I correct it:

1st query:

select distinct SMS_R_System.ResourceId, SMS_R_System.NetbiosName, SMS_R_System.OperatingSystemNameandVersion from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Forefront Endpoint Protection 2010" or SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName = "Microsoft Forefront Endpoint Protection 2010" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Forefront Endpoint Protection" or SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName = "Microsoft Forefront Endpoint Protection"

 

Subselected query:

select distinct SMS_R_System.ResourceId, SMS_R_System.NetbiosName, SMS_R_System.OperatingSystemNameandVersion from SMS_R_System where SMS_R_System.ResourceId not in (select distinct SMS_R_System.ResourceId from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Forefront Endpoint Protection 2010" or SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName = "Microsoft Forefront Endpoint Protection 2010" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Forefront Endpoint Protection" or SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName = "Microsoft Forefront Endpoint Protection")

 

Now I am able to see 2 results (from 1st query and from subselected), but in second (subselected) query I see computers in first query or computers, where I am 100% sure, that this computers has FEP.

 

Do anyone know why please?

 

Regards,

Lucas

[GarthMJ]

So your query will only show you x64 PCs as written. Try this. (watch out for typo and I write this freehand.)

select distinct 
	SMS_R_System.ResourceId, 
	SMS_R_System.NetbiosName, 
	SMS_R_System.OperatingSystemNameandVersion 
from  
	SMS_R_System 
where 
	SMS_R_System.ResourceId not in 
		(
			select 
				SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID
			from  
				SMS_G_System_ADD_REMOVE_PROGRAMS
			where 
				SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Forefront Endpoint Protection 2010" 
				or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Forefront Endpoint Protection"
		)
	and SMS_R_System.ResourceId not in 
		(
			select 
				SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID
			from  
				SMS_G_System_ADD_REMOVE_PROGRAMS_64 
			where 
				SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName = "Microsoft Forefront Endpoint Protection 2010" 
				or SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName = "Microsoft Forefront Endpoint Protection"
		)

[mercy92]

Thank you for your query .

 

I tested it, but it seems to be some mistake on other place.

SCCM show me computers with FEP in this query. I do not know why. I think that I check every options of installed FEP (x64 and x86 program, ...).

 

Is there option, that we install computers with Acronis True Image, where we have images with FEP. When recover is done I add computer to our domain and install SCCM client.

I am not sure, if there is some trouble on our SCCM server with recognizing installed file.

 

Other option, how to check can be check the file of FEP (for example msseces.exe insted of searching Forefront Endpoint Protection in Add or Remover programs).

 

L.