How to implement a simple computer move tracker in the database for tracking computer moves between domains.

Document ID : KB000016340
Last Modified Date : 02/03/2018
Show Technical Document Details
Introduction:

In an Enterprise environment with multiple Domain Managers, computers moves between domains may be initiated via a number of methods.  As such it may be necessary for reporting, tracking or troubleshooting purposes to review the history of computer moves between domains.  This document describes a database customization for Microsoft SQL, that introduces a new database table for tracking computer moves.

Question:

How to implement a simple computer move tracker in the database for tracking computer moves between domains.

Environment:
Client Automation (ITCM) -- all versions. This customization applies only to the Microsoft SQL database, though may be translated to Oracle.
Answer:

The following script will create a new database table called, usd_move_history.  The table is populated by adding a trigger to the dbo.usd_target table, which will track new inserts or updates that act on the column controlling the software delivery lock status of the computer.  Additionally, the trigger is coded to remove any move history older than 30 days, in order to ensure the new table does not forever populate with move history:

------------------------------
-- Create dbo.usd_move_history table
if not exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='dbo' and TABLE_NAME = 'usd_move_history')
begin
  create table dbo.usd_move_history (
  object_uuid binary(16) NOT NULL,
  agent_name nchar(255) NOT NULL,
  from_manager nchar(255) NULL,
  move_date_human nchar(255) NULL,
  move_date_sql int NULL)
end
go

-- Grant ca_itrm permissions on the table
grant select,update,insert,delete on dbo.usd_move_history to ca_itrm
go


-- Add trigger to usd_target
create trigger dbo.usd_trg_iu_move_history
on dbo.usd_target
after update as
if update(locks)
  begin
    insert into usd_move_history(object_uuid, agent_name, from_manager, move_date_human, move_date_sql)
    select i.objectid,
    agt.agent_name,
    agt.prev_manager,
    dateadd(s, datediff(s, getutcdate(), getdate()), dateadd(s, agt.last_run_date, '19700101')),
    agt.last_run_date
    from inserted i
    inner join ca_agent agt with(nolock) on i.objectid=agt.object_uuid and agt.agent_type=1
    where agt.derived_status_sd = 2
    and not exists (
      select *
      from usd_move_history
      where object_uuid=i.objectid 
      and (move_date_sql >= agt.last_run_date-600))
end
go

delete from usd_move_history where move_date_sql < DATEDIFF(s, '19700101', GETUTCDATE()) - 2592000
go

-- Enable trigger
alter table dbo.usd_target enable trigger usd_trg_iu_move_history
go

------------------------------

Once committed to SQL, you can run "select * from usd_move_history" to view the contents of the table.

Additional Information:

The following script will reverse the customization, and remove the changes made to the MDB:

------------------------------
-- Disable trigger
alter table dbo.usd_target disable trigger usd_trg_iu_move_history
go

-- Drop trigger
drop trigger dbo.usd_trg_iu_move_history
go

-- Drop table
drop table dbo.usd_move_history
go

------------------------------