Asset reporting

[Philip Hanlon]

Hi, 

 

We do a daily import of our current asset management tool data into Hornbill. I'm trying to Link link assets user IDs to their corresponding  user departments. Any ideas on how this might be achieved? Our departments are recorded in our requests table under custom D. Attached is the report but missing the requests table

asset-by-user---ph.report.txt

[Bob Dickinson]

Hi @Philip Hanlon

Unless you are looking to report on a combination of tickets raised relating to assets (including departmental information), the department that you have being recorded in Custom D is not really going to useful, as it's obviously dependant of tickets being raised. 

By the looks of things, this report actually should have no relation to Incidents/Services requestss and is actually an extract of current assets, associated to users and you need to also include their departmental info. 

For most of our customer, the department of a user is stored against their User Profile, within the "Organisations" tab:

 

In terms of the DB - these associations are help in a table called h_sys_account_groups, and the group information itself is in a table called h_sys_groups. 

I have attached an amended version of your report with these to tables joined, and some additional columns being displayed on the output. 
A couple of things to keep in mind:

• You may need to be care with the Joins - if you come across a user who is associated to an Asset, but for some reason DOES NOT have a department, then the row may not show in the report, so this may need some tweaking
• Conversly, in Hornbill we actually allow for users to be members of multiple departments. If this happens to be the case in your business, the report will show a row for each department they are a member of so it may look like duplicated data.

I hope this helps as a starting point,

Kind Regards

Bob

asset-by-user---ph.report (1).txt

 

[Philip Hanlon]

Hi @Bob Dickinson,

 

Thank you for this. Worked perfectly

[Philip Hanlon]

Hi @Bob Dickinson,

 

Just looking at this again. Is there any way to put a filter so that it only pulls through the value where department matches the - h_sys_accounts->Attribute 1 that way there wont be duplicate values?

[Bob Dickinson]

Hi @Philip Hanlon

Can you let me know what is being duplicated (and why?)
Are you getting two rows for the same asset because the associated user is a member of two departments rather than one?

Kind Regards

Bob

[Philip Hanlon]

@Bob Dickinson

yup exactly, so im hoping to only return rows where the department matches attribute one which is the current department according to AD is this possible. i used the report you supplied above so if that could be tweaked to meet the requirement it would be great

[Bob Dickinson]

Hi @Philip Hanlon

Ok - I think I have added the extra filter that should allow for this. 

Can you upload this file and try it to see if it gives you the results you are expecting, and let me know?

asset-by-user---ph-v2.report.txt

Kind Regards

Bob