How to find and delete computers that have not reported Inventory in days
Article ID: 180818
Updated On:
Products
Issue/Introduction
How can I delete computers from my SMP Server database based on the criteria of not receiving Inventory in a certain amount of time?
Environment
ITMS 7.x, 8.x
Resolution
The following query is based in the Latest Inventory Date contained in the database for all the computers. By default, this query will return computers that have not sent Inventory information for the last 30 days. If you want to change the default value of deleting computers that have not reported inventory in 30 days, locate the following line:
set @days = 30
Change 30 to the amount of days that you want to delete (for example 45).
You can you this query to create your own report or filter and use the right-click>delete option after selecting the machines that you desire to delete.
declare @days int
set @days = 30
select cr.[Guid], dt.[LatestInventoryDate]
from [vComputerResource] cr
inner join (
select [ResourceGuid], max([ModifiedDate]) as LatestInventoryDate
from dbo.ResourceUpdateSummary
group by [ResourceGuid]
) as dt on cr.[Guid] = dt.[ResourceGuid]
inner join vResourceEx rx on rx.[Guid] = cr.[Guid]
where rx.[IsManaged] = 1
and dt.[LatestInventoryDate] < dateadd(dd, @days * -1, getdate())
order by LatestInventoryDate desc
Feedback
