Software Assets Users Report

[Joyce]

Hi,

I am trying to get a report of users of various software, but retrive their email address instead of user name/ user ID.

I have join table CMDB Assets and h_sys_accounts to obtain users' email address, but I only get half of the email address, although every user has their email when look from: https://admin.hornbill.com/Inst/accounts/users

Where does the info on https://admin.hornbill.com/inst/accounts/users come from?

Thanks.

Regards,

Joyce

 

 

[ArmandoDM]

Hi @Joyce

the info on users are stored in h_sys_accounts.

Maybe the email addresses missing are on those assets that are shared by multiple users, in which case the info on who's sharing the asset is not in h_cmdb_assets but  in h_cmdb_assets_users

Regards

Armando

[Joyce]

Hi @ArmandoDM

Join of h_cmdb_assets , h_cmdb_assets_users ( left Join using Asset ID) and h_sys_accounts. ( left join with h_cmdb_assets_users  using User ID), does not return any value from h_sys_accounts table. The results, only have a list of assets_Name.

 

How else can I join these tables to get users' email address as well as Asset name and Tag?

Thanks.

 

Regards,

Joyce

 

 

[Joyce]

h_cmdb_assets_users doesn't seems to have any data

[ArmandoDM]

Hi @Joice

if the asset is not shared directly with a User, the join on the accounts table will not return any result.

Because the asset can be shared with a group, site, contact, etc, then a full query should include other tables to pull the info about all the users sharing the asset. Not a short query.

Regards

Armando

 

[ArmandoDM]

If h_cmdb_assets_users  has not any data, then you have not shared assets. 

All the info about the Used By and Owned By will be into h_used_by and h_owned_by.

These 2 columns have a user URN, not the ID, so make sure you take this into consideration when writing the join 

 

 

[Joyce]

Hi @ArmandoDM,

Can you assist with the query for joining h_sys_accounts using h_user_id and CMDB Assets Trimming the URN from the h_used_by?

I am trying Trim function but, is not working. I do remember a previous report where similar query was used, but I can't find the details for it.

Thanks

Joyce

[ArmandoDM]

Hi @Joyce

the easiest way of getting the list you need is through a widget, which will build and run the right query for you.
I attached a few screenshots that you may use to build the widget and get the data.

Hope this helps

Regards

Armando

[Joyce]

Thanks for this, but the result doesn't still bring all the email address from h_sys_Account

[Joyce]

Thanks. This has been resolved, there were discrepancy on the data.

 

Regards,

Joyce