IT Analytics-CMDB [Computers Count] value is incorrect

book

Article ID: 169624

calendar_today

Updated On:

Products

IT Analytics

Cause

"Computers" cube, "Computers Count" measure. The cube joins vITAnalytics_CMDB_ComputerDim & vITAnalytics_CMDB_ComputerFact where the later contains duplicates as a result of [Discovery Date] multiple entries (Normal as a resource discovery is recorded on each discovery)

Environment

ITA Analytics CMDB 8.0 / 8.1

Resolution

Modify ITA view [vITAnalytics_CMDB_ComputerFact] with below SQL query:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[vITAnalytics_CMDB_ComputerFact] AS
SELECT
ResourceGuid
, CreatedDate
, [Last Basic Inventory Date]
, [Discovery Date]
FROM (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
vComputerResource AS r WITH (NOLOCK) INNER JOIN
(SELECT
Guid, max(CreatedDate) CreatedDate
FROM
vRM_Computer_Item AS item WITH (NOLOCK)
group by Guid) AS item ON r.Guid = item.Guid INNER JOIN
Inv_AeX_AC_Identification AS ACID WITH (NOLOCK) ON ACID._ResourceGuid = r.Guid LEFT OUTER JOIN
--Inv_AeX_AC_Discovery AS discovery WITH (NOLOCK) ON discovery._ResourceGuid = r.Guid
(SELECT _ResourceGuid, max(DiscoveryDate) [DiscoveryDate] FROM Inv_AeX_AC_Discovery GROUP BY _ResourceGuid) AS discovery ON discovery._ResourceGuid = r.Guid
) AS ComputerFact
GO