This will let you join an "Application + Deployment Type" in SCCM to software actually installed on the machine, provided it is a MSI file and you imported the product code onto the deployment type
WITH XMLNAMESPACES ( DEFAULT ' http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest ' ) select Distinct Apps.ModelID, Apps.ModelName, Apps.DisplayName, SF.ResourceID >From dbo.fn_ListApplicationCIs(1033) Apps Left Join [vSMS_CIDocuments] D on D.CI_ID = Apps.CI_ID left Join v_GS_INSTALLED_SOFTWARE SF ON SF.SoftwareCode0 = D.Body.value( '(/AppMgmtDigest/DeploymentType/Installer/CustomData/ProductCode)[1]', 'nvarchar(MAX)') Where Apps.IsLatest = 1 AND IsSuperseded = 0 AND DocumentType = 0 AND SF.ResourceID Is Not Null On Fri, Jun 3, 2016 at 12:02 PM, Murray, Mike <[email protected]> wrote: > Maybe something like the query here? > > > > > https://social.technet.microsoft.com/Forums/en-US/0f1ef5a9-7b83-49cf-b47b-90da8d535e85/need-a-sccm-sql-query-report-for-installed-software-with-packages-and-applications?forum=configmanagergeneral > > > > SELECT v_GS_COMPUTER_SYSTEM.Name0 as 'Computer Name', > v_GS_INSTALLED_SOFTWARE.ProductName0 as 'Software Title', > v_GS_INSTALLED_SOFTWARE.InstallSource0 as 'Install Source', > v_GS_INSTALLED_SOFTWARE.ProductVersion0 as 'Version', > v_GS_INSTALLED_SOFTWARE.InstalledLocation0 as 'Installed Location', > v_GS_INSTALLED_SOFTWARE.InstallDate0 as 'Install Date' > > FROM v_GS_COMPUTER_SYSTEM INNER JOIN v_GS_INSTALLED_SOFTWARE ON > v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_INSTALLED_SOFTWARE.ResourceID > > WHERE v_GS_INSTALLED_SOFTWARE.ProductName0 like '%Office 365 Pro%' > > ORDER BY v_GS_COMPUTER_SYSTEM.Name0 > > > > > > *From:* [email protected] [mailto: > [email protected]] *On Behalf Of *Brian McDonald > *Sent:* Thursday, June 2, 2016 6:47 AM > *To:* [email protected] > *Subject:* [mssms] Re: Query Help > > > > Yes. Problem is several of these names aren't coming up when I search the > Parameter Value under Computers with a specific product. > > > > Example of a few include: HTMLDOC, Lotus Domino, ClearingHouse, > CVE-2013-1347, ImageRight Desktop, ImageRightPrinter and more... > > > > Here is the SQL query I'm using: > > > > eclare @CollID char(8) > > Set @CollID = 'SMS00001' > > SELECT DisplayName0, Count(*) AS 'Count', Publisher0, Version0, @CollID as > CollectionID > > FROM v_Add_Remove_Programs arp > > JOIN v_FullCollectionMembership fcm on arp.ResourceID=fcm.ResourceID > > WHERE fcm.CollectionID = @CollID > > GROUP BY DisplayName0, Publisher0, Version0 > > ORDER BY Publisher0, Version0 > > > ------------------------------ > > *From:* [email protected] <[email protected]> > on behalf of Garth Jones <[email protected]> > *Sent:* Wednesday, June 1, 2016 3:45:04 PM > *To:* [email protected] > *Subject:* [mssms] RE: Query Help > > > > Have you looked at the built-in reports for this yet? > > > > > > > > Garth Jones > > Chief Architect > > > > *www.Enhansoft.com* <http://www.enhansoft.com/> > > [image: Description: Description: cid:[email protected]] > <http://www.enhansoft.com/> > > *Enhancing Your Business* > > [image: Description: Description: Description: Description: Description: > Description: Description: cid:[email protected]] > <http://www.enhansoft.com/blog>[image: Description: Description: > Description: Description: Description: Description: Description: > cid:[email protected]] <https://twitter.com/enhansoft>[image: > Description: Description: Description: Description: Description: > Description: Description: cid:[email protected]] > <http://www.facebook.com/EnhansoftInc>[image: Description: Description: > Description: Description: Description: Description: Description: > Description: Description: Description: Description: Description: > Description: Description: Description: Enhansoft's YouTube Page] > <http://www.youtube.com/user/Enhansoft/videos>[image: Description: > Description: Description: Description: Description: Description: > Description: cid:[email protected]] > <http://myitforum.com/myitforumwp/community/groups/enhansoft/> > > > > *Subscribe to Enhansoft’s Newsletter*! <http://www.enhansoft.com/register> > > > > *From:* [email protected] [ > mailto:[email protected] <[email protected]>] *On > Behalf Of *Brian McDonald > *Sent:* Wednesday, June 1, 2016 4:02 PM > *To:* [email protected] > *Subject:* [mssms] Query Help > > > > Hello, > > > > Management has provided me a list of applications they're researching and > would like to know which devices have these applications have. Is there a > SQL query I could use as a template and re-use it by modifying the > DisplayName? > > > > For example, one of the apps in question is 'AOL Toolbar'. > > > > Can someone provide an example of a query I could use for this task? And > make modifications to the query based on the name? > > > > Thanks, > > > Brian > > > > > > > >
