Linking Asset to h_sys_accounts

[Claire B]

Hi,

Apologises if this is already covered elsewhere within the forums but I cannot find what I am looking for.

I'm trying to produce a report linking CMDB Asset (and CMDB Computer) tables to h_sys_accounts to allow me to display a list of laptops (for example) with users allocated, and using h_sys_accounts to filter the group of users from a specific department or area.

I've tried using CMDB Users to link the CMDB Asset and h_sys_accounts together but it would not work.

Hope someone can help.

Thank you :-)

[James Ainsworth]

Hi @Claire B

The table h_cmdb_assets is the main asset table.  This table also contains the user that uses that asset which is held in the h_used_by and h_used_by_name columns.

Assets can also be shared with multiple users.  Information about the shared users can be found in the h_cmdb_assets_users table.  As you are looking for "users allocated" I don't think this is something that you are looking for in your report.

Departments can be both stored against an asset and a user.  If you are using the Department field on the asset and want the report based on this, it simplifies how the report is created as you don't need any info from the h_sys_accounts table? You'll only need to run a report that lists all assets that are allocated to users (excluding shared) and group by department.  This report can look a little like this...

 

If you want to use the department that the user belongs to, this is a bit more complicated as a user can be a member of multiple departments. 

asset-users-by-department.report.txt

[Claire B]

Hi James - thank you for coming back to me.

Unfortunately we're not able to make use of the department field within the Asset record, as it's currently not being populated/updated, which is the reason we're looking into the use of h_sys_accounts table where we can pull a report listing the laptops with the criteria of a specific department/job titles to list all of the laptops in use by a specific department/team (basing on the user name being recorded on the asset record (Used By Name)).

[Met]

Hey,

 

Something like this might work based on what you've said above?

 

 

Then add your filters against h_sys_accounts as you see fit.

 

It just splits h_used_by into substrings using : as the delimiter (since it's a URN), and then returns the first substring from the right (the negative means start from the right, 1 being the first match).

[Claire B]

Hi Met,

This worked!! I changed from Login ID to User ID in the drop down on left hand side, and it given me what I needed.

Thank you! What a great start to Monday :-)