Greetings!

My motive is to fetch the list of machines not having Office 2013 installed (Location wise) and I am looking forward to create a report which will be generic.

I am creating a custom report in SCCM to get the list of computers WITHOUT a specific file and I would like to query based on the file name. In this case I am using "Winword.exe" as the file name.

I am also trying to add a collection field so that I can limit that query to a particular collection.

The required fields are NetBIOS name, File Name, File Version, File Path, AD Site or System OU path.

I am not a SQL guy and trying to get this report working for my customer. Any help would be greatly appreciated.

Why are using a file name and not simple add remove software ?

For the report go here http://ccmexec.com/2010/04/report-computers-with-a-specific-file-in-a-specific-collection/

Get the report Jorgen posted and just change the logic for device without the file.

Note: you need to need to make modification as SCCM don't inventory all file by default.

If you looking for something simpler follow this guide :http://blogs.technet.com/b/jchalfant/archive/2014/05/09/collection-of-computers-missing-an-application-software-title-in-configuration-manager.aspx

just be aware of the difference between SMS_G_System_ADD_REMOVE_PROGRAMS_64 and SMS_G_System_ADD_REMOVE_PROGRAMS

• Edited by Frederick Dicaire 12 hours 18 minutes ago

Hello Frederick,

The reason for not using the ARP display name is because Office 2013 and Lync 2013 shares the same display name. In our environment, we do have several machines with Lync 2013 but not Office 2013. We recently started rolling out Skype for Business.

So I thought using any one of the Office files to query will be accurate. If there would be another way, it would be great.

The report shared above says modifying the .mof file and its for CM2007. I am using SCCM 2012 SP1 in our environment.

The blog says how to create a WQL query and I am looking for a SQL query which I can use to create a report that can be leveraged by everyone.

The reason for not using the ARP display name is because Office 2013 and Lync 2013 shares the same display name. In our environment, we do have several machines with Lync 2013 but not Office 2013. We recently started rolling out Skype for Business.

So are you sure about this?  I just check my lab and I have Lync listed in APR, Which I can write a report on, I can also see Skype for Business listed in ARP too.

So why not make one custom report with both items?

What I was trying to convey is that MS Office 2013 & Lync 2013 (standalone install) share the same ARP display name which is "Microsoft Office Professional Plus 2013" so didn't want to use a query which would refer to the ARP entry.

There may be cases where a device could have Lync 2013 standalone and Office 2010 which would still have the same ARP display name.

I am not worried if SfB is installed or not. :)

I badly need this report working.

I did come up with this query, but still couldn't figure out how to add the rest of fields such as Location information of the device and add another variable to choose the collection. The below report queries the entire All Systems collection and brings details of Servers and DCs as well, which I don't need.

select SYS.Netbios_Name0

From fn_rbac_R_System(@UserSIDs) as SYS

Where NOT EXISTS(select SF.ResourceID from v_GS_SoftwareFile as SF where SF.ResourceID=SYS.ResourceID and SF.FileName LIKE @variable)

ORDER BY SYS.Netbios_Name0

Why are using a file name and not simple add remove software ?

For the report go here http://ccmexec.com/2010/04/report-computers-with-a-specific-file-in-a-specific-collection/

Get the report Jorgen posted and just change the logic for device without the file.

Note: you need to need to make modification as SCCM don't inventory all file by default.

If you looking for something simpler follow this guide :http://blogs.technet.com/b/jchalfant/archive/2014/05/09/collection-of-computers-missing-an-application-software-title-in-configuration-manager.aspx

just be aware of the difference between SMS_G_System_ADD_REMOVE_PROGRAMS_64 and SMS_G_System_ADD_REMOVE_PROGRAMS

• Edited by Frederick Dicaire Tuesday, September 08, 2015 7:25 PM

All of my clients show one of these within ARP. Just saying.

Microsoft Lync MUI (English) 2013 or Update for Skype for Business 2015 (KB2889853) 32-Bit Edition or Update for Skype for Business 2015 (KB2889853) 64-Bit Edition

So where do you see the Location details?

There is no real trick to it, add JOINS and columns to the query.

I did update your query to follow best practices too.

BTW look at this blog post as to why you should use the "Valid": view http://www.enhansoft.com/blog/get-the-most-accurate-and-up-to-date-data-using-the-v_r_system_valid-sql-query-in-configuration-manager-2012

select Distinct
	RV.Netbios_Name0
From 
	fn_rbac_R_System_Valid(@UserSIDs) as RV
Where 
	RV.ResourceID not in 
	(
		select 
			SF.ResourceID 
		from 
			dbo.v_GS_SoftwareFile as SF 
		Where 
			SF.FileName LIKE @variable
		)
ORDER BY 
	RV.Netbios_Name0

Thanks a lot Garth. And the explanation on the blog was informative.

Can you please help me add a collection variable as well to this query? so that I can choose which ever collection I need to run this report.

Thanks a lot Garth. And the explanation on the blog was informative.

Can you please help me add a collection variable as well to this query? so that I can choose which ever collection I need to run this report.

This query below is what I wanted and I got that working.

select SYS.Netbios_Name0, SYS.AD_Site_Name0

From fn_rbac_R_System(@UserSIDs) as SYS

Where NOT EXISTS(select SF.ResourceID from v_GS_SoftwareFile as SF where SF.ResourceID=SYS.ResourceID and SF.FileName LIKE @variable)

AND ((@collection is NULL) OR (SF.ResourceID in (select fcm.ResourceID from v_FullCollectionMembership fcm WHERE SF.ResourceID = fcm.ResourceID AND @collection=fcm.CollectionID)))

ORDER BY SYS.Netbios_Name0

Thanks for your help!

• Marked as answer by Rog M Thursday, September 10, 2015 10:49 PM