SQL report to list basic inventory received date of all managed clients

Products

• IT Management Suite

Issue/Introduction

 You need a way to list 'basic inventory received date' of all managed client machines.

Environment

ITMS 7.x, 8.x

Resolution

You can use the following SQL code to get a report to list 'basic inventory received date' of all managed clients

The report might be required in the following scenarios, to make sure;

1. All clients are successfully submitting basic inventory
2. In a large & distributed network all clients are maintaining connection with SMP.
3. SMP agent service is Active & in a healthy state i.e. agent service crash issue

 

Copy the following code in SQL code section while creating a new report.

1. SMP Console > Reports > all reports
2. Reports > Right Click > New > Report > SQL report

 

--- SQL to get basic inventory report

select vcx.Name, vcx.Domain, vcx.[IP Address], vcx.[User], vcx.[OS Name],
rus.ModifiedDate as 'Basic.Inventory.Received'
from ResourceUpdateSummary rus
inner join vComputerEx vcx on vcx.Guid = rus.ResourceGuid
where rus.InventoryClassGuid = 'C74002B6-C7B9-47BB-A5D6-3031AF73BB8D' --AeX AC Identification
and vcx.IsManaged = 1
order by rus.ModifiedDate

-- end of report.

 Example results when ran directly from SQL Management Studio: