Joyce Posted February 7, 2020 Posted February 7, 2020 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 Posted February 7, 2020 Posted February 7, 2020 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 Posted February 7, 2020 Author Posted February 7, 2020 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 Posted February 7, 2020 Author Posted February 7, 2020 h_cmdb_assets_users doesn't seems to have any data
ArmandoDM Posted February 7, 2020 Posted February 7, 2020 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 Posted February 7, 2020 Posted February 7, 2020 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 Posted February 7, 2020 Author Posted February 7, 2020 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 Posted February 10, 2020 Posted February 10, 2020 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 Posted February 18, 2020 Author Posted February 18, 2020 Thanks for this, but the result doesn't still bring all the email address from h_sys_Account
Joyce Posted February 21, 2020 Author Posted February 21, 2020 Thanks. This has been resolved, there were discrepancy on the data. Regards, Joyce
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now