HOW TO - Summary Report - Cireson Hardware Assets

Conner_Wood

USE [ServiceManager];

WITH EnumText AS
(
   SELECT DISTINCT Enum.EnumTypeId, Enum.EnumTypeName, LT.LTValue AS 'EnumText' 
		FROM [dbo].[EnumType] Enum (nolock)
		JOIN [dbo].[LocalizedText] LT (nolock)
		ON LT.[LanguageCode] = 'ENU'
		AND LT.[LTStringType] = 1 -- 1 is localized DisplayName for the element, 2 is Description
		AND LT.[LTStringId] = Enum.[EnumTypeId]
)
SELECT replace(replace(replace(ISNULL(HW_Asset.[DisplayName],'? ' + CAST(HW_Asset.[BaseManagedEntityId]AS nvarchar(100))),char(10),''),char(13),''),char(9),'') AS 'Hardware Asset'
      ,replace(replace(replace(ISNULL(AssetPrimaryUser.[DisplayName], ''), char(10), ''), char(13), ''), char(9), '') AS 'Primary User'
	  ,replace(replace(replace(ISNULL(CatalogItem.[DisplayName], ''), char(10), ''), char(13), ''), char(9), '') AS 'Catalog Item'
	  ,replace(replace(replace(ISNULL(CostCenter.[DisplayName], ''), char(10), ''), char(13), ''), char(9), '') AS 'Cost Center'
	  ,replace(replace(replace(ISNULL(CostCenter.[CostCenterOrg], ''), char(10), ''), char(13), ''), char(9), '') AS 'Cost Center Org'
      ,replace(replace(replace(ISNULL(HW_Asset.[Description_5B632FAF_E192_B752_6919_31AB42E90F4C], ''), char(10), ''), char(13), ''), char(9), '') AS 'Description'
	  ,replace(replace(replace(ISNULL(ET_Manufacturer.[EnumText], ''), char(10), ''), char(13), ''), char(9), '') AS 'Manufacturer'
	  ,replace(replace(replace(ISNULL(ET_Model.[EnumText], ''), char(10), ''), char(13), ''), char(9), '') AS 'Model'
	  ,replace(replace(replace(ISNULL(HW_Asset.[SerialNumber_C8CF2E89_7A83_1C26_0AD0_887DF9140D5A], ''), char(10), ''), char(13), ''), char(9), '') AS 'SerialNumber'
	  ,replace(replace(replace(ISNULL(ET_HWAssetStatus.[EnumText], ''), char(10), ''), char(13), ''), char(9), '') AS 'HW Asset Status'
	  ,replace(replace(replace(ISNULL(ET_HWAssetType.[EnumText], ''), char(10), ''), char(13), ''), char(9), '') AS 'HW Asset Type'
	  ,replace(replace(replace(ISNULL(AssetLocation.[DisplayName], ''), char(10), ''), char(13), ''), char(9), '') AS 'Location'
	  ,replace(replace(replace(ISNULL(HW_Asset.[LocationDetails_0B39A057_2BE8_11B2_BBE2_1E03564AA5CA], ''), char(10), ''), char(13), ''), char(9), '') AS 'Location Details'
  FROM [dbo].[MTV_Cireson$AssetManagement$HardwareAsset] HW_Asset (nolock)

  --Catalog Item
  LEFT JOIN 
  (
        SELECT R.SourceEntityId AS 'BaseManagedEntityId', Item.DisplayName
        FROM [dbo].[Relationship] R (nolock)
        JOIN [dbo].[RelationshipType] rType (nolock)
        ON rType.RelationshipTypeId = R.RelationshipTypeId
        JOIN [dbo].[BaseManagedEntity] Item (nolock)
        ON Item.BaseManagedEntityId = R.TargetEntityId
        WHERE rType.RelationshipTypeName = 'Cireson.AssetManagement.HardwareAssetHasCatalogItem' AND R.IsDeleted = 0
  ) AS CatalogItem ON CatalogItem.[BaseManagedEntityId] = HW_Asset.[BaseManagedEntityId]

  --Cost Center
    LEFT JOIN 
	(       
        SELECT R.SourceEntityId AS 'BaseManagedEntityId', Item.DisplayName, CostCenterOrg.[DisplayName] AS 'CostCenterOrg'
        FROM [dbo].[Relationship] R (nolock)
        JOIN [dbo].[RelationshipType] rType (nolock)
        ON rType.RelationshipTypeId = R.RelationshipTypeId
        JOIN [dbo].[BaseManagedEntity] Item (nolock)
        ON Item.BaseManagedEntityId = R.TargetEntityId

		--Get Cost Center Organization
		LEFT JOIN
		(
			SELECT R.SourceEntityId AS 'BaseManagedEntityId', Item.DisplayName
			FROM [dbo].[Relationship] R (nolock)
			JOIN [dbo].[RelationshipType] rType (nolock)
			ON rType.RelationshipTypeId = R.RelationshipTypeId
			JOIN [dbo].[BaseManagedEntity] Item (nolock)
			ON Item.BaseManagedEntityId = R.TargetEntityId
			WHERE rType.RelationshipTypeName = 'Cireson.AssetManagement.CostCenterHasOrganization' AND R.IsDeleted = 0
		) AS CostCenterOrg ON CostCenterOrg.[BaseManagedEntityId] = Item.[BaseManagedEntityId]

        WHERE rType.RelationshipTypeName = 'Cireson.AssetManagement.HardwareAssetHasCostCenter' AND R.IsDeleted = 0
	) AS CostCenter ON CostCenter.[BaseManagedEntityId] = HW_Asset.[BaseManagedEntityId]

	--Primary User
	LEFT JOIN
	(
        SELECT R.SourceEntityId AS 'BaseManagedEntityId', Item.DisplayName
        FROM [dbo].[Relationship] R (nolock)
        JOIN [dbo].[RelationshipType] rType (nolock)
        ON rType.RelationshipTypeId = R.RelationshipTypeId
        JOIN [dbo].[BaseManagedEntity] Item (nolock)
        ON Item.BaseManagedEntityId = R.TargetEntityId
        WHERE rType.RelationshipTypeName = 'Cireson.AssetManagement.HardwareAssetHasPrimaryUser' AND R.IsDeleted = 0
	) AS AssetPrimaryUser ON AssetPrimaryUser.[BaseManagedEntityId] = HW_Asset.[BaseManagedEntityId]

	--Manufacturer
	LEFT JOIN EnumText ET_Manufacturer ON
    ET_Manufacturer.[EnumTypeId] = HW_Asset.[ManufacturerEnum_E7E77D57_6855_918F_CAD2_89F1BAD2FF08]

	--Model
	LEFT JOIN EnumText ET_Model ON
    ET_Model.[EnumTypeId] = HW_Asset.[ModelEnum_1A5CE615_58C0_B30A_535E_D6FAD298AD50]

	--HW Asset Status
	LEFT JOIN EnumText ET_HWAssetStatus ON
    ET_HWAssetStatus.[EnumTypeId] = HW_Asset.[HardwareAssetStatus_3019ADDF_4F3D_2C55_2024_72C22E11F4CF]

	--HW Asset Type
	LEFT JOIN EnumText ET_HWAssetType ON
    ET_HWAssetType.[EnumTypeId] = HW_Asset.[HardwareAssetType_07E9CFB0_049C_855E_D6CA_135808C8B964]

	--HW Asset Location
	LEFT JOIN
	(
        SELECT R.SourceEntityId AS 'BaseManagedEntityId', Item.DisplayName
        FROM [dbo].[Relationship] R (nolock)
        JOIN [dbo].[RelationshipType] rType (nolock)
        ON rType.RelationshipTypeId = R.RelationshipTypeId
        JOIN [dbo].[BaseManagedEntity] Item (nolock)
        ON Item.BaseManagedEntityId = R.TargetEntityId
        WHERE rType.RelationshipTypeName = 'Cireson.AssetManagement.HardwareAssetHasLocation' AND R.IsDeleted = 0
	) AS AssetLocation ON AssetLocation.[BaseManagedEntityId] = HW_Asset.[BaseManagedEntityId]

  WHERE HW_Asset.[DisplayName] LIKE '%Server%'