Help requested: I would like a report for the Asset history of a User

[Berto2002]

We've been asked to provide a report on what devices (assets) a given user had in the past. This is information held as a history item in some assets but it's finding those assets that's hard.

Firstly, can we find this information through the UI? I.e. we can see user X had laptop1 then laptop2?

Or if not, what tables and joins would I need to drill into the asset history to draw-up the asset names/IDs where the Used By was previously User X?

Thanks for any pointers.

[Met]

On 9/21/2023 at 3:24 PM, Berto2002 said:

We've been asked to provide a report on what devices (assets) a given user had in the past. This is information held as a history item in some assets but it's finding those assets that's hard.

Firstly, can we find this information through the UI? I.e. we can see user X had laptop1 then laptop2?

Or if not, what tables and joins would I need to drill into the asset history to draw-up the asset names/IDs where the Used By was previously User X?

Thanks for any pointers.

Try joining h_sys_audit_trail (ID) on h_cmdb_assets (Asset ID).

Then need to add filters:

h_sys_audit_trail Table = h_cmdb_assets

h_sys_audit_trail New Value = <user input>

h_sys_audit_trail Action Type = Insert or Update

h_sys_audit_trail Column = h_used_by (and use URN for user input), alternatively use h_used_by_name (and use their name for user input - but might include wrong assets for someone with the same name). Or also use h_sys_accounts and set up a join where it builds the URN, and the filter is on h_sys_accounts account name instead.

Should give you a starting point.

[Berto2002]

@Met thank you this helps. I got a report started. I have an anomaly though. When I initially ran it, I had 8 rows. I then found an old asset I used to have and re-assigned it back to myself and two rows appeared. One was the new Update row and one was the original Insert row which had not previously been listed. In other words, it only displayed the Insert row after an Update row was added. In other words, the report could be missing Insert Data. I do note that there is a row for an Asset which is Insert only so this is not a cut and dry situation. Just asking if you can see a reason for this in the structure.

No Adobe InDesign showing at first:

Then two rows appear and the Insert row is the date the record was created with me as Used By but which was not previously showing:

I have another example which I have not yet edited. This Adobe Photoshop asset is not showing in the report but it was created with me as Used By (2022-06-16 10:51:32)

These are my filters:

I am using my Name as the search although each entry does also match with the same URN also.

A mystery?

[Met]

Just now, Berto2002 said:

@Met thank you this helps. I got a report started. I have an anomaly though. When I initially ran it, I had 8 rows. I then found an old asset I used to have and re-assigned it back to myself and two rows appeared. One was the new Update row and one was the original Insert row which had not previously been listed. In other words, it only displayed the Insert row after an Update row was added. In other words, the report could be missing Insert Data. I do note that there is a row for an Asset which is Insert only so this is not a cut and dry situation. Just asking if you can see a reason for this in the structure.

No Adobe InDesign showing at first:

Then two rows appear and the Insert row is the date the record was created with me as Used By but which was not previously showing:

I have another example which I have not yet edited. This Adobe Photoshop asset is not showing in the report but it was created with me as Used By (2022-06-16 10:51:32)

These are my filters:

I am using my Name as the search although each entry does also match with the same URN also.

A mystery?

@Berto2002 I would remove the CMDB Assets -> Used By Name filter. This will only return assets that are currently used by the user, not historical ones where someone else now uses it or nobody uses it. 

[Berto2002]

Perfect. Obvious when I think about it... Thanks!

asset-history-for-selected-user-variable-to-enter.report.txt