Here is one I use a lot and tweak at least once a week to show software not installed.
start with all workstations then have a column set to show what is installed. do a simple pivot table to show installed and not installed
Note i am very old school and do not use a lot of the built in views. What can happen is a view does not get a full inventory record so the view will drop it
select distinct
ac.name,
ac.[OS Name] AS 'OS Name',
ac.[OS version],
ac.[OS Build Number],
coalesce (
case when ac.[OS Build Number] = '18363' then 'Windows 10 (1909)' ELSE NULL end,
case when ac.[OS Build Number] = '18362' then 'Windows 10 (1903)' ELSE NULL end,
case when ac.[OS Build Number] = '17763' then 'Windows 10 (1809)' ELSE NULL end,
case when ac.[OS Build Number] = '17134' then 'Windows 10 (1803)' ELSE NULL end,
case when ac.[OS Build Number] = '16299' then 'Windows 10 (1709)' ELSE NULL end,
case when ac.[OS Build Number] = '15063' then 'Windows 10 (1703)' ELSE NULL end,
case when ac.[OS Build Number] = '14393' then 'Windows 10 (1607)' ELSE NULL end,
case when ac.[OS Build Number] = '10586' then 'Windows 10 (1511)' ELSE NULL end,
case when ac.[OS Build Number] = '10240' then 'Windows 10 (0)' ELSE NUll end )
as 'OS Buid Name' ,
convert(varchar, ac.[client date], 101) as 'HeartBeat',
--Display Auto Log on devices - this finds us the Clinical Devicesf
CASE when (ac.Name = prime.[primary user]) then 'Auto Log' else 'Information Worker' end as 'Machine Type',
vc.[IP Address],
convert(varchar,os.[Last Boot Up Time],101) as 'Last Boot Up',
convert(varchar, [Created Date], 101) as 'Created Date',
convert(varchar, ac.[client date], 101) as 'Basic ScanDate',
convert(varchar,os.[Install Date],101) as 'OS Install Date',
datepart(MONTH,(convert(varchar,os.[Install Date],101))) as 'month date OS Install',
datepart(year,(convert(varchar,os.[Install Date],101))) as 'year date OS Install',
ac.[os revision],
ac.[os version],
Prime.[primary user],
T20.[Given Name] as 'First Name',
ISNULL(T20.[Surname],'') AS 'Last Name',
T20.[office telephone],
t20.[office location],
T20.department,
t20.email,
CASE WHEN LOWER (i.[System Type]) LIKE 'win32' THEN '32-bit' ELSE '64-bit' END 'Architecture [32/64-bit]',
bi.[Version] as 'Bios Version',
bi.[Release Date] as 'Bios Date',
ac.[hw chassis serial number],
vcs.[Manufacturer], vcs.[Model],
forescout.*,
teamviewer.*
FROM dbo.Inv_AeX_AC_Identification ac
left JOIN vcomputer i ON i.Guid = ac._ResourceGuid
left join dbo.Inv_AeX_AC_TCPIP tc on tc._ResourceGuid = ac._ResourceGuid
left join vComputer vc on vc.Guid =ac._ResourceGuid
left join dbo.vHWComputerSystem vcs on ac._ResourceGuid = vcs._ResourceGuid
left join (SELECT distinct _ResourceGuid, [Chassis Package Type] , [Serial Number] ,[Manufacturer]
FROM [Symantec_CMDB].[dbo].[vHWChassis]) chas on chas._ResourceGuid = ac._ResourceGuid
left join ( select MAX (Left(prog.DisplayVersion,2))as 'Display Version', i.[guid]
FROM dbo.vComputer i INNER JOIN dbo.Inv_AddRemoveProgram prog
ON i.[Guid]= prog._ResourceGuid
where ( prog.DisplayName ='Microsoft Office Professional Plus 2007'
or prog.DisplayName ='Microsoft Office Professional Plus 2010'or prog.DisplayName ='Microsoft Office Professional Edition 2003'
or prog.DisplayName ='Microsoft Office 2010'or prog.DisplayName ='Microsoft Office Standard 2010'
or prog.DisplayName ='Microsoft Office 2000 SR-1 Professional'or prog.DisplayName ='2007 Microsoft Office system'
or prog.DisplayName ='Microsoft Office Enterprise 2007'or prog.DisplayName ='Microsoft Office Basic Edition 2003'
or prog.DisplayName ='Microsoft Office 2000 SR-1 Disc 2' or prog.DisplayName ='Microsoft Office Professional Plus 2013'
or prog.DisplayName ='Microsoft Office Professional Plus 2016'or prog.DisplayName ='Microsoft Office 365 ProPlus - en-us' )
and prog.InstallFlag =1
group by i.guid ) office1
on i.Guid =office1.[guid]
left join (SELECT _ResourceGuid, MAX([user]) AS [primary user]
FROM dbo.Inv_AeX_AC_Primary_User
WHERE ([User] IS NOT NULL) AND ([User] <> '') GROUP BY _ResourceGuid ) Prime
on ac._resourceguid = Prime._ResourceGuid
-- Did this query to remove User records that were incomplete
LEFT JOIN (select distinct vuser.* from vuser where [Given Name] !='' )t20
ON Prime.[primary user] = T20.name
left join vHWProcessor vhp on vhp._ResourceGuid = i.Guid
left JOIN dbo.vHWComputerSystem hpm
ON hpm._ResourceGuid = i.Guid
left join Inv_OS_Operating_System os on vc.Guid = os._ResourceGuid
left JOIN (SELECT CAST (SUM ([Max Media Size (Kilobytes)] / (1024.0 * 1024.0))
AS DECIMAL (10,2)) [Hard DISK Size],_ResourceGuid FROM vHWStorage
WHERE [Media Type] = 29 GROUP BY _ResourceGuid) a
ON a._ResourceGuid = i.Guid
left JOIN (SELECT MAX (t.[Hard Disk Free Space]) [Hard Disk Free Space], t.Guid
FROM (SELECT ld._ResourceGuid AS Guid, CAST (SUM (ld.[Free Space (Bytes)] / (1024.0 * 1024.0 * 1024.0))
AS DECIMAL (10,2)) AS 'Hard Disk Free Space', ld.[Device ID]
FROM dbo.vHWLogicalDisk ld
JOIN dbo.vHWLogicalDiskBasedOnPartition ld1
ON ld._ResourceGuid = ld1._ResourceGuid
AND ld.[Device ID] = ld1.Dependent
JOIN dbo.vHWStorageToDiskPartition s1
ON s1._ResourceGuid = ld._ResourceGuid
AND (s1.Dependent = ld1.Antecedent OR s1.Dependent = ld.[Device ID])
JOIN dbo.vHWStorage sh
ON sh._ResourceGuid = s1._ResourceGuid
AND sh.[Device ID] = s1.Antecedent
AND sh.[Media Type] = 29
GROUP BY ld._ResourceGuid, ld.[Device ID])t
GROUP BY t.Guid) b
ON b.Guid = i.Guid
left join dbo.Inv_SW_BIOS_Element bi on bi._ResourceGuid = i.Guid
left join (select distinct prog._ResourceGuid ,prog.Publisher, prog.DisplayName, prog.DisplayVersion
FROM dbo.Inv_AeX_AC_Identification ac
left join dbo.Inv_AddRemoveProgram prog on prog._ResourceGuid = ac._ResourceGuid
left join Inv_OS_Operating_System os on ac._ResourceGuid= os._ResourceGuid where prog.DisplayName like '%teamview%') teamviewer on teamviewer._ResourceGuid = ac._ResourceGuid
left join ( select ifd._ResourceGuid , vc.[OS Name], ifd.Path, vsc.Name
from inv_installed_file_details ifd
join vcomputer vc on vc.guid = ifd._resourceguid
join resourceassociation ra on ifd.fileresourceguid = ra.childresourceguid
left join vsoftwarecomponent vsc on vsc.guid = ra.parentresourceguid
left outer join inv_software_component isc on isc._resourceguid = vsc.guid
left outer join inv_file_details ifdet on ifdet._resourceguid = ifd.fileresourceguid
where
vsc.name ='ForeScout SecureConnector 11.1.00.0080') forescout on forescout._ResourceGuid = ac._ResourceGuid
left join (select _ResourceGuid, MIN (auditdate) as 'Created Date' from dbo.Inv_Audit
group by _ResourceGuid) created on ac._ResourceGuid = created._ResourceGuid
WHERE ac.[Client Date] > getdate ()-90
order by ac.[Name]asc