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