Jump to content

Recommended Posts

Posted

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

 

 

Posted

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

Posted

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

 

 

Posted

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

 

Posted

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 

 

 

Posted

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

Posted

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

image.pngimage.pngimage.pngimage.png

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...