Hardware Reconciliation Reports

Products

• CA Service Management - Asset Portfolio Management
• ASSET PORTFOLIO MGMT- SERVER
• CA IT Asset Manager
• CA IT Asset Manager Asset Portfolio Management

Issue/Introduction

Are there any database views or queries that can be leveraged for ITAM Hardware Reconciliation reporting?

Environment

CA Asset Portfolio Management/CA IT Asset Manager 17.3 and higher

SQL MDB

Resolution

The following two (2) database queries can be used:

1. Assets that have been discovered via SAM, but do not have a matching Owned asset already in APM /ITAM

SELECT * FROM al_discovered_hardware aldh WHERE NOT EXISTS( SELECT owned.resource_name FROM ca_owned_resource owned WHERE aldh.host_name= owned.resource_name AND owned.inactive=0 )

2. Owned assets within APM/ITAM that have not been discovered or perhaps even stale devices

SELECT * FROM ca_owned_resource owned WHERE owned.inactive=0 AND ( reconciliation_date IS NULL OR NOT EXISTS( SELECT NULL FROM al_discovered_hardware aldh WHERE aldh.host_name= owned.resource_name ))

Additional Information

Note: One must use the condition based on the reconciliation rule's matching criteria in the inner select.  For example, inventory's asset name will be matched to discovery's host_name