ALTER proc [dbo].[spITAnalytics_CSMP_CreateViews] as begin BEGIN begin -- Update date that stores last time views were regenerated declare @StateXml1 as xml select @StateXml1 = convert(xml,[State]) from Item where Guid = '051bbf59-285b-4917-856f-d05a7374a858' SET @StateXml1.modify(' delete (/item/LastPackValidationDate[1])[1] ') declare @sql1 nvarchar(max) set @sql1 = N'SET @StateXml1.modify(''insert ' + convert(nvarchar(50), GETDATE(), 121) + ' into (/item[1])[1]'')' execute sp_executesql @sql1,N'@StateXml1 XML Output', @StateXml1 Output update Item set [State] = convert(nvarchar(max),@StateXml1) where Guid = '051bbf59-285b-4917-856f-d05a7374a858' end begin -- Declare procedural variables declare @Source as nvarchar(250) declare @DropSql as nvarchar(4000) declare @TmpSql as nvarchar(4000) declare @ServerName sysname declare @DatabaseName sysname declare @Schema sysname declare @FirstServer bit declare @UseHostCMDB bit declare @LocalResourceManagerURL as nvarchar(250) declare @LocalResourceEditURL as nvarchar(250) declare @thisResourceManagerURL as nvarchar(250) declare @thisResourceEditURL as nvarchar(250) select @UseHostCMDB = xmlPolicy.CMDB.value('(./IncludeLocalCMDB)[1]', 'bit') , @LocalResourceManagerURL = xmlPolicy.CMDB.value('(./LocalResourceManagerURL)[1]', 'nvarchar(250)') , @LocalResourceEditURL = xmlPolicy.CMDB.value('(./LocalResourceEditURL)[1]', 'nvarchar(250)') from ( select convert(xml,[State]) as [StateXml] from vItem where Guid = '051bbf59-285b-4917-856f-d05a7374a858' ) xmlSettings CROSS APPLY xmlSettings.[StateXml].nodes('/item') AS xmlPolicy(CMDB) end begin -- Drop Existing Views set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[spITAnalytics_CMDB_GetFullyQualifiedDataSource]'')) DROP PROC [dbo].[spITAnalytics_CMDB_GetFullyQualifiedDataSource]' exec (@DropSql) -- Drop any older views used to process cubes set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_ApplicablePatchFact]'')) DROP VIEW [dbo].[vITAnalytics_ApplicablePatchFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_VulnerablePatchFact]'')) DROP VIEW [dbo].[vITAnalytics_VulnerablePatchFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_InstalledPatchFact]'')) DROP VIEW [dbo].[vITAnalytics_InstalledPatchFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_ApplicationMeteringFact]'')) DROP VIEW [dbo].[vITAnalytics_ApplicationMeteringFact]' exec (@DropSql) -- End Older Views set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_AddRemoveProgramsDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_AddRemoveProgramsDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_AdvertisementDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_AdvertisementDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_AlertActionAuditFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_AlertActionAuditFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_AlertActionAuditTypeDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_AlertActionAuditTypeDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_AlertCategoryDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_AlertCategoryDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_AlertDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_AlertDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_AlertFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_AlertFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_AlertProtocolDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_AlertProtocolDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_AlertSeverityDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_AlertSeverityDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_AltirisAgentConfigurationRequestEventFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_AltirisAgentConfigurationRequestEventFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_ApplicablePatchFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_ApplicablePatchFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_ApplicationMeteringFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_ApplicationMeteringFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_ClientConfigStatusCodeDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_ClientConfigStatusCodeDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_CollectionMembershipFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_CollectionMembershipFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_ComputerDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_ComputerDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_ComputerFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_ComputerFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_ESXServerFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_ESXServerFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_ESXStorageVolumeDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_ESXStorageVolumeDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_ESXVirtualMachineDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_ESXVirtualMachineDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_ForwardServerDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_ForwardServerDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_IISFTPSiteDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_IISFTPSiteDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_IISFTPSiteFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_IISFTPSiteFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_IISServerDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_IISServerDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_IISServerFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_IISServerFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_IISVirtualDirectoryDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_IISVirtualDirectoryDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_IISWebSiteDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_IISWebSiteDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_InventoryClassDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_InventoryClassDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_InventoryForwardingFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_InventoryForwardingFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_InventoryUpdatedSinceForwardedFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_InventoryUpdatedSinceForwardedFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_InventoryUpdateFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_InventoryUpdateFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_InstalledPatchFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_InstalledPatchFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_InstalledFileFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_InstalledFileFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_InstalledSoftwareFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_InstalledSoftwareFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_LogicalDiskDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_LogicalDiskDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_LogicalDiskFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_LogicalDiskFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_MonitorRuleDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_MonitorRuleDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_MonitorMetricDataFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_MonitorMetricDataFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_MonitorMetricDetailLevelDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_MonitorMetricDetailLevelDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_MonitorMetricDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_MonitorMetricDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_MonitorMetricInstanceDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_MonitorMetricInstanceDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_MonitorMetricSourceDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_MonitorMetricSourceDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_MonitorNtEventCategoryDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_MonitorNtEventCategoryDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_MonitorNtEventDescriptionDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_MonitorNtEventDescriptionDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_MonitorNtEventIdDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_MonitorNtEventIdDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_MonitorNtEventLogFileDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_MonitorNtEventLogFileDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_MonitorNtEventMessageDLLDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_MonitorNtEventMessageDLLDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_MonitorNtEventRuleTriggeredDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_MonitorNtEventRuleTriggeredDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_MonitorNtEventsFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_MonitorNtEventsFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_MonitorNtEventSourceDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_MonitorNtEventSourceDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_MonitorNtEventTypeDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_MonitorNtEventTypeDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_MonitorNtEventUserDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_MonitorNtEventUserDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_MonitorPolicyDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_MonitorPolicyDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_MonitorProcessFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_MonitorProcessFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_MonitorProcessNameDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_MonitorProcessNameDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_MonitorProcessOwnerDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_MonitorProcessOwnerDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_MonitorTaskFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_MonitorTaskFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_PackageDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_PackageDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_PackageDownloadSourceDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_PackageDownloadSourceDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_PackageDownloadMethodDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_PackageDownloadMethodDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_PackageServerConfigurationRequestFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_PackageServerConfigurationRequestFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_PackageServerDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_PackageServerDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_PackageServerPackageDistributionEventDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_PackageServerPackageDistributionEventDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_PackageServerPackageDistributionEventFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_PackageServerPackageDistributionEventFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_PackageServerPackageStatusDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_PackageServerPackageStatusDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_PackageServerPackageStatusFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_PackageServerPackageStatusFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_ParentTaskInstancesDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_ParentTaskInstancesDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_PatchManagementSoftwareUpdateDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_PatchManagementSoftwareUpdateDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_PatchVulnerabilityFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_PatchVulnerabilityFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_PhysicalMemoryArrayFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_PhysicalMemoryArrayFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_PhysicalMemoryFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_PhysicalMemoryFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_ProcessorDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_ProcessorDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_ProcessorFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_ProcessorFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_ResourceDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_ResourceDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_SiteDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_SiteDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_SoftwareComponentDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_SoftwareComponentDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_SoftwareComponentFileFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_SoftwareComponentFileFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_SoftwareDeliveryExecutionEventCommandLineDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_SoftwareDeliveryExecutionEventCommandLineDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_SoftwareDeliveryExecutionEventFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_SoftwareDeliveryExecutionEventFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_SoftwareDeliveryExecutionEventStatusDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_SoftwareDeliveryExecutionEventStatusDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_SoftwareDeliveryPackageEventFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_SoftwareDeliveryPackageEventFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_SoftwareDeliveryPackageEventStatusDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_SoftwareDeliveryPackageEventStatusDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_SoftwareDeliveryPackageEventURLDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_SoftwareDeliveryPackageEventURLDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_SoftwareDeliveryStatusEventFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_SoftwareDeliveryStatusEventFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_SoftwareDeliveryStatusEventStatusDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_SoftwareDeliveryStatusEventStatusDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_SoftwareDeliveryStatusEventTypeDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_SoftwareDeliveryStatusEventTypeDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_SoftwareProductComponentFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_SoftwareProductComponentFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_SQLClusterDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_SQLClusterDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_SQLClusterFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_SQLClusterFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_SQLClusterResourceDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_SQLClusterResourceDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_SQLDatabaseDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_SQLDatabaseDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_SQLDatabaseFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_SQLDatabaseFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_SQLDatabaseSystemDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_SQLDatabaseSystemDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_SQLStorageAreaDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_SQLStorageAreaDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_SQLUserDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_SQLUserDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_SQLUserFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_SQLUserFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_SubnetDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_SubnetDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_TaskDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_TaskDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_TaskInstancesFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_TaskInstancesFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_TaskServerDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_TaskServerDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_WindowsFileDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_WindowsFileDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_AssetCostCenterFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_AssetCostCenterFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_AssetDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_AssetDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_AssetFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_AssetFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_AssetStatusDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_AssetStatusDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_AssetTypeDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_AssetTypeDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_CostCenterDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_CostCenterDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_DepartmentDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_DepartmentDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_LocationDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_LocationDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_OrganizationalGroupDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_OrganizationalGroupDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_OrganizationalGroupFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_OrganizationalGroupFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_UserDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_UserDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_UserFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_UserFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_BorrowedLicenseCountFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_BorrowedLicenseCountFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_CollectionDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_CollectionDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_DateDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_DateDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_DonatedLicenseFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_DonatedLicenseFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_NonInventoriedInstallFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_NonInventoriedInstallFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_SoftwareLicenseDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_SoftwareLicenseDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_SoftwareLicenseInstallationEvaluationFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_SoftwareLicenseInstallationEvaluationFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_SoftwareLicenseInstallationFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_SoftwareLicenseInstallationFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_SoftwareLicenseOwnedFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_SoftwareLicenseOwnedFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_SoftwareLicenseUsageEvaluationFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_SoftwareLicenseUsageEvaluationFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_SoftwareLicenseUsageFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_SoftwareLicenseUsageFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_SoftwareProductDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_SoftwareProductDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_SoftwareProductLicenseFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_SoftwareProductLicenseFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_SoftwarePurchaseDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_SoftwarePurchaseDim]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_SoftwarePurchaseFact]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_SoftwarePurchaseFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[vITAnalytics_CMDB_TimeDim]'')) DROP VIEW [dbo].[vITAnalytics_CMDB_TimeDim]' exec (@DropSql) end begin -- Drop Existing Functions set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[fnITAnalytics_CMDB_NonInventoriedInstallFact]'')) DROP FUNCTION [dbo].[fnITAnalytics_CMDB_NonInventoriedInstallFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[fnITAnalytics_CMDB_BorrowedLicenseFact]'')) DROP FUNCTION [dbo].[fnITAnalytics_CMDB_BorrowedLicenseFact]' exec (@DropSql) set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[fnITAnalytics_CMDB_DonatedLicenseFact]'')) DROP FUNCTION [dbo].[fnITAnalytics_CMDB_DonatedLicenseFact]' exec (@DropSql) end begin -- Create SP to determine DB Schema declare @CreateSQL_GetFullyQualifiedDataSource varchar(max) select @CreateSQL_GetFullyQualifiedDataSource = 'CREATE proc [dbo].[spITAnalytics_CMDB_GetFullyQualifiedDataSource] ( @LinkedServerName as nvarchar(1000) , @DatabaseName as nvarchar(1000) , @ObjectName as nvarchar(1000) , @Source as nvarchar(1000) OUTPUT ) AS BEGIN declare @sql as nvarchar(1000) DECLARE @ParmDefinition nvarchar(1000); set @sql = '' SELECT top 1 @Source=''''['' + @LinkedServerName + ''].['' + @DatabaseName + ''].['''''' + '' + '' + ''schema_name(o.schema_id)'' + '' + '' + ''''''].['' + @ObjectName + '']'''''' + '' FROM ['' + @LinkedServerName + ''].['' + @DatabaseName + ''].sys.objects o Where o.name = '''''' + @ObjectName + ''''''order by o.create_date DESC'' SET @ParmDefinition = N''@Source varchar(255) OUTPUT''; execute sp_executesql @sql, @ParmDefinition, @Source OUTPUT; END' exec (@CreateSQL_GetFullyQualifiedDataSource) end begin -- Declare Create SQL variables declare @CreateSQL_AssetCostCenterFact varchar(max) select @CreateSQL_AssetCostCenterFact = '' declare @CreateSQL_AssetDim varchar(max) select @CreateSQL_AssetDim = '' declare @CreateSQL_AssetFact varchar(max) select @CreateSQL_AssetFact = '' declare @CreateSQL_AssetStatusDim varchar(max) select @CreateSQL_AssetStatusDim = '' declare @CreateSQL_AssetTypeDim varchar(max) select @CreateSQL_AssetTypeDim = '' declare @CreateSQL_CostCenterDim varchar(max) select @CreateSQL_CostCenterDim = '' declare @CreateSQL_DepartmentDim varchar(max) select @CreateSQL_DepartmentDim = '' declare @CreateSQL_LocationDim varchar(max) select @CreateSQL_LocationDim = '' declare @CreateSQL_OrganizationalGroupDim varchar(max) select @CreateSQL_OrganizationalGroupDim = '' declare @CreateSQL_OrganizationalGroupFact varchar(max) select @CreateSQL_OrganizationalGroupFact = '' declare @CreateSQL_UserDim varchar(max) select @CreateSQL_UserDim = '' declare @CreateSQL_BorrowedLicenseCountFact varchar(max) select @CreateSQL_BorrowedLicenseCountFact = '' declare @CreateSQL_CollectionDim varchar(max) select @CreateSQL_CollectionDim = '' declare @CreateSQL_DateDim varchar(max) select @CreateSQL_DateDim = '' declare @CreateSQL_DonatedLicenseFact varchar(max) select @CreateSQL_DonatedLicenseFact = '' declare @CreateSQL_NonInventoriedInstallFact varchar(max) select @CreateSQL_NonInventoriedInstallFact = '' declare @CreateSQL_SoftwareLicenseDim varchar(max) select @CreateSQL_SoftwareLicenseDim = '' declare @CreateSQL_SoftwareLicenseInstallationEvaluationFact varchar(max) select @CreateSQL_SoftwareLicenseInstallationEvaluationFact = '' declare @CreateSQL_SoftwareLicenseInstallationFact varchar(max) select @CreateSQL_SoftwareLicenseInstallationFact = '' declare @CreateSQL_SoftwareLicenseOwnedFact varchar(max) select @CreateSQL_SoftwareLicenseOwnedFact = '' declare @CreateSQL_SoftwareLicenseUsageEvaluationFact varchar(max) select @CreateSQL_SoftwareLicenseUsageEvaluationFact = '' declare @CreateSQL_SoftwareLicenseUsageFact varchar(max) select @CreateSQL_SoftwareLicenseUsageFact = '' declare @CreateSQL_SoftwareProductDim varchar(max) select @CreateSQL_SoftwareProductDim = '' declare @CreateSQL_SoftwareProductLicenseFact varchar(max) select @CreateSQL_SoftwareProductLicenseFact = '' declare @CreateSQL_SoftwarePurchaseDim varchar(max) select @CreateSQL_SoftwarePurchaseDim = '' declare @CreateSQL_SoftwarePurchaseFact varchar(max) select @CreateSQL_SoftwarePurchaseFact = '' declare @CreateSQL_TimeDim varchar(max) select @CreateSQL_TimeDim = '' declare @CreateSQL_AddRemoveProgramsDim varchar(max) select @CreateSQL_AddRemoveProgramsDim = '' declare @CreateSQL_AdvertisementDim varchar(max) select @CreateSQL_AdvertisementDim = '' declare @CreateSQL_AlertActionAuditFact varchar(max) select @CreateSQL_AlertActionAuditFact = '' declare @CreateSQL_AlertActionAuditTypeDim varchar(max) select @CreateSQL_AlertActionAuditTypeDim = '' declare @CreateSQL_AlertCategoryDim varchar(max) select @CreateSQL_AlertCategoryDim = '' declare @CreateSQL_AlertDim varchar(max) select @CreateSQL_AlertDim = '' declare @CreateSQL_AlertFact varchar(max) select @CreateSQL_AlertFact = '' declare @CreateSQL_AlertProtocolDim varchar(max) select @CreateSQL_AlertProtocolDim = '' declare @CreateSQL_AlertSeverityDim varchar(max) select @CreateSQL_AlertSeverityDim = '' declare @CreateSQL_AltirisAgentConfigurationRequestEventFact varchar(max) select @CreateSQL_AltirisAgentConfigurationRequestEventFact = '' declare @CreateSQL_ApplicablePatchFact varchar(max) select @CreateSQL_ApplicablePatchFact = '' declare @CreateSQL_ApplicationMeteringFact varchar(max) select @CreateSQL_ApplicationMeteringFact = '' declare @CreateSQL_ClientConfigStatusCodeDim varchar(max) select @CreateSQL_ClientConfigStatusCodeDim = '' declare @CreateSQL_CollectionMembershipFact varchar(max) select @CreateSQL_CollectionMembershipFact = '' declare @CreateSQL_ComputerDim varchar(max) select @CreateSQL_ComputerDim = '' declare @CreateSQL_ComputerFact varchar(max) select @CreateSQL_ComputerFact = '' declare @CreateSQL_ESXServerFact varchar(max) select @CreateSQL_ESXServerFact = '' declare @CreateSQL_ESXStorageVolumeDim varchar(max) select @CreateSQL_ESXStorageVolumeDim = '' declare @CreateSQL_ESXVirtualMachineDim varchar(max) select @CreateSQL_ESXVirtualMachineDim = '' declare @CreateSQL_ForwardServerDim varchar(max) select @CreateSQL_ForwardServerDim = '' declare @CreateSQL_IISFTPSiteDim varchar(max) select @CreateSQL_IISFTPSiteDim = '' declare @CreateSQL_IISFTPSiteFact varchar(max) select @CreateSQL_IISFTPSiteFact = '' declare @CreateSQL_IISServerDim varchar(max) select @CreateSQL_IISServerDim = '' declare @CreateSQL_IISServerFact varchar(max) select @CreateSQL_IISServerFact = '' declare @CreateSQL_IISVirtualDirectoryDim varchar(max) select @CreateSQL_IISVirtualDirectoryDim = '' declare @CreateSQL_IISWebSiteDim varchar(max) select @CreateSQL_IISWebSiteDim = '' declare @CreateSQL_InventoryClassDim varchar(max) select @CreateSQL_InventoryClassDim = '' declare @CreateSQL_InventoryForwardingFact varchar(max) select @CreateSQL_InventoryForwardingFact = '' declare @CreateSQL_InventoryUpdatedSinceForwardedFact varchar(max) select @CreateSQL_InventoryUpdatedSinceForwardedFact = '' declare @CreateSQL_InventoryUpdateFact varchar(max) select @CreateSQL_InventoryUpdateFact = '' declare @CreateSQL_InstalledPatchFact varchar(max) select @CreateSQL_InstalledPatchFact = '' declare @CreateSQL_InstalledFileFact varchar(max) select @CreateSQL_InstalledFileFact = '' declare @CreateSQL_InstalledSoftwareFact varchar(max) select @CreateSQL_InstalledSoftwareFact = '' declare @CreateSQL_LogicalDiskDim varchar(max) select @CreateSQL_LogicalDiskDim = '' declare @CreateSQL_LogicalDiskFact varchar(max) select @CreateSQL_LogicalDiskFact = '' declare @CreateSQL_MonitorRuleDim varchar(max) select @CreateSQL_MonitorRuleDim = '' declare @CreateSQL_MonitorMetricDataFact varchar(max) select @CreateSQL_MonitorMetricDataFact = '' declare @CreateSQL_MonitorMetricDetailLevelDim varchar(max) select @CreateSQL_MonitorMetricDetailLevelDim = '' declare @CreateSQL_MonitorMetricDim varchar(max) select @CreateSQL_MonitorMetricDim = '' declare @CreateSQL_MonitorMetricInstanceDim varchar(max) select @CreateSQL_MonitorMetricInstanceDim = '' declare @CreateSQL_MonitorMetricSourceDim varchar(max) select @CreateSQL_MonitorMetricSourceDim = '' declare @CreateSQL_MonitorNtEventCategoryDim varchar(max) select @CreateSQL_MonitorNtEventCategoryDim = '' declare @CreateSQL_MonitorNtEventDescriptionDim varchar(max) select @CreateSQL_MonitorNtEventDescriptionDim = '' declare @CreateSQL_MonitorNtEventIdDim varchar(max) select @CreateSQL_MonitorNtEventIdDim = '' declare @CreateSQL_MonitorNtEventLogFileDim varchar(max) select @CreateSQL_MonitorNtEventLogFileDim = '' declare @CreateSQL_MonitorNtEventMessageDLLDim varchar(max) select @CreateSQL_MonitorNtEventMessageDLLDim = '' declare @CreateSQL_MonitorNtEventRuleTriggeredDim varchar(max) select @CreateSQL_MonitorNtEventRuleTriggeredDim = '' declare @CreateSQL_MonitorNtEventsFact varchar(max) select @CreateSQL_MonitorNtEventsFact = '' declare @CreateSQL_MonitorNtEventSourceDim varchar(max) select @CreateSQL_MonitorNtEventSourceDim = '' declare @CreateSQL_MonitorNtEventTypeDim varchar(max) select @CreateSQL_MonitorNtEventTypeDim = '' declare @CreateSQL_MonitorNtEventUserDim varchar(max) select @CreateSQL_MonitorNtEventUserDim = '' declare @CreateSQL_MonitorPolicyDim varchar(max) select @CreateSQL_MonitorPolicyDim = '' declare @CreateSQL_MonitorProcessFact varchar(max) select @CreateSQL_MonitorProcessFact = '' declare @CreateSQL_MonitorProcessNameDim varchar(max) select @CreateSQL_MonitorProcessNameDim = '' declare @CreateSQL_MonitorProcessOwnerDim varchar(max) select @CreateSQL_MonitorProcessOwnerDim = '' declare @CreateSQL_MonitorTaskFact varchar(max) select @CreateSQL_MonitorTaskFact = '' declare @CreateSQL_PackageDim varchar(max) select @CreateSQL_PackageDim = '' declare @CreateSQL_PackageDownloadSourceDim varchar(max) select @CreateSQL_PackageDownloadSourceDim = '' declare @CreateSQL_PackageDownloadMethodDim varchar(max) select @CreateSQL_PackageDownloadMethodDim = '' declare @CreateSQL_PackageServerConfigurationRequestFact varchar(max) select @CreateSQL_PackageServerConfigurationRequestFact = '' declare @CreateSQL_PackageServerDim varchar(max) select @CreateSQL_PackageServerDim = '' declare @CreateSQL_PackageServerPackageDistributionEventDim varchar(max) select @CreateSQL_PackageServerPackageDistributionEventDim = '' declare @CreateSQL_PackageServerPackageDistributionEventFact varchar(max) select @CreateSQL_PackageServerPackageDistributionEventFact = '' declare @CreateSQL_PackageServerPackageStatusDim varchar(max) select @CreateSQL_PackageServerPackageStatusDim = '' declare @CreateSQL_PackageServerPackageStatusFact varchar(max) select @CreateSQL_PackageServerPackageStatusFact = '' declare @CreateSQL_ParentTaskInstancesDim varchar(max) select @CreateSQL_ParentTaskInstancesDim = '' declare @CreateSQL_PatchManagementSoftwareUpdateDim varchar(max) select @CreateSQL_PatchManagementSoftwareUpdateDim = '' declare @CreateSQL_PatchVulnerabilityFact varchar(max) select @CreateSQL_PatchVulnerabilityFact = '' declare @CreateSQL_PhysicalMemoryArrayFact varchar(max) select @CreateSQL_PhysicalMemoryArrayFact = '' declare @CreateSQL_PhysicalMemoryFact varchar(max) select @CreateSQL_PhysicalMemoryFact = '' declare @CreateSQL_ProcessorDim varchar(max) select @CreateSQL_ProcessorDim = '' declare @CreateSQL_ProcessorFact varchar(max) select @CreateSQL_ProcessorFact = '' declare @CreateSQL_ResourceDim varchar(max) select @CreateSQL_ResourceDim = '' declare @CreateSQL_SiteDim varchar(max) select @CreateSQL_SiteDim = '' declare @CreateSQL_SoftwareComponentDim varchar(max) select @CreateSQL_SoftwareComponentDim = '' declare @CreateSQL_SoftwareComponentFileFact varchar(max) select @CreateSQL_SoftwareComponentFileFact = '' declare @CreateSQL_SoftwareDeliveryExecutionEventCommandLineDim varchar(max) select @CreateSQL_SoftwareDeliveryExecutionEventCommandLineDim = '' declare @CreateSQL_SoftwareDeliveryExecutionEventFact varchar(max) select @CreateSQL_SoftwareDeliveryExecutionEventFact = '' declare @CreateSQL_SoftwareDeliveryExecutionEventStatusDim varchar(max) select @CreateSQL_SoftwareDeliveryExecutionEventStatusDim = '' declare @CreateSQL_SoftwareDeliveryPackageEventFact varchar(max) select @CreateSQL_SoftwareDeliveryPackageEventFact = '' declare @CreateSQL_SoftwareDeliveryPackageEventStatusDim varchar(max) select @CreateSQL_SoftwareDeliveryPackageEventStatusDim = '' declare @CreateSQL_SoftwareDeliveryPackageEventURLDim varchar(max) select @CreateSQL_SoftwareDeliveryPackageEventURLDim = '' declare @CreateSQL_SoftwareDeliveryStatusEventFact varchar(max) select @CreateSQL_SoftwareDeliveryStatusEventFact = '' declare @CreateSQL_SoftwareDeliveryStatusEventStatusDim varchar(max) select @CreateSQL_SoftwareDeliveryStatusEventStatusDim = '' declare @CreateSQL_SoftwareDeliveryStatusEventTypeDim varchar(max) select @CreateSQL_SoftwareDeliveryStatusEventTypeDim = '' declare @CreateSQL_SoftwareProductComponentFact varchar(max) select @CreateSQL_SoftwareProductComponentFact = '' declare @CreateSQL_SQLClusterDim varchar(max) select @CreateSQL_SQLClusterDim = '' declare @CreateSQL_SQLClusterFact varchar(max) select @CreateSQL_SQLClusterFact = '' declare @CreateSQL_SQLClusterResourceDim varchar(max) select @CreateSQL_SQLClusterResourceDim = '' declare @CreateSQL_SQLDatabaseDim varchar(max) select @CreateSQL_SQLDatabaseDim = '' declare @CreateSQL_SQLDatabaseFact varchar(max) select @CreateSQL_SQLDatabaseFact = '' declare @CreateSQL_SQLDatabaseSystemDim varchar(max) select @CreateSQL_SQLDatabaseSystemDim = '' declare @CreateSQL_SQLStorageAreaDim varchar(max) select @CreateSQL_SQLStorageAreaDim = '' declare @CreateSQL_SQLUserDim varchar(max) select @CreateSQL_SQLUserDim = '' declare @CreateSQL_SQLUserFact varchar(max) select @CreateSQL_SQLUserFact = '' declare @CreateSQL_SubnetDim varchar(max) select @CreateSQL_SubnetDim = '' declare @CreateSQL_TaskDim varchar(max) select @CreateSQL_TaskDim = '' declare @CreateSQL_TaskInstancesFact varchar(max) select @CreateSQL_TaskInstancesFact = '' declare @CreateSQL_TaskServerDim varchar(max) select @CreateSQL_TaskServerDim = '' declare @CreateSQL_WindowsFileDim varchar(max) select @CreateSQL_WindowsFileDim = '' declare @CreateSQL_fnNonInventoriedInstallFact varchar(max) select @CreateSQL_fnNonInventoriedInstallFact = '' declare @CreateSQL_fnBorrowedLicenseFact varchar(max) select @CreateSQL_fnBorrowedLicenseFact = '' declare @CreateSQL_fnDonatedLicenseFact varchar(max) select @CreateSQL_fnDonatedLicenseFact = '' end begin -- Declare Union SQL variables declare @Union_OrganizationalGroupDim varchar(max) select @Union_OrganizationalGroupDim = '(' declare @Union_OrganizationalGroupFact varchar(max) select @Union_OrganizationalGroupFact = '(' declare @Union_UserDim varchar(max) select @Union_UserDim = '(' declare @Union_CollectionDim varchar(max) select @Union_CollectionDim = '(' declare @Union_PackageDim varchar(max) select @Union_PackageDim = '(' declare @Union_PackageServerConfigurationRequestFact varchar(max) select @Union_PackageServerConfigurationRequestFact = '(' declare @Union_PackageDownloadSourceDim varchar(max) select @Union_PackageDownloadSourceDim = '(' declare @Union_PackageDownloadMethodDim varchar(max) select @Union_PackageDownloadMethodDim = '(' declare @Union_PackageServerDim varchar(max) select @Union_PackageServerDim = '(' declare @Union_PackageServerPackageDistributionEventDim varchar(max) select @Union_PackageServerPackageDistributionEventDim = '(' declare @Union_PackageServerPackageDistributionEventFact varchar(max) select @Union_PackageServerPackageDistributionEventFact = '(' declare @Union_PackageServerPackageStatusDim varchar(max) select @Union_PackageServerPackageStatusDim = '(' declare @Union_PackageServerPackageStatusFact varchar(max) select @Union_PackageServerPackageStatusFact = '(' declare @Union_CollectionMembershipFact varchar(max) select @Union_CollectionMembershipFact = '(' declare @Union_AdvertisementDim varchar(max) select @Union_AdvertisementDim = '(' declare @Union_AltirisAgentConfigurationRequestEventFact varchar(max) select @Union_AltirisAgentConfigurationRequestEventFact = '(' declare @Union_ForwardServerDim varchar(max) select @Union_ForwardServerDim = '(' declare @Union_InventoryClassDim varchar(max) select @Union_InventoryClassDim = '(' declare @Union_ApplicationMeteringFact varchar(max) select @Union_ApplicationMeteringFact = '(' declare @Union_InventoryForwardingFact varchar(max) select @Union_InventoryForwardingFact = '(' declare @Union_InventoryUpdatedSinceForwardedFact varchar(max) select @Union_InventoryUpdatedSinceForwardedFact = '(' declare @Union_InventoryUpdateFact varchar(max) select @Union_InventoryUpdateFact = '(' declare @Union_ResourceDim varchar(max) select @Union_ResourceDim = '(' declare @Union_SiteDim varchar(max) select @Union_SiteDim = '(' declare @Union_SoftwareDeliveryExecutionEventCommandLineDim varchar(max) select @Union_SoftwareDeliveryExecutionEventCommandLineDim = '(' declare @Union_SoftwareDeliveryExecutionEventFact varchar(max) select @Union_SoftwareDeliveryExecutionEventFact = '(' declare @Union_SoftwareDeliveryExecutionEventStatusDim varchar(max) select @Union_SoftwareDeliveryExecutionEventStatusDim = '(' declare @Union_SoftwareDeliveryPackageEventFact varchar(max) select @Union_SoftwareDeliveryPackageEventFact = '(' declare @Union_SoftwareDeliveryPackageEventStatusDim varchar(max) select @Union_SoftwareDeliveryPackageEventStatusDim = '(' declare @Union_SoftwareDeliveryPackageEventURLDim varchar(max) select @Union_SoftwareDeliveryPackageEventURLDim = '(' declare @Union_SoftwareDeliveryStatusEventFact varchar(max) select @Union_SoftwareDeliveryStatusEventFact = '(' declare @Union_SoftwareDeliveryStatusEventStatusDim varchar(max) select @Union_SoftwareDeliveryStatusEventStatusDim = '(' declare @Union_SoftwareDeliveryStatusEventTypeDim varchar(max) select @Union_SoftwareDeliveryStatusEventTypeDim = '(' declare @Union_SubnetDim varchar(max) select @Union_SubnetDim = '(' declare @Union_InstalledFileFact varchar(max) select @Union_InstalledFileFact = '(' declare @Union_InstalledSoftwareFact varchar(max) select @Union_InstalledSoftwareFact = '(' declare @Union_ComputerDim varchar(max) select @Union_ComputerDim = '(' declare @Union_ComputerFact varchar(max) select @Union_ComputerFact = '(' declare @Union_ApplicablePatchFact varchar(max) select @Union_ApplicablePatchFact = '(' declare @Union_InstalledPatchFact varchar(max) select @Union_InstalledPatchFact = '(' declare @Union_PatchVulnerabilityFact varchar(max) select @Union_PatchVulnerabilityFact = '(' declare @Union_PatchManagementSoftwareUpdateDim varchar(max) select @Union_PatchManagementSoftwareUpdateDim = '(' declare @Union_AddRemoveProgramsDim varchar(max) select @Union_AddRemoveProgramsDim = '(' declare @Union_WindowsFileDim varchar(max) select @Union_WindowsFileDim = '(' declare @Union_LogicalDiskDim varchar(max) select @Union_LogicalDiskDim = '(' declare @Union_LogicalDiskFact varchar(max) select @Union_LogicalDiskFact = '(' declare @Union_PhysicalMemoryArrayFact varchar(max) select @Union_PhysicalMemoryArrayFact = '(' declare @Union_PhysicalMemoryFact varchar(max) select @Union_PhysicalMemoryFact = '(' declare @Union_ProcessorDim varchar(max) select @Union_ProcessorDim = '(' declare @Union_ProcessorFact varchar(max) select @Union_ProcessorFact = '(' declare @Union_SoftwareComponentDim varchar(max) select @Union_SoftwareComponentDim = '(' declare @Union_SoftwareComponentFileFact varchar(max) select @Union_SoftwareComponentFileFact = '(' declare @Union_SoftwareProductDim varchar(max) select @Union_SoftwareProductDim = '(' declare @Union_SoftwareProductComponentFact varchar(max) select @Union_SoftwareProductComponentFact = '(' declare @Union_AlertDim varchar(max) select @Union_AlertDim = '(' declare @Union_AlertFact varchar(max) select @Union_AlertFact = '(' declare @Union_AlertActionAuditFact varchar(max) select @Union_AlertActionAuditFact = '(' declare @Union_AlertActionAuditTypeDim varchar(max) select @Union_AlertActionAuditTypeDim = '(' declare @Union_AlertCategoryDim varchar(max) select @Union_AlertCategoryDim = '(' declare @Union_AlertProtocolDim varchar(max) select @Union_AlertProtocolDim = '(' declare @Union_AlertSeverityDim varchar(max) select @Union_AlertSeverityDim = '(' declare @Union_MonitorRuleDim varchar(max) select @Union_MonitorRuleDim = '(' declare @Union_MonitorMetricDataFact varchar(max) select @Union_MonitorMetricDataFact = '(' declare @Union_MonitorMetricDim varchar(max) select @Union_MonitorMetricDim = '(' declare @Union_MonitorMetricInstanceDim varchar(max) select @Union_MonitorMetricInstanceDim = '(' declare @Union_MonitorMetricSourceDim varchar(max) select @Union_MonitorMetricSourceDim = '(' declare @Union_MonitorNtEventCategoryDim varchar(max) select @Union_MonitorNtEventCategoryDim = '(' declare @Union_MonitorNtEventDescriptionDim varchar(max) select @Union_MonitorNtEventDescriptionDim = '(' declare @Union_MonitorNtEventIdDim varchar(max) select @Union_MonitorNtEventIdDim = '(' declare @Union_MonitorNtEventLogFileDim varchar(max) select @Union_MonitorNtEventLogFileDim = '(' declare @Union_MonitorNtEventMessageDLLDim varchar(max) select @Union_MonitorNtEventMessageDLLDim = '(' declare @Union_MonitorNtEventsFact varchar(max) select @Union_MonitorNtEventsFact = '(' declare @Union_MonitorNtEventSourceDim varchar(max) select @Union_MonitorNtEventSourceDim = '(' declare @Union_MonitorNtEventTypeDim varchar(max) select @Union_MonitorNtEventTypeDim = '(' declare @Union_MonitorNtEventUserDim varchar(max) select @Union_MonitorNtEventUserDim = '(' declare @Union_MonitorPolicyDim varchar(max) select @Union_MonitorPolicyDim = '(' declare @Union_MonitorProcessFact varchar(max) select @Union_MonitorProcessFact = '(' declare @Union_MonitorProcessNameDim varchar(max) select @Union_MonitorProcessNameDim = '(' declare @Union_MonitorProcessOwnerDim varchar(max) select @Union_MonitorProcessOwnerDim = '(' declare @Union_MonitorTaskFact varchar(max) select @Union_MonitorTaskFact = '(' declare @Union_ESXServerFact varchar(max) select @Union_ESXServerFact = '(' declare @Union_ESXStorageVolumeDim varchar(max) select @Union_ESXStorageVolumeDim = '(' declare @Union_ESXVirtualMachineDim varchar(max) select @Union_ESXVirtualMachineDim = '(' declare @Union_IISFTPSiteDim varchar(max) select @Union_IISFTPSiteDim = '(' declare @Union_IISFTPSiteFact varchar(max) select @Union_IISFTPSiteFact = '(' declare @Union_IISServerDim varchar(max) select @Union_IISServerDim = '(' declare @Union_IISServerFact varchar(max) select @Union_IISServerFact = '(' declare @Union_IISVirtualDirectoryDim varchar(max) select @Union_IISVirtualDirectoryDim = '(' declare @Union_IISWebSiteDim varchar(max) select @Union_IISWebSiteDim = '(' declare @Union_SQLClusterDim varchar(max) select @Union_SQLClusterDim = '(' declare @Union_SQLClusterFact varchar(max) select @Union_SQLClusterFact = '(' declare @Union_SQLClusterResourceDim varchar(max) select @Union_SQLClusterResourceDim = '(' declare @Union_SQLDatabaseSystemDim varchar(max) select @Union_SQLDatabaseSystemDim = '(' declare @Union_SQLDatabaseDim varchar(max) select @Union_SQLDatabaseDim = '(' declare @Union_SQLDatabaseFact varchar(max) select @Union_SQLDatabaseFact = '(' declare @Union_SQLStorageAreaDim varchar(max) select @Union_SQLStorageAreaDim = '(' declare @Union_SQLUserDim varchar(max) select @Union_SQLUserDim = '(' declare @Union_SQLUserFact varchar(max) select @Union_SQLUserFact = '(' declare @Union_TaskDim varchar(max) select @Union_TaskDim = '(' declare @Union_TaskInstancesFact varchar(max) select @Union_TaskInstancesFact = '(' declare @Union_TaskServerDim varchar(max) select @Union_TaskServerDim = '(' declare @Union_ParentTaskInstancesDim varchar(max) select @Union_ParentTaskInstancesDim = '(' declare @Union_AssetCostCenterFact varchar(max) select @Union_AssetCostCenterFact = '(' declare @Union_AssetDim varchar(max) select @Union_AssetDim = '(' declare @Union_AssetFact varchar(max) select @Union_AssetFact = '(' declare @Union_AssetStatusDim varchar(max) select @Union_AssetStatusDim = '(' declare @Union_AssetTypeDim varchar(max) select @Union_AssetTypeDim = '(' declare @Union_CostCenterDim varchar(max) select @Union_CostCenterDim = '(' declare @Union_DepartmentDim varchar(max) select @Union_DepartmentDim = '(' declare @Union_LocationDim varchar(max) select @Union_LocationDim = '(' declare @Union_SoftwareLicenseDim varchar(max) select @Union_SoftwareLicenseDim = '(' declare @Union_SoftwareLicenseInstallationEvaluationFact varchar(max) select @Union_SoftwareLicenseInstallationEvaluationFact = '(' declare @Union_SoftwareLicenseInstallationFact varchar(max) select @Union_SoftwareLicenseInstallationFact = '(' declare @Union_SoftwareLicenseOwnedFact varchar(max) select @Union_SoftwareLicenseOwnedFact = '(' declare @Union_SoftwareLicenseUsageEvaluationFact varchar(max) select @Union_SoftwareLicenseUsageEvaluationFact = '(' declare @Union_SoftwareLicenseUsageFact varchar(max) select @Union_SoftwareLicenseUsageFact = '(' declare @Union_SoftwarePurchaseDim varchar(max) select @Union_SoftwarePurchaseDim = '(' declare @Union_SoftwarePurchaseFact varchar(max) select @Union_SoftwarePurchaseFact = '(' declare @Union_NonInventoriedInstallFact varchar(max) select @Union_NonInventoriedInstallFact = '' declare @Union_BorrowedLicenseCountFact varchar(max) select @Union_BorrowedLicenseCountFact = '' declare @Union_DonatedLicenseFact varchar(max) select @Union_DonatedLicenseFact = '' declare @Union_SoftwareProductLicenseFact varchar(max) select @Union_SoftwareProductLicenseFact = '(' end begin -- Declare Source SQL variables declare @Source_vAsset varchar(max) declare @Source_ResourceAssociation varchar(max) declare @Source_vAssetList varchar(max) declare @Source_vFixedAssetStatus varchar(max) declare @Source_vCostCenter varchar(max) declare @Source_vDepartment varchar(max) declare @Source_vLocation varchar(max) declare @Source_vSoftwareLicense varchar(max) declare @Source_InvHist_SoftwareProduct_InstallationInfo varchar(max) declare @Source_vCollection varchar(max) declare @Source_vUser varchar(max) declare @Source_vSoftwarePurchase varchar(max) declare @Source_Inv_Software_Purchase_Cost_Center_Ownership varchar(max) declare @Source_Inv_Software_Purchase_Owners varchar(max) declare @Source_InvHist_Non_Inventoried_Installs_Delta varchar(max) declare @Source_AssetCostCenterFact varchar(max) declare @Source_InvHist_Software_License_Sharing_Delta varchar(max) declare @Source_ScopeMembership varchar(max) declare @Source_Item varchar(max) declare @Source_SWDPackage varchar(max) declare @Source_Evt_NS_Client_Config_Request varchar(max) declare @Source_Inv_AeX_AC_Identification varchar(max) declare @Source_SWDPackageServer varchar(max) declare @Source_Evt_AeX_SWD_Package varchar(max) declare @Source_vNotificationServerSource varchar(max) declare @Source_vComputer varchar(max) declare @Source_CollectionMembership varchar(max) declare @Source_Evt_AeX_Package_Server_Package_Event varchar(max) declare @Source_SWDAdvertisement varchar(max) declare @Source_vResourceItem varchar(max) declare @Source_ForwardServer varchar(max) declare @Source_vInventoryClass varchar(max) declare @Source_ItemFolder varchar(max) declare @Source_vFolder varchar(max) declare @Source_ForwardingHistorySummary varchar(max) declare @Source_DataClass varchar(max) declare @Source_vResource varchar(max) declare @Source_ResourceUpdateSummary varchar(max) declare @Source_vResourceType varchar(max) declare @Source_Evt_AeX_SWD_Execution varchar(max) declare @Source_Evt_AeX_SWD_Status varchar(max) declare @Source_Inv_Subnet varchar(max) declare @Source_Inv_Installed_File_Details varchar(max) declare @Source_Inv_AddRemoveProgram varchar(max) declare @Source_Inv_InstalledSoftware varchar(max) declare @Source_vRM_Computer_Item varchar(max) declare @Source_vSource varchar(max) declare @Source_Inv_AeX_AC_Discovery varchar(max) declare @Source_Inv_AeX_AC_Primary_User varchar(max) declare @Source_Inv_AeX_AC_TCPIP varchar(max) declare @Source_vHWChassis varchar(max) declare @Source_vHWComputerSystem varchar(max) declare @Source_vComputerResource varchar(max) declare @Source_vWindowsFile varchar(max) declare @Source_vSoftwareProduct varchar(max) declare @Source_vSoftwareComponentSearch varchar(max) declare @Source_Inv_HW_Processor varchar(max) declare @Source_Inv_HW_Logical_Device varchar(max) declare @Source_Inv_HW_Physical_Memory varchar(max) declare @Source_Inv_HW_Physical_Memory_Array varchar(max) declare @Source_Inv_HW_Logical_Disk varchar(max) declare @Source_ec_alert varchar(max) declare @Source_ec_alert_variable varchar(max) declare @Source_ec_alert_action_audit varchar(max) declare @Source_ec_alert_action_audit_type varchar(max) declare @Source_ItemClass varchar(max) declare @Source_PPA_AlertProtocol varchar(max) declare @Source_PPA_AlertSeverity varchar(max) declare @Source_ItemReference varchar(max) declare @Source_MonitorMetricDailySummary varchar(max) declare @Source_MonitorMetricInstances varchar(max) declare @Source_MonitorMetricHourlySummary varchar(max) declare @Source_MonitorMetricData varchar(max) declare @Source_ClassBaseClass varchar(max) declare @Source_MonitorNtEvents varchar(max) declare @Source_MonitorProcessData varchar(max) declare @Source_MonitorProcessNames varchar(max) declare @Source_MonitorProcessOwners varchar(max) declare @Source_Evt_Task_Instances varchar(max) declare @Source_Inv_UNIX_Hosted_VMware_ESX_Storage_Volume varchar(max) declare @Source_Inv_UNIX_Hosted_Dependency varchar(max) declare @Source_Inv_UNIX_Stored_VMware_ESX_Virtual_Computer_System varchar(max) declare @Source_Inv_UNIX_Virtual_Computer_System varchar(max) declare @Source_Inv_UNIX_VMware_ESX_Storage_Volume varchar(max) declare @Source_Inv_UNIX_Settings_Define_VMware_ESX varchar(max) declare @Source_Inv_UNIX_Virtual_System_Setting_Data varchar(max) declare @Source_Inv_UNIX_Settings_Define_Virtual_Computer_System varchar(max) declare @Source_Inv_UNIX_Virtual_Logical_Device varchar(max) declare @Source_Inv_UNIX_Virtual_System_Device varchar(max) declare @Source_Inv_UNIX_Concrete_Virtual_Component varchar(max) declare @Source_Inv_UNIX_Resource_Allocation_Setting_Data varchar(max) declare @Source_Inv_IIS_FTP_Site_Details varchar(max) declare @Source_Inv_IIS_Setting varchar(max) declare @Source_Inv_IIS_Root_VirtualDir varchar(max) declare @Source_Inv_IIS_Http_Host_Setting_Data varchar(max) declare @Source_Inv_IISHttp_VirtualDir_Setting_Data varchar(max) declare @Source_Inv_Cluster varchar(max) declare @Source_Inv_Associate_Cluster_To_Node varchar(max) declare @Source_Inv_Cluster_Resource_SQL_Server varchar(max) declare @Source_Inv_Associate_Database_System_to_Service varchar(max) declare @Source_Inv_Database_System varchar(max) declare @Source_Inv_MS_SQL_Server_Processors varchar(max) declare @Source_Inv_SQL_Server_License varchar(max) declare @Source_Inv_Database varchar(max) declare @Source_Inv_MS_SQL_Server_Databases varchar(max) declare @Source_Inv_Associate_Database_To_Service varchar(max) declare @Source_Inv_MS_SQL_Server_Transaction_Logs varchar(max) declare @Source_Inv_Database_Storage_Area varchar(max) declare @Source_Inv_Associate_Database_Service_to_Storage varchar(max) declare @Source_Inv_Associate_Database_to_Database_Storage_Area varchar(max) declare @Source_Inv_Database_User varchar(max) declare @Source_Inv_Associate_Database_User_to_Database varchar(max) declare @Source_Inv_Monthly_summary varchar(max) declare @Source_InvHist_Monthly_summary varchar(max) declare @Source_vPMCore_SoftwareUpdate varchar(max) declare @Source_Inv_Software_Bulletin varchar(max) declare @Source_vResourceEx varchar(max) declare @Source_vCompany varchar(max) declare @Source_Inv_Applicable_Microsoft_Software_Update varchar(max) declare @Source_Inv_Applicable_Adobe_Software_Update varchar(max) declare @Source_Inv_Installed_Microsoft_Software_Update varchar(max) declare @Source_Inv_Installed_Adobe_Software_Update varchar(max) declare @Source_Inv_Applicable_Novell_Software_Update varchar(max) declare @Source_Inv_Applicable_Red_Hat_Software_Update varchar(max) declare @Source_Inv_Installed_Novell_Software_Update varchar(max) declare @Source_Inv_Installed_Red_Hat_Software_Update varchar(max) declare @Source_vSoftwareUpdateInventoryRuleAssociations varchar(max) declare @Source_vRM_Asset_Item varchar(max) declare @Source_ResourceType varchar(max) declare @Source_Inv_Manufacturer varchar(max) declare @Source_Inv_Serial_Number varchar(max) declare @Source_Inv_Identity varchar(max) declare @Source_Inv_Barcode varchar(max) declare @Source_ITAnalytics_DateDim varchar(max) select @ServerName = @@ServerName select @DatabaseName = DB_NAME() exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'ITAnalytics_DateDim', @Source_ITAnalytics_DateDim OUTPUT end if exists ( select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#DependencyMap') ) DROP TABLE #DependencyMap; select srv.name as [ServerName] , srv.catalog as [DatabaseName] , 1 as [SymantecManagementPlatformDSV] , 1 as [AssetDSV] , 1 as [SoftwareLicenseDSV] , 1 as [PatchManagementDSV] , 1 as [ClientInventoryDSV] , 1 as [EventConsoleDSV] , 1 as [MonitorDSV] , 1 as [ServerInventoryDSV] , 1 as [TaskDSV] , convert(nvarchar(250),'http://localhost/Altiris/Console/Dashboard/DashboardView.aspx') AS [ResourceManagerURL] , convert(nvarchar(250),'http://localhost/Altiris/AssetContractCommon/Manager/EditCreateResource.aspx') AS [ResourceEditURL] into #DependencyMap from sys.servers srv WHERE srv.name LIKE 'ITANALYTICS_CMDB_' + UPPER(DB_NAME()) + '[_]%' if @UseHostCMDB = 1 begin insert into #DependencyMap ([ServerName], [DatabaseName], [SymantecManagementPlatformDSV], [AssetDSV], [SoftwareLicenseDSV], [PatchManagementDSV], [ClientInventoryDSV], [EventConsoleDSV], [MonitorDSV], [ServerInventoryDSV], [TaskDSV], [ResourceManagerURL], [ResourceEditURL]) values (@@ServerName,DB_NAME(),1,1,1,1,1,1,1,1,1, @LocalResourceManagerURL, @LocalResourceEditURL) end declare @dependencyCount as integer DECLARE @ParmDefinition nvarchar(1000); SET @ParmDefinition = N'@dependencyCount integer OUTPUT'; declare DependencyMapCursor cursor forward_only for select [ServerName], [DatabaseName] from #DependencyMap open DependencyMapCursor fetch next from DependencyMapCursor into @ServerName, @DatabaseName while @@fetch_status = 0 begin select @thisResourceManagerURL = xmlPolicy.LinkedServers.value('(./@ResourceManagerURL)[1]', 'nvarchar(250)') , @thisResourceEditURL = xmlPolicy.LinkedServers.value('(./@ResourceEditURL)[1]', 'nvarchar(250)') from ( select convert(xml,xmlPolicy.CMDB.value('(./LinkedServers)[1]', 'nvarchar(4000)')) as [LinkedServerXml] from ( select convert(xml,[State]) as [StateXml] from vItem where Guid = '051bbf59-285b-4917-856f-d05a7374a858' ) xmlSettings CROSS APPLY xmlSettings.[StateXml].nodes('/item') AS xmlPolicy(CMDB) ) xmlLinkedServers CROSS APPLY xmlLinkedServers.[LinkedServerXml].nodes('./LinkedServers/LinkedServer') AS xmlPolicy(LinkedServers) where xmlPolicy.LinkedServers.value('(./@LinkedServerName)[1]', 'nvarchar(250)') = @ServerName if len(@thisResourceManagerURL) > 0 and len(@thisResourceEditURL) > 0 begin update #DependencyMap set [ResourceManagerURL] = @thisResourceManagerURL, [ResourceEditURL] = @thisResourceEditURL where [ServerName] = @ServerName and [DatabaseName] = @DatabaseName end begin -- Symantec Management Platform DSV set @TmpSql = 'select @dependencyCount = count(*) from [' + @ServerName + '].[' + @DatabaseName + '].[sys].[sysobjects] where (type = ''V'' or type = ''U'') and name in ( ''Collection'' ,''CollectionMembership'' ,''Evt_AeX_Package_Server_Package_Event'' ,''Evt_AeX_SWD_Execution'' ,''Evt_AeX_SWD_Package'' ,''Evt_AeX_SWD_Status'' ,''Evt_NS_Client_Config_Request'' ,''Inv_AeX_AC_Discovery'' ,''Inv_AeX_AC_Identification'' ,''Inv_AeX_AC_Primary_User'' ,''Inv_AeX_AC_TCPIP'' ,''Inv_HW_Logical_Disk'' ,''ItemFolder'' ,''SWDAdvertisement'' ,''SWDPackageServer'' ,''SWDPackage'' ,''vComputer'' ,''vHWChassis'' ,''vHWComputerSystem'' ,''vInventoryClass'' ,''vFolder'' ,''vNotificationServerSource'' ,''vResource'' ,''vResourceItem'' ,''vResourceType'' ,''vRM_Computer_Item'' ,''vSource'' ,''Item'' ,''ScopeMembership'' ,''vUser'' )' EXEC sp_executesql @TmpSql, @ParmDefinition, @dependencyCount OUTPUT if @dependencyCount < 30 update #DependencyMap set [SymantecManagementPlatformDSV] = 0 where [ServerName] = @ServerName and [DatabaseName] = @DatabaseName end begin -- Asset DSV set @TmpSql = 'select @dependencyCount = count(*) from [' + @ServerName + '].[' + @DatabaseName + '].[sys].[sysobjects] where (type = ''V'' or type = ''U'') and name in ( ''vAsset'' ,''ResourceAssociation'' ,''vAssetList'' ,''vFixedAssetStatus'' ,''vCostCenter'' ,''vDepartment'' ,''vLocation'' ,''vUser'' )' --print @TmpSql EXEC sp_executesql @TmpSql, @ParmDefinition, @dependencyCount OUTPUT if @dependencyCount < 8 update #DependencyMap set [AssetDSV] = 0 where [ServerName] = @ServerName and [DatabaseName] = @DatabaseName end begin -- Software License DSV set @TmpSql = 'select @dependencyCount = count(*) from [' + @ServerName + '].[' + @DatabaseName + '].[sys].[sysobjects] where (type = ''V'' or type = ''U'') and name in ( ''vAsset'' ,''ResourceAssociation'' ,''vSoftwareLicense'' ,''vCostCenter'' ,''vDepartment'' ,''vLocation'' ,''InvHist_SoftwareProduct_InstallationInfo'' ,''vSoftwarePurchase'' ,''Inv_Software_Purchase_Cost_Center_Ownership'' ,''Inv_Software_Purchase_Owners'' ,''vUser'' ,''vSoftwareProduct'' )' EXEC sp_executesql @TmpSql, @ParmDefinition, @dependencyCount OUTPUT if @dependencyCount < 12 update #DependencyMap set [SoftwareLicenseDSV] = 0 where [ServerName] = @ServerName and [DatabaseName] = @DatabaseName end begin -- Patch Management DSV set @TmpSql = 'select @dependencyCount = count(*) from [' + @ServerName + '].[' + @DatabaseName + '].[sys].[sysobjects] where (type = ''V'' or type = ''U'') and name in ( ''Inv_AddRemoveProgram'' ,''Inv_AeX_AC_Identification'' ,''Inv_AeX_AC_Discovery'' ,''Inv_Installed_File_Details'' ,''Inv_InstalledSoftware'' ,''Inv_HW_Logical_Disk'' ,''Inv_HW_Physical_Memory_Array'' ,''Inv_HW_Physical_Memory'' ,''Inv_HW_Processor'' ,''Inv_Monthly_summary'' ,''vComputerResource'' ,''vPMCore_SoftwareUpdate'' ,''vPMCore_SeverityRating'' ,''ResourceAssociation'' ,''vResourceItem'' ,''vSoftwareUpdateInventoryRuleAssociations'' ,''Inv_Applicable_Microsoft_Software_Update'' ,''Inv_Installed_Microsoft_Software_Update'' ,''Inv_Applicable_Adobe_Software_Update'' ,''Inv_Installed_Adobe_Software_Update'' )' EXEC sp_executesql @TmpSql, @ParmDefinition, @dependencyCount OUTPUT if @dependencyCount < 20 update #DependencyMap set [PatchManagementDSV] = 0 where [ServerName] = @ServerName and [DatabaseName] = @DatabaseName end begin -- Client Inventory DSV set @TmpSql = 'select @dependencyCount = count(*) from [' + @ServerName + '].[' + @DatabaseName + '].[sys].[sysobjects] where (type = ''V'' or type = ''U'') and name in ( ''Inv_AddRemoveProgram'' ,''Inv_AeX_AC_Identification'' ,''Inv_AeX_AC_Discovery'' ,''Inv_Installed_File_Details'' ,''Inv_InstalledSoftware'' ,''Inv_HW_Logical_Disk'' ,''Inv_HW_Physical_Memory_Array'' ,''Inv_HW_Physical_Memory'' ,''Inv_HW_Processor'' ,''Inv_Monthly_summary'' ,''vComputerResource'' ,''vRM_Computer_Item'' ,''vOSOperatingSystem'' ,''vSoftwareComponentSearch'' ,''vWindowsFile'' )' EXEC sp_executesql @TmpSql, @ParmDefinition, @dependencyCount OUTPUT if @dependencyCount < 15 update #DependencyMap set [ClientInventoryDSV] = 0 where [ServerName] = @ServerName and [DatabaseName] = @DatabaseName end begin -- Event Console DSV set @TmpSql = 'select @dependencyCount = count(*) from [' + @ServerName + '].[' + @DatabaseName + '].[sys].[sysobjects] where (type = ''V'' or type = ''U'') and name in ( ''Collection'' ,''CollectionMembership'' ,''ec_alert'' ,''ec_alert_action_audit'' ,''ec_alert_action_audit_type'' ,''ec_alert_variable'' ,''Inv_AeX_AC_Identification'' ,''Inv_AeX_AC_Discovery'' ,''Inv_AeX_AC_Primary_User'' ,''Inv_AeX_AC_TCPIP'' ,''Item'' ,''ItemClass'' ,''ItemFolder'' ,''ItemReference'' ,''PPA_AlertDefinition'' ,''PPA_AlertProtocol'' ,''PPA_AlertSeverity'' ,''vComputer'' ,''vRM_Computer_Item'' ,''vSource'' )' EXEC sp_executesql @TmpSql, @ParmDefinition, @dependencyCount OUTPUT if @dependencyCount < 20 update #DependencyMap set [EventConsoleDSV] = 0 where [ServerName] = @ServerName and [DatabaseName] = @DatabaseName end begin -- Monitor DSV set @TmpSql = 'select @dependencyCount = count(*) from [' + @ServerName + '].[' + @DatabaseName + '].[sys].[sysobjects] where (type = ''V'' or type = ''U'') and name in ( ''Collection'' ,''CollectionMembership'' ,''ec_alert'' ,''ec_alert_action_audit'' ,''ec_alert_action_audit_type'' ,''ec_alert_variable'' ,''Evt_Task_Instances'' ,''Inv_AeX_AC_Identification'' ,''Inv_AeX_AC_Discovery'' ,''Inv_AeX_AC_Primary_User'' ,''Inv_AeX_AC_TCPIP'' ,''Item'' ,''ItemFolder'' ,''ItemReference'' ,''MonitorMetricData'' ,''MonitorMetricHourlySummary'' ,''MonitorMetricDailySummary'' ,''MonitorNtEvents'' ,''MonitorProcessData'' ,''MonitorProcessNames'' ,''MonitorProcessOwners'' ,''PPA_AlertDefinition'' ,''PPA_AlertProtocol'' ,''PPA_AlertSeverity'' ,''ScopeMembership'' ,''vComputer'' ,''vResourceItem'' ,''vRM_Computer_Item'' ,''vSource'' )' EXEC sp_executesql @TmpSql, @ParmDefinition, @dependencyCount OUTPUT if @dependencyCount < 29 update #DependencyMap set [MonitorDSV] = 0 where [ServerName] = @ServerName and [DatabaseName] = @DatabaseName end begin -- Server Inventory DSV set @TmpSql = 'select @dependencyCount = count(*) from [' + @ServerName + '].[' + @DatabaseName + '].[sys].[sysobjects] where (type = ''V'' or type = ''U'') and name in ( ''Collection'' ,''CollectionMembership'' ,''Evt_Task_Instances'' ,''Inv_AeX_AC_Identification'' ,''Inv_AeX_AC_Discovery'' ,''Inv_AeX_AC_Primary_User'' ,''Inv_AeX_AC_TCPIP'' ,''Item'' ,''vComputer'' ,''vRM_Computer_Item'' ,''vSource'' ,''Inv_Associate_Cluster_To_Node'' ,''Inv_Associate_Database_to_Database_Storage_Area'' ,''Inv_Associate_Database_Service_to_Storage'' ,''Inv_Associate_Database_System_to_Service'' ,''Inv_Associate_Database_To_Service'' ,''Inv_Associate_Database_User_to_Database'' ,''Inv_Cluster'' ,''Inv_Cluster_Resource_SQL_Server'' ,''Inv_Database'' ,''Inv_Database_System'' ,''Inv_Database_Storage_Area'' ,''Inv_Database_User'' ,''Inv_IIS_FTP_Site_Details'' ,''Inv_IIS_Http_Host_Setting_Data'' ,''Inv_IIS_Root_VirtualDir'' ,''Inv_IIS_Setting'' ,''Inv_IISHttp_VirtualDir_Setting_Data'' ,''Inv_MS_SQL_Server_Databases'' ,''Inv_MS_SQL_Server_Processors'' ,''Inv_MS_SQL_Server_Transaction_Logs'' ,''Inv_SQL_Server_License'' ,''Inv_UNIX_Concrete_Virtual_Component'' ,''Inv_UNIX_Hosted_Dependency'' ,''Inv_UNIX_Hosted_VMware_ESX_Storage_Volume'' ,''Inv_UNIX_Resource_Allocation_Setting_Data'' ,''Inv_UNIX_Settings_Define_Virtual_Computer_System'' ,''Inv_UNIX_Settings_Define_VMware_ESX'' ,''Inv_UNIX_Stored_VMware_ESX_Virtual_Computer_System'' ,''Inv_UNIX_Virtual_Computer_System'' ,''Inv_UNIX_Virtual_Logical_Device'' ,''Inv_UNIX_Virtual_System_Device'' ,''Inv_UNIX_Virtual_System_Setting_Data'' ,''Inv_UNIX_VMware_ESX_Storage_Volume'' )' EXEC sp_executesql @TmpSql, @ParmDefinition, @dependencyCount OUTPUT if @dependencyCount < 44 update #DependencyMap set [ServerInventoryDSV] = 0 where [ServerName] = @ServerName and [DatabaseName] = @DatabaseName end begin -- Task DSV set @TmpSql = 'select @dependencyCount = count(*) from [' + @ServerName + '].[' + @DatabaseName + '].[sys].[sysobjects] where (type = ''V'' or type = ''U'') and name in ( ''Collection'' ,''CollectionMembership'' ,''Evt_Task_Instances'' ,''Inv_AeX_AC_Identification'' ,''Inv_AeX_AC_Discovery'' ,''Inv_AeX_AC_Primary_User'' ,''Inv_AeX_AC_TCPIP'' ,''Item'' ,''vComputer'' ,''vRM_Computer_Item'' ,''vSource'' )' EXEC sp_executesql @TmpSql, @ParmDefinition, @dependencyCount OUTPUT if @dependencyCount < 11 update #DependencyMap set [TaskDSV] = 0 where [ServerName] = @ServerName and [DatabaseName] = @DatabaseName end fetch next from DependencyMapCursor into @ServerName, @DatabaseName end close DependencyMapCursor deallocate DependencyMapCursor declare @StateXml as xml select @StateXml = convert(xml,[State]) from Item where Guid = '051bbf59-285b-4917-856f-d05a7374a858' SET @StateXml.modify(' delete (/item/DependencyMap[1])[1] ') declare @xmlDependencyMap xml set @xmlDependencyMap = ( SELECT [ServerName], [SymantecManagementPlatformDSV], [AssetDSV], [SoftwareLicenseDSV], [PatchManagementDSV], [ClientInventoryDSV], [EventConsoleDSV], [MonitorDSV], [ServerInventoryDSV], [TaskDSV] FROM #DependencyMap FOR XML PATH ) declare @sql nvarchar(max) set @sql = N'SET @StateXml.modify(''insert ' + convert(nvarchar(max),@xmlDependencyMap) + ' into (/item[1])[1]'')' execute sp_executesql @sql,N'@StateXml XML Output, @xmlDependencyMap XML', @StateXml Output, @xmlDependencyMap update Item set [State] = convert(nvarchar(max),@StateXml) where Guid = '051bbf59-285b-4917-856f-d05a7374a858' begin -- Define Union SQL Statements begin -- Symantec Management Platform DSV select @FirstServer = 1 declare LinkedServerCursor cursor forward_only for select [ServerName], [DatabaseName] from #DependencyMap where [SymantecManagementPlatformDSV] = 1 open LinkedServerCursor fetch next from LinkedServerCursor into @ServerName, @DatabaseName while @@fetch_status = 0 begin if @FirstServer = 0 begin select @Union_OrganizationalGroupDim = @Union_OrganizationalGroupDim + ' UNION ' select @Union_OrganizationalGroupFact = @Union_OrganizationalGroupFact + ' UNION ' select @Union_UserDim = @Union_UserDim + ' UNION ' select @Union_CollectionDim = @Union_CollectionDim + ' UNION ' select @Union_PackageDim = @Union_PackageDim + ' UNION ' select @Union_PackageServerConfigurationRequestFact = @Union_PackageServerConfigurationRequestFact + ' UNION ' select @Union_PackageDownloadSourceDim = @Union_PackageDownloadSourceDim + ' UNION ' select @Union_PackageDownloadMethodDim = @Union_PackageDownloadMethodDim + ' UNION ' select @Union_PackageServerDim = @Union_PackageServerDim + ' UNION ' select @Union_PackageServerPackageDistributionEventDim = @Union_PackageServerPackageDistributionEventDim + ' UNION ' select @Union_PackageServerPackageDistributionEventFact = @Union_PackageServerPackageDistributionEventFact + ' UNION ' select @Union_PackageServerPackageStatusDim = @Union_PackageServerPackageStatusDim + ' UNION ' select @Union_PackageServerPackageStatusFact = @Union_PackageServerPackageStatusFact + ' UNION ' select @Union_CollectionMembershipFact = @Union_CollectionMembershipFact + ' UNION ' select @Union_AdvertisementDim = @Union_AdvertisementDim + ' UNION ' select @Union_AltirisAgentConfigurationRequestEventFact = @Union_AltirisAgentConfigurationRequestEventFact + ' UNION ' select @Union_ForwardServerDim = @Union_ForwardServerDim + ' UNION ' select @Union_InventoryClassDim = @Union_InventoryClassDim + ' UNION ' select @Union_InventoryForwardingFact = @Union_InventoryForwardingFact + ' UNION ' select @Union_InventoryUpdatedSinceForwardedFact = @Union_InventoryUpdatedSinceForwardedFact + ' UNION ' select @Union_InventoryUpdateFact = @Union_InventoryUpdateFact + ' UNION ' select @Union_ResourceDim = @Union_ResourceDim + ' UNION ' select @Union_SiteDim = @Union_SiteDim + ' UNION ' select @Union_SoftwareDeliveryExecutionEventCommandLineDim = @Union_SoftwareDeliveryExecutionEventCommandLineDim + ' UNION ' select @Union_SoftwareDeliveryExecutionEventFact = @Union_SoftwareDeliveryExecutionEventFact + ' UNION ' select @Union_SoftwareDeliveryExecutionEventStatusDim = @Union_SoftwareDeliveryExecutionEventStatusDim + ' UNION ' select @Union_SoftwareDeliveryPackageEventFact = @Union_SoftwareDeliveryPackageEventFact + ' UNION ' select @Union_SoftwareDeliveryPackageEventStatusDim = @Union_SoftwareDeliveryPackageEventStatusDim + ' UNION ' select @Union_SoftwareDeliveryPackageEventURLDim = @Union_SoftwareDeliveryPackageEventURLDim + ' UNION ' select @Union_SoftwareDeliveryStatusEventFact = @Union_SoftwareDeliveryStatusEventFact + ' UNION ' select @Union_SoftwareDeliveryStatusEventStatusDim = @Union_SoftwareDeliveryStatusEventStatusDim + ' UNION ' select @Union_SoftwareDeliveryStatusEventTypeDim = @Union_SoftwareDeliveryStatusEventTypeDim + ' UNION ' select @Union_SubnetDim = @Union_SubnetDim + ' UNION ' end else begin select @FirstServer = 0 end exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'ScopeMembership', @Source_ScopeMembership OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Item', @Source_Item OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vUser', @Source_vUser OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vCollection', @Source_vCollection OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'SWDPackage', @Source_SWDPackage OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Evt_NS_Client_Config_Request', @Source_Evt_NS_Client_Config_Request OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_AeX_AC_Identification', @Source_Inv_AeX_AC_Identification OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'SWDPackageServer', @Source_SWDPackageServer OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Evt_AeX_SWD_Package', @Source_Evt_AeX_SWD_Package OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vNotificationServerSource', @Source_vNotificationServerSource OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vComputer', @Source_vComputer OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'CollectionMembership', @Source_CollectionMembership OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Evt_AeX_Package_Server_Package_Event', @Source_Evt_AeX_Package_Server_Package_Event OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'SWDAdvertisement', @Source_SWDAdvertisement OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vResourceItem', @Source_vResourceItem OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'ForwardServer', @Source_ForwardServer OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vInventoryClass', @Source_vInventoryClass OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'ItemFolder', @Source_ItemFolder OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vFolder', @Source_vFolder OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'ForwardingHistorySummary', @Source_ForwardingHistorySummary OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'DataClass', @Source_DataClass OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vResource', @Source_vResource OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'ResourceUpdateSummary', @Source_ResourceUpdateSummary OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vResourceType', @Source_vResourceType OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Evt_AeX_SWD_Execution', @Source_Evt_AeX_SWD_Execution OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Evt_AeX_SWD_Status', @Source_Evt_AeX_SWD_Status OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_Subnet', @Source_Inv_Subnet OUTPUT select @Union_OrganizationalGroupDim = @Union_OrganizationalGroupDim + 'SELECT i.Name COLLATE DATABASE_DEFAULT AS ScopeName , sm.ScopeCollectionGuid AS ScopeCollectionGuid FROM ' + @Source_ScopeMembership + ' AS sm INNER JOIN ' + @Source_Item + ' AS i ON sm.ScopeCollectionGuid = i.Guid ' select @Union_OrganizationalGroupFact = @Union_OrganizationalGroupFact + 'SELECT ScopeCollectionGuid , ResourceGuid FROM ' + @Source_ScopeMembership + ' AS ScopeMembership ' select @Union_UserDim = @Union_UserDim + 'SELECT Guid AS UserGuid , Name COLLATE DATABASE_DEFAULT AS UserName , Domain COLLATE DATABASE_DEFAULT AS Domain , [Given Name] COLLATE DATABASE_DEFAULT AS [Given Name] , Surname COLLATE DATABASE_DEFAULT AS Surname , [Office Location] COLLATE DATABASE_DEFAULT AS [Office Location] , [Display Name] COLLATE DATABASE_DEFAULT AS [Display Name] , Email COLLATE DATABASE_DEFAULT AS Email , [Job Title] COLLATE DATABASE_DEFAULT AS [Job Title] , Company COLLATE DATABASE_DEFAULT AS Company , City COLLATE DATABASE_DEFAULT AS City , [Street Address] COLLATE DATABASE_DEFAULT AS [Street Address] , State COLLATE DATABASE_DEFAULT AS State , Zip COLLATE DATABASE_DEFAULT AS Zip , Country COLLATE DATABASE_DEFAULT AS Country FROM ' + @Source_vUser + ' as vUser' select @Union_CollectionDim = @Union_CollectionDim + 'SELECT Guid AS CollectionGuid , Name COLLATE DATABASE_DEFAULT AS CollectionName FROM ' + @Source_vCollection + ' as vCollection' select @Union_PackageDim = @Union_PackageDim + 'SELECT PackageId , Name COLLATE DATABASE_DEFAULT AS PackageName , [Package Location] COLLATE DATABASE_DEFAULT AS [Package Location] , [Package Size] , Publisher COLLATE DATABASE_DEFAULT AS Publisher FROM ' + @Source_SWDPackage + ' WHERE (_Latest = 1)' select @Union_PackageServerConfigurationRequestFact = @Union_PackageServerConfigurationRequestFact + 'SELECT CCR._id AS ConfigRequestID , CCR.ResourceGuid AS PackageServerGuid , ACID._id AS PackageServerResourceID , CONVERT(datetime, CONVERT(varchar, CCR.StartTime, 112), 112) AS FactDate , CONVERT(datetime, CONVERT(varchar, CCR.StartTime, 108), 108) AS FactTime , CCR.StatusCode FROM ' + @Source_Evt_NS_Client_Config_Request + ' AS CCR INNER JOIN ' + @Source_Inv_AeX_AC_Identification + ' AS ACID ON ACID._ResourceGuid = CCR.ResourceGuid WHERE (CCR.ResourceGuid IN (SELECT DISTINCT PkgSvrId FROM ' + @Source_SWDPackageServer + ' AS PS))' select @Union_PackageDownloadSourceDim = @Union_PackageDownloadSourceDim + 'SELECT CASE WHEN charindex(''.'', [Name]) > 0 THEN upper(substring([Name], 0, charindex(''.'', [Name]))) ELSE Name END COLLATE DATABASE_DEFAULT AS PackageDownloadSourceName, ''Notification Server'' COLLATE DATABASE_DEFAULT AS PackageDownloadSourceType FROM ' + @Source_vNotificationServerSource + ' as vNotificationServerSource UNION SELECT DISTINCT vc.Name COLLATE DATABASE_DEFAULT AS PackageDownloadSourceName, ''Package Server'' COLLATE DATABASE_DEFAULT AS PackageDownloadSourceType FROM ' + @Source_vComputer + ' AS vc INNER JOIN ' + @Source_SWDPackageServer + ' ON vc.Guid = SWDPackageServer.PkgSvrId UNION SELECT PackageDownloadSourceName COLLATE DATABASE_DEFAULT AS PackageDownloadSourceName , ''Non-Altiris Server'' COLLATE DATABASE_DEFAULT AS PackageDownloadSourceType FROM (SELECT DISTINCT CASE WHEN charindex(''//'', [URL]) > 0 THEN upper(substring(substring([URL], charindex(''//'', [URL]) + 2, len([URL]) - charindex(''//'', [URL]) - 1), 0, charindex(''/'', replace(substring([URL], charindex(''//'', [URL]) + 2, len([URL]) - charindex(''//'', [URL]) - 1), ''.'', ''/'')))) WHEN charindex(''\\'', [URL]) > 0 THEN upper(substring(substring([URL], charindex(''\\'', [URL]) + 2, len([URL]) - charindex(''\\'', [URL]) - 1), 0, charindex(''\'', replace(substring([URL], charindex(''\\'', [URL]) + 2, len([URL]) - charindex(''\\'', [URL]) - 1), ''.'', ''\'')))) WHEN charindex(''Multicast download complete. Master: '', [URL]) > 0 THEN upper(substring([URL], charindex(''Multicast download complete. Master: '', [URL]) + 37, len([URL]) - charindex(''Multicast download complete. Master: '', [URL]) - 36)) ELSE NULL END COLLATE DATABASE_DEFAULT AS PackageDownloadSourceName, ''Non-Altiris Server''COLLATE DATABASE_DEFAULT AS PackageDownloadSourceType FROM ' + @Source_Evt_AeX_SWD_Package + ' AS Evt_AeX_SWD_Package WHERE (LEN(URL) > 0) AND (EventType = ''End'')) AS PSSource WHERE (PackageDownloadSourceName NOT IN (SELECT CASE WHEN charindex(''.'', [Name]) > 0 THEN upper(substring([Name], 0, charindex(''.'', [Name]))) ELSE Name END AS Name FROM ' + @Source_vNotificationServerSource + ' AS vNotificationServerSource_1 UNION SELECT DISTINCT vc.Name FROM ' + @Source_vComputer + ' AS vc INNER JOIN ' + @Source_SWDPackageServer + ' AS SWDPackageServer_1 ON vc.Guid = SWDPackageServer_1.PkgSvrId)) ' select @Union_PackageDownloadMethodDim = @Union_PackageDownloadMethodDim + 'SELECT DISTINCT CASE WHEN charindex(''http://'', [URL]) > 0 THEN ''HTTP'' WHEN charindex(''https://'', [URL]) > 0 THEN ''HTTPS'' WHEN charindex(''\\'', [URL]) > 0 THEN ''UNC'' WHEN charindex(''Multicast'', [URL]) > 0 THEN ''Multicast'' ELSE '''' END COLLATE DATABASE_DEFAULT AS PackageDownloadMethod FROM ' + @Source_Evt_AeX_SWD_Package + ' AS SWDEvt WHERE (LEN(URL) > 0) AND (EventType = ''End'') ' select @Union_PackageServerDim = @Union_PackageServerDim + 'SELECT DISTINCT vc.Name COLLATE DATABASE_DEFAULT AS ''PackageServerName'', SWDPackageServer.PkgSvrId AS PackageServerGuid, CASE WHEN cPS.PackageServerType = ''Constrained'' THEN ''Constrained'' ELSE ''Unconstrained'' END COLLATE DATABASE_DEFAULT AS PackageServerType FROM ' + @Source_vComputer + ' AS vc INNER JOIN ' + @Source_SWDPackageServer + ' as SWDPackageServer ON vc.Guid = SWDPackageServer.PkgSvrId LEFT OUTER JOIN (SELECT CollectionGuid, ResourceGuid, ''Constrained'' AS PackageServerType FROM ' + @Source_CollectionMembership + ' as CollectionMembership WHERE (CollectionGuid = ''3E9E0920-043D-40e3-BABB-A49F5C0F5E6B'')) AS cPS ON cPS.ResourceGuid = SWDPackageServer.PkgSvrId ' select @Union_PackageServerPackageDistributionEventDim = @Union_PackageServerPackageDistributionEventDim + 'SELECT Status COLLATE DATABASE_DEFAULT AS Status FROM ' + @Source_Evt_AeX_Package_Server_Package_Event + ' as Evt_AeX_Package_Server_Package_Event GROUP BY EventType, Status ' select @Union_PackageServerPackageDistributionEventFact = @Union_PackageServerPackageDistributionEventFact + 'SELECT _id AS PackageDistributionEventID , _ResourceGuid AS PackageServerGuid , Status COLLATE DATABASE_DEFAULT AS Status, PackageId, CONVERT(datetime, CONVERT(varchar, Time, 112), 112) AS FactDate, CONVERT(datetime, CONVERT(varchar, Time, 108), 108) AS FactTime FROM ' + @Source_Evt_AeX_Package_Server_Package_Event + ' as Evt_AeX_Package_Server_Package_Event' select @Union_PackageServerPackageStatusDim = @Union_PackageServerPackageStatusDim + 'SELECT DISTINCT Status COLLATE DATABASE_DEFAULT AS Status FROM ' + @Source_SWDPackageServer + ' as SWDPackageServer' select @Union_PackageServerPackageStatusFact = @Union_PackageServerPackageStatusFact + 'SELECT SWDPackageServer.PackageId, SWDPackageServer.PkgSvrId AS PackageServerGuid, SWDPackageServer.Version, SWDPackageServer.Status COLLATE DATABASE_DEFAULT AS Status, CASE WHEN SWDPackage.[Package Size] IS NULL THEN 0 ELSE CONVERT(decimal(10, 2), SWDPackage.[Package Size] / (1024 * 1024.00)) END AS [Package Size GB], ACID._id AS PackageServerResourceID FROM ' + @Source_SWDPackageServer + ' as SWDPackageServer INNER JOIN ' + @Source_Inv_AeX_AC_Identification + ' AS ACID ON ACID._ResourceGuid = SWDPackageServer.PkgSvrId INNER JOIN ' + @Source_SWDPackage + ' as SWDPackage ON SWDPackage.PackageId = SWDPackageServer.PackageId ' select @Union_CollectionMembershipFact = @Union_CollectionMembershipFact + 'SELECT CollectionGuid, ResourceGuid FROM ' + @Source_CollectionMembership + ' as CollectionMembership ' select @Union_AdvertisementDim = @Union_AdvertisementDim + 'SELECT AdvertisementId, ProgramId COLLATE DATABASE_DEFAULT AS ProgramId, Name COLLATE DATABASE_DEFAULT AS [Advertisement Name] FROM ' + @Source_SWDAdvertisement + ' as SWDAdvertisement WHERE (_Latest = 1)' select @Union_AltirisAgentConfigurationRequestEventFact = @Union_AltirisAgentConfigurationRequestEventFact + 'SELECT CCR._id AS ConfigRequestID , CCR.ResourceGuid , ACID._id AS ResourceID , CONVERT(datetime, CONVERT(varchar, CCR.StartTime, 112), 112) AS FactDate, CONVERT(datetime, CONVERT(varchar, CCR.StartTime, 108), 108) AS FactTime , CCR.StatusCode FROM ' + @Source_Evt_NS_Client_Config_Request + ' AS CCR INNER JOIN ' + @Source_Inv_AeX_AC_Identification + ' AS ACID ON ACID._ResourceGuid = CCR.ResourceGuid LEFT OUTER JOIN ' + @Source_vResourceItem + ' AS vRI ON vRI.Guid = CCR.ResourceGuid' select @Union_ForwardServerDim = @Union_ForwardServerDim + 'SELECT Guid AS ForwardServerGuid, Name COLLATE DATABASE_DEFAULT AS ForwardServerName FROM ' + @Source_ForwardServer + ' AS ForwardServer' select @Union_InventoryClassDim = @Union_InventoryClassDim + 'SELECT vInventoryClass.Guid AS InventoryClassGuid, vInventoryClass.Name COLLATE DATABASE_DEFAULT AS InventoryClassName , vFolder.Name COLLATE DATABASE_DEFAULT AS InventoryClassType FROM ' + @Source_vInventoryClass + ' AS vInventoryClass INNER JOIN ' + @Source_ItemFolder + ' AS ItemFolder ON vInventoryClass.Guid = ItemFolder.ItemGuid INNER JOIN ' + @Source_vFolder + ' AS vFolder ON vFolder.Guid = ItemFolder.ParentFolderGuid' select @Union_InventoryForwardingFact = @Union_InventoryForwardingFact + 'SELECT FHS.ClassGuid AS InventoryClassGuid , DataClass.id AS InventoryClassId , FHS.ResourceGuid , ACID._id AS ResourceId, FHS.ForwardServerGuid, CONVERT(datetime, CONVERT(varchar, FHS.ForwardDate, 112), 112) AS ForwardDate , CONVERT(datetime, CONVERT(varchar, InventoryUpdate.ModifiedDate, 112), 112) AS LastUpdateDate, DATEDIFF(d, FHS.ForwardDate, InventoryUpdate.ModifiedDate) AS TmpDateDiff , CASE WHEN datediff(d, FHS.[ForwardDate], InventoryUpdate.[ModifiedDate]) > 0 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS UpdatedSinceLastForward FROM ' + @Source_ForwardingHistorySummary + ' AS FHS INNER JOIN ' + @Source_Inv_AeX_AC_Identification + ' AS ACID ON ACID._ResourceGuid = FHS.ResourceGuid INNER JOIN ' + @Source_DataClass + ' AS DataClass ON DataClass.Guid = FHS.ClassGuid INNER JOIN ' + @Source_vResource + ' AS vResource ON vResource.Guid = FHS.ResourceGuid LEFT OUTER JOIN (SELECT InventoryClassGuid, ResourceGuid, CONVERT(datetime, CONVERT(varchar, MAX(ModifiedDate), 112), 112) AS ModifiedDate FROM ' + @Source_ResourceUpdateSummary + ' AS RUS GROUP BY InventoryClassGuid, ResourceGuid) AS InventoryUpdate ON InventoryUpdate.InventoryClassGuid = FHS.ClassGuid AND InventoryUpdate.ResourceGuid = FHS.ResourceGuid' select @Union_InventoryUpdatedSinceForwardedFact = @Union_InventoryUpdatedSinceForwardedFact + 'SELECT FHS.ClassGuid AS InventoryClassGuid , DataClass.id AS InventoryClassId , FHS.ResourceGuid , ACID._id AS ResourceId , FHS.ForwardServerGuid, CONVERT(datetime, CONVERT(varchar, MAX(FHS.ForwardDate), 112), 112) AS ForwardDate , CONVERT(datetime, CONVERT(varchar, MAX(InventoryUpdate.ModifiedDate), 112), 112) AS LastUpdatedDate FROM ' + @Source_ForwardingHistorySummary + ' AS FHS INNER JOIN ' + @Source_Inv_AeX_AC_Identification + ' AS ACID ON ACID._ResourceGuid = FHS.ResourceGuid INNER JOIN ' + @Source_DataClass + ' AS DataClass ON DataClass.Guid = FHS.ClassGuid INNER JOIN ' + @Source_vResource + ' AS vResource ON vResource.Guid = FHS.ResourceGuid LEFT OUTER JOIN (SELECT InventoryClassGuid, ResourceGuid, CONVERT(datetime, CONVERT(varchar, MAX(ModifiedDate), 112), 112) AS ModifiedDate FROM ' + @Source_ResourceUpdateSummary + ' AS RUS GROUP BY InventoryClassGuid, ResourceGuid) AS InventoryUpdate ON InventoryUpdate.InventoryClassGuid = FHS.ClassGuid AND InventoryUpdate.ResourceGuid = FHS.ResourceGuid GROUP BY FHS.ClassGuid, DataClass.id, FHS.ResourceGuid, ACID._id, FHS.ForwardServerGuid HAVING (DATEDIFF(d, MAX(FHS.ForwardDate), MAX(InventoryUpdate.ModifiedDate)) > 0) ' select @Union_InventoryUpdateFact = @Union_InventoryUpdateFact + 'SELECT RUS.InventoryClassGuid , DataClass.id AS InventoryClassId , RUS.ResourceGuid, ACID._id AS ResourceId , CONVERT(datetime, CONVERT(varchar, RUS.CreatedDate, 112), 112) AS CreatedDate, CONVERT(datetime, CONVERT(varchar, RUS.ModifiedDate, 112), 112) AS ModifiedDate, RUS.[RowCount] FROM ' + @Source_ResourceUpdateSummary + ' RUS INNER JOIN ' + @Source_Inv_AeX_AC_Identification + ' AS ACID ON ACID._ResourceGuid = RUS.ResourceGuid INNER JOIN ' + @Source_DataClass + ' AS DataClass ON DataClass.Guid = RUS.InventoryClassGuid INNER JOIN ' + @Source_vResource + ' AS vResource ON vResource.Guid = RUS.ResourceGuid' select @Union_ResourceDim = @Union_ResourceDim + 'SELECT vResourceItem.Guid AS ResourceGuid , vResourceItem.Name COLLATE DATABASE_DEFAULT AS ResourceName , vResourceType.Name COLLATE DATABASE_DEFAULT AS ResourceType, CASE WHEN vResourceItem.IsManaged = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Altiris Managed] FROM ' + @Source_vResourceItem + ' AS vResourceItem INNER JOIN ' + @Source_vResourceType + ' AS vResourceType ON vResourceItem.ResourceTypeGuid = vResourceType.Guid' select @Union_SiteDim = @Union_SiteDim + 'SELECT Guid AS SiteGuid, Name COLLATE DATABASE_DEFAULT AS SiteName FROM ' + @Source_vResourceItem + ' AS vResourceItem WHERE (ResourceTypeGuid = ''28C3406A-8476-407D-8F99-81656E8890F8'')' select @Union_SoftwareDeliveryExecutionEventCommandLineDim = @Union_SoftwareDeliveryExecutionEventCommandLineDim + 'SELECT DISTINCT CommandLine COLLATE DATABASE_DEFAULT AS CommandLine FROM ' + @Source_Evt_AeX_SWD_Execution + ' AS Evt_AeX_SWD_Execution' select @Union_SoftwareDeliveryExecutionEventFact = @Union_SoftwareDeliveryExecutionEventFact + 'SELECT SWDEvt._id AS SoftwareDeliveryExecutionEventID , SWDEvt._ResourceGuid AS ResourceGUID , SWDEvt.AdvertisementId , SWDEvt.PackageId , SWDEvt.Status COLLATE DATABASE_DEFAULT AS Status , SWDEvt.CommandLine COLLATE DATABASE_DEFAULT AS CommandLine , CONVERT(datetime, CONVERT(varchar, SWDEvt.[End], 112), 112) AS EndDate , CONVERT(datetime, CONVERT(varchar, SWDEvt.[End], 108), 108) AS EndTime , CASE WHEN datediff(ss, SWDEvt.Start, SWDEvt.[End]) IS NULL THEN 0 WHEN datediff(ss, SWDEvt.Start, SWDEvt.[End]) < 0 THEN 0 ELSE datediff(ss, SWDEvt.Start, SWDEvt.[End]) END AS Duration, ACID._id AS ResourceId FROM ' + @Source_vResourceItem + ' AS r INNER JOIN ' + @Source_Evt_AeX_SWD_Execution+ ' AS SWDEvt ON r.Guid = SWDEvt._ResourceGuid LEFT OUTER JOIN ' + @Source_Inv_AeX_AC_Identification + ' AS ACID ON ACID._ResourceGuid = SWDEvt._ResourceGuid LEFT OUTER JOIN ' + @Source_vResourceItem + ' AS rPackage ON rPackage.Guid = SWDEvt.PackageId' select @Union_SoftwareDeliveryExecutionEventStatusDim = @Union_SoftwareDeliveryExecutionEventStatusDim + 'SELECT DISTINCT Status COLLATE DATABASE_DEFAULT AS Status FROM ' + @Source_Evt_AeX_SWD_Execution+ ' AS Evt_AeX_SWD_Execution' select @Union_SoftwareDeliveryPackageEventFact = @Union_SoftwareDeliveryPackageEventFact + 'SELECT MAX(SWDEndEvt._id) AS SoftwareDeliveryPackageEventID , MAX(SWDEndEvt.[Cursor]) AS EndCursor , MAX(SWDStartEvt.[Cursor]) AS StartCursor, CASE WHEN MAX(SWDEndEvt.[Cursor]) - MAX(SWDStartEvt.[Cursor]) IS NULL THEN MAX(SWDEndEvt.[Cursor]) ELSE MAX(SWDEndEvt.[Cursor]) - MAX(SWDStartEvt.[Cursor]) END AS BytesTransferred , MAX(SWDEndEvt.Status) COLLATE DATABASE_DEFAULT AS EndStatus , MAX(SWDStartEvt.Status) COLLATE DATABASE_DEFAULT AS StartStatus , CONVERT(datetime, CONVERT(varchar, MAX(SWDEndEvt.Time), 112), 112) AS EndDate , CONVERT(datetime, CONVERT(varchar, MAX(SWDEndEvt.Time), 108), 108) AS EndTime, CASE WHEN datediff(ss, MAX(SWDStartEvt.Time), MAX(SWDEndEvt.Time)) IS NULL THEN 0 WHEN datediff(ss, MAX(SWDStartEvt.Time), MAX(SWDEndEvt.Time)) < 0 THEN 0 ELSE datediff(ss, MAX(SWDStartEvt.Time), MAX(SWDEndEvt.Time)) END AS Duration , CONVERT(datetime, CONVERT(varchar, MAX(SWDStartEvt.Time), 112), 112) AS StartDate, CONVERT(datetime, CONVERT(varchar, MAX(SWDStartEvt.Time), 108), 108) AS StartTime , SWDEndEvt._ResourceGuid AS ResourceGuid, ACID._id AS ResourceId , SWDEndEvt.PackageId , SWDEndEvt.URL COLLATE DATABASE_DEFAULT AS URL , CASE WHEN charindex(''http://'', [URL]) > 0 THEN ''HTTP'' WHEN charindex(''https://'', [URL]) > 0 THEN ''HTTPS'' WHEN charindex(''\\'', [URL]) > 0 THEN ''UNC'' WHEN charindex(''Multicast'', [URL]) > 0 THEN ''Multicast'' ELSE '''' END AS PackageDownloadMethod, CASE WHEN charindex(''//'', [URL]) > 0 THEN upper(substring(substring([URL], charindex(''//'', [URL]) + 2, len([URL]) - charindex(''//'', [URL]) - 1), 0, charindex(''/'', replace(substring([URL], charindex(''//'', [URL]) + 2, len([URL]) - charindex(''//'', [URL]) - 1), ''.'', ''/'')))) WHEN charindex(''\\'', [URL]) > 0 THEN upper(substring(substring([URL], charindex(''\\'', [URL]) + 2, len([URL]) - charindex(''\\'', [URL]) - 1), 0, charindex(''\'', replace(substring([URL], charindex(''\\'', [URL]) + 2, len([URL]) - charindex(''\\'', [URL]) - 1), ''.'', ''\'')))) WHEN charindex(''Multicast download complete. Master: '', [URL]) > 0 THEN upper(substring([URL], charindex(''Multicast download complete. Master: '', [URL]) + 37, len([URL]) - charindex(''Multicast download complete. Master: '', [URL]) - 36)) ELSE NULL END COLLATE DATABASE_DEFAULT AS PackageDownloadSourceName FROM ' + @Source_Evt_AeX_SWD_Package+ ' AS SWDEndEvt INNER JOIN ' + @Source_Inv_AeX_AC_Identification + ' AS ACID ON ACID._ResourceGuid = SWDEndEvt._ResourceGuid LEFT OUTER JOIN ' + @Source_vResourceItem + ' AS rPackage ON rPackage.Guid = SWDEndEvt.PackageId LEFT OUTER JOIN (SELECT MAX(_id) AS _id, MAX([Cursor]) AS [Cursor], _ResourceGuid, DownloadNumber, PackageId, MAX(Time) AS Time, MAX(Status) AS Status FROM ' + @Source_Evt_AeX_SWD_Package+ 'Evt_AeX_SWD_Package WHERE (EventType = ''Start'') GROUP BY _ResourceGuid, DownloadNumber, PackageId) AS SWDStartEvt ON SWDStartEvt._ResourceGuid = SWDEndEvt._ResourceGuid AND SWDStartEvt.PackageId = SWDEndEvt.PackageId AND SWDStartEvt.DownloadNumber = SWDEndEvt.DownloadNumber WHERE (SWDEndEvt.EventType = ''End'') GROUP BY SWDEndEvt._ResourceGuid, SWDEndEvt.DownloadNumber, SWDEndEvt.PackageId, ACID._id, SWDEndEvt.URL ' select @Union_SoftwareDeliveryPackageEventStatusDim = @Union_SoftwareDeliveryPackageEventStatusDim + 'SELECT DISTINCT Status COLLATE DATABASE_DEFAULT AS Status FROM ' + @Source_Evt_AeX_SWD_Package + ' AS SWDEvt' select @Union_SoftwareDeliveryPackageEventURLDim = @Union_SoftwareDeliveryPackageEventURLDim + 'SELECT DISTINCT URL COLLATE DATABASE_DEFAULT AS URL FROM ' + @Source_Evt_AeX_SWD_Package + ' AS SWDEvt WHERE (EventType = ''End'')' select @Union_SoftwareDeliveryStatusEventFact = @Union_SoftwareDeliveryStatusEventFact + 'SELECT SWDEvt._id AS SoftwareDeliveryStatusEventID , SWDEvt._ResourceGuid AS ResourceGUID , SWDEvt.EventType COLLATE DATABASE_DEFAULT AS EventType , SWDEvt.AdvertisementId , SWDEvt.PackageId , SWDEvt.Status COLLATE DATABASE_DEFAULT AS Status , CONVERT(datetime, CONVERT(varchar, SWDEvt.Time, 112), 112) AS FactDate , CONVERT(datetime, CONVERT(varchar, SWDEvt.Time, 108), 108) AS FactTime, ACID._id AS ResourceId FROM ' + @Source_Evt_AeX_SWD_Status + ' AS SWDEvt INNER JOIN ' + @Source_Inv_AeX_AC_Identification + ' AS ACID ON ACID._ResourceGuid = SWDEvt._ResourceGuid LEFT OUTER JOIN ' + @Source_vResourceItem + ' AS rComp ON rComp.Guid = SWDEvt._ResourceGuid LEFT OUTER JOIN ' + @Source_vResourceItem + ' AS rPackage ON rPackage.Guid = SWDEvt.PackageId' select @Union_SoftwareDeliveryStatusEventStatusDim = @Union_SoftwareDeliveryStatusEventStatusDim + 'SELECT DISTINCT Status COLLATE DATABASE_DEFAULT AS Status FROM ' + @Source_Evt_AeX_SWD_Status + ' AS Evt_AeX_SWD_Status' select @Union_SoftwareDeliveryStatusEventTypeDim = @Union_SoftwareDeliveryStatusEventTypeDim + 'SELECT DISTINCT EventType COLLATE DATABASE_DEFAULT AS EventType FROM ' + @Source_Evt_AeX_SWD_Status + ' AS Evt_AeX_SWD_Status' select @Union_SubnetDim = @Union_SubnetDim + 'SELECT subnet._ResourceGuid AS SubnetGuid , vRI.Name COLLATE DATABASE_DEFAULT AS [Subnet Name] , subnet.Subnet COLLATE DATABASE_DEFAULT AS Subnet , subnet.[Subnet Mask] COLLATE DATABASE_DEFAULT AS [Subnet Mask] FROM ' + @Source_Inv_Subnet + ' AS subnet INNER JOIN ' + @Source_vResourceItem + ' AS vRI ON subnet._ResourceGuid = vRI.Guid' fetch next from LinkedServerCursor into @ServerName, @DatabaseName end close LinkedServerCursor deallocate LinkedServerCursor select @Union_OrganizationalGroupDim = @Union_OrganizationalGroupDim + ')' select @Union_OrganizationalGroupFact = @Union_OrganizationalGroupFact + ')' select @Union_UserDim = @Union_UserDim + ')' select @Union_CollectionDim = @Union_CollectionDim + ')' select @Union_PackageDim = @Union_PackageDim + ')' select @Union_PackageServerConfigurationRequestFact = @Union_PackageServerConfigurationRequestFact + ')' select @Union_PackageDownloadSourceDim = @Union_PackageDownloadSourceDim + ')' select @Union_PackageDownloadMethodDim = @Union_PackageDownloadMethodDim + ')' select @Union_PackageServerDim = @Union_PackageServerDim + ')' select @Union_PackageServerPackageDistributionEventDim = @Union_PackageServerPackageDistributionEventDim + ')' select @Union_PackageServerPackageDistributionEventFact = @Union_PackageServerPackageDistributionEventFact + ')' select @Union_PackageServerPackageStatusDim = @Union_PackageServerPackageStatusDim + ')' select @Union_PackageServerPackageStatusFact = @Union_PackageServerPackageStatusFact + ')' select @Union_CollectionMembershipFact = @Union_CollectionMembershipFact + ')' select @Union_AdvertisementDim = @Union_AdvertisementDim + ')' select @Union_AltirisAgentConfigurationRequestEventFact = @Union_AltirisAgentConfigurationRequestEventFact + ')' select @Union_ForwardServerDim = @Union_ForwardServerDim + ')' select @Union_InventoryClassDim = @Union_InventoryClassDim + ')' select @Union_InventoryForwardingFact = @Union_InventoryForwardingFact + ')' select @Union_InventoryUpdatedSinceForwardedFact = @Union_InventoryUpdatedSinceForwardedFact + ')' select @Union_InventoryUpdateFact = @Union_InventoryUpdateFact + ')' select @Union_ResourceDim = @Union_ResourceDim + ')' select @Union_SiteDim = @Union_SiteDim + ')' select @Union_SoftwareDeliveryExecutionEventCommandLineDim = @Union_SoftwareDeliveryExecutionEventCommandLineDim + ')' select @Union_SoftwareDeliveryExecutionEventFact = @Union_SoftwareDeliveryExecutionEventFact + ')' select @Union_SoftwareDeliveryExecutionEventStatusDim = @Union_SoftwareDeliveryExecutionEventStatusDim + ')' select @Union_SoftwareDeliveryPackageEventFact = @Union_SoftwareDeliveryPackageEventFact + ')' select @Union_SoftwareDeliveryPackageEventStatusDim = @Union_SoftwareDeliveryPackageEventStatusDim + ')' select @Union_SoftwareDeliveryPackageEventURLDim = @Union_SoftwareDeliveryPackageEventURLDim + ')' select @Union_SoftwareDeliveryStatusEventFact = @Union_SoftwareDeliveryStatusEventFact + ')' select @Union_SoftwareDeliveryStatusEventStatusDim = @Union_SoftwareDeliveryStatusEventStatusDim + ')' select @Union_SoftwareDeliveryStatusEventTypeDim = @Union_SoftwareDeliveryStatusEventTypeDim + ')' select @Union_SubnetDim = @Union_SubnetDim + ')' end begin -- Asset DSV select @FirstServer = 1 declare LinkedServerCursor cursor forward_only for select [ServerName], [DatabaseName], [ResourceManagerURL], [ResourceEditURL] from #DependencyMap where [AssetDSV] = 1 open LinkedServerCursor fetch next from LinkedServerCursor into @ServerName, @DatabaseName, @thisResourceManagerURL, @thisResourceEditURL while @@fetch_status = 0 begin if @FirstServer = 0 begin select @Union_AssetCostCenterFact = @Union_AssetCostCenterFact + ' UNION ' select @Union_AssetDim = @Union_AssetDim + ' UNION ' select @Union_AssetFact = @Union_AssetFact + ' UNION ' select @Union_AssetStatusDim = @Union_AssetStatusDim + ' UNION ' select @Union_AssetTypeDim = @Union_AssetTypeDim + ' UNION ' select @Union_CostCenterDim = @Union_CostCenterDim + ' UNION ' select @Union_DepartmentDim = @Union_DepartmentDim + ' UNION ' select @Union_LocationDim = @Union_LocationDim + ' UNION ' end else begin select @FirstServer = 0 end exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vAsset', @Source_vAsset OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'ResourceAssociation', @Source_ResourceAssociation OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vFixedAssetStatus', @Source_vFixedAssetStatus OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vCostCenter', @Source_vCostCenter OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vDepartment', @Source_vDepartment OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vLocation', @Source_vLocation OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vRM_Asset_Item', @Source_vRM_Asset_Item OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'ResourceType', @Source_ResourceType OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vSource', @Source_vSource OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_Manufacturer', @Source_Inv_Manufacturer OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_Serial_Number', @Source_Inv_Serial_Number OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_Identity', @Source_Inv_Identity OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_Barcode', @Source_Inv_Barcode OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'ResourceAssociation', @Source_ResourceAssociation OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Item', @Source_Item OUTPUT select @Union_AssetCostCenterFact = @Union_AssetCostCenterFact + 'SELECT DISTINCT vAsset._ResourceGuid AS ResourceGuid , raCC.ChildResourceGuid AS CostCenterGuid FROM ' + @Source_vAsset + ' as vAsset LEFT OUTER JOIN ' + @Source_ResourceAssociation + ' AS raCC ON vAsset._ResourceGuid = raCC.ParentResourceGuid AND raCC.ResourceAssociationTypeGuid = ''9bc22d17-c0cf-45d5-9a8f-d62bbafd955d''' select @Union_AssetDim = @Union_AssetDim + 'SELECT va.Guid as [ResourceGuid] , va.Name COLLATE DATABASE_DEFAULT AS [Name] , ISNULL(s.Name, N'''') COLLATE DATABASE_DEFAULT AS [Server] , Inv_Manufacturer.Manufacturer COLLATE DATABASE_DEFAULT AS Manufacturer , Inv_Manufacturer.Model COLLATE DATABASE_DEFAULT AS Model , Inv_Serial_Number.[Serial Number] COLLATE DATABASE_DEFAULT AS [Serial Number] , Inv_Barcode.Barcode COLLATE DATABASE_DEFAULT AS Barcode , Inv_Identity.[System Number] COLLATE DATABASE_DEFAULT AS [Asset Tag] , ''' + @thisResourceManagerURL + '?ItemGuid='' + convert(varchar(50),va.Guid) COLLATE DATABASE_DEFAULT AS [ResourceManagerURL] , ''' + @thisResourceEditURL + '?ItemGuid='' + convert(varchar(50),va.Guid) COLLATE DATABASE_DEFAULT AS [ResourceEditURL] FROM ' + @Source_vRM_Asset_Item + ' as va JOIN ' + @Source_ResourceType + ' as ResourceType ON va.[ResourceTypeGuid] = ResourceType.Guid LEFT JOIN ' + @Source_vSource + ' AS s ON va.OwnerNSGuid = s.Guid LEFT JOIN ' + @Source_Inv_Manufacturer + ' as Inv_Manufacturer ON va.Guid = Inv_Manufacturer.[_ResourceGuid] LEFT JOIN ' + @Source_Inv_Serial_Number + ' as Inv_Serial_Number ON va.Guid = Inv_Serial_Number.[_ResourceGuid] LEFT JOIN ' + @Source_Inv_Identity + ' as Inv_Identity ON va.Guid = Inv_Identity.[_ResourceGuid] LEFT JOIN ' + @Source_Inv_Barcode + ' as Inv_Barcode ON va.Guid = Inv_Barcode.[_ResourceGuid] LEFT JOIN ' + @Source_ResourceAssociation + ' as ResourceAssociation ON va.Guid = ResourceAssociation.ParentResourceGuid AND ResourceAssociationTypeGuid = ''3028166F-C0D6-41d8-9CB7-F64852E0FD01'' LEFT JOIN ' + @Source_Item + ' as Item ON ResourceAssociation.ChildResourceGuid = Item.Guid' select @Union_AssetFact = @Union_AssetFact + 'SELECT DISTINCT vAsset._ResourceGuid AS ResourceGuid , vAsset._ResourceTypeGuid AS ResourceTypeGuid , vAsset._AssetStatusGuid AS AssetStatusGuid , raLoc.ChildResourceGuid AS LocationGuid , raOwner.ChildResourceGuid AS UserOwnerGuid , CASE WHEN raDept.ChildResourceGuid IS NULL THEN raUserDept.ChildResourceGuid ELSE raDept.ChildResourceGuid END AS DepartmentOwnerGuid FROM ' + @Source_vAsset + ' as vAsset LEFT OUTER JOIN ' + @Source_ResourceAssociation + ' AS raLoc ON vAsset._ResourceGuid = raLoc.ParentResourceGuid AND raLoc.ResourceAssociationTypeGuid = ''05DE450F-39EA-4AAE-8C5F-77817889C27C'' LEFT OUTER JOIN ' + @Source_ResourceAssociation + ' AS raOwner ON vAsset._ResourceGuid = raOwner.ParentResourceGuid AND raOwner.ResourceAssociationTypeGuid = ''ed35a8d1-bf60-4771-9dde-092c146c485a'' LEFT OUTER JOIN ' + @Source_ResourceAssociation + ' AS raUserDept ON raUserDept.ParentResourceGuid = raOwner.ChildResourceGuid AND raUserDept.ResourceAssociationTypeGuid = ''d9656701-b8ee-48c1-a4e3-5741ca18471d'' LEFT OUTER JOIN ' + @Source_ResourceAssociation + ' AS raDept ON vAsset._ResourceGuid = raDept.ParentResourceGuid AND raDept.ResourceAssociationTypeGuid = ''1466e770-4413-4517-a89d-6599b8a7f144''' select @Union_AssetStatusDim = @Union_AssetStatusDim + 'SELECT _ResourceGuid AS AssetStatusGuid , Name COLLATE DATABASE_DEFAULT AS [Asset Status] FROM ' + @Source_vFixedAssetStatus + ' AS vFixedAssetStatus' select @Union_AssetTypeDim = @Union_AssetTypeDim + 'SELECT DISTINCT _ResourceTypeGuid AS AssetTypeGuid , [Asset Type] COLLATE DATABASE_DEFAULT AS [Asset Type] FROM ' + @Source_vAsset + ' AS vAsset' select @Union_CostCenterDim = @Union_CostCenterDim + 'SELECT vCC._ResourceGuid AS CostCenterGuid , vCC.Name COLLATE DATABASE_DEFAULT AS [Cost Center Name] , vCC.[Cost Center Code] COLLATE DATABASE_DEFAULT AS [Cost Center Code] , fnCC.Path COLLATE DATABASE_DEFAULT AS [Hierarchy Path] , fnCC.Level AS [Hierarchy Level] FROM ' + @Source_vCostCenter + ' AS vCC LEFT OUTER JOIN dbo.fnAssetHierarchyTree(''03E525D8-43B1-4AE3-8592-9A9E5AE9C8C0'', N''select ''''Guid''''=newid() where 1 = 0'', 0x0, 0x0) AS fnCC ON vCC._ResourceGuid = fnCC.Guid' select @Union_DepartmentDim = @Union_DepartmentDim + 'SELECT vD._ResourceGuid AS DepartmentGuid , vD.Name COLLATE DATABASE_DEFAULT AS [Department Name] , fnD.Path COLLATE DATABASE_DEFAULT AS [Hierarchy Path] , fnD.Level AS [Hierarchy Level] FROM ' + @Source_vDepartment + ' AS vD LEFT OUTER JOIN dbo.fnAssetHierarchyTree(''857b18cd-2603-4ac5-b8cc-b116bae5bbbe'', N''select ''''Guid''''=newid() where 1 = 0'', 0x0, 0x0) AS fnD ON vD._ResourceGuid = fnD.Guid' select @Union_LocationDim = @Union_LocationDim + 'SELECT _ResourceGuid AS LocationGuid , Name COLLATE DATABASE_DEFAULT AS [Location Name] , Address COLLATE DATABASE_DEFAULT AS Address , City COLLATE DATABASE_DEFAULT AS City , State COLLATE DATABASE_DEFAULT AS State , Country COLLATE DATABASE_DEFAULT AS Country , Zip COLLATE DATABASE_DEFAULT AS Zip FROM ' + @Source_vLocation + ' AS vLocation' fetch next from LinkedServerCursor into @ServerName, @DatabaseName, @thisResourceManagerURL, @thisResourceEditURL end close LinkedServerCursor deallocate LinkedServerCursor select @Union_AssetCostCenterFact = @Union_AssetCostCenterFact + ')' select @Union_AssetDim = @Union_AssetDim + ')' select @Union_AssetFact = @Union_AssetFact + ')' select @Union_AssetStatusDim = @Union_AssetStatusDim + ')' select @Union_AssetTypeDim = @Union_AssetTypeDim + ')' select @Union_CostCenterDim = @Union_CostCenterDim + ')' select @Union_DepartmentDim = @Union_DepartmentDim + ')' select @Union_LocationDim = @Union_LocationDim + ')' end begin -- Software License DSV select @FirstServer = 1 declare LinkedServerCursor cursor forward_only for select [ServerName], [DatabaseName] from #DependencyMap where [SoftwareLicenseDSV] = 1 open LinkedServerCursor fetch next from LinkedServerCursor into @ServerName, @DatabaseName while @@fetch_status = 0 begin if @FirstServer = 0 begin select @Union_SoftwareLicenseDim = @Union_SoftwareLicenseDim + ' UNION ' select @Union_SoftwareLicenseInstallationEvaluationFact = @Union_SoftwareLicenseInstallationEvaluationFact + ' UNION ALL ' select @Union_SoftwareLicenseInstallationFact = @Union_SoftwareLicenseInstallationFact + ' UNION ALL ' select @Union_SoftwareLicenseOwnedFact = @Union_SoftwareLicenseOwnedFact + ' UNION ALL ' select @Union_SoftwareLicenseUsageEvaluationFact = @Union_SoftwareLicenseUsageEvaluationFact + ' UNION ALL ' select @Union_SoftwareLicenseUsageFact = @Union_SoftwareLicenseUsageFact + ' UNION ALL ' select @Union_SoftwarePurchaseDim = @Union_SoftwarePurchaseDim + ' UNION ' select @Union_SoftwarePurchaseFact = @Union_SoftwarePurchaseFact + ' UNION ALL ' select @Union_NonInventoriedInstallFact = @Union_NonInventoriedInstallFact + ' ' select @Union_BorrowedLicenseCountFact = @Union_BorrowedLicenseCountFact + ' ' select @Union_DonatedLicenseFact = @Union_DonatedLicenseFact + ' ' select @Union_SoftwareProductLicenseFact = @Union_SoftwareProductLicenseFact + ' UNION ALL ' end else begin select @FirstServer = 0 end exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vSoftwareLicense', @Source_vSoftwareLicense OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'InvHist_SoftwareProduct_InstallationInfo', @Source_InvHist_SoftwareProduct_InstallationInfo OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'ResourceAssociation', @Source_ResourceAssociation OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vAsset', @Source_vAsset OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vSoftwarePurchase', @Source_vSoftwarePurchase OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_Software_Purchase_Cost_Center_Ownership', @Source_Inv_Software_Purchase_Cost_Center_Ownership OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_Software_Purchase_Owners', @Source_Inv_Software_Purchase_Owners OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vUser', @Source_vUser OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vDepartment', @Source_vDepartment OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'InvHist_Non_Inventoried_Installs_Delta', @Source_InvHist_Non_Inventoried_Installs_Delta OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'AssetCostCenterFact', @Source_AssetCostCenterFact OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'InvHist_Software_License_Sharing_Delta', @Source_InvHist_Software_License_Sharing_Delta OUTPUT select @Union_SoftwareLicenseDim = @Union_SoftwareLicenseDim + 'SELECT DISTINCT _ResourceGuid AS SoftwareLicenseGuid , Name COLLATE DATABASE_DEFAULT AS Name FROM ' + @Source_vSoftwareLicense + ' AS vSoftwareLicense' select @Union_SoftwareLicenseInstallationEvaluationFact = @Union_SoftwareLicenseInstallationEvaluationFact + 'SELECT SPII._ResourceGuid AS SoftwareProductGuid , SPII._ComputerResourceGuid AS ComputerGuid , CONVERT(datetime, CONVERT(varchar, SPII.LastEvaluationDate, 112), 112) AS FactMonth , raLoc.ChildResourceGuid AS LocationGuid , CASE WHEN raDept.ChildResourceGuid IS NULL THEN raUserDept.ChildResourceGuid ELSE raDept.ChildResourceGuid END AS DepartmentGuid , a._AssetStatusGuid AS AssetStatusGuid FROM ' + @Source_InvHist_SoftwareProduct_InstallationInfo + ' AS SPII LEFT OUTER JOIN ' + @Source_ResourceAssociation + ' AS raLoc ON SPII._ComputerResourceGuid = raLoc.ParentResourceGuid AND raLoc.ResourceAssociationTypeGuid = ''05DE450F-39EA-4AAE-8C5F-77817889C27C'' LEFT OUTER JOIN ' + @Source_ResourceAssociation + ' AS raOwner ON SPII._ComputerResourceGuid = raOwner.ParentResourceGuid AND raOwner.ResourceAssociationTypeGuid = ''ed35a8d1-bf60-4771-9dde-092c146c485a'' LEFT OUTER JOIN ' + @Source_ResourceAssociation + ' AS raUserDept ON raUserDept.ParentResourceGuid = raOwner.ChildResourceGuid AND raUserDept.ResourceAssociationTypeGuid = ''d9656701-b8ee-48c1-a4e3-5741ca18471d'' LEFT OUTER JOIN ' + @Source_ResourceAssociation + ' AS raDept ON SPII._ComputerResourceGuid = raDept.ParentResourceGuid AND raDept.ResourceAssociationTypeGuid = ''1466e770-4413-4517-a89d-6599b8a7f144'' LEFT OUTER JOIN ' + @Source_vAsset + ' AS a ON SPII._ComputerResourceGuid = a._ResourceGuid' select @Union_SoftwareLicenseInstallationFact = @Union_SoftwareLicenseInstallationFact + 'SELECT DISTINCT SPII._ResourceGuid AS SoftwareProductGuid , SPII._ComputerResourceGuid AS ComputerGuid , raLoc.ChildResourceGuid AS LocationGuid , CASE WHEN raDept.ChildResourceGuid IS NULL THEN raUserDept.ChildResourceGuid ELSE raDept.ChildResourceGuid END AS DepartmentGuid , a._AssetStatusGuid AS AssetStatusGuid FROM ' + @Source_InvHist_SoftwareProduct_InstallationInfo + ' AS SPII LEFT OUTER JOIN ' + @Source_ResourceAssociation + ' AS raLoc ON SPII._ComputerResourceGuid = raLoc.ParentResourceGuid AND raLoc.ResourceAssociationTypeGuid = ''05DE450F-39EA-4AAE-8C5F-77817889C27C'' LEFT OUTER JOIN ' + @Source_ResourceAssociation + ' AS raOwner ON SPII._ComputerResourceGuid = raOwner.ParentResourceGuid AND raOwner.ResourceAssociationTypeGuid = ''ed35a8d1-bf60-4771-9dde-092c146c485a'' LEFT OUTER JOIN ' + @Source_ResourceAssociation + ' AS raUserDept ON raUserDept.ParentResourceGuid = raOwner.ChildResourceGuid AND raUserDept.ResourceAssociationTypeGuid = ''d9656701-b8ee-48c1-a4e3-5741ca18471d'' LEFT OUTER JOIN ' + @Source_ResourceAssociation + ' AS raDept ON SPII._ComputerResourceGuid = raDept.ParentResourceGuid AND raDept.ResourceAssociationTypeGuid = ''1466e770-4413-4517-a89d-6599b8a7f144'' LEFT OUTER JOIN ' + @Source_vAsset + ' AS a ON SPII._ComputerResourceGuid = a._ResourceGuid INNER JOIN ' + @Source_ResourceAssociation + ' AS raLic ON raLic.ChildResourceGuid = SPII._ResourceGuid AND raLic.ResourceAssociationTypeGuid = ''59c323aa-e616-489a-bd30-a671f4f6c8c8'' ' select @Union_SoftwareLicenseOwnedFact = @Union_SoftwareLicenseOwnedFact + 'SELECT vSP._ResourceGuid AS SoftwarePurchaseGuid , DateDim.CalendarDate AS FactMonth , SPCC.Owner AS CostCenterGuid , CASE WHEN SPO.Owner IS NULL THEN 1 ELSE SPO.[Ownership Percentage] / 100 END * CASE WHEN SPCC.Owner IS NULL THEN 1 ELSE SPCC.[Ownership Percentage] / 100 END * vSP.Quantity AS Quantity , raLoc.ChildResourceGuid AS InstalledLocationGuid , raLic.ParentResourceGuid AS SoftwareLicenseGuid , vUser.Guid AS UserOwnerGuid , CASE WHEN vDepartment._ResourceGuid IS NULL THEN raUserDept.ChildResourceGuid ELSE vDepartment._ResourceGuid END AS DepartmentOwnerGuid FROM ' + @Source_vSoftwarePurchase + ' AS vSP INNER JOIN (SELECT DISTINCT CalendarDate, MonthName FROM ' + @Source_ITAnalytics_DateDim + ') AS DateDim ON DATEDIFF(d, vSP.[Purchase Date], DateDim.CalendarDate) >= 0 AND DATEDIFF(d, DateDim.CalendarDate, GETDATE()) >= 0 LEFT OUTER JOIN ' + @Source_vSoftwarePurchase + ' AS vSPQty ON vSP._ResourceGuid = vSPQty._ResourceGuid AND DATEADD(day, 1 - DATEPART(day, vSPQty.[Purchase Date]), CONVERT(datetime, CONVERT(varchar, vSPQty.[Purchase Date], 112), 112)) = DateDim.CalendarDate LEFT OUTER JOIN ' + @Source_ResourceAssociation + ' AS raLoc ON raLoc.ParentResourceGuid = vSP._ResourceGuid AND raLoc.ResourceAssociationTypeGuid = ''83311b0e-5c47-44d7-9469-66720cbeb041'' LEFT OUTER JOIN ' + @Source_ResourceAssociation + ' AS raLic ON raLic.ChildResourceGuid = vSP._ResourceGuid AND raLic.ResourceAssociationTypeGuid = ''f7002a49-11a6-44ea-af19-2e06632ff831'' LEFT OUTER JOIN ' + @Source_Inv_Software_Purchase_Cost_Center_Ownership + ' AS SPCC ON vSP._ResourceGuid = SPCC._ResourceGuid LEFT OUTER JOIN ' + @Source_Inv_Software_Purchase_Owners + ' AS SPO ON vSP._ResourceGuid = SPO._ResourceGuid LEFT OUTER JOIN ' + @Source_vUser + ' AS vUser ON vUser.Guid = SPO.Owner LEFT OUTER JOIN ' + @Source_ResourceAssociation + ' AS raUserDept ON raUserDept.ParentResourceGuid = vUser.Guid AND raUserDept.ResourceAssociationTypeGuid = ''d9656701-b8ee-48c1-a4e3-5741ca18471d'' LEFT OUTER JOIN ' + @Source_vDepartment + ' AS vDepartment ON vDepartment._ResourceGuid = SPO.Owner WHERE (raLic.ParentResourceGuid IS NOT NULL)' select @Union_SoftwareLicenseUsageEvaluationFact = @Union_SoftwareLicenseUsageEvaluationFact + 'SELECT DISTINCT SPII._ResourceGuid AS SoftwareProductGuid , SPII._ComputerResourceGuid AS ComputerGuid , CONVERT(datetime, CONVERT(varchar, SPII.LastEvaluationDate, 112), 112) AS EvalDate , raLoc.ChildResourceGuid AS LocationGuid , CASE WHEN raDept.ChildResourceGuid IS NULL THEN raUserDept.ChildResourceGuid ELSE raDept.ChildResourceGuid END AS DepartmentGuid , a._AssetStatusGuid AS AssetStatusGuid FROM ' + @Source_InvHist_SoftwareProduct_InstallationInfo + ' AS SPII LEFT OUTER JOIN ' + @Source_ResourceAssociation + ' AS raLoc ON SPII._ComputerResourceGuid = raLoc.ParentResourceGuid AND raLoc.ResourceAssociationTypeGuid = ''05DE450F-39EA-4AAE-8C5F-77817889C27C'' LEFT OUTER JOIN ' + @Source_ResourceAssociation + ' AS raOwner ON SPII._ComputerResourceGuid = raOwner.ParentResourceGuid AND raOwner.ResourceAssociationTypeGuid = ''ed35a8d1-bf60-4771-9dde-092c146c485a'' LEFT OUTER JOIN ' + @Source_ResourceAssociation + ' AS raUserDept ON raUserDept.ParentResourceGuid = raOwner.ChildResourceGuid AND raUserDept.ResourceAssociationTypeGuid = ''d9656701-b8ee-48c1-a4e3-5741ca18471d'' LEFT OUTER JOIN ' + @Source_ResourceAssociation + ' AS raDept ON SPII._ComputerResourceGuid = raDept.ParentResourceGuid AND raDept.ResourceAssociationTypeGuid = ''1466e770-4413-4517-a89d-6599b8a7f144'' LEFT OUTER JOIN ' + @Source_vAsset + ' AS a ON SPII._ComputerResourceGuid = a._ResourceGuid WHERE (SPII.Usage > 0)' select @Union_SoftwareLicenseUsageFact = @Union_SoftwareLicenseUsageFact + 'SELECT DISTINCT SPII._ResourceGuid AS SoftwareProductGuid , SPII._ComputerResourceGuid AS ComputerGuid , raLoc.ChildResourceGuid AS LocationGuid , CASE WHEN raDept.ChildResourceGuid IS NULL THEN raUserDept.ChildResourceGuid ELSE raDept.ChildResourceGuid END AS DepartmentGuid , a._AssetStatusGuid AS AssetStatusGuid FROM ' + @Source_InvHist_SoftwareProduct_InstallationInfo + ' AS SPII LEFT OUTER JOIN ' + @Source_ResourceAssociation + ' AS raLoc ON SPII._ComputerResourceGuid = raLoc.ParentResourceGuid AND raLoc.ResourceAssociationTypeGuid = ''05DE450F-39EA-4AAE-8C5F-77817889C27C'' LEFT OUTER JOIN ' + @Source_ResourceAssociation + ' AS raOwner ON SPII._ComputerResourceGuid = raOwner.ParentResourceGuid AND raOwner.ResourceAssociationTypeGuid = ''ed35a8d1-bf60-4771-9dde-092c146c485a'' LEFT OUTER JOIN ' + @Source_ResourceAssociation + ' AS raUserDept ON raUserDept.ParentResourceGuid = raOwner.ChildResourceGuid AND raUserDept.ResourceAssociationTypeGuid = ''d9656701-b8ee-48c1-a4e3-5741ca18471d'' LEFT OUTER JOIN ' + @Source_ResourceAssociation + ' AS raDept ON SPII._ComputerResourceGuid = raDept.ParentResourceGuid AND raDept.ResourceAssociationTypeGuid = ''1466e770-4413-4517-a89d-6599b8a7f144'' LEFT OUTER JOIN ' + @Source_vAsset + ' AS a ON SPII._ComputerResourceGuid = a._ResourceGuid WHERE (SPII.Usage > 0)' select @Union_SoftwarePurchaseDim = @Union_SoftwarePurchaseDim + 'SELECT _ResourceGuid AS SoftwarePurchaseGuid , Name COLLATE DATABASE_DEFAULT AS Name , Description COLLATE DATABASE_DEFAULT AS Description FROM ' + @Source_vSoftwarePurchase + ' AS vSP' select @Union_SoftwarePurchaseFact = @Union_SoftwarePurchaseFact + 'SELECT vSP._ResourceGuid AS SoftwarePurchaseGuid , SPCC.Owner AS CostCenterGuid , CASE WHEN SPO.Owner IS NULL THEN 1 ELSE SPO.[Ownership Percentage] / 100 END * CASE WHEN SPCC.Owner IS NULL THEN 1 ELSE SPCC.[Ownership Percentage] / 100 END * vSP.Quantity AS Quantity , CONVERT(datetime, CONVERT(varchar, vSP.[Purchase Date], 112), 112) AS PurchaseDate , raLoc.ChildResourceGuid AS InstalledLocationGuid , raLic.ParentResourceGuid AS SoftwareLicenseGuid , vUser.Guid AS UserOwnerGuid , CASE WHEN vDepartment._ResourceGuid IS NULL THEN raUserDept.ChildResourceGuid ELSE vDepartment._ResourceGuid END AS DepartmentOwnerGuid FROM ' + @Source_vSoftwarePurchase + ' AS vSP LEFT OUTER JOIN ' + @Source_ResourceAssociation + ' AS raLoc ON raLoc.ParentResourceGuid = vSP._ResourceGuid AND raLoc.ResourceAssociationTypeGuid = ''83311b0e-5c47-44d7-9469-66720cbeb041'' LEFT OUTER JOIN ' + @Source_ResourceAssociation + ' AS raLic ON raLic.ChildResourceGuid = vSP._ResourceGuid AND raLic.ResourceAssociationTypeGuid = ''f7002a49-11a6-44ea-af19-2e06632ff831'' LEFT OUTER JOIN ' + @Source_Inv_Software_Purchase_Cost_Center_Ownership + ' AS SPCC ON vSP._ResourceGuid = SPCC._ResourceGuid LEFT OUTER JOIN ' + @Source_Inv_Software_Purchase_Owners + ' AS SPO ON vSP._ResourceGuid = SPO._ResourceGuid LEFT OUTER JOIN ' + @Source_vUser + ' AS vUser ON vUser.Guid = SPO.Owner LEFT OUTER JOIN ' + @Source_ResourceAssociation + ' AS raUserDept ON raUserDept.ParentResourceGuid = vUser.Guid AND raUserDept.ResourceAssociationTypeGuid = ''d9656701-b8ee-48c1-a4e3-5741ca18471d'' LEFT OUTER JOIN ' + @Source_vDepartment + ' AS vDepartment ON vDepartment._ResourceGuid = SPO.Owner' if @Source_InvHist_Non_Inventoried_Installs_Delta is not null begin select @Union_NonInventoriedInstallFact = @Union_NonInventoriedInstallFact + 'IF EXISTS ( SELECT 1 FROM [' + @ServerName + '].[' + @DatabaseName + '].[sys].[sysobjects] WHERE name = ''InvHist_Non_Inventoried_Installs_Delta'') insert into @retNonInvInstallFact SELECT DateDim_1.CalendarDate AS FactMonth , CASE WHEN _RowAdded = 0 THEN nii.Count * - 1 ELSE Count END AS NonInventoriedInstalls , nii._ResourceGuid AS SoftwareLicenseGuid , NULL AS CostCenterGuid , NULL AS DepartmentGuid , NULL AS LocationGuid FROM ' + @Source_InvHist_Non_Inventoried_Installs_Delta + ' AS nii INNER JOIN (SELECT DISTINCT CalendarDate, MonthName FROM ' + @Source_ITAnalytics_DateDim + ' AS ITAnalytics_DateDim_1) AS DateDim_1 ON DATEDIFF(d, nii.CreatedDate, DateDim_1.CalendarDate) >= 0 AND DATEDIFF(d, DateDim_1.CalendarDate, GETDATE()) >= 0 WHERE (nii._ResourceGuid IN (SELECT _ResourceGuid FROM ' + @Source_vSoftwareLicense + ' AS vSoftwareLicense)) ' end if @Source_InvHist_Software_License_Sharing_Delta is not null begin select @Union_BorrowedLicenseCountFact = @Union_BorrowedLicenseCountFact + 'IF EXISTS ( SELECT 1 FROM [' + @ServerName + '].[' + @DatabaseName + '].[sys].[sysobjects] WHERE name = ''InvHist_Software_License_Sharing_Delta'') insert into @retBorrowedLicenseFact SELECT NULL AS SoftwarePurchaseGuid , DateDim_1.CalendarDate AS FactMonth , NULL AS CostCenterGuid , CASE WHEN _RowAdded = 0 THEN Number * - 1 ELSE Number END AS Quantity , NULL AS InstalledLocationGuid , lic._ResourceGuid AS SoftwareLicenseGuid , lic.[License Donator] AS BorrowingFromLicenseGuid , NULL AS UserOwnerGuid , NULL AS DepartmentOwnerGuid FROM ' + @Source_InvHist_Software_License_Sharing_Delta + ' AS lic INNER JOIN (SELECT DISTINCT CalendarDate, MonthName FROM ' + @Source_ITAnalytics_DateDim + ' AS ITAnalytics_DateDim_1) AS DateDim_1 ON DATEDIFF(d, lic.CreatedDate, DateDim_1.CalendarDate) >= 0 AND DATEDIFF(D, DateDim_1.CalendarDate, GETDATE()) >= 0 ' end if @Source_InvHist_Software_License_Sharing_Delta is not null begin select @Union_DonatedLicenseFact = @Union_DonatedLicenseFact + 'IF EXISTS ( SELECT 1 FROM [' + @ServerName + '].[' + @DatabaseName + '].[sys].[sysobjects] WHERE name = ''InvHist_Software_License_Sharing_Delta'') insert into @retDonatedLicenseFact SELECT NULL AS SoftwarePurchaseGuid , DateDim_1.CalendarDate AS FactMonth , NULL AS CostCenterGuid , (CASE WHEN _RowAdded = 0 THEN Number * - 1 ELSE Number END) AS Quantity , NULL AS InstalledLocationGuid , lic.[License Donator] AS SoftwareLicenseGuid , lic._ResourceGuid AS DonatedToLicenseGuid , NULL AS UserOwnerGuid , NULL AS DepartmentOwnerGuid FROM ' + @Source_InvHist_Software_License_Sharing_Delta + ' AS lic INNER JOIN (SELECT DISTINCT CalendarDate, MonthName FROM ' + @Source_ITAnalytics_DateDim + ' AS ITAnalytics_DateDim_1) AS DateDim_1 ON DATEDIFF(d, lic.CreatedDate, DateDim_1.CalendarDate) >= 0 AND DATEDIFF(d, DateDim_1.CalendarDate, GETDATE()) >= 0 ' end select @Union_SoftwareProductLicenseFact = @Union_SoftwareProductLicenseFact + 'SELECT raLic.ChildResourceGuid AS SoftwareProductGuid , raLic.ParentResourceGuid AS SoftwareLicenseGuid FROM ' + @Source_ResourceAssociation + ' AS raLic LEFT OUTER JOIN ' + @Source_InvHist_SoftwareProduct_InstallationInfo + ' AS SPII ON raLic.ChildResourceGuid = SPII._ResourceGuid WHERE (raLic.ResourceAssociationTypeGuid = ''59c323aa-e616-489a-bd30-a671f4f6c8c8'') GROUP BY SPII._ResourceGuid, raLic.ChildResourceGuid, raLic.ParentResourceGuid' fetch next from LinkedServerCursor into @ServerName, @DatabaseName end close LinkedServerCursor deallocate LinkedServerCursor select @Union_SoftwareLicenseDim = @Union_SoftwareLicenseDim + ')' select @Union_SoftwareLicenseInstallationEvaluationFact = @Union_SoftwareLicenseInstallationEvaluationFact + ')' select @Union_SoftwareLicenseInstallationFact = @Union_SoftwareLicenseInstallationFact + ')' select @Union_SoftwareLicenseOwnedFact = @Union_SoftwareLicenseOwnedFact + ')' select @Union_SoftwareLicenseUsageEvaluationFact = @Union_SoftwareLicenseUsageEvaluationFact + ')' select @Union_SoftwareLicenseUsageFact = @Union_SoftwareLicenseUsageFact + ')' select @Union_SoftwarePurchaseDim = @Union_SoftwarePurchaseDim + ')' select @Union_SoftwarePurchaseFact = @Union_SoftwarePurchaseFact + ')' select @Union_NonInventoriedInstallFact = @Union_NonInventoriedInstallFact + '' select @Union_BorrowedLicenseCountFact = @Union_BorrowedLicenseCountFact + '' select @Union_DonatedLicenseFact = @Union_DonatedLicenseFact + '' select @Union_SoftwareProductLicenseFact = @Union_SoftwareProductLicenseFact + ')' end begin -- Patch Management DSV select @FirstServer = 1 declare LinkedServerCursor cursor forward_only for select [ServerName], [DatabaseName] from #DependencyMap where [PatchManagementDSV] = 1 open LinkedServerCursor fetch next from LinkedServerCursor into @ServerName, @DatabaseName while @@fetch_status = 0 begin if @FirstServer = 0 begin select @Union_PatchManagementSoftwareUpdateDim = @Union_PatchManagementSoftwareUpdateDim + ' UNION ' select @Union_ApplicablePatchFact = @Union_ApplicablePatchFact + ' UNION ' select @Union_PatchVulnerabilityFact = @Union_PatchVulnerabilityFact + ' UNION ' select @Union_InstalledPatchFact = @Union_InstalledPatchFact + ' UNION ' end else begin select @FirstServer = 0 end exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vPMCore_SoftwareUpdate', @Source_vPMCore_SoftwareUpdate OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'ResourceAssociation', @Source_ResourceAssociation OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_Software_Bulletin', @Source_Inv_Software_Bulletin OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vResourceEx', @Source_vResourceEx OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vCompany', @Source_vCompany OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_Applicable_Microsoft_Software_Update', @Source_Inv_Applicable_Microsoft_Software_Update OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_Applicable_Adobe_Software_Update', @Source_Inv_Applicable_Adobe_Software_Update OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_Installed_Microsoft_Software_Update', @Source_Inv_Installed_Microsoft_Software_Update OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_Installed_Adobe_Software_Update', @Source_Inv_Installed_Adobe_Software_Update OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_Applicable_Novell_Software_Update', @Source_Inv_Applicable_Novell_Software_Update OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_Applicable_Red_Hat_Software_Update', @Source_Inv_Applicable_Red_Hat_Software_Update OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_Installed_Novell_Software_Update', @Source_Inv_Installed_Novell_Software_Update OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_Installed_Red_Hat_Software_Update', @Source_Inv_Installed_Red_Hat_Software_Update OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_AeX_AC_Identification', @Source_Inv_AeX_AC_Identification OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vSoftwareUpdateInventoryRuleAssociations', @Source_vSoftwareUpdateInventoryRuleAssociations OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vResourceItem', @Source_vResourceItem OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vSoftwareUpdateInventoryRuleAssociations', @Source_vSoftwareUpdateInventoryRuleAssociations OUTPUT select @Union_PatchManagementSoftwareUpdateDim = @Union_PatchManagementSoftwareUpdateDim + 'SELECT DISTINCT vpm._ResourceGuid AS SWUGuid , vpm.Name COLLATE DATABASE_DEFAULT AS [Filename] , ISNULL(vpm.SeverityName, ''Unclassified'') COLLATE DATABASE_DEFAULT AS Severity , ISNULL(sevr.SeverityName, ''Not Set'') COLLATE DATABASE_DEFAULT AS CustomSeverity , swbName.Name COLLATE DATABASE_DEFAULT AS [Reference] , vpm.ReleaseDate , c.Name COLLATE DATABASE_DEFAULT AS [Provider] , vpm.RebootRequired , vpm.PatchSize FROM ' + @Source_vPMCore_SoftwareUpdate + ' AS vpm INNER JOIN ' + @Source_ResourceAssociation + ' AS upd2bul ON vpm._ResourceGuid = upd2bul.ChildResourceGuid INNER JOIN ' + @Source_Inv_Software_Bulletin + ' AS sb ON upd2bul.ParentResourceGuid = sb._ResourceGuid INNER JOIN ' + @Source_vResourceEx + ' AS swbName ON sb._ResourceGuid = swbName.Guid INNER JOIN ' + @Source_ResourceAssociation + ' AS upd2co ON vpm._ResourceGuid = upd2co.ParentResourceGuid AND upd2co.ResourceAssociationTypeGuid = ''292DBD81-1526-423A-AE6D-F44EB46C5B16'' INNER JOIN ' + @Source_vCompany + ' AS c ON upd2co.ChildResourceGuid = c._ResourceGuid LEFT OUTER JOIN ' + @Source_ResourceAssociation + ' AS ra2 ON vpm._ResourceGuid = ra2.ChildResourceGuid LEFT OUTER JOIN (SELECT _ResourceGuid, SeverityName FROM vPMCore_SeverityRating WHERE (SeverityRatingSystemGuid = ''6CCEF81F-F791-4DC4-8FC6-90D149FC0187'')) AS sevr ON ra2.ParentResourceGuid = sevr._ResourceGuid WHERE (vpm.PatchSize IS NOT NULL)' declare @PM_novellExists as integer declare @PM_redhatExists as integer declare @PM_applicableSql as nvarchar(4000) declare @PM_installedSql as nvarchar(4000) set @PM_applicableSql = 'select * from ' + @Source_Inv_Applicable_Microsoft_Software_Update + ' AS Inv_Applicable_Microsoft_Software_Update union select * from ' + @Source_Inv_Applicable_Adobe_Software_Update + ' AS Inv_Applicable_Adobe_Software_Update' set @PM_installedSql = 'select * from ' + @Source_Inv_Installed_Microsoft_Software_Update + ' AS Inv_Installed_Microsoft_Software_Update union select * from ' + @Source_Inv_Installed_Adobe_Software_Update + ' AS Inv_Installed_Adobe_Software_Update' if len(@Source_Inv_Applicable_Novell_Software_Update) > 0 begin set @PM_applicableSql = @PM_applicableSql + ' UNION select * from ' + @Source_Inv_Applicable_Novell_Software_Update + ' AS Inv_Applicable_Novell_Software_Update' end if len(@Source_Inv_Applicable_Red_Hat_Software_Update) > 0 begin set @PM_applicableSql = @PM_applicableSql + ' UNION select * from ' + @Source_Inv_Applicable_Red_Hat_Software_Update + ' AS Inv_Applicable_Red_Hat_Software_Update' end if len(@Source_Inv_Installed_Novell_Software_Update) > 0 begin set @PM_installedSql = @PM_installedSql + ' UNION select * from ' + @Source_Inv_Installed_Novell_Software_Update + ' AS Inv_Installed_Novell_Software_Update' end if len(@Source_Inv_Installed_Red_Hat_Software_Update) > 0 begin set @PM_installedSql = @PM_installedSql + ' UNION select * from ' + @Source_Inv_Installed_Red_Hat_Software_Update + ' AS Inv_Installed_Red_Hat_Software_Update' end set @Union_ApplicablePatchFact = @Union_ApplicablePatchFact + 'SELECT ACID._id AS ResourceId, asu._ResourceGuid AS ResourceGuid, ira.SWUGuid FROM ' + @Source_vResourceItem + ' AS r INNER JOIN ' + @Source_Inv_AeX_AC_Identification + ' AS ACID ON ACID._ResourceGuid = r.Guid INNER JOIN (' + @PM_applicableSql + ') AS asu ON r.Guid = asu._ResourceGuid INNER JOIN ' + @Source_vSoftwareUpdateInventoryRuleAssociations + ' AS ira ON asu.InventoryRuleGuid = ira.ApplicableGuid WHERE (ira.SupersededBy IS NULL)' set @Union_PatchVulnerabilityFact = @Union_PatchVulnerabilityFact + 'SELECT ACID._id AS ResourceId, pmta.ResourceGuid, pmta.SWUGuid FROM ' + @Source_vResourceItem + ' AS r INNER JOIN ' + @Source_Inv_AeX_AC_Identification + ' AS ACID ON ACID._ResourceGuid = r.Guid INNER JOIN (SELECT asu._ResourceGuid AS ResourceGuid, ira.SWUGuid FROM (' + @PM_applicableSql + ') AS asu INNER JOIN ' + @Source_vSoftwareUpdateInventoryRuleAssociations + ' AS ira ON asu.InventoryRuleGuid = ira.ApplicableGuid WHERE (ira.SupersededBy IS NULL)) AS pmta ON r.Guid = pmta.ResourceGuid LEFT OUTER JOIN (SELECT isu._ResourceGuid AS ResourceGuid, ira.SWUGuid FROM (' + @PM_installedSql + ') AS isu INNER JOIN ' + @Source_vSoftwareUpdateInventoryRuleAssociations + ' AS ira ON isu.InventoryRuleGuid = ira.InstalledGuid WHERE (ira.SupersededBy IS NULL)) AS pmti ON pmta.ResourceGuid = pmti.ResourceGuid AND pmta.SWUGuid = pmti.SWUGuid WHERE (pmti.ResourceGuid IS NULL)' set @Union_InstalledPatchFact = @Union_InstalledPatchFact + 'SELECT ACID._id AS ResourceId, isu._ResourceGuid AS ResourceGuid, ira.SWUGuid FROM ' + @Source_vResourceItem + ' AS r INNER JOIN ' + @Source_Inv_AeX_AC_Identification + ' AS ACID ON ACID._ResourceGuid = r.Guid INNER JOIN (' + @PM_installedSql + ') AS isu ON r.Guid = isu._ResourceGuid INNER JOIN ' + @Source_vSoftwareUpdateInventoryRuleAssociations + ' AS ira ON isu.InventoryRuleGuid = ira.InstalledGuid WHERE (ira.SupersededBy IS NULL)' fetch next from LinkedServerCursor into @ServerName, @DatabaseName end close LinkedServerCursor deallocate LinkedServerCursor select @Union_PatchManagementSoftwareUpdateDim = @Union_PatchManagementSoftwareUpdateDim + ')' select @Union_ApplicablePatchFact = @Union_ApplicablePatchFact + ')' select @Union_PatchVulnerabilityFact = @Union_PatchVulnerabilityFact + ')' select @Union_InstalledPatchFact = @Union_InstalledPatchFact + ')' end begin -- Client Inventory DSV select @FirstServer = 1 declare LinkedServerCursor cursor forward_only for select [ServerName], [DatabaseName], [ResourceManagerURL], [ResourceEditURL] from #DependencyMap where [ClientInventoryDSV] = 1 open LinkedServerCursor fetch next from LinkedServerCursor into @ServerName, @DatabaseName, @thisResourceManagerURL, @thisResourceEditURL while @@fetch_status = 0 begin if @FirstServer = 0 begin select @Union_InstalledFileFact = @Union_InstalledFileFact + ' UNION ' select @Union_InstalledSoftwareFact = @Union_InstalledSoftwareFact + ' UNION ' select @Union_ComputerDim = @Union_ComputerDim + ' UNION ' select @Union_ComputerFact = @Union_ComputerFact + ' UNION ' select @Union_AddRemoveProgramsDim = @Union_AddRemoveProgramsDim + ' UNION ' select @Union_WindowsFileDim = @Union_WindowsFileDim + ' UNION ' select @Union_LogicalDiskDim = @Union_LogicalDiskDim + ' UNION ' select @Union_LogicalDiskFact = @Union_LogicalDiskFact + ' UNION ' select @Union_PhysicalMemoryArrayFact = @Union_PhysicalMemoryArrayFact + ' UNION ' select @Union_PhysicalMemoryFact = @Union_PhysicalMemoryFact + ' UNION ' select @Union_ProcessorDim = @Union_ProcessorDim + ' UNION ' select @Union_ProcessorFact = @Union_ProcessorFact + ' UNION ' select @Union_SoftwareComponentDim = @Union_SoftwareComponentDim + ' UNION ' select @Union_SoftwareComponentFileFact = @Union_SoftwareComponentFileFact + ' UNION ' select @Union_SoftwareProductDim = @Union_SoftwareProductDim + ' UNION ' select @Union_SoftwareProductComponentFact = @Union_SoftwareProductComponentFact + ' UNION ' select @Union_ApplicationMeteringFact = @Union_ApplicationMeteringFact + ' UNION ' end else begin select @FirstServer = 0 end exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_AeX_AC_Identification', @Source_Inv_AeX_AC_Identification OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'ResourceAssociation', @Source_ResourceAssociation OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vSoftwareProduct', @Source_vSoftwareProduct OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vSoftwareComponentSearch', @Source_vSoftwareComponentSearch OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_HW_Processor', @Source_Inv_HW_Processor OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_HW_Logical_Device', @Source_Inv_HW_Logical_Device OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_HW_Physical_Memory', @Source_Inv_HW_Physical_Memory OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_HW_Physical_Memory_Array', @Source_Inv_HW_Physical_Memory_Array OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_HW_Logical_Disk', @Source_Inv_HW_Logical_Disk OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vWindowsFile', @Source_vWindowsFile OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_AeX_AC_Discovery', @Source_Inv_AeX_AC_Discovery OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vRM_Computer_Item', @Source_vRM_Computer_Item OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vComputerResource', @Source_vComputerResource OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vHWComputerSystem', @Source_vHWComputerSystem OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vHWChassis', @Source_vHWChassis OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_AeX_AC_TCPIP', @Source_Inv_AeX_AC_TCPIP OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_AeX_AC_Primary_User', @Source_Inv_AeX_AC_Primary_User OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vSource', @Source_vSource OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_InstalledSoftware', @Source_Inv_InstalledSoftware OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_AddRemoveProgram', @Source_Inv_AddRemoveProgram OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_Installed_File_Details', @Source_Inv_Installed_File_Details OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_Monthly_summary', @Source_Inv_Monthly_summary OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'InvHist_Monthly_summary', @Source_InvHist_Monthly_summary OUTPUT declare @AMArchiveExists as integer --See if the table exists. set @AMArchiveExists = (SELECT count(*) FROM sysobjects O WHERE ObjectProperty(O.ID,'IsUserTable')=1 AND O.Name='InvHist_Monthly_summary') IF @AMArchiveExists = 1 -- archive table Exists begin set @Union_ApplicationMeteringFact = @Union_ApplicationMeteringFact + 'SELECT ACID._id AS ResourceId , ims._ResourceGuid AS ResourceGuid , ims.FileResourceGuid , ims.UserGuid , ims.[Last Start] , ims.[Month Year] COLLATE DATABASE_DEFAULT AS [Month Year] , ims.[Run Count] , ims.[Denial Count] , ims.[Total Run Time] , ims.[Peak Memory] , ims.[Average CPU Usage] , ims.[Month End Summary] , MONTH(DateDim.Month) as [Month] , YEAR(DateDim.Month) as [Year] , DateDim.Month AS FactMonth FROM ' + @Source_Inv_Monthly_summary + ' AS ims INNER JOIN ' + @Source_Inv_AeX_AC_Identification + ' AS ACID ON ACID._ResourceGuid = ims._ResourceGuid INNER JOIN (SELECT DISTINCT Month, MonthName FROM ' + @Source_ITAnalytics_DateDim + ') AS DateDim ON ims.[Month Year] COLLATE DATABASE_DEFAULT = DateDim.MonthName COLLATE DATABASE_DEFAULT union SELECT ACID._id AS ResourceId , ims._ResourceGuid AS ResourceGuid , ims.FileResourceGuid, ims.UserGuid , ims.[Last Start] , ims.[Month Year] COLLATE DATABASE_DEFAULT AS [Month Year] , ims.[Run Count] , ims.[Denial Count] , ims.[Total Run Time] , ims.[Peak Memory] , ims.[Average CPU Usage] , ims.[Month End Summary] , MONTH(DateDim.Month) as [Month] , YEAR(DateDim.Month) as [Year] , DateDim.Month AS FactMonth FROM ' + @Source_InvHist_Monthly_summary + ' AS ims INNER JOIN ' + @Source_Inv_AeX_AC_Identification + ' AS ACID ON ACID._ResourceGuid = ims._ResourceGuid INNER JOIN (SELECT DISTINCT Month, MonthName FROM ' + @Source_ITAnalytics_DateDim + ') AS DateDim ON ims.[Month Year] COLLATE DATABASE_DEFAULT = DateDim.MonthName COLLATE DATABASE_DEFAULT where ims.SnapshotId = (select max(SnapshotId) from InvHist_Monthly_summary)' end ELSE begin set @Union_ApplicationMeteringFact = @Union_ApplicationMeteringFact + 'SELECT ACID._id AS ResourceId , ims._ResourceGuid AS ResourceGuid , ims.FileResourceGuid , ims.UserGuid , ims.[Last Start] , ims.[Month Year] COLLATE DATABASE_DEFAULT AS [Month Year] , ims.[Run Count] , ims.[Denial Count] , ims.[Total Run Time] , ims.[Peak Memory] , ims.[Average CPU Usage] , ims.[Month End Summary] , MONTH(DateDim.Month) as [Month] , YEAR(DateDim.Month) as [Year] , DateDim.Month AS FactMonth FROM ' + @Source_Inv_Monthly_summary+ ' AS ims INNER JOIN ' + @Source_Inv_AeX_AC_Identification + ' AS ACID ON ACID._ResourceGuid = ims._ResourceGuid INNER JOIN (SELECT DISTINCT Month, MonthName FROM ' + @Source_ITAnalytics_DateDim + ') AS DateDim ON ims.[Month Year] COLLATE DATABASE_DEFAULT = DateDim.MonthName COLLATE DATABASE_DEFAULT' end select @Union_InstalledFileFact = @Union_InstalledFileFact + 'SELECT Inv_Installed_File_Details._ResourceGuid AS ResourceGuid , Inv_Installed_File_Details._id AS InstalledFileID , ACID._id AS ResourceId , Inv_Installed_File_Details.FileResourceGuid , Inv_Installed_File_Details.Name COLLATE DATABASE_DEFAULT AS Name , Inv_Installed_File_Details.Path COLLATE DATABASE_DEFAULT AS Path , Inv_Installed_File_Details.ModifiedTime , Inv_Installed_File_Details.FileAttributes , Inv_Installed_File_Details.VirtualSoftwareGuid FROM ' + @Source_Inv_Installed_File_Details + ' AS Inv_Installed_File_Details INNER JOIN ' + @Source_Inv_AeX_AC_Identification + ' AS ACID ON ACID._ResourceGuid = Inv_Installed_File_Details._ResourceGuid' select @Union_InstalledSoftwareFact = @Union_InstalledSoftwareFact + 'SELECT DISTINCT Inv_InstalledSoftware._id AS InstalledSoftwareID , ACID._id AS ResourceId , Inv_InstalledSoftware._ResourceGuid AS ResourceGuid , Inv_InstalledSoftware._SoftwareComponentGuid AS SoftwareComponentGuid , Inv_AddRemoveProgram._id AS AddRemoveProgramID FROM ' + @Source_Inv_InstalledSoftware + ' AS Inv_InstalledSoftware INNER JOIN ' + @Source_Inv_AeX_AC_Identification + ' AS ACID ON ACID._ResourceGuid = Inv_InstalledSoftware._ResourceGuid LEFT OUTER JOIN ' + @Source_Inv_AddRemoveProgram + ' AS Inv_AddRemoveProgram ON Inv_InstalledSoftware._ResourceGuid = Inv_AddRemoveProgram._ResourceGuid AND Inv_InstalledSoftware._SoftwareComponentGuid = Inv_AddRemoveProgram._SoftwareComponentGuid' select @Union_ComputerDim = @Union_ComputerDim + 'SELECT item.Guid AS ResourceGuid , ISNULL(acid.Name, item.Name) COLLATE DATABASE_DEFAULT AS [Name] , ISNULL(acid.Domain, N'''') COLLATE DATABASE_DEFAULT AS [Domain] , ISNULL(usr.[User], N'''') COLLATE DATABASE_DEFAULT AS [Primary User] , ISNULL(acid.[Last Logon User], N'''') COLLATE DATABASE_DEFAULT AS [Last Logon User] , ISNULL(acid.[OS Name], N'''') COLLATE DATABASE_DEFAULT AS [OS Name] , ISNULL(acid.[OS Type], N'''') COLLATE DATABASE_DEFAULT AS [OS Type] , ISNULL(acid.[OS Version], N'''') COLLATE DATABASE_DEFAULT AS [OS Version] , ISNULL(acid.[OS Revision], N'''') COLLATE DATABASE_DEFAULT AS [OS Revision] , ISNULL(tcp.[IP Address], N'''') COLLATE DATABASE_DEFAULT AS [IP Address] , ISNULL(s.Name, N'''') COLLATE DATABASE_DEFAULT AS [Server] , ISNULL(tcp.[MAC Address], N'''') COLLATE DATABASE_DEFAULT AS [MAC Address] , ISNULL(acid.[System Type], N'''') COLLATE DATABASE_DEFAULT AS [System Type] , CONVERT(datetime, CONVERT(varchar, item.CreatedDate, 112), 112) AS CreatedDate , CONVERT(datetime, CONVERT(varchar, acid.[Client Date], 112), 112) AS [Last Basic Inventory Date] , vHWComputerSystem.[Number Of Processors] , vHWComputerSystem.[Total Physical Memory (Bytes)] / (1024 * 1024) AS [Total Physical Memory MB] , ld.[Total Physical Disk MB] , ld.[Total Free Space MB] , vHWComputerSystem.Manufacturer COLLATE DATABASE_DEFAULT AS [Computer System Manufacturer] , vHWComputerSystem.Model COLLATE DATABASE_DEFAULT AS [Computer System Model] , vHWComputerSystem.[Identifying Number] COLLATE DATABASE_DEFAULT AS [Computer System Serial Number] , vHWChassis.Manufacturer COLLATE DATABASE_DEFAULT AS [Chassis Manufacturer] , vHWChassis.Model COLLATE DATABASE_DEFAULT AS [Chassis Model] , vHWChassis.[Serial Number] COLLATE DATABASE_DEFAULT AS [Chassis Serial Number] , discovery.[Discovery Method] COLLATE DATABASE_DEFAULT AS [Discovery Method] , discovery.DiscoveryDate , CASE WHEN item.IsManaged = 1 THEN ''Yes'' ELSE ''No'' END AS [Altiris Managed] , CPU.[CPU Speed] , CPU.[CPU Count] , CPU.[CPU Type] COLLATE DATABASE_DEFAULT AS [CPU Type] , ''' + @thisResourceManagerURL + '?ItemGuid='' + convert(varchar(50),item.Guid) COLLATE DATABASE_DEFAULT AS [ResourceManagerURL] , ''' + @thisResourceEditURL + '?ItemGuid='' + convert(varchar(50),item.Guid) COLLATE DATABASE_DEFAULT AS [ResourceEditURL] FROM ' + @Source_vRM_Computer_Item + ' AS item LEFT OUTER JOIN ' + @Source_vSource + ' AS s ON item.OwnerNSGuid = s.Guid LEFT OUTER JOIN ' + @Source_Inv_AeX_AC_Identification + ' AS acid ON acid._ResourceGuid = item.Guid LEFT OUTER JOIN ' + @Source_Inv_AeX_AC_Discovery + ' AS discovery ON discovery._ResourceGuid = item.Guid LEFT OUTER JOIN ' + @Source_Inv_AeX_AC_Primary_User + ' AS usr ON usr._ResourceGuid = item.Guid AND usr._id = (SELECT MAX(_id) AS PUID FROM ' + @Source_Inv_AeX_AC_Primary_User + ' AS p WHERE (item.Guid = _ResourceGuid)) LEFT OUTER JOIN ' + @Source_Inv_AeX_AC_TCPIP + ' AS tcp ON tcp._ResourceGuid = item.Guid AND tcp._id = (SELECT MAX(_id) AS Expr1 FROM ' + @Source_Inv_AeX_AC_TCPIP + ' AS t WHERE (item.Guid = _ResourceGuid) AND ([IP Address] NOT IN (''127.0.0.1'', ''0.0.0.0'', '''')) AND (NOT ([IP Address] IS NULL))) LEFT OUTER JOIN ' + @Source_vHWChassis + ' AS vHWChassis ON item.Guid = vHWChassis._ResourceGuid LEFT OUTER JOIN ' + @Source_vHWComputerSystem + ' AS vHWComputerSystem ON item.Guid = vHWComputerSystem._ResourceGuid LEFT OUTER JOIN (SELECT _ResourceGuid, SUM([Size (Bytes)]) / (1024 * 1024) AS [Total Physical Disk MB], SUM([Free Space (Bytes)]) / (1024 * 1024) AS [Total Free Space MB] FROM ' + @Source_Inv_HW_Logical_Disk + ' AS Inv_HW_Logical_Disk WHERE ([Logical Disk Type] = 3) GROUP BY _ResourceGuid) AS ld ON ld._ResourceGuid = item.Guid LEFT OUTER JOIN (SELECT Inv_HW_Processor._ResourceGuid , MAX(Inv_HW_Processor.[Max Clock Speed (Mega-hertz)]) AS [CPU Speed] , MAX(Inv_HW_Logical_Device.Model) AS [CPU Type] , COUNT(Inv_HW_Processor._id) AS [CPU Count] FROM ' + @Source_Inv_HW_Logical_Device + ' AS Inv_HW_Logical_Device INNER JOIN ' + @Source_Inv_HW_Processor + ' AS Inv_HW_Processor ON Inv_HW_Logical_Device.[Device ID] = Inv_HW_Processor.[Device ID] AND Inv_HW_Logical_Device._ResourceGuid = Inv_HW_Processor._ResourceGuid GROUP BY Inv_HW_Processor._ResourceGuid) AS CPU ON item.Guid = CPU._ResourceGuid ' select @Union_ComputerFact = @Union_ComputerFact + 'SELECT r.Guid AS ResourceGuid , CONVERT(datetime, CONVERT(varchar, item.CreatedDate, 112), 112) AS CreatedDate , CONVERT(datetime, CONVERT(varchar, ACID.[Client Date], 112), 112) AS [Last Basic Inventory Date] , CONVERT(datetime, CONVERT(varchar, discovery.DiscoveryDate, 112), 112) AS [Discovery Date] FROM ' + @Source_vComputerResource + ' AS r INNER JOIN ' + @Source_vRM_Computer_Item + ' AS item ON r.Guid = item.Guid INNER JOIN ' + @Source_Inv_AeX_AC_Identification + ' AS ACID ON ACID._ResourceGuid = r.Guid LEFT OUTER JOIN ' + @Source_Inv_AeX_AC_Discovery + ' AS discovery ON discovery._ResourceGuid = r.Guid ' select @Union_AddRemoveProgramsDim = @Union_AddRemoveProgramsDim + 'SELECT _id AS AddRemoveProgramID , DisplayName COLLATE DATABASE_DEFAULT AS DisplayName , CASE Hidden WHEN 1 THEN ''True'' WHEN 0 THEN ''False'' END COLLATE DATABASE_DEFAULT AS Hidden FROM ' + @Source_Inv_AddRemoveProgram + ' AS Inv_AddRemoveProgram ' select @Union_WindowsFileDim = @Union_WindowsFileDim + 'SELECT [Guid] , [Name] COLLATE DATABASE_DEFAULT AS [Name] , Description COLLATE DATABASE_DEFAULT AS Description , InternalName COLLATE DATABASE_DEFAULT AS InternalName , Language COLLATE DATABASE_DEFAULT AS Language , FileVersionString COLLATE DATABASE_DEFAULT AS FileVersionString , FileVersionBinary COLLATE DATABASE_DEFAULT AS FileVersionBinary , ProductName COLLATE DATABASE_DEFAULT AS ProductName , ProductVersion COLLATE DATABASE_DEFAULT AS ProductVersion , Manufacturer COLLATE DATABASE_DEFAULT AS Manufacturer FROM ' + @Source_vWindowsFile + ' AS vWindowsFile' select @Union_LogicalDiskDim = @Union_LogicalDiskDim + 'SELECT _id AS LogicalDiskID , _ResourceGuid AS ResourceGuid , Description COLLATE DATABASE_DEFAULT AS Description , [File System] COLLATE DATABASE_DEFAULT AS [File System] , Name COLLATE DATABASE_DEFAULT AS Name , [Device ID] COLLATE DATABASE_DEFAULT AS [Device ID] FROM ' + @Source_Inv_HW_Logical_Disk + ' AS Inv_HW_Logical_Disk' select @Union_LogicalDiskFact = @Union_LogicalDiskFact + 'SELECT Inv_HW_Logical_Disk._id AS LogicalDiskID , Inv_HW_Logical_Disk._ResourceGuid AS ResourceGuid , ACID._id AS ResourceId , CASE WHEN [Logical Disk Type] = 3 THEN CONVERT(decimal(10, 2), Inv_HW_Logical_Disk.[Free Space (Bytes)] / (1024 * 1024 * 1024.00)) ELSE 0 END AS [Free Space GB] , CASE WHEN [Logical Disk Type] = 3 THEN CONVERT(decimal(10, 2), Inv_HW_Logical_Disk.[Size (Bytes)] / (1024 * 1024 * 1024.00)) ELSE 0 END AS [Size GB] FROM ' + @Source_Inv_HW_Logical_Disk + ' AS Inv_HW_Logical_Disk INNER JOIN ' + @Source_Inv_AeX_AC_Identification + ' AS ACID ON ACID._ResourceGuid = Inv_HW_Logical_Disk._ResourceGuid' select @Union_PhysicalMemoryArrayFact = @Union_PhysicalMemoryArrayFact + 'SELECT Inv_HW_Physical_Memory_Array._id AS PhysicalMemoryArrayID , Inv_HW_Physical_Memory_Array._ResourceGuid AS ResourceGuid , ACID._id AS ResourceId , CONVERT(decimal(10, 3), Inv_HW_Physical_Memory_Array.[Max Capacity (Kilobytes)] / (1024 * 1024.00)) AS [Max Capacity GB] , Inv_HW_Physical_Memory_Array.[Memory Devices] , Inv_HW_Physical_Memory_Array.Tag COLLATE DATABASE_DEFAULT AS Tag FROM ' + @Source_Inv_HW_Physical_Memory_Array + ' AS Inv_HW_Physical_Memory_Array INNER JOIN ' + @Source_Inv_AeX_AC_Identification + ' AS ACID ON ACID._ResourceGuid = Inv_HW_Physical_Memory_Array._ResourceGuid WHERE (Inv_HW_Physical_Memory_Array.[Use] = 3)' select @Union_PhysicalMemoryFact = @Union_PhysicalMemoryFact + 'SELECT Inv_HW_Logical_Device._id AS PhysicalMemoryID , Inv_HW_Logical_Device._ResourceGuid AS ResourceGuid , ACID._id AS ResourceId, CONVERT(decimal(10, 3), Inv_HW_Physical_Memory.[Capacity (Bytes)] / (1024 * 1024 * 1024.00)) AS [Capacity GB] , Inv_HW_Physical_Memory.[Speed (Mega-hertz)] AS [Speed MHz] , Inv_HW_Logical_Device.[Device ID] COLLATE DATABASE_DEFAULT AS [Device ID] FROM ' + @Source_Inv_HW_Logical_Device + ' AS Inv_HW_Logical_Device INNER JOIN ' + @Source_Inv_HW_Physical_Memory + ' AS Inv_HW_Physical_Memory ON Inv_HW_Logical_Device.[Device ID] = Inv_HW_Physical_Memory.[Device ID] AND Inv_HW_Logical_Device._ResourceGuid = Inv_HW_Physical_Memory._ResourceGuid INNER JOIN ' + @Source_Inv_AeX_AC_Identification + ' AS ACID ON ACID._ResourceGuid = Inv_HW_Logical_Device._ResourceGuid' select @Union_ProcessorDim = @Union_ProcessorDim + 'SELECT Inv_HW_Processor._ResourceGuid AS ResourceGuid , CONVERT(decimal(10, 2), MAX(Inv_HW_Processor.[Max Clock Speed (Mega-hertz)]) / 1024.00) AS [Speed GHz] , MAX(Inv_HW_Logical_Device.Description) COLLATE DATABASE_DEFAULT AS Description , MAX(Inv_HW_Logical_Device.Manufacturer) COLLATE DATABASE_DEFAULT AS Manufacturer , MAX(Inv_HW_Logical_Device.Model) COLLATE DATABASE_DEFAULT AS Model , COUNT(Inv_HW_Processor._id) AS [Number Of Processors] FROM ' + @Source_Inv_HW_Logical_Device + ' AS Inv_HW_Logical_Device INNER JOIN ' + @Source_Inv_HW_Processor + ' AS Inv_HW_Processor ON Inv_HW_Logical_Device.[Device ID] = Inv_HW_Processor.[Device ID] AND Inv_HW_Logical_Device._ResourceGuid = Inv_HW_Processor._ResourceGuid GROUP BY Inv_HW_Processor._ResourceGuid' select @Union_ProcessorFact = @Union_ProcessorFact + 'SELECT Inv_HW_Logical_Device._id AS ProcessorID , ACID._id AS ResourceId , Inv_HW_Logical_Device._ResourceGuid AS ResourceGuid , Inv_HW_Processor.[External Clock Speed (Mega-hertz)] AS [External Clock Speed MHz] , CONVERT(decimal(10, 2), Inv_HW_Processor.[Max Clock Speed (Mega-hertz)] / 1024.00) AS [Speed GHz] , Inv_HW_Processor.[Number Of Cores] , Inv_HW_Processor.[Number Of Logical Processors] , Inv_HW_Logical_Device.[Device ID] COLLATE DATABASE_DEFAULT AS [Device ID] FROM ' + @Source_Inv_HW_Logical_Device + ' AS Inv_HW_Logical_Device INNER JOIN ' + @Source_Inv_HW_Processor + ' AS Inv_HW_Processor ON Inv_HW_Logical_Device.[Device ID] = Inv_HW_Processor.[Device ID] AND Inv_HW_Logical_Device._ResourceGuid = Inv_HW_Processor._ResourceGuid INNER JOIN ' + @Source_Inv_AeX_AC_Identification + ' AS ACID ON ACID._ResourceGuid = Inv_HW_Logical_Device._ResourceGuid' select @Union_SoftwareComponentDim = @Union_SoftwareComponentDim + 'SELECT [Guid] , Name COLLATE DATABASE_DEFAULT AS Name , Description COLLATE DATABASE_DEFAULT AS Description , Version COLLATE DATABASE_DEFAULT AS Version , SoftwareComponentTypeGuid , SoftwareComponentTypeName COLLATE DATABASE_DEFAULT AS SoftwareComponentTypeName , SoftwareComponentCreatedDate , SoftwareProductGuid , SoftwareProductName COLLATE DATABASE_DEFAULT AS SoftwareProductName , SoftwareTypeGuid , SoftwareTypeName COLLATE DATABASE_DEFAULT AS SoftwareTypeName , CompanyGuid , CompanyName COLLATE DATABASE_DEFAULT AS CompanyName , ParentFolderGuid FROM ' + @Source_vSoftwareComponentSearch + ' AS vSoftwareComponentSearch' select @Union_SoftwareComponentFileFact = @Union_SoftwareComponentFileFact + 'SELECT ParentResourceGuid AS SoftwareComponentGuid , ChildResourceGuid AS FileGuid FROM ' + @Source_ResourceAssociation + ' AS ResourceAssociation WHERE (ResourceAssociationTypeGuid = ''eabe86d3-aafd-487a-af63-5c95d7511af6'')' select @Union_SoftwareProductDim = @Union_SoftwareProductDim + 'SELECT Guid AS SoftwareProductGuid , Name COLLATE DATABASE_DEFAULT AS Name FROM ' + @Source_vSoftwareProduct + ' AS vSoftwareProduct' select @Union_SoftwareProductComponentFact = @Union_SoftwareProductComponentFact + 'SELECT ParentResourceGuid AS SoftwareProductGuid , ChildResourceGuid AS SoftwareComponentGuid FROM ' + @Source_ResourceAssociation + ' AS ResourceAssociation WHERE (ResourceAssociationTypeGuid = ''9d67b0c6-beff-4fcd-86c1-4a40028fe483'')' fetch next from LinkedServerCursor into @ServerName, @DatabaseName, @thisResourceManagerURL, @thisResourceEditURL end close LinkedServerCursor deallocate LinkedServerCursor select @Union_InstalledFileFact = @Union_InstalledFileFact + ')' select @Union_InstalledSoftwareFact = @Union_InstalledSoftwareFact + ')' select @Union_ComputerDim = @Union_ComputerDim + ')' select @Union_ComputerFact = @Union_ComputerFact + ')' select @Union_AddRemoveProgramsDim = @Union_AddRemoveProgramsDim + ')' select @Union_WindowsFileDim = @Union_WindowsFileDim + ')' select @Union_LogicalDiskDim = @Union_LogicalDiskDim + ')' select @Union_LogicalDiskFact = @Union_LogicalDiskFact + ')' select @Union_PhysicalMemoryArrayFact = @Union_PhysicalMemoryArrayFact + ')' select @Union_PhysicalMemoryFact = @Union_PhysicalMemoryFact + ')' select @Union_ProcessorDim = @Union_ProcessorDim + ')' select @Union_ProcessorFact = @Union_ProcessorFact + ')' select @Union_SoftwareComponentDim = @Union_SoftwareComponentDim + ')' select @Union_SoftwareComponentFileFact = @Union_SoftwareComponentFileFact + ')' select @Union_SoftwareProductDim = @Union_SoftwareProductDim + ')' select @Union_SoftwareProductComponentFact = @Union_SoftwareProductComponentFact + ')' select @Union_ApplicationMeteringFact = @Union_ApplicationMeteringFact + ')' end begin -- Event Console DSV select @FirstServer = 1 declare LinkedServerCursor cursor forward_only for select [ServerName], [DatabaseName] from #DependencyMap where [EventConsoleDSV] = 1 open LinkedServerCursor fetch next from LinkedServerCursor into @ServerName, @DatabaseName while @@fetch_status = 0 begin if @FirstServer = 0 begin select @Union_AlertDim = @Union_AlertDim + ' UNION ' select @Union_AlertFact = @Union_AlertFact + ' UNION ' select @Union_AlertActionAuditFact = @Union_AlertActionAuditFact + ' UNION ' select @Union_AlertActionAuditTypeDim = @Union_AlertActionAuditTypeDim + ' UNION ' select @Union_AlertCategoryDim = @Union_AlertCategoryDim + ' UNION ' select @Union_AlertProtocolDim = @Union_AlertProtocolDim + ' UNION ' select @Union_AlertSeverityDim = @Union_AlertSeverityDim + ' UNION ' select @Union_MonitorRuleDim = @Union_MonitorRuleDim + ' UNION ' end else begin select @FirstServer = 0 end exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'ec_alert', @Source_ec_alert OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_AeX_AC_Identification', @Source_Inv_AeX_AC_Identification OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'ec_alert_variable', @Source_ec_alert_variable OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'ec_alert_action_audit', @Source_ec_alert_action_audit OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'ec_alert_action_audit_type', @Source_ec_alert_action_audit_type OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Item', @Source_Item OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'ItemClass', @Source_ItemClass OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'PPA_AlertProtocol', @Source_PPA_AlertProtocol OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'PPA_AlertSeverity', @Source_PPA_AlertSeverity OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'ItemReference', @Source_ItemReference OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'ItemFolder', @Source_ItemFolder OUTPUT select @Union_AlertDim = @Union_AlertDim + 'SELECT id AS AlertID , guid AS AlertGuid , message COLLATE DATABASE_DEFAULT AS Message , hostname COLLATE DATABASE_DEFAULT AS Hostname FROM ' + @Source_ec_alert + ' as ec_alert' select @Union_AlertFact = @Union_AlertFact + 'SELECT ec_alert.id AS AlertID , ec_alert.guid AS AlertGuid , ec_alert.resource_guid AS ResourceGuid , ACID._id AS ResourceId , ec_alert.product_guid AS ProductGuid , ec_alert.protocol_guid AS ProtocolGuid , ec_alert.definition_guid AS DefinitionGuid , ec_alert.category_guid AS CategoryGuid , ec_alert.severity_guid AS SeverityGuid , ec_alert.timestamp , CONVERT(datetime, CONVERT(varchar, ec_alert.timestamp, 112), 112) AS FactDate , CONVERT(datetime, CONVERT(varchar, ec_alert.timestamp, 108), 108) AS FactTime , CONVERT(uniqueidentifier, CONVERT(varchar(36), ec_alert_variable.value)) AS RuleGuid FROM ' + @Source_ec_alert + ' as ec_alert INNER JOIN ' + @Source_Inv_AeX_AC_Identification + ' AS ACID ON ACID._ResourceGuid = ec_alert.resource_guid LEFT OUTER JOIN ' + @Source_ec_alert_variable + ' as ec_alert_variable ON ec_alert.guid = ec_alert_variable.alert_guid AND ec_alert_variable.name = ''ruleguid''' select @Union_AlertActionAuditFact = @Union_AlertActionAuditFact + 'SELECT ec_alert_action_audit.guid AS AlertActionAuditGuid , ec_alert_action_audit.alert_guid AS AlertGuid , ec_alert_action_audit.action_id AS ActionTypeID , ec_alert_action_audit.[user] COLLATE DATABASE_DEFAULT AS UserName , ec_alert_action_audit.timestamp , CONVERT(datetime, CONVERT(varchar, ec_alert_action_audit.timestamp, 112), 112) AS FactDate , CONVERT(datetime, CONVERT(varchar, ec_alert_action_audit.timestamp, 108), 108) AS FactTime , ec_alert.id AS AlertID , ec_alert.resource_guid AS ResourceGuid , ACID._id AS ResourceId , ec_alert.product_guid AS ProductGuid , ec_alert.protocol_guid AS ProtocolGuid , ec_alert.definition_guid AS DefinitionGuid , ec_alert.category_guid AS CategoryGuid , ec_alert.severity_guid AS SeverityGuid , CONVERT(uniqueidentifier, CONVERT(varchar(36), ec_alert_variable.value)) AS RuleGuid FROM ' + @Source_ec_alert_action_audit + ' as ec_alert_action_audit INNER JOIN ' + @Source_ec_alert + ' as ec_alert ON ec_alert_action_audit.alert_guid = ec_alert.guid INNER JOIN ' + @Source_Inv_AeX_AC_Identification + ' as ACID ON ACID._ResourceGuid = ec_alert.resource_guid LEFT OUTER JOIN ' + @Source_ec_alert_variable + ' as ec_alert_variable ON ec_alert.guid = ec_alert_variable.alert_guid AND ec_alert_variable.name = ''ruleguid'' ' select @Union_AlertActionAuditTypeDim = @Union_AlertActionAuditTypeDim + 'SELECT id AS ActionTypeID , name COLLATE DATABASE_DEFAULT AS ActionTypeName , description COLLATE DATABASE_DEFAULT AS ActionTypeDescription FROM ' + @Source_ec_alert_action_audit_type + ' as ec_alert_action_audit_type ' select @Union_AlertCategoryDim = @Union_AlertCategoryDim + 'SELECT ic.Guid AS CategoryGuid , i.Name COLLATE DATABASE_DEFAULT AS CategoryName FROM ' + @Source_Item + 'AS i INNER JOIN ' + @Source_ItemClass + ' AS ic ON i.Guid = ic.Guid AND ic.ClassGuid = ''DB47E89E-9AE4-427A-A30D-B59AED216C57'' ' select @Union_AlertProtocolDim = @Union_AlertProtocolDim + 'SELECT Guid AS ProtocolGuid , Abbreviation COLLATE DATABASE_DEFAULT AS ProtocolAbbreviation , Name COLLATE DATABASE_DEFAULT AS ProtocolName FROM ' + @Source_PPA_AlertProtocol + ' as PPA_AlertProtocol' select @Union_AlertSeverityDim = @Union_AlertSeverityDim + 'SELECT Guid AS SeverityGuid , Name COLLATE DATABASE_DEFAULT AS SeverityName , Level SeverityLevel FROM ' + @Source_PPA_AlertSeverity + ' as PPA_AlertSeverity' select @Union_MonitorRuleDim = @Union_MonitorRuleDim + 'SELECT r.Guid AS RuleGuid , r.Name COLLATE DATABASE_DEFAULT AS RuleName , m.Guid AS MetricGuid , m.Name COLLATE DATABASE_DEFAULT AS MetricName , c.Guid AS CategoryGuid , c.Name COLLATE DATABASE_DEFAULT AS CategoryName , mp.Guid AS MonitorPackGuid , mp.Name COLLATE DATABASE_DEFAULT AS MonitorPackName FROM (SELECT rc.ParentItemGuid AS RuleGuid, rc.ChildItemGuid AS CategoryGuid, rm.ChildItemGuid AS MetricGuid , mpr.ParentItemGuid AS MonitorPackGuid FROM (SELECT ParentItemGuid, ChildItemGuid, Hint, ReferenceType, CreatedDate, ModifiedDate FROM ' + @Source_ItemReference + ' as ItemReference WHERE (Hint = ''rulecategory'')) AS rc INNER JOIN (SELECT ParentItemGuid, ChildItemGuid, Hint, ReferenceType, CreatedDate, ModifiedDate FROM ' + @Source_ItemReference + ' AS ItemReference_5 WHERE (Hint = ''rulemetric'')) AS rm ON rc.ParentItemGuid = rm.ParentItemGuid INNER JOIN (SELECT ParentItemGuid, ChildItemGuid, Hint, ReferenceType, CreatedDate, ModifiedDate FROM ' + @Source_ItemReference + ' AS ItemReference_4 WHERE (Hint = ''monpackrule'') AND (ParentItemGuid = ''B4BF716A-8B62-4DEB-964A-8467DBCF4F44'')) AS mpr ON rm.ParentItemGuid = mpr.ChildItemGuid) AS ref INNER JOIN ' + @Source_Item + ' AS r ON ref.RuleGuid = r.Guid INNER JOIN ' + @Source_Item + ' AS m ON ref.MetricGuid = m.Guid INNER JOIN ' + @Source_Item + ' AS c ON ref.CategoryGuid = c.Guid INNER JOIN ' + @Source_Item + ' AS mp ON ref.MonitorPackGuid = mp.Guid UNION SELECT r.Guid AS RuleGuid, r.Name AS RuleName, m.Guid AS MetricGuid, m.Name AS MetricName , c.Guid AS CategoryGuid, c.Name AS CategoryName, mp.Guid AS MonitorPackGuid, mp.Name AS MonitorPackName FROM (SELECT rc_1.ParentItemGuid AS RuleGuid, rc_1.ChildItemGuid AS CategoryGuid , rm_1.ChildItemGuid AS MetricGuid, pf.ParentFolderGuid AS MonitorPackGuid FROM (SELECT ParentItemGuid, ChildItemGuid, Hint, ReferenceType, CreatedDate, ModifiedDate FROM ' + @Source_ItemReference + ' AS ItemReference_3 WHERE (Hint = ''rulecategory'')) AS rc_1 INNER JOIN (SELECT ParentItemGuid, ChildItemGuid, Hint, ReferenceType, CreatedDate, ModifiedDate FROM ' + @Source_ItemReference + ' AS ItemReference_2 WHERE (Hint = ''rulemetric'')) AS rm_1 ON rc_1.ParentItemGuid = rm_1.ParentItemGuid INNER JOIN (SELECT ParentItemGuid, ChildItemGuid, Hint, ReferenceType, CreatedDate, ModifiedDate FROM' + @Source_ItemReference + ' AS ItemReference_1 WHERE (Hint = ''monpackrule'') AND (ParentItemGuid <> ''B4BF716A-8B62-4DEB-964A-8467DBCF4F44'')) AS mpr_1 ON rm_1.ParentItemGuid = mpr_1.ChildItemGuid INNER JOIN ' + @Source_ItemFolder + ' AS pf ON mpr_1.ParentItemGuid = pf.ItemGuid) AS ref_1 INNER JOIN ' + @Source_Item + ' AS r ON ref_1.RuleGuid = r.Guid INNER JOIN ' + @Source_Item + ' AS m ON ref_1.MetricGuid = m.Guid INNER JOIN ' + @Source_Item + ' AS c ON ref_1.CategoryGuid = c.Guid INNER JOIN ' + @Source_Item + ' AS mp ON ref_1.MonitorPackGuid = mp.Guid' fetch next from LinkedServerCursor into @ServerName, @DatabaseName end close LinkedServerCursor deallocate LinkedServerCursor select @Union_AlertDim = @Union_AlertDim + ')' select @Union_AlertFact = @Union_AlertFact + ')' select @Union_AlertActionAuditFact = @Union_AlertActionAuditFact + ')' select @Union_AlertActionAuditTypeDim = @Union_AlertActionAuditTypeDim + ')' select @Union_AlertCategoryDim = @Union_AlertCategoryDim + ')' select @Union_AlertProtocolDim = @Union_AlertProtocolDim + ')' select @Union_AlertSeverityDim = @Union_AlertSeverityDim + ')' select @Union_MonitorRuleDim = @Union_MonitorRuleDim + ')' end begin -- Monitor DSV select @FirstServer = 1 declare LinkedServerCursor cursor forward_only for select [ServerName], [DatabaseName] from #DependencyMap where [MonitorDSV] = 1 open LinkedServerCursor fetch next from LinkedServerCursor into @ServerName, @DatabaseName while @@fetch_status = 0 begin if @FirstServer = 0 begin select @Union_MonitorMetricDataFact = @Union_MonitorMetricDataFact + ' UNION ' select @Union_MonitorMetricDim = @Union_MonitorMetricDim + ' UNION ' select @Union_MonitorMetricInstanceDim = @Union_MonitorMetricInstanceDim + ' UNION ' select @Union_MonitorMetricSourceDim = @Union_MonitorMetricSourceDim + ' UNION ' select @Union_MonitorNtEventCategoryDim = @Union_MonitorNtEventCategoryDim + ' UNION ' select @Union_MonitorNtEventDescriptionDim = @Union_MonitorNtEventDescriptionDim + ' UNION ' select @Union_MonitorNtEventIdDim = @Union_MonitorNtEventIdDim + ' UNION ' select @Union_MonitorNtEventLogFileDim = @Union_MonitorNtEventLogFileDim + ' UNION ' select @Union_MonitorNtEventMessageDLLDim = @Union_MonitorNtEventMessageDLLDim + ' UNION ' select @Union_MonitorNtEventsFact = @Union_MonitorNtEventsFact + ' UNION ' select @Union_MonitorNtEventSourceDim = @Union_MonitorNtEventSourceDim + ' UNION ' select @Union_MonitorNtEventTypeDim = @Union_MonitorNtEventTypeDim + ' UNION ' select @Union_MonitorNtEventUserDim = @Union_MonitorNtEventUserDim + ' UNION ' select @Union_MonitorPolicyDim = @Union_MonitorPolicyDim + ' UNION ' select @Union_MonitorProcessFact = @Union_MonitorProcessFact + ' UNION ' select @Union_MonitorProcessNameDim = @Union_MonitorProcessNameDim + ' UNION ' select @Union_MonitorProcessOwnerDim = @Union_MonitorProcessOwnerDim + ' UNION ' select @Union_MonitorTaskFact = @Union_MonitorTaskFact + ' UNION ' end else begin select @FirstServer = 0 end exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'MonitorMetricDailySummary', @Source_MonitorMetricDailySummary OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'MonitorMetricInstances', @Source_MonitorMetricInstances OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'MonitorMetricHourlySummary', @Source_MonitorMetricHourlySummary OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'MonitorMetricData', @Source_MonitorMetricData OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_AeX_AC_Identification', @Source_Inv_AeX_AC_Identification OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Item', @Source_Item OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'ItemClass', @Source_ItemClass OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'ClassBaseClass', @Source_ClassBaseClass OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vResourceItem', @Source_vResourceItem OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'MonitorNtEvents', @Source_MonitorNtEvents OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'ItemReference', @Source_ItemReference OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'ItemFolder', @Source_ItemFolder OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'MonitorProcessData', @Source_MonitorProcessData OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'MonitorProcessNames', @Source_MonitorProcessNames OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'MonitorProcessOwners', @Source_MonitorProcessOwners OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Evt_Task_Instances', @Source_Evt_Task_Instances OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'ec_alert', @Source_ec_alert OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'ec_alert_variable', @Source_ec_alert_variable OUTPUT select @Union_MonitorMetricDataFact = @Union_MonitorMetricDataFact + 'SELECT CONVERT(datetime, CONVERT(varchar, DATEADD(hh, DATEDIFF(hour, GETUTCDATE(), GETDATE()), MMD.TimeStamp), 112), 112) AS FactDate , CONVERT(datetime, CONVERT(varchar, DATEADD(hh, DATEDIFF(hour, GETUTCDATE(), GETDATE()), MMD.TimeStamp), 108), 108) AS FactTime , MMD.ResourceGuid , ACID._id AS ResourceId , MMD.SourceGuid , MMD.MetricGuid , MMD.Instance COLLATE DATABASE_DEFAULT AS Instance , MMD.InstanceKey , MMD.NumValue , MMD.Min , MMD.Max , MMD.Duration , MMD.DetailLevel FROM (SELECT TimeStamp, ResourceGuid, SourceGuid, MetricGuid, Instance, InstanceKey , Average AS NumValue, Min, Max, Duration, DetailLevel FROM (SELECT TimeStamp, ResourceGuid, SourceGuid, MetricGuid, Instance , InstanceKey, Average, Min, Max, Duration, DetailLevel FROM (SELECT ms.TimeStamp, mi.ResourceGuid, mi.SourceGuid, mi.MetricGuid, mi.Instance , mi.InstanceKey, ms.Average, ms.Min, ms.Max, ms.Duration, ''Daily'' AS DetailLevel FROM ' + @Source_MonitorMetricDailySummary + ' AS ms LEFT OUTER JOIN ' + @Source_MonitorMetricInstances + ' AS mi ON mi.InstanceKey = ms.InstanceKey) AS MMDS UNION ALL SELECT TimeStamp, ResourceGuid, SourceGuid, MetricGuid, Instance, InstanceKey, Average, Min, Max, Duration, DetailLevel FROM (SELECT ms.TimeStamp, mi.ResourceGuid, mi.SourceGuid, mi.MetricGuid, mi.Instance , mi.InstanceKey, ms.Average, ms.Min, ms.Max, ms.Duration, ''Hourly'' AS DetailLevel FROM ' + @Source_MonitorMetricHourlySummary + ' AS ms LEFT OUTER JOIN ' + @Source_MonitorMetricInstances + ' AS mi ON mi.InstanceKey = ms.InstanceKey) AS MMHS) AS ms_1 UNION ALL SELECT TimeStamp, ResourceGuid, SourceGuid, MetricGuid, Instance, InstanceKey, NumValue , NumValue AS Min, NumValue AS Max, Duration, ''Detailed'' AS DetailLevel FROM (SELECT md.TimeStamp, mi.ResourceGuid, mi.SourceGuid, mi.MetricGuid, mi.Instance , mi.InstanceKey, md.NumValue, md.StrValue, md.Duration FROM ' + @Source_MonitorMetricData + ' AS md LEFT OUTER JOIN ' + @Source_MonitorMetricInstances + ' AS mi ON mi.InstanceKey = md.InstanceKey) AS md_1) AS MMD INNER JOIN ' + @Source_Inv_AeX_AC_Identification + ' AS ACID ON ACID._ResourceGuid = MMD.ResourceGuid' select @Union_MonitorMetricDim = @Union_MonitorMetricDim + 'SELECT Name COLLATE DATABASE_DEFAULT AS [MetricName] , Guid AS [MetricGuid] FROM ' + @Source_Item + ' as Item WHERE (Guid IN (SELECT Guid FROM ' + @Source_ItemClass + ' as ItemClass WHERE (ClassGuid IN (SELECT ClassGuid FROM ' + @Source_ClassBaseClass + ' as ClassBaseClass WHERE (BaseClassGuid = ''CFB6A743-EFB5-42A6-988C-7D7BB2A540C6''))) AND (ClassGuid <> ''8aab1b36-3b41-4f43-b622-20f06f7f7517'') AND (ClassGuid <> ''3792ace6-692f-4223-b713-578a2a03a853''))) ' select @Union_MonitorMetricInstanceDim = @Union_MonitorMetricInstanceDim + 'SELECT InstanceKey , ResourceGuid , SourceGuid , MetricGuid , Instance COLLATE DATABASE_DEFAULT AS Instance FROM ' + @Source_MonitorMetricInstances + ' as MonitorMetricInstances' select @Union_MonitorMetricSourceDim = @Union_MonitorMetricSourceDim + 'SELECT DISTINCT mmi.SourceGuid , vResourceItem.Name COLLATE DATABASE_DEFAULT AS SourceName FROM ' + @Source_MonitorMetricInstances + ' AS mmi INNER JOIN ' + @Source_vResourceItem + ' as vResourceItem ON mmi.SourceGuid = vResourceItem.Guid' select @Union_MonitorNtEventCategoryDim = @Union_MonitorNtEventCategoryDim + 'SELECT DISTINCT Category COLLATE DATABASE_DEFAULT AS Category FROM ' + @Source_MonitorNtEvents + ' as MonitorNtEvents' select @Union_MonitorNtEventDescriptionDim = @Union_MonitorNtEventDescriptionDim + 'SELECT DISTINCT Description COLLATE DATABASE_DEFAULT AS Description FROM ' + @Source_MonitorNtEvents + ' as MonitorNtEvents' select @Union_MonitorNtEventIdDim = @Union_MonitorNtEventIdDim + 'SELECT DISTINCT EventId FROM ' + @Source_MonitorNtEvents + ' as MonitorNtEvents' select @Union_MonitorNtEventLogFileDim = @Union_MonitorNtEventLogFileDim + 'SELECT DISTINCT LogFile COLLATE DATABASE_DEFAULT AS LogFile FROM ' + @Source_MonitorNtEvents + ' as MonitorNtEvents' select @Union_MonitorNtEventMessageDLLDim = @Union_MonitorNtEventMessageDLLDim + 'SELECT DISTINCT MessageDLL COLLATE DATABASE_DEFAULT AS MessageDLL FROM ' + @Source_MonitorNtEvents + ' as MonitorNtEvents' select @Union_MonitorNtEventsFact = @Union_MonitorNtEventsFact + 'SELECT CONVERT(datetime, CONVERT(varchar, DATEADD(hh, DATEDIFF(hour, GETUTCDATE(), GETDATE()), MonitorNtEvents.TimeStamp), 112), 112) AS FactDate , CONVERT(datetime, CONVERT(varchar, DATEADD(hh, DATEDIFF(hour, GETUTCDATE(), GETDATE()), MonitorNtEvents.TimeStamp), 108), 108) AS FactTime , MonitorNtEvents.ResourceGuid , ACID._id AS ResourceId , MonitorNtEvents.Type COLLATE DATABASE_DEFAULT AS Type , MonitorNtEvents.[User] COLLATE DATABASE_DEFAULT AS [User] , MonitorNtEvents.Computer COLLATE DATABASE_DEFAULT AS Computer , MonitorNtEvents.Source COLLATE DATABASE_DEFAULT AS Source , MonitorNtEvents.Category COLLATE DATABASE_DEFAULT AS Category , MonitorNtEvents.LogFile COLLATE DATABASE_DEFAULT AS LogFile , MonitorNtEvents.MessageDLL COLLATE DATABASE_DEFAULT AS MessageDLL , MonitorNtEvents.RuleTriggered , MonitorNtEvents.EventId , MonitorNtEvents.Description COLLATE DATABASE_DEFAULT AS Description FROM ' + @Source_MonitorNtEvents + ' as MonitorNtEvents INNER JOIN ' + @Source_Inv_AeX_AC_Identification + ' as ACID ON ACID._ResourceGuid = MonitorNtEvents.ResourceGuid' select @Union_MonitorNtEventSourceDim = @Union_MonitorNtEventSourceDim + 'SELECT DISTINCT Source COLLATE DATABASE_DEFAULT AS Source FROM ' + @Source_MonitorNtEvents + ' as MonitorNtEvents' select @Union_MonitorNtEventTypeDim = @Union_MonitorNtEventTypeDim + 'SELECT DISTINCT Type COLLATE DATABASE_DEFAULT AS Type FROM ' + @Source_MonitorNtEvents + ' as MonitorNtEvents' select @Union_MonitorNtEventUserDim = @Union_MonitorNtEventUserDim + 'SELECT DISTINCT [User] COLLATE DATABASE_DEFAULT AS [User] FROM ' + @Source_MonitorNtEvents + ' as MonitorNtEvents' select @Union_MonitorPolicyDim = @Union_MonitorPolicyDim + 'SELECT r.Guid AS RuleGuid , r.Name COLLATE DATABASE_DEFAULT AS RuleName , m.Guid AS MetricGuid , m.Name COLLATE DATABASE_DEFAULT AS MetricName , c.Guid AS CategoryGuid , c.Name COLLATE DATABASE_DEFAULT AS CategoryName , mp.Guid AS MonitorPackGuid , mp.Name COLLATE DATABASE_DEFAULT AS MonitorPackName FROM (SELECT rc.ParentItemGuid AS RuleGuid, rc.ChildItemGuid AS CategoryGuid, rm.ChildItemGuid AS MetricGuid, mpr.ParentItemGuid AS MonitorPackGuid FROM (SELECT ParentItemGuid, ChildItemGuid, Hint, ReferenceType, CreatedDate, ModifiedDate FROM ' + @Source_ItemReference + ' as ItemReference WHERE (Hint = ''rulecategory'')) AS rc INNER JOIN (SELECT ParentItemGuid, ChildItemGuid, Hint, ReferenceType, CreatedDate, ModifiedDate FROM ' + @Source_ItemReference + ' AS ItemReference_5 WHERE (Hint = ''rulemetric'')) AS rm ON rc.ParentItemGuid = rm.ParentItemGuid INNER JOIN (SELECT ParentItemGuid, ChildItemGuid, Hint, ReferenceType, CreatedDate, ModifiedDate FROM ' + @Source_ItemReference + ' AS ItemReference_4 WHERE (Hint = ''monpackrule'') AND (ParentItemGuid = ''B4BF716A-8B62-4DEB-964A-8467DBCF4F44'')) AS mpr ON rm.ParentItemGuid = mpr.ChildItemGuid) AS ref INNER JOIN ' + @Source_Item + ' AS r ON ref.RuleGuid = r.Guid INNER JOIN ' + @Source_Item + ' AS m ON ref.MetricGuid = m.Guid INNER JOIN ' + @Source_Item + ' AS c ON ref.CategoryGuid = c.Guid INNER JOIN ' + @Source_Item + ' AS mp ON ref.MonitorPackGuid = mp.Guid UNION SELECT r.Guid AS RuleGuid, r.Name AS RuleName, m.Guid AS MetricGuid, m.Name AS MetricName, c.Guid AS CategoryGuid, c.Name AS CategoryName, mp.Guid AS MonitorPackGuid, mp.Name AS MonitorPackName FROM (SELECT rc_1.ParentItemGuid AS RuleGuid, rc_1.ChildItemGuid AS CategoryGuid, rm_1.ChildItemGuid AS MetricGuid, pf.ParentFolderGuid AS MonitorPackGuid FROM (SELECT ParentItemGuid, ChildItemGuid, Hint, ReferenceType, CreatedDate, ModifiedDate FROM ' + @Source_ItemReference + ' AS ItemReference_3 WHERE (Hint = ''rulecategory'')) AS rc_1 INNER JOIN (SELECT ParentItemGuid, ChildItemGuid, Hint, ReferenceType, CreatedDate, ModifiedDate FROM ' + @Source_ItemReference + ' AS ItemReference_2 WHERE (Hint = ''rulemetric'')) AS rm_1 ON rc_1.ParentItemGuid = rm_1.ParentItemGuid INNER JOIN (SELECT ParentItemGuid, ChildItemGuid, Hint, ReferenceType, CreatedDate, ModifiedDate FROM ' + @Source_ItemReference + ' AS ItemReference_1 WHERE (Hint = ''monpackrule'') AND (ParentItemGuid <> ''B4BF716A-8B62-4DEB-964A-8467DBCF4F44'')) AS mpr_1 ON rm_1.ParentItemGuid = mpr_1.ChildItemGuid INNER JOIN ' + @Source_ItemFolder + ' AS pf ON mpr_1.ParentItemGuid = pf.ItemGuid) AS ref_1 INNER JOIN ' + @Source_Item + ' AS r ON ref_1.RuleGuid = r.Guid INNER JOIN ' + @Source_Item + ' AS m ON ref_1.MetricGuid = m.Guid INNER JOIN ' + @Source_Item + ' AS c ON ref_1.CategoryGuid = c.Guid INNER JOIN ' + @Source_Item + ' AS mp ON ref_1.MonitorPackGuid = mp.Guid' select @Union_MonitorProcessFact = @Union_MonitorProcessFact + 'SELECT MachineGuid AS ComputerGuid , ProcessNameKey , OwnerKey , CONVERT(datetime, CONVERT(varchar, DATEADD(hh, DATEDIFF(hour, GETUTCDATE(), GETDATE()), TimeStamp), 112), 112) AS FactDate , CONVERT(datetime, CONVERT(varchar, DATEADD(hh, DATEDIFF(hour, GETUTCDATE(), GETDATE()), TimeStamp), 108), 108) AS FactTime , CPU , CpuTime , WorkingSetSize , HandleCount , ThreadCount , VmSize FROM ' + @Source_MonitorProcessData + ' as MonitorProcessData WHERE (ProcessNameKey NOT IN (SELECT ProcessNameKey FROM ' + @Source_MonitorProcessNames + ' as MonitorProcessNames WHERE (ProcessName = ''System Idle Process'')))' select @Union_MonitorProcessNameDim = @Union_MonitorProcessNameDim + 'SELECT ProcessNameKey , ProcessName COLLATE DATABASE_DEFAULT AS ProcessName FROM ' + @Source_MonitorProcessNames + ' as MonitorProcessNames WHERE (ProcessName <> ''System Idle Process'')' select @Union_MonitorProcessOwnerDim = @Union_MonitorProcessOwnerDim + 'SELECT OwnerKey , Owner COLLATE DATABASE_DEFAULT AS Owner FROM ' + @Source_MonitorProcessOwners + ' as MonitorProcessOwners' select @Union_MonitorTaskFact = @Union_MonitorTaskFact + 'SELECT eti._ResourceGuid AS ComputerGuid , eti._eventTime AS eventTime , eti.TaskInstanceGuid , eti.InstanceType COLLATE DATABASE_DEFAULT AS TaskInstanceType , eti.ParentTaskInstanceGuid AS RuleGuid , eti.TaskVersionGuid , eti.StartTime AS TaskStartTime , eti.EndTime AS TaskEndTime , eti.TaskServerGuid , ir1.ChildItemGuid AS TaskGuid , i.Name COLLATE DATABASE_DEFAULT AS TaskName , ir3.ChildItemGuid AS MetricGuid , ir4.ChildItemGuid AS CategoryGuid , CONVERT(datetime, CONVERT(varchar, eti._eventTime, 112), 112) AS FactDate , CONVERT(datetime, CONVERT(varchar, eti._eventTime, 108), 108) AS FactTime FROM ' + @Source_Evt_Task_Instances + ' AS eti INNER JOIN ' + @Source_ItemReference + ' AS ir1 ON eti.ParentTaskInstanceGuid = ir1.ParentItemGuid AND ir1.Hint = ''ruleclienttask'' INNER JOIN ' + @Source_ItemReference + ' AS ir3 ON ir1.ParentItemGuid = ir3.ParentItemGuid AND ir3.Hint = ''rulemetric'' INNER JOIN ' + @Source_ItemReference + ' AS ir4 ON ir1.ParentItemGuid = ir4.ParentItemGuid AND ir4.Hint = ''rulecategory'' INNER JOIN ' + @Source_Item + ' AS i ON ir1.ChildItemGuid = i.Guid LEFT OUTER JOIN (SELECT ec_alert.guid AS AlertGuid, ec_alert.resource_guid, ec_alert.timestamp, CONVERT(uniqueidentifier, CONVERT(varchar(36), ec_alert_variable.value)) AS RuleGuid FROM ' + @Source_ec_alert + ' as ec_alert LEFT OUTER JOIN ' + @Source_ec_alert_variable + ' as ec_alert_variable ON ec_alert.guid = ec_alert_variable.alert_guid AND ec_alert_variable.name = ''ruleguid'') AS ec ON ec.resource_guid = eti._ResourceGuid AND eti._eventTime > ec.timestamp AND eti._eventTime < DATEADD(mi, 1, ec.timestamp) AND ir1.ParentItemGuid = ec.RuleGuid' fetch next from LinkedServerCursor into @ServerName, @DatabaseName end close LinkedServerCursor deallocate LinkedServerCursor select @Union_MonitorMetricDataFact = @Union_MonitorMetricDataFact + ')' select @Union_MonitorMetricDim = @Union_MonitorMetricDim + ')' select @Union_MonitorMetricInstanceDim = @Union_MonitorMetricInstanceDim + ')' select @Union_MonitorMetricSourceDim = @Union_MonitorMetricSourceDim + ')' select @Union_MonitorNtEventCategoryDim = @Union_MonitorNtEventCategoryDim + ')' select @Union_MonitorNtEventDescriptionDim = @Union_MonitorNtEventDescriptionDim + ')' select @Union_MonitorNtEventIdDim = @Union_MonitorNtEventIdDim + ')' select @Union_MonitorNtEventLogFileDim = @Union_MonitorNtEventLogFileDim + ')' select @Union_MonitorNtEventMessageDLLDim = @Union_MonitorNtEventMessageDLLDim + ')' select @Union_MonitorNtEventsFact = @Union_MonitorNtEventsFact + ')' select @Union_MonitorNtEventSourceDim = @Union_MonitorNtEventSourceDim + ')' select @Union_MonitorNtEventTypeDim = @Union_MonitorNtEventTypeDim + ')' select @Union_MonitorNtEventUserDim = @Union_MonitorNtEventUserDim + ')' select @Union_MonitorPolicyDim = @Union_MonitorPolicyDim + ')' select @Union_MonitorProcessFact = @Union_MonitorProcessFact + ')' select @Union_MonitorProcessNameDim = @Union_MonitorProcessNameDim + ')' select @Union_MonitorProcessOwnerDim = @Union_MonitorProcessOwnerDim + ')' select @Union_MonitorTaskFact = @Union_MonitorTaskFact + ')' end begin -- Server Inventory DSV select @FirstServer = 1 declare LinkedServerCursor cursor forward_only for select [ServerName], [DatabaseName] from #DependencyMap where [ServerInventoryDSV] = 1 open LinkedServerCursor fetch next from LinkedServerCursor into @ServerName, @DatabaseName while @@fetch_status = 0 begin if @FirstServer = 0 begin select @Union_ESXServerFact = @Union_ESXServerFact + ' UNION ' select @Union_ESXStorageVolumeDim = @Union_ESXStorageVolumeDim + ' UNION ' select @Union_ESXVirtualMachineDim = @Union_ESXVirtualMachineDim + ' UNION ' select @Union_IISFTPSiteDim = @Union_IISFTPSiteDim + ' UNION ' select @Union_IISFTPSiteFact = @Union_IISFTPSiteFact + ' UNION ' select @Union_IISServerDim = @Union_IISServerDim + ' UNION ' select @Union_IISServerFact = @Union_IISServerFact + ' UNION ' select @Union_IISVirtualDirectoryDim = @Union_IISVirtualDirectoryDim + ' UNION ' select @Union_IISWebSiteDim = @Union_IISWebSiteDim + ' UNION ' select @Union_SQLClusterDim = @Union_SQLClusterDim + ' UNION ' select @Union_SQLClusterFact = @Union_SQLClusterFact + ' UNION ' select @Union_SQLClusterResourceDim = @Union_SQLClusterResourceDim + ' UNION ' select @Union_SQLDatabaseSystemDim = @Union_SQLDatabaseSystemDim + ' UNION ' select @Union_SQLDatabaseDim = @Union_SQLDatabaseDim + ' UNION ' select @Union_SQLDatabaseFact = @Union_SQLDatabaseFact + ' UNION ' select @Union_SQLStorageAreaDim = @Union_SQLStorageAreaDim + ' UNION ' select @Union_SQLUserDim = @Union_SQLUserDim + ' UNION ' select @Union_SQLUserFact = @Union_SQLUserFact + ' UNION ' end else begin select @FirstServer = 0 end exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_UNIX_Hosted_VMware_ESX_Storage_Volume', @Source_Inv_UNIX_Hosted_VMware_ESX_Storage_Volume OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_UNIX_Hosted_Dependency', @Source_Inv_UNIX_Hosted_Dependency OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_UNIX_Stored_VMware_ESX_Virtual_Computer_System', @Source_Inv_UNIX_Stored_VMware_ESX_Virtual_Computer_System OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_UNIX_Virtual_Computer_System', @Source_Inv_UNIX_Virtual_Computer_System OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_UNIX_VMware_ESX_Storage_Volume', @Source_Inv_UNIX_VMware_ESX_Storage_Volume OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_UNIX_Settings_Define_VMware_ESX', @Source_Inv_UNIX_Settings_Define_VMware_ESX OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_UNIX_Virtual_System_Setting_Data', @Source_Inv_UNIX_Virtual_System_Setting_Data OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_UNIX_Settings_Define_Virtual_Computer_System', @Source_Inv_UNIX_Settings_Define_Virtual_Computer_System OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_UNIX_Virtual_Logical_Device', @Source_Inv_UNIX_Virtual_Logical_Device OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_UNIX_Virtual_System_Device', @Source_Inv_UNIX_Virtual_System_Device OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_UNIX_Concrete_Virtual_Component', @Source_Inv_UNIX_Concrete_Virtual_Component OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_UNIX_Resource_Allocation_Setting_Data', @Source_Inv_UNIX_Resource_Allocation_Setting_Data OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_IIS_FTP_Site_Details', @Source_Inv_IIS_FTP_Site_Details OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_IIS_Setting', @Source_Inv_IIS_Setting OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_IIS_Root_VirtualDir', @Source_Inv_IIS_Root_VirtualDir OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_IIS_Http_Host_Setting_Data', @Source_Inv_IIS_Http_Host_Setting_Data OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_IISHttp_VirtualDir_Setting_Data', @Source_Inv_IISHttp_VirtualDir_Setting_Data OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_Cluster', @Source_Inv_Cluster OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_Associate_Cluster_To_Node', @Source_Inv_Associate_Cluster_To_Node OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_Associate_Database_System_to_Service', @Source_Inv_Associate_Database_System_to_Service OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_Cluster_Resource_SQL_Server', @Source_Inv_Cluster_Resource_SQL_Server OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_Database_System', @Source_Inv_Database_System OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_MS_SQL_Server_Processors', @Source_Inv_MS_SQL_Server_Processors OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_SQL_Server_License', @Source_Inv_SQL_Server_License OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_Database', @Source_Inv_Database OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_MS_SQL_Server_Databases', @Source_Inv_MS_SQL_Server_Databases OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_Associate_Database_To_Service', @Source_Inv_Associate_Database_To_Service OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_MS_SQL_Server_Transaction_Logs', @Source_Inv_MS_SQL_Server_Transaction_Logs OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_Database_Storage_Area', @Source_Inv_Database_Storage_Area OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_Associate_Database_Service_to_Storage', @Source_Inv_Associate_Database_Service_to_Storage OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_Associate_Database_to_Database_Storage_Area', @Source_Inv_Associate_Database_to_Database_Storage_Area OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_Database_User', @Source_Inv_Database_User OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_Associate_Database_User_to_Database', @Source_Inv_Associate_Database_User_to_Database OUTPUT --exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_UNIX_Hosted_VMware_ESX_Storage_Volume', @Source_Inv_UNIX_Hosted_VMware_ESX_Storage_Volume OUTPUT select @Union_ESXServerFact = @Union_ESXServerFact + 'SELECT Inv_UNIX_Virtual_Computer_System._ResourceGuid AS ResourceGuid , Inv_UNIX_Virtual_Computer_System._id AS ESXVirtualMachineID , Inv_UNIX_VMware_ESX_Storage_Volume._id AS ESXStorageVolumeID , Inv_UNIX_Settings_Define_VMware_ESX._id AS ESXHostID FROM ' + @Source_Inv_UNIX_Hosted_VMware_ESX_Storage_Volume + ' as Inv_UNIX_Hosted_VMware_ESX_Storage_Volume INNER JOIN ' + @Source_Inv_UNIX_Hosted_Dependency + ' as Inv_UNIX_Hosted_Dependency ON Inv_UNIX_Hosted_Dependency._ResourceGuid = Inv_UNIX_Hosted_VMware_ESX_Storage_Volume._ResourceGuid AND Inv_UNIX_Hosted_Dependency.Host = Inv_UNIX_Hosted_VMware_ESX_Storage_Volume.Host INNER JOIN ' + @Source_Inv_UNIX_Stored_VMware_ESX_Virtual_Computer_System + ' as Inv_UNIX_Stored_VMware_ESX_Virtual_Computer_System ON Inv_UNIX_Hosted_Dependency._ResourceGuid = Inv_UNIX_Stored_VMware_ESX_Virtual_Computer_System._ResourceGuid AND Inv_UNIX_Hosted_Dependency.[Virtual Machine] = Inv_UNIX_Stored_VMware_ESX_Virtual_Computer_System.[Virtual Machine] AND Inv_UNIX_Hosted_VMware_ESX_Storage_Volume.[Storage Volume] = Inv_UNIX_Stored_VMware_ESX_Virtual_Computer_System.[Storage Volume] INNER JOIN ' + @Source_Inv_UNIX_Virtual_Computer_System + ' as Inv_UNIX_Virtual_Computer_System ON Inv_UNIX_Virtual_Computer_System._ResourceGuid = Inv_UNIX_Stored_VMware_ESX_Virtual_Computer_System._ResourceGuid AND Inv_UNIX_Virtual_Computer_System.Name = Inv_UNIX_Stored_VMware_ESX_Virtual_Computer_System.[Virtual Machine] INNER JOIN ' + @Source_Inv_UNIX_VMware_ESX_Storage_Volume + ' as Inv_UNIX_VMware_ESX_Storage_Volume ON Inv_UNIX_VMware_ESX_Storage_Volume._ResourceGuid = Inv_UNIX_Stored_VMware_ESX_Virtual_Computer_System._ResourceGuid AND Inv_UNIX_VMware_ESX_Storage_Volume.[Instance ID] = Inv_UNIX_Stored_VMware_ESX_Virtual_Computer_System.[Storage Volume] INNER JOIN ' + @Source_Inv_UNIX_Settings_Define_VMware_ESX + ' as Inv_UNIX_Settings_Define_VMware_ESX ON Inv_UNIX_Settings_Define_VMware_ESX._ResourceGuid = Inv_UNIX_Hosted_VMware_ESX_Storage_Volume._ResourceGuid AND Inv_UNIX_Settings_Define_VMware_ESX.Host = Inv_UNIX_Hosted_VMware_ESX_Storage_Volume.Host' select @Union_ESXStorageVolumeDim = @Union_ESXStorageVolumeDim + 'SELECT _id AS ESXStorageVolumeID , _ResourceGuid AS ResourceGuid , [Instance ID] COLLATE DATABASE_DEFAULT AS [Instance ID] , [Element Name] COLLATE DATABASE_DEFAULT AS [Volume Name] , [File System Version] COLLATE DATABASE_DEFAULT AS [File System Version] , [Total Size] / (1024 * 1024 * 1024) AS [Total Size GB] , [Free Size] / (1024 * 1024 * 1024) AS [Free Size GB] FROM ' + @Source_Inv_UNIX_VMware_ESX_Storage_Volume + ' as Inv_UNIX_VMware_ESX_Storage_Volume' select @Union_ESXVirtualMachineDim = @Union_ESXVirtualMachineDim + 'SELECT DISTINCT VCS._id AS ESXVirtualMachineID , VSSD._ResourceGuid AS ResourceGuid , DVCS.[Virtual Machine] COLLATE DATABASE_DEFAULT AS [Virtual Machine] , VSSD.[Instance ID] COLLATE DATABASE_DEFAULT AS [Instance ID] , VSSD.[Element Name] COLLATE DATABASE_DEFAULT AS Name , VSSD.[Virtual System Type] COLLATE DATABASE_DEFAULT AS [Virtual System Type] , VSSD.[Automatic Startup Action] , VSSD.[Automatic Shutdown Action] , VSSD.[Automatic Startup Delay] , VSSD.[Automatic Shutdown Delay] , VSSD.[Automatic Startup Action Sequence Number] , ISNULL(dbo.fnLocalizeString(''listitem.virtualcs.operating.status.'' + CAST(VCS.[Operating Status] AS nvarchar), N''9E82E754-52FA-4B78-8A47-237259EFF505'', ''en-US''), dbo.fnLocalizeString(''listitem.virtualcs.operating.status.'' + CAST(VCS.[Operating Status] AS nvarchar), N''9E82E754-52FA-4B78-8A47-237259EFF505'', ''en'')) COLLATE DATABASE_DEFAULT AS State , temp_cpu.[CPUs Assigned] , CONVERT(decimal(10, 2), temp_memory.[Virtual Quantity] / 1024.00) AS [Memory Allocated GB] , CONVERT(int, temp_disk.[Disk Used] / 1024.00) AS [Disk Used GB] , CONVERT(int, temp_disk.[Max Disk Size] / 1024.00) AS [Max Disk Size GB] FROM ' + @Source_Inv_UNIX_Virtual_System_Setting_Data + ' AS VSSD INNER JOIN ' + @Source_Inv_UNIX_Settings_Define_Virtual_Computer_System + ' AS DVCS ON VSSD._ResourceGuid = DVCS._ResourceGuid AND VSSD.[Instance ID] = DVCS.[Configuration Settings] INNER JOIN ' + @Source_Inv_UNIX_Virtual_Computer_System + 'AS VCS ON VCS._ResourceGuid = DVCS._ResourceGuid AND VCS.Name = DVCS.[Virtual Machine] LEFT OUTER JOIN (SELECT DISTINCT VSD._ResourceGuid, VSD.[Configuration Settings], COUNT(DISTINCT VLD.Name) AS [CPUs Assigned] FROM ' + @Source_Inv_UNIX_Virtual_System_Device + ' AS VSD INNER JOIN ' + @Source_Inv_UNIX_Virtual_Logical_Device + ' AS VLD ON VSD._ResourceGuid = VLD._ResourceGuid AND VSD.[Virtual Device] = VLD.[Instance ID] WHERE (VLD.[Device Type] = 1) GROUP BY VSD._ResourceGuid, VSD.[Configuration Settings]) AS temp_cpu ON temp_cpu._ResourceGuid = VSSD._ResourceGuid AND temp_cpu.[Configuration Settings] = VSSD.[Instance ID] LEFT OUTER JOIN (SELECT DISTINCT CVC._ResourceGuid, CVC.[Virtual Machine Configuration], RASD.[Virtual Quantity] FROM ' + @Source_Inv_UNIX_Concrete_Virtual_Component + ' AS CVC INNER JOIN Inv_UNIX_Resource_Allocation_Setting_Data AS RASD ON CVC._ResourceGuid = RASD._ResourceGuid AND CVC.[Resource Allocation] COLLATE DATABASE_DEFAULT = RASD.[Instance ID] COLLATE DATABASE_DEFAULT WHERE (RASD.[Resource Type] = 4)) AS temp_memory ON temp_memory._ResourceGuid = VSSD._ResourceGuid AND temp_memory.[Virtual Machine Configuration] = VSSD.[Instance ID] LEFT OUTER JOIN (SELECT CVC._ResourceGuid, CVC.[Virtual Machine Configuration], SUM(RASD.Reservation) AS [Disk Used], SUM(RASD.Limit) AS [Max Disk Size] FROM ' + @Source_Inv_UNIX_Concrete_Virtual_Component + ' AS CVC INNER JOIN ' + @Source_Inv_UNIX_Resource_Allocation_Setting_Data + ' AS RASD ON CVC._ResourceGuid = RASD._ResourceGuid AND CVC.[Resource Allocation] COLLATE DATABASE_DEFAULT = RASD.[Instance ID] COLLATE DATABASE_DEFAULT WHERE (RASD.[Resource Type] = 49152) GROUP BY CVC._ResourceGuid, CVC.[Virtual Machine Configuration]) AS temp_disk ON temp_disk._ResourceGuid = VSSD._ResourceGuid AND temp_disk.[Virtual Machine Configuration] = VSSD.[Instance ID] ' select @Union_IISFTPSiteDim = @Union_IISFTPSiteDim + 'SELECT _ResourceGuid AS IISServerResourceGuid , _id AS IISFTPSiteID , [FTP Site Name] COLLATE DATABASE_DEFAULT AS [FTP Site Name] , Path COLLATE DATABASE_DEFAULT AS Path , Rights COLLATE DATABASE_DEFAULT AS Rights FROM ' + @Source_Inv_IIS_FTP_Site_Details + ' as Inv_IIS_FTP_Site_Details' select @Union_IISFTPSiteFact = @Union_IISFTPSiteFact + 'SELECT _ResourceGuid AS IISServerResourceGuid , _id AS IISFTPSiteID FROM ' + @Source_Inv_IIS_FTP_Site_Details + ' as Inv_IIS_FTP_Site_Details' select @Union_IISServerDim = @Union_IISServerDim + 'SELECT _id AS IISServerID , [Instance ID] COLLATE DATABASE_DEFAULT AS [Instance ID] , [IIS Version] COLLATE DATABASE_DEFAULT AS [IIS Version] , CASE WHEN [IIS Service] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [IIS Service] , CASE WHEN [SMTP Service] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [SMTP Service] , CASE WHEN [BITS Service] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [BITS Service] , CASE WHEN [NNTP Service] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [NNTP Service] , CASE WHEN [ASP DotNet Installed] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [ASP DotNet Installed] , CASE WHEN [ASP Installed] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [ASP Installed] , CASE WHEN [COM Plus Service] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [COM Plus Service] , CASE WHEN [Network DTC Insatlled] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Network DTC Insatlled] , CASE WHEN [ Message Queue Service ] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Message Queue Service] , CASE WHEN [Application Server Console Insatlled] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Application Server Console Insatlled] , CASE WHEN [Internet Data Connector Installed] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Internet Data Connector Installed] , CASE WHEN [Remote Admin Installed] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Remote Admin Installed] , CASE WHEN [Remote Desktop Web Connection Installed] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Remote Desktop Web Connection Installed] , CASE WHEN [ WWW Service] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [WWW Service] FROM ' + @Source_Inv_IIS_Setting + ' as Inv_IIS_Setting' select @Union_IISServerFact = @Union_IISServerFact + 'SELECT Inv_IIS_Root_VirtualDir._ResourceGuid AS IISServerResourceGuid , Inv_IIS_Setting._id AS IISServerID , Inv_IIS_Http_Host_Setting_Data._id AS IISWebSiteID , Inv_IISHttp_VirtualDir_Setting_Data._id AS IISVirtualDirectoryID FROM ' + @Source_Inv_IIS_Root_VirtualDir + ' as Inv_IIS_Root_VirtualDir INNER JOIN ' + @Source_Inv_IIS_Setting + ' as Inv_IIS_Setting ON Inv_IIS_Setting._ResourceGuid = Inv_IIS_Root_VirtualDir._ResourceGuid INNER JOIN ' + @Source_Inv_IIS_Http_Host_Setting_Data + ' as Inv_IIS_Http_Host_Setting_Data ON Inv_IIS_Http_Host_Setting_Data._ResourceGuid = Inv_IIS_Root_VirtualDir._ResourceGuid AND Inv_IIS_Root_VirtualDir.[Http Host] = Inv_IIS_Http_Host_Setting_Data.[Instance ID] INNER JOIN ' + @Source_Inv_IISHttp_VirtualDir_Setting_Data + ' as Inv_IISHttp_VirtualDir_Setting_Data ON Inv_IISHttp_VirtualDir_Setting_Data._ResourceGuid = Inv_IIS_Root_VirtualDir._ResourceGuid AND Inv_IIS_Root_VirtualDir.[IIS Virtual Directory] = Inv_IISHttp_VirtualDir_Setting_Data.[IIS Virtual Directory]' select @Union_IISVirtualDirectoryDim = @Union_IISVirtualDirectoryDim + 'SELECT _id AS IISVirtualDirectoryID , [IIS Virtual Directory] COLLATE DATABASE_DEFAULT AS [IIS Virtual Directory] , [Element Name] COLLATE DATABASE_DEFAULT AS [Element Name] , [Content Location Path] COLLATE DATABASE_DEFAULT AS [Content Location Path] , [Content Location] COLLATE DATABASE_DEFAULT AS [Content Location] , [Application Name] COLLATE DATABASE_DEFAULT AS [Application Name] , CASE WHEN [Default Document Enabled] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Default Document Enabled] , [Defaul Document Name] COLLATE DATABASE_DEFAULT AS [Defaul Document Name] , CASE WHEN [Anonymous Authentication Enabled] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Anonymous Authentication Enabled] , CASE WHEN [Integrated Windows Authentication Enabled] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Integrated Windows Authentication Enabled] , CASE WHEN [Basic Authentication Enabled] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Basic Authentication Enabled] , CASE WHEN [Digest Authentication Enabled] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Digest Authentication Enabled] , CASE WHEN [Session State Enabled] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Session State Enabled] , CASE WHEN [Session Timeout] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Session Timeout] , CASE WHEN [Script Source Access Enabled] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Script Source Access Enabled] , CASE WHEN [Access Read Enabled] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Access Read Enabled] , CASE WHEN [Access Write Enabled] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Access Write Enabled] , CASE WHEN [Directory Browsing Enabled] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Directory Browsing Enabled] , CASE WHEN [Log Enabled] = 1 THEN ''Yes'' ELSE ''No'' END AS [Log Enabled] , [Execute Permission] COLLATE DATABASE_DEFAULT AS [Execute Permission] , [Application Pool] COLLATE DATABASE_DEFAULT AS [Application Pool] , CASE WHEN [DotNet Passport Authentication Enabled] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [DotNet Passport Authentication Enabled] , CASE WHEN [SSL Access Enabled] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [SSL Access Enabled] , CASE WHEN [Content Expiration Enabled] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Content Expiration Enabled] , [Content Expiration Setting] COLLATE DATABASE_DEFAULT AS [Content Expiration Setting] FROM ' + @Source_Inv_IISHttp_VirtualDir_Setting_Data + ' as Inv_IISHttp_VirtualDir_Setting_Data' select @Union_IISWebSiteDim = @Union_IISWebSiteDim + 'SELECT _id AS IISWebSiteID , [Instance ID] COLLATE DATABASE_DEFAULT AS [Instance ID] , [Web Site Name] COLLATE DATABASE_DEFAULT AS [Web Site Name] , CASE WHEN [Process Throttling enabled] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Process Throttling enabled] , CASE WHEN [Max CPU Use Percenatage] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Max CPU Use Percenatage] , CASE WHEN [Enforce Process Throttling] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Enforce Process Throttling] , [Content Location] COLLATE DATABASE_DEFAULT AS [Content Location] , CASE WHEN [Content Expiration Enabled] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Content Expiration Enabled] , [Content Expiration Setting] COLLATE DATABASE_DEFAULT AS [Content Expiration Setting] , [Application Name] COLLATE DATABASE_DEFAULT AS [Application Name] , CASE WHEN [Default Document Enabled] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Default Document Enabled] , [Defaul Document Name] COLLATE DATABASE_DEFAULT AS [Defaul Document Name] , CASE WHEN [Anonymous Authentication Enabled] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Anonymous Authentication Enabled] , CASE WHEN [Integrated Windows Authentication Enabled] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Integrated Windows Authentication Enabled] , CASE WHEN [Basic Authentication Enabled] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Basic Authentication Enabled] , CASE WHEN [Digest Authentication Enabled] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Digest Authentication Enabled] , CASE WHEN [Certificate Enabled] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Certificate Enabled] , CASE WHEN [Session State Enabled] = 1 THEN ''Yes'' ELSE ''No'' END AS [Session State Enabled] , [Session Timeout] , CASE WHEN [Script Source Access Enabled] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Script Source Access Enabled] , CASE WHEN [Access Read Enabled] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Access Read Enabled] , CASE WHEN [AccessWrite Enabled] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [AccessWrite Enabled] , CASE WHEN [Directory Browsing Enabled] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Directory Browsing Enabled] , CASE WHEN [Log Enabled] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Log Enabled] , [Execute Permission] COLLATE DATABASE_DEFAULT AS [Execute Permission] , [Application Pool] COLLATE DATABASE_DEFAULT AS [Application Pool] , CASE WHEN [DotNet Passport Authentication Enabled] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [DotNet Passport Authentication Enabled] , CASE WHEN [Buffering Enabled] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Buffering Enabled] , CASE WHEN [Parents Path Enabled] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Parents Path Enabled] , CASE WHEN [Bandwidth Throttling Enabled] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Bandwidth Throttling Enabled] , CASE WHEN [Bandwidth Throttling Limit] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Bandwidth Throttling Limit] , CASE WHEN [Connection Limit] = - 1 THEN ''Unlimited'' ELSE CONVERT(nvarchar(50), [Connection Limit]) END COLLATE DATABASE_DEFAULT AS [Connection Limit] FROM ' + @Source_Inv_IIS_Http_Host_Setting_Data + ' as Inv_IIS_Http_Host_Setting_Data' select @Union_SQLClusterDim = @Union_SQLClusterDim + 'SELECT _id AS SQLClusterID , [Cluster Name] COLLATE DATABASE_DEFAULT AS [Cluster Name] , Description COLLATE DATABASE_DEFAULT AS Description , [IP Address] COLLATE DATABASE_DEFAULT AS [IP Address] , [Default Network Role] , [Max Number of Nodes] , [Total Number of Nodes] FROM ' + @Source_Inv_Cluster + ' as Inv_Cluster' select @Union_SQLClusterFact = @Union_SQLClusterFact + 'SELECT DS._id AS SQLDatabaseSystemID , Cluster.SQLClusterResourceID , Cluster.SQLClusterID , DS._ResourceGuid AS ResourceGuid FROM (SELECT CR._id AS SQLClusterResourceID, C._id AS SQLClusterID, CR._ResourceGuid, CR.[Virtual Server Name] + ''\'' + CR.[Instance Name] AS System FROM ' + @Source_Inv_Associate_Cluster_To_Node + ' AS CtN INNER JOIN ' + @Source_Inv_Cluster_Resource_SQL_Server + 'AS CR ON CR._ResourceGuid = CtN._ResourceGuid AND CR.[Owner Node] = CtN.[Node Name] INNER JOIN ' + @Source_Inv_Cluster + ' AS C ON C._ResourceGuid = CtN._ResourceGuid AND C.[Cluster Name] = CtN.[Cluster Name]) AS Cluster INNER JOIN ' + @Source_Inv_Associate_Database_System_to_Service + ' AS SServ ON Cluster._ResourceGuid = SServ._ResourceGuid AND Cluster.System = SServ.System LEFT OUTER JOIN ' + @Source_Inv_Database_System + ' AS DS ON SServ._ResourceGuid = DS._ResourceGuid AND SServ.System = DS.Name' select @Union_SQLClusterResourceDim = @Union_SQLClusterResourceDim + 'SELECT _id AS SQLClusterResourceID , [SQL Server Resource Name] COLLATE DATABASE_DEFAULT AS [SQL Server Resource Name] , [Virtual Server Name] COLLATE DATABASE_DEFAULT AS [Virtual Server Name] , [Instance Name] COLLATE DATABASE_DEFAULT AS [Instance Name] , [IP Address] COLLATE DATABASE_DEFAULT AS [IP Address] , [Owner Node] COLLATE DATABASE_DEFAULT AS [Owner Node] FROM ' + @Source_Inv_Cluster_Resource_SQL_Server + ' as Inv_Cluster_Resource_SQL_Server' select @Union_SQLDatabaseSystemDim = @Union_SQLDatabaseSystemDim + 'SELECT DS._id AS SQLDatabaseSystemID , DS._ResourceGuid AS ResourceGuid , DS.Name COLLATE DATABASE_DEFAULT AS Name , DS.Vendor COLLATE DATABASE_DEFAULT AS Vendor , DS.Version COLLATE DATABASE_DEFAULT AS Version , DS.Path COLLATE DATABASE_DEFAULT AS Path , SProc.Processors , SProc.[Threads Allocated] , SLic.[Current License In Use] , SLic.[License Type] COLLATE DATABASE_DEFAULT AS [License Type] , SLic.[Number of License] COLLATE DATABASE_DEFAULT AS [Number of License] , SLic.[License Code] , SServ.Service COLLATE DATABASE_DEFAULT AS Service FROM ' + @Source_Inv_Database_System + ' AS DS LEFT OUTER JOIN ' + @Source_Inv_MS_SQL_Server_Processors + ' AS SProc ON SProc._ResourceGuid = DS._ResourceGuid AND SProc.[Instance Name] = DS.Name LEFT OUTER JOIN ' + @Source_Inv_SQL_Server_License + ' AS SLic ON SLic._ResourceGuid = DS._ResourceGuid AND SLic.[Instance Name] = DS.Name LEFT OUTER JOIN ' + @Source_Inv_Associate_Database_System_to_Service + ' AS SServ ON SServ._ResourceGuid = DS._ResourceGuid AND SServ.System = DS.Name' select @Union_SQLDatabaseDim = @Union_SQLDatabaseDim + 'SELECT DB._id AS SQLDatabaseID , DB._ResourceGuid AS ResourceGuid , DB.Name COLLATE DATABASE_DEFAULT AS Name , DB.[Size Allocated] AS [Size Allocated MB] , DB.Language COLLATE DATABASE_DEFAULT AS Language , SQLDBs.[Instance Name] COLLATE DATABASE_DEFAULT AS [Instance Name] , SQLDBs.[Space Available (MegaByte)] AS [Space Available MB] , SQLDBs.[DB Owner] COLLATE DATABASE_DEFAULT AS [DB Owner] , SQLDBs.[Number Of Users] , CASE WHEN SQLDBs.[Automatically Grow File] = 1 THEN ''Yes'' ELSE ''No'' END COLLATE DATABASE_DEFAULT AS [Automatically Grow File] , SQLDBs.[Data File Growth Mode] COLLATE DATABASE_DEFAULT AS [Data File Growth Mode] , SQLDBs.[Data File Growth Size] COLLATE DATABASE_DEFAULT AS [Data File Growth Size MB] , TLogs.[Log File Path] COLLATE DATABASE_DEFAULT AS [Log File Path] , TLogs.[Log File Size (MegaByte)] AS [Log File Size MB] FROM ' + @Source_Inv_Database + ' AS DB LEFT OUTER JOIN ' + @Source_Inv_MS_SQL_Server_Databases + ' AS SQLDBs ON DB._ResourceGuid = SQLDBs._ResourceGuid AND DB.[Instance ID] COLLATE DATABASE_DEFAULT = SQLDBs.[Instance Name] COLLATE DATABASE_DEFAULT LEFT OUTER JOIN ' + @Source_Inv_Associate_Database_To_Service + ' AS DtS ON DtS._ResourceGuid = DB._ResourceGuid AND DtS.[Database] COLLATE DATABASE_DEFAULT = DB.[Instance ID] COLLATE DATABASE_DEFAULT LEFT OUTER JOIN ' + @Source_Inv_Associate_Database_System_to_Service + ' AS StS ON StS._ResourceGuid = DtS._ResourceGuid AND StS.Service COLLATE DATABASE_DEFAULT = DtS.Service COLLATE DATABASE_DEFAULT LEFT OUTER JOIN ' + @Source_Inv_MS_SQL_Server_Transaction_Logs + ' AS TLogs ON DB._ResourceGuid = TLogs._ResourceGuid AND TLogs.[Instance Name] COLLATE DATABASE_DEFAULT = StS.System COLLATE DATABASE_DEFAULT AND TLogs.[Database Name] COLLATE DATABASE_DEFAULT = DB.Name COLLATE DATABASE_DEFAULT' select @Union_SQLDatabaseFact = @Union_SQLDatabaseFact + 'SELECT DB._id AS SQLDatabaseID , StorageAreas.SQLStorageAreaID , DS._id AS SQLDatabaseSystemID , DB._ResourceGuid AS ResourceGuid , CONVERT(datetime, CONVERT(varchar, DB.[Created Date], 112), 112) AS [Created Date] FROM ' + @Source_Inv_Database + ' AS DB LEFT OUTER JOIN ' + @Source_Inv_Associate_Database_To_Service + ' AS DtS ON DtS._ResourceGuid = DB._ResourceGuid AND DtS.[Database] = DB.[Instance ID] LEFT OUTER JOIN ' + @Source_Inv_Associate_Database_System_to_Service + ' AS StS ON StS._ResourceGuid = DtS._ResourceGuid AND StS.Service = DtS.Service LEFT OUTER JOIN ' + @Source_Inv_MS_SQL_Server_Transaction_Logs + ' AS TLogs ON DB._ResourceGuid = TLogs._ResourceGuid AND TLogs.[Instance Name] = StS.System AND TLogs.[Database Name] = DB.Name LEFT OUTER JOIN (SELECT SArea._id AS SQLStorageAreaID, SArea._ResourceGuid, SArea.Name, DStS.DatabaseService, DtDS.[Database] FROM ' + @Source_Inv_Database_Storage_Area + ' AS SArea LEFT OUTER JOIN ' + @Source_Inv_Associate_Database_Service_to_Storage + ' AS DStS ON DStS._ResourceGuid = SArea._ResourceGuid AND DStS.StorageArea = SArea.Name LEFT OUTER JOIN ' + @Source_Inv_Associate_Database_to_Database_Storage_Area + ' AS DtDS ON DtDS._ResourceGuid = SArea._ResourceGuid AND DtDS.StorageArea = SArea.Name) AS StorageAreas ON StorageAreas._ResourceGuid = DB._ResourceGuid AND StorageAreas.[Database] = DB.[Instance ID] AND StorageAreas.DatabaseService = DtS.Service LEFT OUTER JOIN ' + @Source_Inv_Associate_Database_System_to_Service + ' AS SServ ON SServ._ResourceGuid = DB._ResourceGuid AND SServ.Service = DtS.Service AND SServ.System = StS.System LEFT OUTER JOIN ' + @Source_Inv_Database_System + ' AS DS ON SServ._ResourceGuid = DS._ResourceGuid AND SServ.System = DS.Name' select @Union_SQLStorageAreaDim = @Union_SQLStorageAreaDim + 'SELECT _id AS SQLStorageAreaID , Name COLLATE DATABASE_DEFAULT AS Name , [File System Type] COLLATE DATABASE_DEFAULT AS [File System Type] , [File System Size] / (1024 * 1024) AS [File System Size GB] FROM ' + @Source_Inv_Database_Storage_Area + ' AS SArea' select @Union_SQLUserDim = @Union_SQLUserDim + 'SELECT _id AS SQLUserID , Name COLLATE DATABASE_DEFAULT AS Name FROM ' + @Source_Inv_Database_User + ' as Inv_Database_User' select @Union_SQLUserFact = @Union_SQLUserFact + 'SELECT DB._id AS SQLDatabaseID , DB._ResourceGuid AS ResourceGuid , DUser._id AS SQLUserID FROM ' + @Source_Inv_Database + ' AS DB INNER JOIN ' + @Source_Inv_Associate_Database_User_to_Database + ' AS DUtD ON DB.[Instance ID] = DUtD.[Database] AND DB._ResourceGuid = DUtD._ResourceGuid INNER JOIN ' + @Source_Inv_Database_User + ' AS DUser ON DUtD.[User] = DUser.Name AND DUtD._ResourceGuid = DUser._ResourceGuid' fetch next from LinkedServerCursor into @ServerName, @DatabaseName end close LinkedServerCursor deallocate LinkedServerCursor select @Union_ESXServerFact = @Union_ESXServerFact + ')' select @Union_ESXStorageVolumeDim = @Union_ESXStorageVolumeDim + ')' select @Union_ESXVirtualMachineDim = @Union_ESXVirtualMachineDim + ')' select @Union_IISFTPSiteDim = @Union_IISFTPSiteDim + ')' select @Union_IISFTPSiteFact = @Union_IISFTPSiteFact + ')' select @Union_IISServerDim = @Union_IISServerDim + ')' select @Union_IISServerFact = @Union_IISServerFact + ')' select @Union_IISVirtualDirectoryDim = @Union_IISVirtualDirectoryDim + ')' select @Union_IISWebSiteDim = @Union_IISWebSiteDim + ')' select @Union_SQLClusterDim = @Union_SQLClusterDim + ')' select @Union_SQLClusterFact = @Union_SQLClusterFact + ')' select @Union_SQLClusterResourceDim = @Union_SQLClusterResourceDim + ')' select @Union_SQLDatabaseSystemDim = @Union_SQLDatabaseSystemDim + ')' select @Union_SQLDatabaseDim = @Union_SQLDatabaseDim + ')' select @Union_SQLDatabaseFact = @Union_SQLDatabaseFact + ')' select @Union_SQLStorageAreaDim = @Union_SQLStorageAreaDim + ')' select @Union_SQLUserDim = @Union_SQLUserDim + ')' select @Union_SQLUserFact = @Union_SQLUserFact + ')' end begin -- Task DSV select @FirstServer = 1 declare LinkedServerCursor cursor forward_only for select [ServerName], [DatabaseName] from #DependencyMap where [TaskDSV] = 1 open LinkedServerCursor fetch next from LinkedServerCursor into @ServerName, @DatabaseName while @@fetch_status = 0 begin if @FirstServer = 0 begin select @Union_TaskDim = @Union_TaskDim + ' UNION ' select @Union_TaskInstancesFact = @Union_TaskInstancesFact + ' UNION ' select @Union_TaskServerDim = @Union_TaskServerDim + ' UNION ' select @Union_ParentTaskInstancesDim = @Union_ParentTaskInstancesDim + ' UNION ' end else begin select @FirstServer = 0 end exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Evt_Task_Instances', @Source_Evt_Task_Instances OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Inv_AeX_AC_Identification', @Source_Inv_AeX_AC_Identification OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'vComputer', @Source_vComputer OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'CollectionMembership', @Source_CollectionMembership OUTPUT exec dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource @ServerName, @DatabaseName, 'Item', @Source_Item OUTPUT select @Union_TaskDim = @Union_TaskDim + 'SELECT TaskInstanceGuid , TaskName COLLATE DATABASE_DEFAULT AS [Task Name] , Result AS ReturnCode , case when [Success] = 0 then ''No'' when [Success] = 1 then ''Yes'' else ''Unknown'' end COLLATE DATABASE_DEFAULT AS [Success] FROM ' + @Source_Evt_Task_Instances + ' as Evt_Task_Instances' select @Union_TaskInstancesFact = @Union_TaskInstancesFact + 'SELECT CONVERT(datetime, CONVERT(varchar, eti._eventTime, 112), 112) AS EventDate , CONVERT(datetime, CONVERT(varchar, eti._eventTime, 108), 108) AS EventTime , CONVERT(datetime, CONVERT(varchar, eti.StartTime, 112), 112) AS TaskStartDate , CONVERT(datetime, CONVERT(varchar, eti.StartTime, 108), 108) AS TaskStartTime , CONVERT(datetime, CONVERT(varchar, eti.EndTime, 112), 112) AS TaskEndDate , CONVERT(datetime, CONVERT(varchar, eti.EndTime, 108), 108) AS TaskEndTime , ACID._id AS ResourceID , eti._ResourceGuid AS ComputerGuid , eti.TaskServerGuid , eti.TaskInstanceGuid FROM ' + @Source_Evt_Task_Instances + ' AS eti LEFT OUTER JOIN ' + @Source_Inv_AeX_AC_Identification + ' AS ACID ON ACID._ResourceGuid = eti._ResourceGuid' select @Union_TaskServerDim = @Union_TaskServerDim + 'SELECT [Guid] , Name COLLATE DATABASE_DEFAULT AS Name , Domain COLLATE DATABASE_DEFAULT AS Domain , [User] COLLATE DATABASE_DEFAULT AS [User] , [OS Name] COLLATE DATABASE_DEFAULT AS [OS Name] , [OS Version] COLLATE DATABASE_DEFAULT AS [OS Version] , [IP Address] COLLATE DATABASE_DEFAULT AS [IP Address] , Server COLLATE DATABASE_DEFAULT AS Server , [OS Primary Language] , [OS Sub Language] , [MAC Address] COLLATE DATABASE_DEFAULT AS [MAC Address] , [System Type] COLLATE DATABASE_DEFAULT AS [System Type] , IsManaged , IsLocal , CreatedDate FROM ' + @Source_vComputer + ' as vComputer WHERE (Guid IN (SELECT ResourceGuid FROM ' + @Source_CollectionMembership + ' as CollectionMembership WHERE (CollectionGuid = ''F6BD3835-23A3-4A41-8FBD-7056DF400FD8'')))' select @Union_ParentTaskInstancesDim = @Union_ParentTaskInstancesDim + 'SELECT TaskInstanceGuid , '''' COLLATE DATABASE_DEFAULT AS [Parent Task/Policy] , '''' COLLATE DATABASE_DEFAULT AS ParentInstanceType FROM ' + @Source_Evt_Task_Instances + ' as Evt_Task_Instances WHERE (InstanceType = ''Server'') UNION SELECT eti1.TaskInstanceGuid , eti2.TaskName COLLATE DATABASE_DEFAULT AS [Parent Task/Policy] , ''Task'' COLLATE DATABASE_DEFAULT AS ParentInstanceType FROM ' + @Source_Evt_Task_Instances + ' AS eti1 INNER JOIN ' + @Source_Evt_Task_Instances + ' AS eti2 ON eti1.ParentTaskInstanceGuid = eti2.TaskInstanceGuid UNION SELECT eti.TaskInstanceGuid , i.Name COLLATE DATABASE_DEFAULT AS [Parent Task/Policy] , ''Policy'' COLLATE DATABASE_DEFAULT AS ParentInstanceType FROM ' + @Source_Evt_Task_Instances + ' AS eti INNER JOIN ' + @Source_Item + ' AS i ON eti.ParentTaskInstanceGuid = i.Guid' fetch next from LinkedServerCursor into @ServerName, @DatabaseName end close LinkedServerCursor deallocate LinkedServerCursor select @Union_TaskDim = @Union_TaskDim + ')' select @Union_TaskInstancesFact = @Union_TaskInstancesFact + ')' select @Union_TaskServerDim = @Union_TaskServerDim + ')' select @Union_ParentTaskInstancesDim = @Union_ParentTaskInstancesDim + ')' end end begin -- Define Create SQL Statements begin -- Symantec Management Platform DSV if @Union_OrganizationalGroupDim != '()' begin select @CreateSQL_OrganizationalGroupDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_OrganizationalGroupDim] AS SELECT ScopeName, ScopeCollectionGuid FROM ' + @Union_OrganizationalGroupDim + ' OrganizationalGroupDim' exec (@CreateSQL_OrganizationalGroupDim) end if @Union_OrganizationalGroupFact != '()' begin select @CreateSQL_OrganizationalGroupFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_OrganizationalGroupFact] AS SELECT ScopeCollectionGuid, ResourceGuid FROM ' + @Union_OrganizationalGroupFact + ' OrganizationalGroupFact' exec (@CreateSQL_OrganizationalGroupFact) end if @Union_UserDim != '()' begin select @CreateSQL_UserDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_UserDim] AS SELECT UserGuid, UserName, Domain, [Given Name], Surname, [Office Location] ,[Display Name], Email, [Job Title], Company, City, [Street Address] , State, Zip, Country FROM ' + @Union_UserDim + ' UserDim' exec (@CreateSQL_UserDim) end if @Source_ITAnalytics_DateDim != '()' begin select @CreateSQL_DateDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_DateDim] AS SELECT CalendarDate, ShortDate, LongDate, Year, YearName, YearNumber, Quarter, QuarterName, CalendarQuarter, Month, MonthName, MonthNumber, CalendarMonth, DayNumberInMonth, Week, WeekName, WeekNumberInYear, DayOfWeek, DayNumberInWeek, IsWeekDay, IsLastDayOfMonth FROM ' + @Source_ITAnalytics_DateDim + '' exec (@CreateSQL_DateDim) end if @Union_CollectionDim != '()' begin select @CreateSQL_CollectionDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_CollectionDim] AS SELECT CollectionGuid, CollectionName FROM ' + @Union_CollectionDim + ' CollectionDim' exec (@CreateSQL_CollectionDim) end select @CreateSQL_TimeDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_TimeDim] AS SELECT CONVERT(datetime, CONVERT(varchar, Hours.Hour) + '':'' + CONVERT(varchar, Minutes.Minute) + '':'' + CONVERT(varchar, Seconds.Second), 108) AS TimeKey, Hours.Hour, Minutes.Minute, Seconds.Second, RIGHT(''0'' + CONVERT(varchar, Hours.Hour), 2) + '':'' + RIGHT(''0'' + CONVERT(varchar, Minutes.Minute), 2) + '':'' + RIGHT(''0'' + CONVERT(varchar, Seconds.Second), 2) AS Time FROM (SELECT 0 AS Hour UNION SELECT 1 AS Hour UNION SELECT 2 AS Hour UNION SELECT 3 AS Hour UNION SELECT 4 AS Hour UNION SELECT 5 AS Hour UNION SELECT 6 AS Hour UNION SELECT 7 AS Hour UNION SELECT 8 AS Hour UNION SELECT 9 AS Hour UNION SELECT 10 AS Hour UNION SELECT 11 AS Hour UNION SELECT 12 AS Hour UNION SELECT 13 AS Hour UNION SELECT 14 AS Hour UNION SELECT 15 AS Hour UNION SELECT 16 AS Hour UNION SELECT 17 AS Hour UNION SELECT 18 AS Hour UNION SELECT 19 AS Hour UNION SELECT 20 AS Hour UNION SELECT 21 AS Hour UNION SELECT 22 AS Hour UNION SELECT 23 AS Hour) AS Hours INNER JOIN (SELECT 0 AS Minute UNION SELECT 1 AS Minute UNION SELECT 2 AS Minute UNION SELECT 3 AS Minute UNION SELECT 4 AS Minute UNION SELECT 5 AS Minute UNION SELECT 6 AS Minute UNION SELECT 7 AS Minute UNION SELECT 8 AS Minute UNION SELECT 9 AS Minute UNION SELECT 10 AS Minute UNION SELECT 11 AS Minute UNION SELECT 12 AS Minute UNION SELECT 13 AS Minute UNION SELECT 14 AS Minute UNION SELECT 15 AS Minute UNION SELECT 16 AS Minute UNION SELECT 17 AS Minute UNION SELECT 18 AS Minute UNION SELECT 19 AS Minute UNION SELECT 20 AS Minute UNION SELECT 21 AS Minute UNION SELECT 22 AS Minute UNION SELECT 23 AS Minute UNION SELECT 24 AS Minute UNION SELECT 25 AS Minute UNION SELECT 26 AS Minute UNION SELECT 27 AS Minute UNION SELECT 28 AS Minute UNION SELECT 29 AS Minute UNION SELECT 30 AS Minute UNION SELECT 31 AS Minute UNION SELECT 32 AS Minute UNION SELECT 33 AS Minute UNION SELECT 34 AS Minute UNION SELECT 35 AS Minute UNION SELECT 36 AS Minute UNION SELECT 37 AS Minute UNION SELECT 38 AS Minute UNION SELECT 39 AS Minute UNION SELECT 40 AS Minute UNION SELECT 41 AS Minute UNION SELECT 42 AS Minute UNION SELECT 43 AS Minute UNION SELECT 44 AS Minute UNION SELECT 45 AS Minute UNION SELECT 46 AS Minute UNION SELECT 47 AS Minute UNION SELECT 48 AS Minute UNION SELECT 49 AS Minute UNION SELECT 50 AS Minute UNION SELECT 51 AS Minute UNION SELECT 52 AS Minute UNION SELECT 53 AS Minute UNION SELECT 54 AS Minute UNION SELECT 55 AS Minute UNION SELECT 56 AS Minute UNION SELECT 57 AS Minute UNION SELECT 58 AS Minute UNION SELECT 59 AS Minute) AS Minutes ON 1 = 1 INNER JOIN (SELECT 0 AS Second UNION SELECT 1 AS Second UNION SELECT 2 AS Second UNION SELECT 3 AS Second UNION SELECT 4 AS Second UNION SELECT 5 AS Second UNION SELECT 6 AS Second UNION SELECT 7 AS Second UNION SELECT 8 AS Second UNION SELECT 9 AS Second UNION SELECT 10 AS Second UNION SELECT 11 AS Second UNION SELECT 12 AS Second UNION SELECT 13 AS Second UNION SELECT 14 AS Second UNION SELECT 15 AS Second UNION SELECT 16 AS Second UNION SELECT 17 AS Second UNION SELECT 18 AS Second UNION SELECT 19 AS Second UNION SELECT 20 AS Second UNION SELECT 21 AS Second UNION SELECT 22 AS Second UNION SELECT 23 AS Second UNION SELECT 24 AS Second UNION SELECT 25 AS Second UNION SELECT 26 AS Second UNION SELECT 27 AS Second UNION SELECT 28 AS Second UNION SELECT 29 AS Second UNION SELECT 30 AS Second UNION SELECT 31 AS Second UNION SELECT 32 AS Second UNION SELECT 33 AS Second UNION SELECT 34 AS Second UNION SELECT 35 AS Second UNION SELECT 36 AS Second UNION SELECT 37 AS Second UNION SELECT 38 AS Second UNION SELECT 39 AS Second UNION SELECT 40 AS Second UNION SELECT 41 AS Second UNION SELECT 42 AS Second UNION SELECT 43 AS Second UNION SELECT 44 AS Second UNION SELECT 45 AS Second UNION SELECT 46 AS Second UNION SELECT 47 AS Second UNION SELECT 48 AS Second UNION SELECT 49 AS Second UNION SELECT 50 AS Second UNION SELECT 51 AS Second UNION SELECT 52 AS Second UNION SELECT 53 AS Second UNION SELECT 54 AS Second UNION SELECT 55 AS Second UNION SELECT 56 AS Second UNION SELECT 57 AS Second UNION SELECT 58 AS Second UNION SELECT 59 AS Second) AS Seconds ON 1 = 1' exec (@CreateSQL_TimeDim) end begin -- Asset DSV if @Union_AssetCostCenterFact != '()' begin select @CreateSQL_AssetCostCenterFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_AssetCostCenterFact] AS SELECT DISTINCT ResourceGuid , CostCenterGuid FROM ' + @Union_AssetCostCenterFact + ' as AssetCostCenterFact' exec (@CreateSQL_AssetCostCenterFact) end if @Union_AssetDim != '()' begin select @CreateSQL_AssetDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_AssetDim] AS SELECT ResourceGuid , Name , Manufacturer , Model , [Server] , [Serial Number] , Barcode , [Asset Tag] , [ResourceManagerURL] , [ResourceEditURL] FROM ' + @Union_AssetDim + ' as AssetDim' exec (@CreateSQL_AssetDim) end if @Union_AssetFact != '()' begin select @CreateSQL_AssetFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_AssetFact] AS SELECT DISTINCT ResourceGuid , ResourceTypeGuid , AssetStatusGuid , LocationGuid , UserOwnerGuid , DepartmentOwnerGuid FROM ' + @Union_AssetFact + ' as AssetFact' exec (@CreateSQL_AssetFact) end if @Union_AssetStatusDim != '()' begin select @CreateSQL_AssetStatusDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_AssetStatusDim] AS SELECT AssetStatusGuid , [Asset Status] FROM ' + @Union_AssetStatusDim + ' as AssetStatusDim' exec (@CreateSQL_AssetStatusDim) end if @Union_AssetTypeDim != '()' begin select @CreateSQL_AssetTypeDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_AssetTypeDim] AS SELECT DISTINCT AssetTypeGuid , [Asset Type] FROM ' + @Union_AssetTypeDim + ' as AssetTypeDim' exec (@CreateSQL_AssetTypeDim) end if @Union_CostCenterDim != '()' begin select @CreateSQL_CostCenterDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_CostCenterDim] AS SELECT CostCenterGuid , [Cost Center Name] , [Cost Center Code] , [Hierarchy Path] , [Hierarchy Level] FROM ' + @Union_CostCenterDim + ' as CostCenterDim' exec (@CreateSQL_CostCenterDim) end if @Union_DepartmentDim != '()' begin select @CreateSQL_DepartmentDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_DepartmentDim] AS SELECT DepartmentGuid , [Department Name] , [Hierarchy Path] , [Hierarchy Level] FROM ' + @Union_DepartmentDim + ' as DepartmentDim' exec (@CreateSQL_DepartmentDim) end if @Union_LocationDim != '()' begin select @CreateSQL_LocationDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_LocationDim] AS SELECT LocationGuid , [Location Name] , Address , City , State , Country , Zip FROM ' + @Union_LocationDim + ' as LocationDim' exec (@CreateSQL_LocationDim) end end begin -- Software License DSV if @Union_SoftwareLicenseDim != '()' begin select @CreateSQL_SoftwareLicenseDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_SoftwareLicenseDim] AS SELECT DISTINCT SoftwareLicenseGuid , Name FROM ' + @Union_SoftwareLicenseDim + ' as SoftwareLicenseDim' exec (@CreateSQL_SoftwareLicenseDim) end if @Union_SoftwareLicenseInstallationEvaluationFact != '()' begin select @CreateSQL_SoftwareLicenseInstallationEvaluationFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_SoftwareLicenseInstallationEvaluationFact] AS SELECT SoftwareProductGuid , ComputerGuid , FactMonth , LocationGuid , DepartmentGuid , AssetStatusGuid FROM ' + @Union_SoftwareLicenseInstallationEvaluationFact + ' as SoftwareLicenseInstallationEvaluationFact' exec (@CreateSQL_SoftwareLicenseInstallationEvaluationFact) end if @Union_SoftwareLicenseInstallationFact != '()' begin select @CreateSQL_SoftwareLicenseInstallationFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_SoftwareLicenseInstallationFact] AS SELECT DISTINCT SoftwareProductGuid , ComputerGuid , LocationGuid , DepartmentGuid , AssetStatusGuid FROM ' + @Union_SoftwareLicenseInstallationFact + ' as SoftwareLicenseInstallationFact' exec (@CreateSQL_SoftwareLicenseInstallationFact) end if @Union_SoftwareLicenseOwnedFact != '()' begin select @CreateSQL_SoftwareLicenseOwnedFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_SoftwareLicenseOwnedFact] AS SELECT SoftwarePurchaseGuid , FactMonth , CostCenterGuid , Quantity , InstalledLocationGuid , SoftwareLicenseGuid , UserOwnerGuid , DepartmentOwnerGuid FROM ' + @Union_SoftwareLicenseOwnedFact + ' as SoftwareLicenseOwnedFact' exec (@CreateSQL_SoftwareLicenseOwnedFact) end if @Union_SoftwareLicenseUsageEvaluationFact != '()' begin select @CreateSQL_SoftwareLicenseUsageEvaluationFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_SoftwareLicenseUsageEvaluationFact] AS SELECT DISTINCT SoftwareProductGuid , ComputerGuid , EvalDate , LocationGuid , DepartmentGuid , AssetStatusGuid FROM ' + @Union_SoftwareLicenseUsageEvaluationFact + ' as SoftwareLicenseUsageEvaluationFact' exec (@CreateSQL_SoftwareLicenseUsageEvaluationFact) end if @Union_SoftwareLicenseUsageFact != '()' begin select @CreateSQL_SoftwareLicenseUsageFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_SoftwareLicenseUsageFact] AS SELECT DISTINCT SoftwareProductGuid , ComputerGuid , LocationGuid , DepartmentGuid , AssetStatusGuid FROM ' + @Union_SoftwareLicenseUsageFact + ' as SoftwareLicenseUsageFact' exec (@CreateSQL_SoftwareLicenseUsageFact) end if @Union_SoftwarePurchaseDim != '()' begin select @CreateSQL_SoftwarePurchaseDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_SoftwarePurchaseDim] AS SELECT SoftwarePurchaseGuid , Name , Description FROM ' + @Union_SoftwarePurchaseDim + ' as SoftwarePurchaseDim' exec (@CreateSQL_SoftwarePurchaseDim) end if @Union_SoftwarePurchaseFact != '()' begin select @CreateSQL_SoftwarePurchaseFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_SoftwarePurchaseFact] AS SELECT SoftwarePurchaseGuid , CostCenterGuid , Quantity , PurchaseDate , InstalledLocationGuid , SoftwareLicenseGuid , UserOwnerGuid , DepartmentOwnerGuid FROM ' + @Union_SoftwarePurchaseFact + ' as SoftwarePurchaseFact' exec (@CreateSQL_SoftwarePurchaseFact) end begin -- Create Function for Non Inventoried Install Fact select @CreateSQL_fnNonInventoriedInstallFact = 'CREATE FUNCTION [dbo].[fnITAnalytics_CMDB_NonInventoriedInstallFact]() RETURNS @retNonInvInstallFact TABLE ( FactMonth datetime null, NonInventoriedInstalls int null, SoftwareLicenseGuid uniqueidentifier null, CostCenterGuid uniqueidentifier null, DepartmentGuid uniqueidentifier null, LocationGuid uniqueidentifier null ) AS BEGIN ' + @Union_NonInventoriedInstallFact + ' return end' exec (@CreateSQL_fnNonInventoriedInstallFact) end --if @Union_NonInventoriedInstallFact != '()' begin select @CreateSQL_NonInventoriedInstallFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_NonInventoriedInstallFact] AS SELECT FactMonth , NonInventoriedInstalls , SoftwareLicenseGuid , CostCenterGuid , DepartmentGuid , LocationGuid FROM fnITAnalytics_CMDB_NonInventoriedInstallFact() as NonInventoriedInstallFact' --FROM ' + @Union_NonInventoriedInstallFact + ' as NonInventoriedInstallFact' exec (@CreateSQL_NonInventoriedInstallFact) end begin -- Create Function for Borrowed License Fact Count select @CreateSQL_fnBorrowedLicenseFact = 'CREATE FUNCTION [dbo].[fnITAnalytics_CMDB_BorrowedLicenseFact]() RETURNS @retBorrowedLicenseFact TABLE ( SoftwarePurchaseGuid uniqueidentifier null, FactMonth datetime null, CostCenterGuid uniqueidentifier null, Quantity int null, InstalledLocationGuid uniqueidentifier null, SoftwareLicenseGuid uniqueidentifier null, BorrowingFromLicenseGuid uniqueidentifier null, UserOwnerGuid uniqueidentifier null, DepartmentOwnerGuid uniqueidentifier null ) AS BEGIN ' + @Union_BorrowedLicenseCountFact + ' return end' exec (@CreateSQL_fnBorrowedLicenseFact) end --if @Union_BorrowedLicenseCountFact != '()' begin select @CreateSQL_BorrowedLicenseCountFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_BorrowedLicenseCountFact] AS SELECT SoftwarePurchaseGuid , FactMonth , CostCenterGuid , Quantity , InstalledLocationGuid , SoftwareLicenseGuid , BorrowingFromLicenseGuid , UserOwnerGuid , DepartmentOwnerGuid FROM fnITAnalytics_CMDB_BorrowedLicenseFact() as BorrowedLicenseFact' --FROM ' + @Union_BorrowedLicenseCountFact + ' as BorrowedLicenseCountFact' exec (@CreateSQL_BorrowedLicenseCountFact) end begin -- Create Function for Donated License Fact Count select @CreateSQL_fnDonatedLicenseFact = 'CREATE FUNCTION [dbo].[fnITAnalytics_CMDB_DonatedLicenseFact]() RETURNS @retDonatedLicenseFact TABLE ( SoftwarePurchaseGuid uniqueidentifier null, FactMonth datetime null, CostCenterGuid uniqueidentifier null, Quantity int null, InstalledLocationGuid uniqueidentifier null, SoftwareLicenseGuid uniqueidentifier null, DonatedToLicenseGuid uniqueidentifier null, UserOwnerGuid uniqueidentifier null, DepartmentOwnerGuid uniqueidentifier null ) AS BEGIN ' + @Union_DonatedLicenseFact + ' return end' exec (@CreateSQL_fnDonatedLicenseFact) end --if @Union_DonatedLicenseFact != '()' begin select @CreateSQL_DonatedLicenseFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_DonatedLicenseFact] AS SELECT SoftwarePurchaseGuid , FactMonth , CostCenterGuid , Quantity , InstalledLocationGuid , SoftwareLicenseGuid , DonatedToLicenseGuid , UserOwnerGuid , DepartmentOwnerGuid FROM fnITAnalytics_CMDB_DonatedLicenseFact() as DonatedLicenseFact' --FROM ' + @Union_DonatedLicenseFact + ' as DonatedLicenseFact' exec (@CreateSQL_DonatedLicenseFact) end if @Union_SoftwareProductLicenseFact != '()' begin select @CreateSQL_SoftwareProductLicenseFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_SoftwareProductLicenseFact] AS SELECT SoftwareProductGuid , SoftwareLicenseGuid FROM ' + @Union_SoftwareProductLicenseFact + ' as SoftwareProductLicenseFact' exec (@CreateSQL_SoftwareProductLicenseFact) end end begin -- Notification Server DSV if @Union_PackageDim != '()' begin select @CreateSQL_PackageDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_PackageDim] AS SELECT PackageId, PackageName, [Package Location], [Package Size], Publisher FROM ' + @Union_PackageDim + ' AS SWDPackage' exec (@CreateSQL_PackageDim) end if @Union_PackageDownloadMethodDim != '()' begin select @CreateSQL_PackageDownloadMethodDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_PackageDownloadMethodDim] AS SELECT DISTINCT PackageDownloadMethod FROM ' + @Union_PackageDownloadMethodDim + ' AS SWDEvt' exec (@CreateSQL_PackageDownloadMethodDim) end if @Union_PackageDownloadSourceDim != '()' begin select @CreateSQL_PackageDownloadSourceDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_PackageDownloadSourceDim] AS SELECT DISTINCT PackageDownloadSourceName, PackageDownloadSourceType FROM ' + @Union_PackageDownloadSourceDim + ' AS PackageDownloadSourceDim' exec (@CreateSQL_PackageDownloadSourceDim) end if @Union_PackageServerConfigurationRequestFact != '()' begin select @CreateSQL_PackageServerConfigurationRequestFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_PackageServerConfigurationRequestFact] AS SELECT ConfigRequestID , PackageServerGuid , PackageServerResourceID , FactDate , FactTime , StatusCode FROM ' + @Union_PackageServerConfigurationRequestFact + ' AS PackageServerConfigurationRequestFact' exec (@CreateSQL_PackageServerConfigurationRequestFact) end if @Union_PackageServerDim != '()' begin select @CreateSQL_PackageServerDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_PackageServerDim] AS SELECT DISTINCT [PackageServerName] , PackageServerGuid , PackageServerType FROM ' + @Union_PackageServerDim + ' as PackageServerDim' exec (@CreateSQL_PackageServerDim) end if @Union_PackageServerPackageDistributionEventDim != '()' begin select @CreateSQL_PackageServerPackageDistributionEventDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_PackageServerPackageDistributionEventDim] AS SELECT Status FROM ' + @Union_PackageServerPackageDistributionEventDim + ' as PackageServerPackageDistributionEventDim' exec (@CreateSQL_PackageServerPackageDistributionEventDim) end if @Union_PackageServerPackageDistributionEventFact != '()' begin select @CreateSQL_PackageServerPackageDistributionEventFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_PackageServerPackageDistributionEventFact] AS SELECT PackageDistributionEventID , PackageServerGuid , Status , PackageId , FactDate , FactTime FROM ' + @Union_PackageServerPackageDistributionEventFact + ' as PackageServerPackageDistributionEventFact' exec (@CreateSQL_PackageServerPackageDistributionEventFact) end if @Union_PackageServerPackageStatusDim != '()' begin select @CreateSQL_PackageServerPackageStatusDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_PackageServerPackageStatusDim] AS SELECT DISTINCT Status FROM ' + @Union_PackageServerPackageStatusDim + ' as PackageServerPackageStatusDim' exec (@CreateSQL_PackageServerPackageStatusDim) end if @Union_PackageServerPackageStatusFact != '()' begin select @CreateSQL_PackageServerPackageStatusFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_PackageServerPackageStatusFact] AS SELECT PackageId , PackageServerGuid , Version , Status , [Package Size GB] , PackageServerResourceID FROM ' + @Union_PackageServerPackageStatusFact + ' as PackageServerPackageStatusFact' exec (@CreateSQL_PackageServerPackageStatusFact) end if @Union_CollectionMembershipFact != '()' begin select @CreateSQL_CollectionMembershipFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_CollectionMembershipFact] AS SELECT CollectionGuid , ResourceGuid FROM ' + @Union_CollectionMembershipFact + ' as CollectionMembershipFact' exec (@CreateSQL_CollectionMembershipFact) end if @Union_AdvertisementDim != '()' begin select @CreateSQL_AdvertisementDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_AdvertisementDim] AS SELECT AdvertisementId , ProgramId , [Advertisement Name] FROM ' + @Union_AdvertisementDim + ' as AdvertisementDim' exec (@CreateSQL_AdvertisementDim) end if @Union_AltirisAgentConfigurationRequestEventFact != '()' begin select @CreateSQL_AltirisAgentConfigurationRequestEventFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_AltirisAgentConfigurationRequestEventFact] AS SELECT ConfigRequestID , ResourceGuid , ResourceID , FactDate , FactTime , StatusCode FROM ' + @Union_AltirisAgentConfigurationRequestEventFact + ' as AltirisAgentConfigurationRequestEventFact' exec (@CreateSQL_AltirisAgentConfigurationRequestEventFact) end select @CreateSQL_ClientConfigStatusCodeDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_ClientConfigStatusCodeDim] AS SELECT 0 AS StatusCode, ''Yes'' AS [Configuration Changed] UNION SELECT 1 AS StatusCode, ''No'' AS [Configuration Changed] ' exec (@CreateSQL_ClientConfigStatusCodeDim) if @Union_ForwardServerDim != '()' begin select @CreateSQL_ForwardServerDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_ForwardServerDim] AS SELECT ForwardServerGuid , ForwardServerName FROM ' + @Union_ForwardServerDim + ' as ForwardServerDim' exec (@CreateSQL_ForwardServerDim) end if @Union_InventoryClassDim != '()' begin select @CreateSQL_InventoryClassDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_InventoryClassDim] AS SELECT InventoryClassGuid , InventoryClassName , InventoryClassType FROM ' + @Union_InventoryClassDim + ' as InventoryClassDim' exec (@CreateSQL_InventoryClassDim) end if @Union_InventoryForwardingFact != '()' begin select @CreateSQL_InventoryForwardingFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_InventoryForwardingFact] AS SELECT InventoryClassGuid , InventoryClassId , ResourceGuid , ResourceId , ForwardServerGuid , ForwardDate , LastUpdateDate , TmpDateDiff , UpdatedSinceLastForward FROM ' + @Union_InventoryForwardingFact + ' as InventoryForwardingFact' exec (@CreateSQL_InventoryForwardingFact) end if @Union_InventoryUpdatedSinceForwardedFact != '()' begin select @CreateSQL_InventoryUpdatedSinceForwardedFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_InventoryUpdatedSinceForwardedFact] AS SELECT InventoryClassGuid , InventoryClassId , ResourceGuid , ResourceId , ForwardServerGuid , ForwardDate , LastUpdatedDate FROM ' + @Union_InventoryUpdatedSinceForwardedFact + ' as InventoryUpdatedSinceForwardedFact' exec (@CreateSQL_InventoryUpdatedSinceForwardedFact) end if @Union_InventoryUpdateFact != '()' begin select @CreateSQL_InventoryUpdateFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_InventoryUpdateFact] AS SELECT InventoryClassGuid , InventoryClassId , ResourceGuid , ResourceId , CreatedDate , ModifiedDate , [RowCount] FROM ' + @Union_InventoryUpdateFact + ' as InventoryUpdateFact' exec (@CreateSQL_InventoryUpdateFact) end if @Union_ResourceDim != '()' begin select @CreateSQL_ResourceDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_ResourceDim] AS SELECT ResourceGuid , ResourceName , ResourceType , [Altiris Managed] FROM ' + @Union_ResourceDim + ' as ResourceDim' exec (@CreateSQL_ResourceDim) end if @Union_SiteDim != '()' begin select @CreateSQL_SiteDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_SiteDim] AS SELECT SiteGuid, SiteName FROM ' + @Union_SiteDim + ' as SiteDim' exec (@CreateSQL_SiteDim) end if @Union_SoftwareDeliveryExecutionEventCommandLineDim != '()' begin select @CreateSQL_SoftwareDeliveryExecutionEventCommandLineDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_SoftwareDeliveryExecutionEventCommandLineDim] AS SELECT DISTINCT CommandLine FROM ' + @Union_SoftwareDeliveryExecutionEventCommandLineDim + ' as SoftwareDeliveryExecutionEventCommandLineDim' exec (@CreateSQL_SoftwareDeliveryExecutionEventCommandLineDim) end if @Union_SoftwareDeliveryExecutionEventFact != '()' begin select @CreateSQL_SoftwareDeliveryExecutionEventFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_SoftwareDeliveryExecutionEventFact] AS SELECT SoftwareDeliveryExecutionEventID , ResourceGUID , AdvertisementId , PackageId , Status , CommandLine , EndDate , EndTime , Duration , ResourceId FROM ' + @Union_SoftwareDeliveryExecutionEventFact + ' as SoftwareDeliveryExecutionEventFact' exec (@CreateSQL_SoftwareDeliveryExecutionEventFact) --print (@CreateSQL_SoftwareDeliveryExecutionEventFact) end if @Union_SoftwareDeliveryExecutionEventStatusDim != '()' begin select @CreateSQL_SoftwareDeliveryExecutionEventStatusDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_SoftwareDeliveryExecutionEventStatusDim] AS SELECT DISTINCT Status FROM ' + @Union_SoftwareDeliveryExecutionEventStatusDim + ' as SoftwareDeliveryExecutionEventStatusDim' exec (@CreateSQL_SoftwareDeliveryExecutionEventStatusDim) end if @Union_SoftwareDeliveryPackageEventFact != '()' begin select @CreateSQL_SoftwareDeliveryPackageEventFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_SoftwareDeliveryPackageEventFact] AS SELECT SoftwareDeliveryPackageEventID , EndCursor , StartCursor , BytesTransferred , EndStatus , StartStatus , EndDate , EndTime , Duration , StartDate , StartTime , ResourceGuid , ResourceId , PackageId , URL , PackageDownloadMethod , PackageDownloadSourceName FROM ' + @Union_SoftwareDeliveryPackageEventFact + ' as SoftwareDeliveryPackageEventFact' exec (@CreateSQL_SoftwareDeliveryPackageEventFact) end if @Union_SoftwareDeliveryPackageEventStatusDim != '()' begin select @CreateSQL_SoftwareDeliveryPackageEventStatusDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_SoftwareDeliveryPackageEventStatusDim] AS SELECT DISTINCT Status FROM ' + @Union_SoftwareDeliveryPackageEventStatusDim + ' as SoftwareDeliveryPackageEventStatusDim' exec (@CreateSQL_SoftwareDeliveryPackageEventStatusDim) end if @Union_SoftwareDeliveryPackageEventURLDim != '()' begin select @CreateSQL_SoftwareDeliveryPackageEventURLDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_SoftwareDeliveryPackageEventURLDim] AS SELECT DISTINCT URL FROM ' + @Union_SoftwareDeliveryPackageEventURLDim + ' as SoftwareDeliveryPackageEventURLDim' exec (@CreateSQL_SoftwareDeliveryPackageEventURLDim) end if @Union_SoftwareDeliveryStatusEventFact != '()' begin select @CreateSQL_SoftwareDeliveryStatusEventFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_SoftwareDeliveryStatusEventFact] AS SELECT SoftwareDeliveryStatusEventID , ResourceGUID , EventType , AdvertisementId , PackageId , Status , FactDate , FactTime , ResourceId FROM ' + @Union_SoftwareDeliveryStatusEventFact + ' as SoftwareDeliveryStatusEventFact' exec (@CreateSQL_SoftwareDeliveryStatusEventFact) end if @Union_SoftwareDeliveryStatusEventStatusDim != '()' begin select @CreateSQL_SoftwareDeliveryStatusEventStatusDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_SoftwareDeliveryStatusEventStatusDim] AS SELECT DISTINCT Status FROM ' + @Union_SoftwareDeliveryStatusEventStatusDim + ' as SoftwareDeliveryStatusEventStatusDim' exec (@CreateSQL_SoftwareDeliveryStatusEventStatusDim) end if @Union_SoftwareDeliveryStatusEventTypeDim != '()' begin select @CreateSQL_SoftwareDeliveryStatusEventTypeDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_SoftwareDeliveryStatusEventTypeDim] AS SELECT DISTINCT EventType FROM ' + @Union_SoftwareDeliveryStatusEventTypeDim + ' as SoftwareDeliveryStatusEventTypeDim' exec (@CreateSQL_SoftwareDeliveryStatusEventTypeDim) end if @Union_SubnetDim != '()' begin select @CreateSQL_SubnetDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_SubnetDim] AS SELECT SubnetGuid, [Subnet Name], Subnet, [Subnet Mask] FROM ' + @Union_SubnetDim + ' as SubnetDim' exec (@CreateSQL_SubnetDim) end end begin -- Patch Management DSV if @Union_ApplicablePatchFact != '()' begin select @CreateSQL_ApplicablePatchFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_ApplicablePatchFact] AS SELECT ResourceId , ResourceGuid , SWUGuid FROM ' + @Union_ApplicablePatchFact + ' as ApplicablePatchFact' exec (@CreateSQL_ApplicablePatchFact) end if @Union_InstalledPatchFact != '()' begin select @CreateSQL_InstalledPatchFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_InstalledPatchFact] AS SELECT ResourceId , ResourceGuid , SWUGuid FROM ' + @Union_InstalledPatchFact + ' as InstalledPatchFact' exec (@CreateSQL_InstalledPatchFact) end if @Union_PatchVulnerabilityFact != '()' begin select @CreateSQL_PatchVulnerabilityFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_PatchVulnerabilityFact] AS SELECT ResourceId, ResourceGuid, SWUGuid FROM ' + @Union_PatchVulnerabilityFact + ' as PatchVulnerabilityFact' exec (@CreateSQL_PatchVulnerabilityFact) end if @Union_PatchManagementSoftwareUpdateDim != '()' begin select @CreateSQL_PatchManagementSoftwareUpdateDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_PatchManagementSoftwareUpdateDim] AS SELECT DISTINCT SWUGuid , [Filename] , Severity , CustomSeverity , [Reference] , ReleaseDate , [Provider] , RebootRequired , PatchSize FROM ' + @Union_PatchManagementSoftwareUpdateDim + ' as PatchManagementSoftwareUpdateDim' exec (@CreateSQL_PatchManagementSoftwareUpdateDim) end end begin -- Client Inventory DSV if @Union_InstalledFileFact != '()' begin select @CreateSQL_InstalledFileFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_InstalledFileFact] AS SELECT ResourceGuid , InstalledFileID , ResourceId , FileResourceGuid , Name , Path , ModifiedTime , FileAttributes , VirtualSoftwareGuid FROM ' + @Union_InstalledFileFact + ' AS InstalledFileFact' exec (@CreateSQL_InstalledFileFact) end if @Union_InstalledSoftwareFact != '()' begin select @CreateSQL_InstalledSoftwareFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_InstalledSoftwareFact] AS SELECT DISTINCT InstalledSoftwareID , ResourceId , ResourceGuid , SoftwareComponentGuid , AddRemoveProgramID FROM ' + @Union_InstalledSoftwareFact + ' AS InstalledSoftwareFact' exec (@CreateSQL_InstalledSoftwareFact) end if @Union_ComputerDim != '()' begin select @CreateSQL_ComputerDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_ComputerDim] AS SELECT ResourceGuid , [Name] , [Domain] , [Primary User] , [Last Logon User] , [OS Name] , [OS Type] , [OS Version] , [OS Revision] , [IP Address] , [Server] , [MAC Address] , [System Type] , CreatedDate , [Last Basic Inventory Date] , [Number Of Processors] , [Total Physical Memory MB] , [Total Physical Disk MB] , [Total Free Space MB] , [Computer System Manufacturer] , [Computer System Model] , [Computer System Serial Number] , [Chassis Manufacturer] , [Chassis Model] , [Chassis Serial Number] , [Discovery Method] , DiscoveryDate , [Altiris Managed] , [CPU Speed] , [CPU Count] , [CPU Type] , [ResourceManagerURL] , [ResourceEditURL] FROM ' + @Union_ComputerDim + ' AS ComputerDim' exec (@CreateSQL_ComputerDim) end if @Union_ComputerFact != '()' begin select @CreateSQL_ComputerFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_ComputerFact] AS SELECT ResourceGuid , CreatedDate , [Last Basic Inventory Date] , [Discovery Date] FROM ' + @Union_ComputerFact + ' AS ComputerFact' exec (@CreateSQL_ComputerFact) end if @Union_AddRemoveProgramsDim != '()' begin select @CreateSQL_AddRemoveProgramsDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_AddRemoveProgramsDim] AS SELECT AddRemoveProgramID , DisplayName , Hidden FROM ' + @Union_AddRemoveProgramsDim + ' AS AddRemoveProgramsDim' exec (@CreateSQL_AddRemoveProgramsDim) end if @Union_WindowsFileDim!= '()' begin select @CreateSQL_WindowsFileDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_WindowsFileDim] AS SELECT Guid , Name , Description , InternalName , Language , FileVersionString , FileVersionBinary , ProductName , ProductVersion , Manufacturer FROM ' + @Union_WindowsFileDim + ' AS WindowsFileDim' exec (@CreateSQL_WindowsFileDim) end if @Union_LogicalDiskDim != '()' begin select @CreateSQL_LogicalDiskDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_LogicalDiskDim] AS SELECT LogicalDiskID , ResourceGuid , Description , [File System] , Name , [Device ID] FROM ' + @Union_LogicalDiskDim + ' AS LogicalDiskDim' exec (@CreateSQL_LogicalDiskDim) end if @Union_LogicalDiskFact != '()' begin select @CreateSQL_LogicalDiskFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_LogicalDiskFact] AS SELECT LogicalDiskID , ResourceGuid , ResourceId , [Free Space GB] , [Size GB] FROM ' + @Union_LogicalDiskFact + ' AS LogicalDiskFact' exec (@CreateSQL_LogicalDiskFact) end if @Union_PhysicalMemoryArrayFact != '()' begin select @CreateSQL_PhysicalMemoryArrayFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_PhysicalMemoryArrayFact] AS SELECT PhysicalMemoryArrayID , ResourceGuid , ResourceId , [Max Capacity GB] , [Memory Devices] , Tag FROM ' + @Union_PhysicalMemoryArrayFact + ' AS PhysicalMemoryArrayFact' exec (@CreateSQL_PhysicalMemoryArrayFact) end if @Union_PhysicalMemoryFact != '()' begin select @CreateSQL_PhysicalMemoryFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_PhysicalMemoryFact] AS SELECT PhysicalMemoryID , ResourceGuid , ResourceId , [Capacity GB] , [Speed MHz] , [Device ID] FROM ' + @Union_PhysicalMemoryFact + ' AS PhysicalMemoryFact' exec (@CreateSQL_PhysicalMemoryFact) end if @Union_ProcessorDim != '()' begin select @CreateSQL_ProcessorDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_ProcessorDim] AS SELECT ResourceGuid , [Speed GHz] , Description , Manufacturer , Model , [Number Of Processors] FROM ' + @Union_ProcessorDim + ' AS ProcessorDim' exec (@CreateSQL_ProcessorDim) end if @Union_ProcessorFact != '()' begin select @CreateSQL_ProcessorFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_ProcessorFact] AS SELECT ProcessorID , ResourceId , ResourceGuid , [External Clock Speed MHz] , [Speed GHz] , [Number Of Cores] , [Number Of Logical Processors] , [Device ID] FROM ' + @Union_ProcessorFact + ' AS ProcessorFact' exec (@CreateSQL_ProcessorFact) end if @Union_SoftwareComponentDim != '()' begin select @CreateSQL_SoftwareComponentDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_SoftwareComponentDim] AS SELECT Guid , Name , Description , Version , SoftwareComponentTypeGuid , SoftwareComponentTypeName , SoftwareComponentCreatedDate , SoftwareProductGuid , SoftwareProductName , SoftwareTypeGuid , SoftwareTypeName , CompanyGuid , CompanyName , ParentFolderGuid FROM ' + @Union_SoftwareComponentDim + ' AS SoftwareComponentDim' exec (@CreateSQL_SoftwareComponentDim) end if @Union_SoftwareComponentFileFact != '()' begin select @CreateSQL_SoftwareComponentFileFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_SoftwareComponentFileFact] AS SELECT SoftwareComponentGuid , FileGuid FROM ' + @Union_SoftwareComponentFileFact + ' AS SoftwareComponentFileFact' exec (@CreateSQL_SoftwareComponentFileFact) end if @Union_SoftwareProductDim != '()' begin select @CreateSQL_SoftwareProductDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_SoftwareProductDim] AS SELECT SoftwareProductGuid , Name FROM ' + @Union_SoftwareProductDim + ' AS SoftwareProductDim' exec (@CreateSQL_SoftwareProductDim) end if @Union_SoftwareProductComponentFact != '()' begin select @CreateSQL_SoftwareProductComponentFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_SoftwareProductComponentFact] AS SELECT SoftwareProductGuid , SoftwareComponentGuid FROM ' + @Union_SoftwareProductComponentFact + ' AS SoftwareProductComponentFact' exec (@CreateSQL_SoftwareProductComponentFact) end if @Union_ApplicationMeteringFact != '()' begin select @CreateSQL_ApplicationMeteringFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_ApplicationMeteringFact] AS SELECT ResourceId , ResourceGuid , FileResourceGuid , UserGuid , [Last Start] , [Month Year] , [Run Count] , [Denial Count] , [Total Run Time] , [Peak Memory] , [Average CPU Usage] , [Month End Summary] , [Month] , [Year] , [FactMonth] FROM ' + @Union_ApplicationMeteringFact + ' as ApplicationMeteringFact' exec (@CreateSQL_ApplicationMeteringFact) end end begin -- Event Console DSV if @Union_AlertDim != '()' begin select @CreateSQL_AlertDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_AlertDim] AS SELECT AlertID , AlertGuid , Message , Hostname FROM ' + @Union_AlertDim + ' as AlertDim' exec (@CreateSQL_AlertDim) end if @Union_AlertFact != '()' begin select @CreateSQL_AlertFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_AlertFact] AS SELECT AlertID , AlertGuid , ResourceGuid , ResourceId , ProductGuid , ProtocolGuid , DefinitionGuid , CategoryGuid , SeverityGuid , timestamp , FactDate , FactTime , RuleGuid FROM ' + @Union_AlertFact + ' as AlertFact' exec (@CreateSQL_AlertFact) end if @Union_AlertActionAuditFact != '()' begin select @CreateSQL_AlertActionAuditFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_AlertActionAuditFact] AS SELECT AlertActionAuditGuid , AlertGuid , ActionTypeID , UserName , timestamp , FactDate , FactTime , AlertID , ResourceGuid , ResourceId , ProductGuid , ProtocolGuid , DefinitionGuid , CategoryGuid , SeverityGuid , RuleGuid FROM ' + @Union_AlertActionAuditFact + ' as AlertActionAuditFact' exec (@CreateSQL_AlertActionAuditFact) end if @Union_AlertActionAuditTypeDim != '()' begin select @CreateSQL_AlertActionAuditTypeDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_AlertActionAuditTypeDim] AS SELECT ActionTypeID , ActionTypeName , ActionTypeDescription FROM ' + @Union_AlertActionAuditTypeDim + ' as AlertActionAuditTypeDim' exec (@CreateSQL_AlertActionAuditTypeDim) end if @Union_AlertCategoryDim != '()' begin select @CreateSQL_AlertCategoryDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_AlertCategoryDim] AS SELECT DISTINCT CategoryGuid , CategoryName FROM ' + @Union_AlertCategoryDim + ' as AlertCategoryDim' exec (@CreateSQL_AlertCategoryDim) end if @Union_AlertProtocolDim != '()' begin select @CreateSQL_AlertProtocolDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_AlertProtocolDim] AS SELECT ProtocolGuid , ProtocolAbbreviation , ProtocolName FROM ' + @Union_AlertProtocolDim + ' as AlertProtocolDim' exec (@CreateSQL_AlertProtocolDim) end if @Union_AlertSeverityDim != '()' begin select @CreateSQL_AlertSeverityDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_AlertSeverityDim] AS SELECT SeverityGuid , SeverityName , SeverityLevel FROM ' + @Union_AlertSeverityDim + ' as AlertSeverityDim' exec (@CreateSQL_AlertSeverityDim) end if @Union_MonitorRuleDim != '()' begin select @CreateSQL_MonitorRuleDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_MonitorRuleDim] AS SELECT RuleGuid , RuleName , MetricGuid , MetricName , CategoryGuid , CategoryName , MonitorPackGuid , MonitorPackName FROM ' + @Union_MonitorRuleDim + ' as MonitorRuleDim' exec (@CreateSQL_MonitorRuleDim) end end begin -- Monitor DSV select @CreateSQL_MonitorNtEventRuleTriggeredDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_MonitorNtEventRuleTriggeredDim] AS SELECT CONVERT(bit, 1) AS RuleTriggeredValue, ''Yes'' AS RuleTriggeredName UNION SELECT CONVERT(bit, 0) AS RuleTriggeredValue, ''No'' AS RuleTriggeredName' exec (@CreateSQL_MonitorNtEventRuleTriggeredDim) select @CreateSQL_MonitorMetricDetailLevelDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_MonitorMetricDetailLevelDim] AS SELECT ''Detailed'' AS DetailLevel UNION SELECT ''Daily'' AS DetailLevel UNION SELECT ''Hourly'' AS DetailLevel ' exec (@CreateSQL_MonitorMetricDetailLevelDim) if @Union_MonitorMetricDataFact != '()' begin select @CreateSQL_MonitorMetricDataFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_MonitorMetricDataFact] AS SELECT FactDate , FactTime , ResourceGuid , ResourceId , SourceGuid , MetricGuid , Instance , InstanceKey , NumValue , Min , Max , Duration , DetailLevel FROM ' + @Union_MonitorMetricDataFact + ' as MonitorMetricDataFact' exec (@CreateSQL_MonitorMetricDataFact) end if @Union_MonitorMetricDim != '()' begin select @CreateSQL_MonitorMetricDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_MonitorMetricDim] AS SELECT [MetricName] , [MetricGuid] FROM ' + @Union_MonitorMetricDim + ' as MonitorMetricDim' exec (@CreateSQL_MonitorMetricDim) end if @Union_MonitorMetricInstanceDim != '()' begin select @CreateSQL_MonitorMetricInstanceDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_MonitorMetricInstanceDim] AS SELECT InstanceKey , ResourceGuid , SourceGuid , MetricGuid , Instance FROM ' + @Union_MonitorMetricInstanceDim + ' as MonitorMetricInstanceDim' exec (@CreateSQL_MonitorMetricInstanceDim) end if @Union_MonitorMetricSourceDim != '()' begin select @CreateSQL_MonitorMetricSourceDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_MonitorMetricSourceDim] AS SELECT DISTINCT SourceGuid , SourceName FROM ' + @Union_MonitorMetricSourceDim + ' as MonitorMetricSourceDim' exec (@CreateSQL_MonitorMetricSourceDim) end if @Union_MonitorNtEventCategoryDim != '()' begin select @CreateSQL_MonitorNtEventCategoryDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_MonitorNtEventCategoryDim] AS SELECT DISTINCT Category FROM ' + @Union_MonitorNtEventCategoryDim + ' as MonitorNtEventCategoryDim' exec (@CreateSQL_MonitorNtEventCategoryDim) end if @Union_MonitorNtEventDescriptionDim != '()' begin select @CreateSQL_MonitorNtEventDescriptionDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_MonitorNtEventDescriptionDim] AS SELECT DISTINCT Description FROM ' + @Union_MonitorNtEventDescriptionDim + ' as MonitorNtEventDescriptionDim' exec (@CreateSQL_MonitorNtEventDescriptionDim) end if @Union_MonitorNtEventIdDim != '()' begin select @CreateSQL_MonitorNtEventIdDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_MonitorNtEventIdDim] AS SELECT DISTINCT EventId FROM ' + @Union_MonitorNtEventIdDim + ' as MonitorNtEventIdDim' exec (@CreateSQL_MonitorNtEventIdDim) end if @Union_MonitorNtEventLogFileDim != '()' begin select @CreateSQL_MonitorNtEventLogFileDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_MonitorNtEventLogFileDim] AS SELECT DISTINCT LogFile FROM ' + @Union_MonitorNtEventLogFileDim + ' as MonitorNtEventLogFileDim' exec (@CreateSQL_MonitorNtEventLogFileDim) end if @Union_MonitorNtEventMessageDLLDim != '()' begin select @CreateSQL_MonitorNtEventMessageDLLDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_MonitorNtEventMessageDLLDim] AS SELECT DISTINCT MessageDLL FROM ' + @Union_MonitorNtEventMessageDLLDim + ' as MonitorNtEventMessageDLLDim' exec (@CreateSQL_MonitorNtEventMessageDLLDim) end if @Union_MonitorNtEventsFact != '()' begin select @CreateSQL_MonitorNtEventsFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_MonitorNtEventsFact] AS SELECT FactDate , FactTime , ResourceGuid , ResourceId , Type , [User] , Computer , Source , Category , LogFile , MessageDLL , RuleTriggered , EventId , Description FROM ' + @Union_MonitorNtEventsFact + ' as MonitorNtEventsFact' exec (@CreateSQL_MonitorNtEventsFact) end if @Union_MonitorNtEventSourceDim != '()' begin select @CreateSQL_MonitorNtEventSourceDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_MonitorNtEventSourceDim] AS SELECT DISTINCT Source FROM ' + @Union_MonitorNtEventSourceDim + ' as MonitorNtEventSourceDim' exec (@CreateSQL_MonitorNtEventSourceDim) end if @Union_MonitorNtEventTypeDim != '()' begin select @CreateSQL_MonitorNtEventTypeDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_MonitorNtEventTypeDim] AS SELECT DISTINCT Type FROM ' + @Union_MonitorNtEventTypeDim + ' as MonitorNtEventTypeDim' exec (@CreateSQL_MonitorNtEventTypeDim) end if @Union_MonitorNtEventUserDim != '()' begin select @CreateSQL_MonitorNtEventUserDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_MonitorNtEventUserDim] AS SELECT DISTINCT [User] FROM ' + @Union_MonitorNtEventUserDim + ' as MonitorNtEventUserDim' exec (@CreateSQL_MonitorNtEventUserDim) end if @Union_MonitorPolicyDim != '()' begin select @CreateSQL_MonitorPolicyDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_MonitorPolicyDim] AS SELECT RuleGuid , RuleName , MetricGuid , MetricName , CategoryGuid , CategoryName , MonitorPackGuid , MonitorPackName FROM ' + @Union_MonitorPolicyDim + ' as MonitorPolicyDim' exec (@CreateSQL_MonitorPolicyDim) end if @Union_MonitorProcessFact != '()' begin select @CreateSQL_MonitorProcessFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_MonitorProcessFact] AS SELECT ComputerGuid , ProcessNameKey , OwnerKey , FactDate , FactTime , CPU , CpuTime , WorkingSetSize , HandleCount , ThreadCount , VmSize FROM ' + @Union_MonitorProcessFact + ' as MonitorProcessFact' exec (@CreateSQL_MonitorProcessFact) end if @Union_MonitorProcessNameDim != '()' begin select @CreateSQL_MonitorProcessNameDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_MonitorProcessNameDim] AS SELECT ProcessNameKey , ProcessName FROM ' + @Union_MonitorProcessNameDim + ' as MonitorProcessNameDim' exec (@CreateSQL_MonitorProcessNameDim) end if @Union_MonitorProcessOwnerDim != '()' begin select @CreateSQL_MonitorProcessOwnerDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_MonitorProcessOwnerDim] AS SELECT OwnerKey , Owner FROM ' + @Union_MonitorProcessOwnerDim + ' as MonitorProcessOwnerDim' exec (@CreateSQL_MonitorProcessOwnerDim) end if @Union_MonitorTaskFact != '()' begin select @CreateSQL_MonitorTaskFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_MonitorTaskFact] AS SELECT ComputerGuid , eventTime , TaskInstanceGuid , TaskInstanceType , RuleGuid , TaskVersionGuid , TaskStartTime , TaskEndTime , TaskServerGuid , TaskGuid , TaskName , MetricGuid , CategoryGuid , FactDate , FactTime FROM ' + @Union_MonitorTaskFact + ' as MonitorTaskFact' exec (@CreateSQL_MonitorTaskFact) end end begin -- Server Inventory DSV if @Union_ESXServerFact != '()' begin select @CreateSQL_ESXServerFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_ESXServerFact] AS SELECT ResourceGuid , ESXVirtualMachineID , ESXStorageVolumeID , ESXHostID FROM ' + @Union_ESXServerFact + ' as ESXServerFact' exec (@CreateSQL_ESXServerFact) end if @Union_ESXStorageVolumeDim != '()' begin select @CreateSQL_ESXStorageVolumeDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_ESXStorageVolumeDim] AS SELECT ESXStorageVolumeID , ResourceGuid , [Instance ID] , [Volume Name] , [File System Version] , [Total Size GB] , [Free Size GB] FROM ' + @Union_ESXStorageVolumeDim + ' as ESXStorageVolumeDim' exec (@CreateSQL_ESXStorageVolumeDim) end if @Union_ESXVirtualMachineDim != '()' begin select @CreateSQL_ESXVirtualMachineDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_ESXVirtualMachineDim] AS SELECT DISTINCT ESXVirtualMachineID , ResourceGuid , [Virtual Machine] , [Instance ID] , Name , [Virtual System Type] , [Automatic Startup Action] , [Automatic Shutdown Action] , [Automatic Startup Delay] , [Automatic Shutdown Delay] , [Automatic Startup Action Sequence Number] , State , [CPUs Assigned] , [Memory Allocated GB] , [Disk Used GB] , [Max Disk Size GB] FROM ' + @Union_ESXVirtualMachineDim + ' as ESXVirtualMachineDim' exec (@CreateSQL_ESXVirtualMachineDim) end if @Union_IISFTPSiteDim != '()' begin select @CreateSQL_IISFTPSiteDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_IISFTPSiteDim] AS SELECT IISServerResourceGuid , IISFTPSiteID , [FTP Site Name] , Path , Rights FROM ' + @Union_IISFTPSiteDim + ' as IISFTPSiteDim' exec (@CreateSQL_IISFTPSiteDim) end if @Union_IISFTPSiteFact != '()' begin select @CreateSQL_IISFTPSiteFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_IISFTPSiteFact] AS SELECT IISServerResourceGuid , IISFTPSiteID FROM ' + @Union_IISFTPSiteFact + ' as IISFTPSiteFact' exec (@CreateSQL_IISFTPSiteFact) end if @Union_IISServerDim != '()' begin select @CreateSQL_IISServerDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_IISServerDim] AS SELECT IISServerID , [Instance ID] , [IIS Version] , [IIS Service] , [SMTP Service] , [BITS Service] , [NNTP Service] , [ASP DotNet Installed] , [ASP Installed] , [COM Plus Service] , [Network DTC Insatlled] , [Message Queue Service] , [Application Server Console Insatlled] , [Internet Data Connector Installed] , [Remote Admin Installed] , [Remote Desktop Web Connection Installed] , [WWW Service] FROM ' + @Union_IISServerDim + ' as IISServerDim' exec (@CreateSQL_IISServerDim) end if @Union_IISServerFact != '()' begin select @CreateSQL_IISServerFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_IISServerFact] AS SELECT IISServerResourceGuid , IISServerID , IISWebSiteID , IISVirtualDirectoryID FROM ' + @Union_IISServerFact + ' as IISServerFact' exec (@CreateSQL_IISServerFact) end if @Union_IISVirtualDirectoryDim != '()' begin select @CreateSQL_IISVirtualDirectoryDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_IISVirtualDirectoryDim] AS SELECT IISVirtualDirectoryID , [IIS Virtual Directory] , [Element Name] , [Content Location Path] , [Content Location] , [Application Name] , [Default Document Enabled] , [Defaul Document Name] , [Anonymous Authentication Enabled] , [Integrated Windows Authentication Enabled] , [Basic Authentication Enabled] , [Digest Authentication Enabled] , [Session State Enabled] , [Session Timeout] , [Script Source Access Enabled] , [Access Read Enabled] , [Access Write Enabled] , [Directory Browsing Enabled] , [Log Enabled] , [Execute Permission] , [Application Pool] , [DotNet Passport Authentication Enabled] , [SSL Access Enabled] , [Content Expiration Enabled] , [Content Expiration Setting] FROM ' + @Union_IISVirtualDirectoryDim + ' as IISVirtualDirectoryDim' exec (@CreateSQL_IISVirtualDirectoryDim) end if @Union_IISWebSiteDim != '()' begin select @CreateSQL_IISWebSiteDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_IISWebSiteDim] AS SELECT IISWebSiteID , [Instance ID] , [Web Site Name] , [Process Throttling enabled] , [Max CPU Use Percenatage] , [Enforce Process Throttling] , [Content Location] , [Content Expiration Enabled] , [Content Expiration Setting] , [Application Name] , [Default Document Enabled] , [Defaul Document Name] , [Anonymous Authentication Enabled] , [Integrated Windows Authentication Enabled] , [Basic Authentication Enabled] , [Digest Authentication Enabled] , [Certificate Enabled] , [Session State Enabled] , [Session Timeout] , [Script Source Access Enabled] , [Access Read Enabled] , [AccessWrite Enabled] , [Directory Browsing Enabled] , [Log Enabled] , [Execute Permission] , [Application Pool] , [DotNet Passport Authentication Enabled] , [Buffering Enabled] , [Parents Path Enabled] , [Bandwidth Throttling Enabled] , [Bandwidth Throttling Limit] , [Connection Limit] FROM ' + @Union_IISWebSiteDim + ' as IISWebSiteDim' exec (@CreateSQL_IISWebSiteDim) end if @Union_SQLClusterDim != '()' begin select @CreateSQL_SQLClusterDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_SQLClusterDim] AS SELECT SQLClusterID , [Cluster Name] , Description , [IP Address] , [Default Network Role] , [Max Number of Nodes] , [Total Number of Nodes] FROM ' + @Union_SQLClusterDim + ' as SQLClusterDim' exec (@CreateSQL_SQLClusterDim) end if @Union_SQLClusterFact != '()' begin select @CreateSQL_SQLClusterFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_SQLClusterFact] AS SELECT SQLDatabaseSystemID , SQLClusterResourceID , SQLClusterID , ResourceGuid FROM ' + @Union_SQLClusterFact + ' as SQLClusterFact' exec (@CreateSQL_SQLClusterFact) end if @Union_SQLClusterResourceDim != '()' begin select @CreateSQL_SQLClusterResourceDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_SQLClusterResourceDim] AS SELECT SQLClusterResourceID , [SQL Server Resource Name] , [Virtual Server Name] , [Instance Name] , [IP Address] , [Owner Node] FROM ' + @Union_SQLClusterResourceDim + ' as SQLClusterResourceDim' exec (@CreateSQL_SQLClusterResourceDim) end if @Union_SQLDatabaseDim != '()' begin select @CreateSQL_SQLDatabaseDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_SQLDatabaseDim] AS SELECT SQLDatabaseID , ResourceGuid , Name , [Size Allocated MB] , Language , [Instance Name] , [Space Available MB] , [DB Owner] , [Number Of Users] , [Automatically Grow File] , [Data File Growth Mode] , [Data File Growth Size MB] , [Log File Path] , [Log File Size MB] FROM ' + @Union_SQLDatabaseDim + ' as SQLDatabaseDim' exec (@CreateSQL_SQLDatabaseDim) end if @Union_SQLDatabaseFact != '()' begin select @CreateSQL_SQLDatabaseFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_SQLDatabaseFact] AS SELECT SQLDatabaseID , SQLStorageAreaID , SQLDatabaseSystemID , ResourceGuid , [Created Date] FROM ' + @Union_SQLDatabaseFact + ' as SQLDatabaseFact' exec (@CreateSQL_SQLDatabaseFact) end if @Union_SQLDatabaseSystemDim != '()' begin select @CreateSQL_SQLDatabaseSystemDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_SQLDatabaseSystemDim] AS SELECT SQLDatabaseSystemID , ResourceGuid , Name , Vendor , Version , Path , Processors , [Threads Allocated] , [Current License In Use] , [License Type] , [Number of License] , [License Code] , Service FROM ' + @Union_SQLDatabaseSystemDim + ' as SQLDatabaseSystemDim' exec (@CreateSQL_SQLDatabaseSystemDim) end if @Union_SQLStorageAreaDim != '()' begin select @CreateSQL_SQLStorageAreaDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_SQLStorageAreaDim] AS SELECT SQLStorageAreaID , Name , [File System Type] , [File System Size GB] FROM ' + @Union_SQLStorageAreaDim + ' as SQLStorageAreaDim' exec (@CreateSQL_SQLStorageAreaDim) end if @Union_SQLUserDim != '()' begin select @CreateSQL_SQLUserDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_SQLUserDim] AS SELECT SQLUserID , Name FROM ' + @Union_SQLUserDim + ' as SQLUserDim' exec (@CreateSQL_SQLUserDim) end if @Union_SQLUserFact != '()' begin select @CreateSQL_SQLUserFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_SQLUserFact] AS SELECT SQLDatabaseID , ResourceGuid , SQLUserID FROM ' + @Union_SQLUserFact + ' as SQLUserFact' exec (@CreateSQL_SQLUserFact) end end begin -- Task DSV if @Union_TaskDim != '()' begin select @CreateSQL_TaskDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_TaskDim] AS SELECT TaskInstanceGuid , [Task Name] , ReturnCode , Success FROM ' + @Union_TaskDim + ' as TaskDim' exec (@CreateSQL_TaskDim) end if @Union_TaskInstancesFact != '()' begin select @CreateSQL_TaskInstancesFact = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_TaskInstancesFact] AS SELECT EventDate , EventTime , TaskStartDate , TaskStartTime , TaskEndDate , TaskEndTime , ResourceID , ComputerGuid , TaskServerGuid , TaskInstanceGuid FROM ' + @Union_TaskInstancesFact + ' as TaskInstancesFact' exec (@CreateSQL_TaskInstancesFact) end if @Union_TaskServerDim != '()' begin select @CreateSQL_TaskServerDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_TaskServerDim] AS SELECT Guid , [Name] , [Domain] , [User] , [OS Name] , [OS Version] , [IP Address] , [Server] , [OS Primary Language] , [OS Sub Language] , [MAC Address] , [System Type] , IsManaged , IsLocal , CreatedDate FROM ' + @Union_TaskServerDim + ' as TaskServerDim' exec (@CreateSQL_TaskServerDim) end if @Union_ParentTaskInstancesDim != '()' begin select @CreateSQL_ParentTaskInstancesDim = 'CREATE VIEW [dbo].[vITAnalytics_CMDB_ParentTaskInstancesDim] AS SELECT TaskInstanceGuid , [Parent Task/Policy] , ParentInstanceType FROM ' + @Union_ParentTaskInstancesDim + ' as ParentTaskInstancesDim' exec (@CreateSQL_ParentTaskInstancesDim) end end end set @DropSql = 'IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[spITAnalytics_CMDB_GetFullyQualifiedDataSource]'')) DROP PROC [dbo].[spITAnalytics_CMDB_GetFullyQualifiedDataSource]' exec (@DropSql) if exists ( select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#DependencyMap') ) DROP TABLE #DependencyMap; end end