Client Management Suite

 Altiris 8.0 Report Software not installed

posted 10-07-2020 04:28 PM
Hoping someone out there is gracious enough to help me. I need a report that shows a piece of software that is not installed on computers. I've found other threads that has that script, but I need a lot more info:

Machine name
User name
OS name
IP address
Machine model
OS language

Thanks in advance for any assistance anyone can provide

Greg
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

Thanks sdmayhew!

Just so I make sure I understand what this does. Your script, as written, finds all computers with the various versions of Office installed. You then take that info against all computers to find computers without Office installed. Is that correct?

sdmayhew, After a little tweaking this works perfectly for me.

Thanks!