Computers exist as both a Physical and Virtual Machine

Products

• IT Management Suite

Issue/Introduction

Computers are appearing as duplicated in reports, fail to merge or other similar issues.

Environment

ITMS 7.x, 8.x

Cause

At some point the same computer was created as a Physical resource then later as a Virtual Machine or vica versa.

Resolution

Use the sql below to create a report that can be used to delete the resource that is not actually being updated.

select r.Guid as _ItemGuid, r.Name,case when r.ResourceTypeGuid = '493435F7-3B17-4C4C-B07F-C23E7AB7781F' then 'Physical Computer' else 'Virtual Machine' end as 'Computer type', cast (max (ccr._eventTime) as nvarchar (111)) as 'Last Config request'
from vRM_Computer_Item r
left join Evt_NS_Client_Config_Request ccr on ccr.ResourceGuid = r.Guid
where r.Guid in (
    select ResourceGuid
    from ResourceKey where KeyValue in (
        select distinct KeyValue
        from ResourceKey rk
        join vRM_Computer c on c.Guid = rk.ResourceGuid
        group by KeyValue
        having COUNT (distinct ResourceGuid
        ) > 1))
group by r.Guid, r.Name, r.ResourceTypeGuid
order by 1, 2 desc