Information on purge settings and projected purge date of each computer

Products

• IT Management Suite

Issue/Introduction

Is there a way to tell if your client machines belongs to active or disabled purge settings, and if any of these machines will be purged and if so when will this occur?

Environment

ITMS 8.x

Resolution

Use the following query to pull the current purge settings assigned to computer, if the computer is managed, along with the projected purge dates.

An example of what would be returned from the query below would look like this:

--Sample query. NOTE: This is provided as-is and there is No Support to help with updating or modifying this query.

declare @retire int = 0
declare @delete int = 0
declare @purgeMethod nvarchar(15)
declare @retireDuration int = 0
declare @retireUnit nvarchar(5) = 0
declare @deleteDuration int = 0
declare @deleteUnit nvarchar(5) = 0
declare @totalDaysRetire int = 0
declare @totalDaysDelete int = 0

if exists (
select cast(state as xml).value('(item/purgeComputers/@enabled)[1]','nvarchar(10)' ) 
 from Item 

 where cast(state as xml).value('(item/purgeComputers/@enabled)[1]','nvarchar(10)' ) = 'True'
  and Guid = 'de4a3a7c-2147-463e-8a06-a23b6c6e719b'
)
BEGIN

  if exists  -- Detect if Purge set to retire computers is enabled and value it is set to
  (
   select cast(state as xml).value('(item/purgeComputers/retire/enabled)[1]','nvarchar(10)' )
   from Item 
   where cast(state as xml).value('(item/purgeComputers/retire/enabled)[1]','nvarchar(10)' ) = 'True'  and Guid = 'de4a3a7c-2147-463e-8a06-a23b6c6e719b'
   )
    begin
      set @retire = 1
      set @retireDuration = (select cast(state as xml).value('(item/purgeComputers/retire/duration)[1]','int' ) from Item where Guid = 'de4a3a7c-2147-463e-8a06-a23b6c6e719b')
      set @retireUnit = (select cast(state as xml).value('(item/purgeComputers/retire/unit)[1]','nvarchar(5)' ) from Item where Guid = 'de4a3a7c-2147-463e-8a06-a23b6c6e719b')
    end

  if exists -- Detect if Purge set to delete computers is enabled and value it is set to
   (
   select cast(state as xml).value('(item/purgeComputers/delete/enabled)[1]','nvarchar(10)' )
   from Item 
   where cast(state as xml).value('(item/purgeComputers/delete/enabled)[1]','nvarchar(10)' ) = 'True'  and Guid = 'de4a3a7c-2147-463e-8a06-a23b6c6e719b'
   )
    begin
      set @delete = 1
      set @deleteduration = (select cast(state as xml).value('(item/purgeComputers/delete/duration)[1]','int' ) from Item where Guid = 'de4a3a7c-2147-463e-8a06-a23b6c6e719b')
      set @deleteUnit = (select cast(state as xml).value('(item/purgeComputers/delete/unit)[1]','nvarchar(5)' ) from Item where Guid = 'de4a3a7c-2147-463e-8a06-a23b6c6e719b')

    end 

if (@retire = 1 and @delete = 1)
    begin
          set @purgeMethod = 'Retire & Delete' 
          if @deleteUnit = 'Years' set @totalDaysDelete = (@deleteDuration * 365)
          if @deleteUnit = 'Month' set @totalDaysDelete = (@deleteDuration * 30)
          if @deleteUnit = 'Weeks' set @totalDaysDelete = (@deleteDuration * 7)
          if @deleteUnit = 'Days' set @totalDaysDelete = @deleteDuration

          if @retireUnit = 'Years' set @totalDaysRetire = (@retireDuration * 365)
          if @retireUnit = 'Month' set @totalDaysRetire = (@retireDuration * 30)
          if @retireUnit = 'Weeks' set @totalDaysRetire = (@retireDuration * 7)
          if @retireUnit = 'Days' set @totalDaysRetire =  @retireDuration
          
    end 

if @delete = 1 and @retire = 0
    begin
        set @purgeMethod = 'Delete'
        if @deleteUnit = 'Years' set @totalDaysDelete = (@deleteDuration * 365)
        if @deleteUnit = 'Month' set @totalDaysDelete = (@deleteDuration * 30)
        if @deleteUnit = 'Weeks' set @totalDaysDelete = (@deleteDuration * 7)
        if @deleteUnit = 'Days' set @totalDaysDelete = @deleteDuration

    end

if @retire = 1 and @delete = 0
   begin
          set @purgeMethod = 'Retire'
          if @retireUnit = 'Years' set @totalDaysRetire = (@retireDuration * 365)
          if @retireUnit = 'Month' set @totalDaysRetire = (@retireDuration * 30)
          if @retireUnit = 'Weeks' set @totalDaysRetire = (@retireDuration * 7)
          if @retireUnit = 'Days' set @totalDaysRetire = @retireDuration
   end

END 
 else set @purgeMethod = 'Purging Disabled'

select 
 c.Name, 
 cid.Domain, 
 ip.[IP Address], 
 cid.[Client Date], 
 isnull(cast(rus.ModifiedDate as nvarchar(24)), 'no record') as ModifiedDate,
 c.IsManaged,
 @purgeMethod as [Computer Purge Setting],
 case when (rus.ModifiedDate is null or c.IsManaged = 0) then 'Never'
      when @delete = 0 then ''
      when @delete = 1 then 'Delete on '+cast((rus.ModifiedDate + @totalDaysDelete) as nvarchar(25))

   end as [Projected Delete Date],

 case when (rus.ModifiedDate is null or c.IsManaged = 0) then 'Never'
      when @retire = 0 then ''
       when @retire = 1 then 'Retire on '+cast((rus.ModifiedDate + @totalDaysRetire) as nvarchar(25))
   end as [Projected Retire Date]

from vRM_Computer_Item c
left join vTcpIpAddress ip on ip._ResourceGuid = c.Guid
join Inv_AeX_AC_Identification cid on cid._ResourceGuid = c.Guid
left join ResourceUpdateSummary rus on rus.ResourceGuid = c.Guid and rus.InventoryClassGuid = '9E6F402A-6A45-4CBA-9299-C2323F73A506'
-- where rus.ResourceGuid is null
order by c.Name