Query the Asset Audit DB to show all assets currently and previously used by an individual

[Frank Reay]

We have an audit requirement to show all assets used by any particular individual. For the current users of any asset that is easy - search by the Used By field :-)

However we also need to show any assets previously used by an individual. We do sort of hold this info in Requests (if the change came through that route) but it is not very easy to do. So I think there are 2 solutions:

1. Raise an enhancement for a Previous User field to be added in Asset Management. It would need to take multiple users including Archived as well as Active users. I don't like this solution as it relies on people entering the info.
2. Run a query against the Audit DB. Is this held in h_sys_audit_trail?? I don't even know where to start with that. Has anybody had any experience of this?

[Steve Giller]

If you try this for a starting point, it should give the Date and Time of the Change, The "From" and "To" value of the Owner, and the User Id making the change.

The Report takes a valid Asset Id as an input.

asset-owner-updates.report.txt

[Met]

Hi @Frank Reay

If you want to take someone's name and then search for all assets they've used then it is a little more complicated. I haven't tried to do this in the normal Reports, but something like this in Database Direct might work (I would test it first). You'll need to replace userid with the individual's userid. This looks at h_used_by but you can change it to look at h_owned_by.

You can probably join the h_cmdb_asset table to the h_sys_audit_trail table instead, but if an asset has been deleted or its ID has changed then you won't pull through all the results. This looks at the h_sys_audit_trail table to first find all the asset IDs that the user has been assigned, and then again to pull the name of the asset from the same table.

You might want to edit this to only include certain asset types too.

SELECT 
  DISTINCT asset_name.h_new_value as 'Asset Name' 
FROM
  (
    SELECT 
      h_id as id 
    FROM 
      h_sys_audit_trail 
    WHERE 
      h_column = 'h_used_by' 
      AND (
        h_action_type = 'Insert' 
        OR h_action_type = 'Update'
      ) 
      AND h_new_value like '%userid%'
  ) asset_id 
  JOIN h_sys_audit_trail asset_name ON asset_id.id = asset_name.h_id 
WHERE
  asset_name.h_column = 'h_name'

 

There is probably a simpler way to achieve this out side of DD.

 

Thanks

 

Met.