Service Manager - CMDB reports - When downloading a report - can the used by field also provide the employee number

[Jake Thaker]

Good morning,

We create asset reports from CMDB that we download as csv. To do audits on assets, we also have the "used by" on the csv file.  This provides the name of the user of the asset but does not provide the persons Employee ID.

The problem arises when there are 2 or more people with the same name but different Employee ID - would it be possible to to make the csv download to also contain the asset user's employee id. This would really help when doing audits of assets especially when you have a number of employees with the same name like e.g. John Smith.

[Steve Giller]

You would need to JOIN the h_sys_accounts table, however the used by value is stored as a URN rather than a User Id, so there would be some manipulation required.

This deals with different tables but the method should be transferrable.

[Jake Thaker]

Thanks @Steve Giller

I was hoping there would be a way of extracting the employee ID from the CMDB by creating a view in Asset Management and download the view created.  

Is there any way this can be a development instead of creating reports? Not every user on our Service Manager Instance has access to create reports, so when we do asset audits, we download for example laptop report by creating a view in Asset management then downloading. We get to select the "used by" field in that report but it does not provide their employee ID. 

When you are adding the user to an asset in Used by field,  it does display their name and with their employee ID. Shame it does not provide the same info in the downloaded csv.  

Not sure if this can be considered as a development. 

[Steve Giller]

The Edit Asset form is a complex form for a single asset and is effectively doing the JOIN I mention above (along with a number of others) however this is for a single Asset.

The Asset View is by design much simpler, as to perform multiple complex joins on potentially tens of thousands of Assets would introduce unmanageable performance issues.

There is no requirement for all of your Users to create Reports, once created they can be run on a schedule (or ad-hoc if required, although I would expect audits to be fairly regular) and the results downloaded or emailed to any User who requires them.

[Jake Thaker]

Apologies for a late response. Thank you so much for all your help @Steve Giller I have now requested out performance and reporting team to compile this report so that a report can be run as and when we wish to perform an audit.