USE [iQDataHub-EDW]
GO


IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[Explore_v2].[VMHostProcessor]'))
DROP VIEW [Explore_v2].[VMHostProcessor]

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-------------------------------------------------------------------------------------------
-- View Name			: [Explore_v2].[VMHostProcessor]
--			
-- Created By       	: 	
-- Created On			: 
-- Description      	: VM Hosts Processor view used by iQExplore
-- Sample Execution		: SELECT * FROM [Explore_v2].[VMHostProcessor] 
-- Modified By			: DS
-- Modified On			: 01-10-2015
-- Description			: 9299 - Changing the JOIN to include all the VMHosts even if they don't have any VMs
-- Modified By			: AT
-- Modified On			: 10/10/2015
-- Description			: 9299 - New column that identifies the Virtualisation Technology
-------------------------------------------------------------------------------------------
CREATE VIEW [Explore_v2].[VMHostProcessor]
AS

SELECT rdi.[DeviceID],
	lk_hostname.[Name] AS [VM Host],
	ISNULL( physical_devices.[Name], N'None' ) AS [VMware Cluster Name],
	COUNT(a_proc.[AttributeDeviceInstanceProcessorID]) AS [VM Host CPU Count],
	ISNULL(AVG(a_proc.[CoreCount]), 1) AS [VM Host Cores Per CPU],
	MAX(plv.[Displayname]) AS [Physical Device Manufacturer],
	MAX(plm.[Name]) AS [Physical Device Model],
	COALESCE(MAX(lpt.[Type]), 'Unknown') AS [Processor Type],
	physical_devices.[VMHostVirtualizationMethod]
FROM [hub].[Device] d WITH( NOLOCK )
JOIN
(
	SELECT rdi.[DeviceID], MAX( rdi.[DeviceInstanceID] ) AS [DeviceInstanceID]
	FROM [hub].[ResolveDeviceInstanceToDevice] rdi WITH( NOLOCK )
	LEFT JOIN [hub].[RelationshipDeviceInstanceLocationInstance] r_dili WITH( NOLOCK ) ON r_dili.[DeviceInstanceID] = rdi.[DeviceInstanceID]
	LEFT JOIN [hub].[LocationInstance] li WITH( NOLOCK ) ON li.[LocationInstanceID] = r_dili.[LocationInstanceID]
	LEFT JOIN [hub].[RelationshipLookupLocationExternalLocation] r_llel WITH( NOLOCK ) ON r_llel.[LookupLocationID] = li.[LookupLocationID]
	LEFT JOIN [Explore_v1].[ExternalLocationToBeDisplayed] e_loc WITH( NOLOCK ) ON e_loc.[LookupExternalLocationID] = r_llel.[LookupExternalLocationID]
	WHERE rdi.[IsLastApproved] = 1
	GROUP BY rdi.[DeviceID]
) rdi ON rdi.[DeviceID] = d.[DeviceID]
JOIN [hub].[DeviceInstance] di WITH( NOLOCK ) ON di.[DeviceInstanceID] = rdi.[DeviceInstanceID]
LEFT JOIN [hub].[AttributeDeviceInstanceProcessor] a_proc WITH( NOLOCK ) ON a_proc.[DeviceInstanceID] = rdi.[DeviceInstanceID] AND a_proc.[IsDataDeprecated] = 0
LEFT JOIN [hub].[LookupHostname] lk_hostname WITH( NOLOCK ) ON lk_hostname.[LookupHostnameID] = di.[LookupHostnameID]
LEFT JOIN [hub].[LookupProcessorType] lpt ON lpt.[LookupProcessorTypeID] = a_proc.[LookupProcessorTypeID]
 JOIN
(
	--SELECT 
	--	MAX( lk_qual.[Name] ) AS [Name],
	--	adiv.[PhysicalDeviceInstanceID]
	--FROM hub.[AttributeDeviceInstanceVirtual] adiv WITH( NOLOCK )
	--LEFT JOIN hub.[AttributeSoftwareInstanceNode] asn WITH( NOLOCK ) ON asn.[HostedSoftwareInstanceID] = adiv.[HostSoftwareInstanceID]
	--LEFT JOIN hub.[SoftwareInstance] si_cluster WITH( NOLOCK ) ON si_cluster.[SoftwareInstanceID] = asn.[SoftwareInstanceID]
	--LEFT JOIN hub.[LookupQualifier] lk_qual WITH( NOLOCK ) ON lk_qual.[LookupQualifierID] = si_cluster.[LookupQualifierID]
	--WHERE adiv.[PhysicalDeviceInstanceID] IS NOT NULL
	--GROUP BY adiv.[PhysicalDeviceInstanceID]

	--UNION 

	SELECT lk_qual.[Name] AS [Name],
	di.[DeviceInstanceID] AS [PhysicalDeviceInstanceID],
	CASE ls.Name 
			   when 'SolarisZoneVirtualization' THEN 'SolarisZone'
			   when 'LPAR' THEN 'LPAR'
			   when 'Virtualization' THEN 'VMWare'
			   when 'HYper-V' THEN 'Hyper-V'
			   when 'Virtual Server' THEN 'Virtual Server'
			else 'Unknown'
	END AS [VMHostVirtualizationMethod]
	FROM [hub].[Device] d WITH( NOLOCK )
	INNER JOIN [hub].[ResolveDeviceInstanceToDevice] rdi WITH (NOLOCK) ON d.DeviceID = rdi.DeviceID
	INNER JOIN [hub].[DeviceInstance] di WITH (NOLOCK) ON di.[DeviceInstanceID] = rdi.[DeviceInstanceID]
	LEFT JOIN [hub].[RelationshipDeviceInstanceSoftwareInstance] rdis ON di.[DeviceInstanceID] = rdis.[DeviceInstanceID]
	LEFT JOIN [hub].[SoftwareInstance] si WITH( NOLOCK ) ON si.[SoftwareInstanceID] = rdis.[SoftwareInstanceID]
	LEFT JOIN [hub].[LookupSoftware] ls WITH( NOLOCK ) ON ls.[LookupSoftwareID] = si.[LookupSoftwareID]
	LEFT JOIN hub.[LookupQualifier] lk_qual WITH( NOLOCK ) ON lk_qual.[LookupQualifierID] = si.[LookupQualifierID]
	WHERE ls.Name IN ('SolarisZoneVirtualization','LPAR','Virtualization','Hyper-V', 'Virtual Server')

) physical_devices ON physical_devices.[PhysicalDeviceInstanceID] = di.[DeviceInstanceID]
LEFT JOIN [hub].[AttributeDeviceInstancePhysical] padip WITH( NOLOCK ) ON padip.[DeviceInstanceID] = physical_devices.[PhysicalDeviceInstanceID]
LEFT JOIN [hub].[LookupVendor] plv WITH( NOLOCK ) ON plv.[LookupVendorID] = padip.[LookupVendorID]
LEFT JOIN [hub].[LookupModel] plm WITH( NOLOCK ) ON plm.[LookupModelID] = padip.[LookupModelID]
GROUP BY rdi.[DeviceID], lk_hostname.[Name], physical_devices.[Name], physical_devices.[VMHostVirtualizationMethod]



GO