--Create analysis result

	CREATE TABLE #DeviceInfo(
		[Table]							nvarchar(64),
		NetBios							nvarchar(64),
		ComputerFQDN					nvarchar(64),
		ComputerID						int,
		OSName							nvarchar(256),
        InventoryDate					datetime2(0),
		InventoryAge					int,
		PrimaryIP						nvarchar(64),
		PrimaryMac						nvarchar(64),
		Serial							nvarchar(64),
		Manufacturer					nvarchar(64),
		Model							nvarchar(64),
		Duplicate						nvarchar(5),
		DuplicateEvidence				nvarchar(16),
		Outdated						nvarchar(5),
		Recommendation					nvarchar(64)
	)
	Insert into #DeviceInfo
	SELECT 'ComputerTable' AS [Table], c.ComputerCN, NULL, c.ComputerID, OperatingSystem.OperatingSystemName, InventoryReport.SWDate, DATEDIFF(day,GetDate(),InventoryReport.SWDate) * -1, NULL, NULL, NULL, NULL, NULL, 'FALSE', 'Not relevant',
	Case 
		when DATEDIFF(day,GetDate(),InventoryReport.SWDate) * -1 > 89 then 'TRUE'
		Else 'FALSE'
	END,
	'None'
	FROM     Computer AS c LEFT OUTER JOIN
					  OperatingSystem ON c.OperatingSystemID = OperatingSystem.OperatingSystemID LEFT OUTER JOIN
					  InventoryReport ON c.ComputerID = InventoryReport.ComputerID
	WHERE  (InventoryReport.SWDate IS NOT NULL AND InventoryReport.UserID = 1)


	--Add PrimaryIP

	CREATE TABLE #TempPrimaryIP(
	ComputerID						int,
	PrimaryIPAddress				nvarchar(64),
	PrimaryMacAddress				nvarchar(64)
	)

	Insert Into #TempPrimaryIP
	SELECT C.ComputerID,
			CASE
			  WHEN CHARINDEX(',', nic.IPAddress) > 0 THEN LEFT(nic.IPAddress, CHARINDEX(',', nic.IPAddress)-1) 
			  ELSE nic.IPAddress
			END                       AS 'Primary IP Address',
			nic.MACAddress
	FROM     Computer c
			CROSS APPLY (SELECT TOP 1 
						 CASE 
						   WHEN nicInt.Description IS NOT NULL THEN nicInt.Description
						   ELSE nicInt.Caption
						 END AS 'Description',
						 nicInt.IPAddress,
						 nicInt.MACAddress
					   FROM NetworkAdapterConfiguration nicInt
					   WHERE nicInt.ComputerID = c.ComputerID
						 AND nicInt.IPEnabled = 'TRUE'
						 AND (nicInt.Caption IS NOT NULL OR nicInt.Description IS NOT NULL)
					   ORDER BY nicInt.ComputerID,nicInt.[Index],nicInt.Caption
					) nic

		UPDATE #DeviceInfo SET 
		PrimaryIP = #TempPrimaryIP.PrimaryIPAddress,
		PrimaryMac = #TempPrimaryIP.PrimaryMacAddress
		FROM #TempPrimaryIP JOIN #DeviceInfo ON #TempPrimaryIP.ComputerID = #DeviceInfo.ComputerID


	--Add Serial

	CREATE TABLE #Serial(
	ComputerID				int,
	Serial					nvarchar(64)
	)

	Insert Into #Serial
	select ComputerID, [Value] From  f_HardwareProperties('CIM_BIOSElement') where Property = 'SerialNumber' order by computerID

	UPDATE #DeviceInfo SET 
	Serial = #Serial.Serial
	FROM #Serial JOIN #DeviceInfo ON #Serial.ComputerID = #DeviceInfo.ComputerID

	--Add Basic System information

	CREATE TABLE #BasicSystem(
	ComputerID				int,
	ComputerFQDN			nvarchar(128),
	Manufacturer			nvarchar(128),
	Model					nvarchar(128)
	)

	Insert Into  #BasicSystem Select ComputerID, ComputerFQDN, Manufacturer, Model from f_System_Base()
	
	UPDATE #DeviceInfo SET 
	ComputerFQDN = #BasicSystem.ComputerFQDN,
	Manufacturer = #BasicSystem.Manufacturer,
	Model = #BasicSystem.Model
	FROM #BasicSystem JOIN #DeviceInfo ON #BasicSystem.ComputerID = #DeviceInfo.ComputerID

	--Mark duplicates
	UPDATE #DeviceInfo SET 
	duplicate = 'TRUE',
	DuplicateEvidence = 'Poor'
	where netbios IN (SELECT netbios FROM #DeviceInfo GROUP BY netbios HAVING ( COUNT(netbios) > 1 ))


	--Mark entries with normal duplicate evidence (same Netbios, FQDN and Serial)
	CREATE TABLE #NormalDuplicateEvidence(
	NetBios					nvarchar(128),
	Serial					nvarchar(128)
	)


	Insert Into  #NormalDuplicateEvidence Select netbios, serial from #DeviceInfo group by netbios, serial having count(1) > 1

	UPDATE #DeviceInfo SET 
	DuplicateEvidence = 'Normal'
	where serial IN (Select Serial from #NormalDuplicateEvidence)

	
	--Mark entries with high duplicate evidence (same Netbios, FQDN and Serial)
	CREATE TABLE #HighDuplicateEvidence(
	ComputerFQDN			nvarchar(64),
	NetBios					nvarchar(64),
	Serial					nvarchar(64)
	)

	Insert Into  #HighDuplicateEvidence Select ComputerFQDN, netbios, serial from #DeviceInfo group by ComputerFQDN, netbios, serial having count(1) > 1

	UPDATE #DeviceInfo SET 
	DuplicateEvidence = 'High'
	where serial IN (Select Serial from #HighDuplicateEvidence)


	UPDATE 
	#DeviceInfo
	SET
		Recommendation = CASE 
		WHEN Outdated = 'TRUE' THEN 'Delete Inventory'
		WHEN Outdated = 'FALSE' AND (DuplicateEvidence = 'Normal' OR DuplicateEvidence = 'High') THEN 'Confirm Keep/Delete inventory'
		WHEN Outdated = 'FALSE' AND (DuplicateEvidence = 'Poor' OR Duplicate = 'FALSE') THEN 'Keep inventory'
		ELSE 'Nothing'
		END
	FROM
    #DeviceInfo 

	Select * From #DeviceInfo where Recommendation <>'Nothing' order by netbios 


	Drop Table #DeviceInfo
	Drop Table #TempPrimaryIP
	Drop Table #Serial
	Drop Table #BasicSystem
	Drop Table #HighDuplicateEvidence
	Drop Table #NormalDuplicateEvidence




