Dan Munns Posted December 1, 2017 Share Posted December 1, 2017 Hi all, I am trying to create a report of assets attached to a call. I want the analysts to be able to run the report, type in a call reference and output a csv of all linked assets with the following info: Type Name Serial number I am having difficulty working out the table joins to pull out the required information. Thanks Link to comment Share on other sites More sharing options...
Victor Posted December 1, 2017 Share Posted December 1, 2017 @Dan Munns so it's requests table (h_itsm_requests) and this: which is basically this table: Link to comment Share on other sites More sharing options...
Dan Munns Posted December 4, 2017 Author Share Posted December 4, 2017 @Victor thanks for the confirmation. I had those two tables (and h_cmdb_assets) but I still cant work it out. I run the report (with only the two tables you mentioned) and it runs against a SR which has two assets attached but returned 14 asset IDs. I think I have the join wrong but I can't work out what it is actually pulling. If I try and add the other table (which is where I think the other info is held) it fails altogether. I have decided that SQL hates me.... Link to comment Share on other sites More sharing options...
Victor Posted December 4, 2017 Share Posted December 4, 2017 @Dan Munns maybe you've done something to SQL in a previous life ... The first thing that is obvious is the join definition there... against user prompted value? ... a join should be between two fields of two tables I have attached a report I put together in my test instance that should return the info you need... You can add various filters in "Filter" tab and add other columns as needed... let me know how it works assetsexample.report.txt Link to comment Share on other sites More sharing options...
Dan Munns Posted December 4, 2017 Author Share Posted December 4, 2017 @Victor my bad I was getting confused with filter. It was Friday. And we had our works do this weekend so I may not be with it still..... Well thats my excuse anyway. Now however the report generated is blank apart from the Request ID, and I cant find serial number (I am hoping it not on another table). It says 2 records returned and the assets have the information filled in but the report doesnt show it... Again, SQL hates me..... Link to comment Share on other sites More sharing options...
Victor Posted December 4, 2017 Share Posted December 4, 2017 @Dan Munns have you tried my report? Link to comment Share on other sites More sharing options...
Dan Munns Posted December 4, 2017 Author Share Posted December 4, 2017 @Victor Sorry, that is your report that is blank.... Link to comment Share on other sites More sharing options...
Victor Posted December 4, 2017 Share Posted December 4, 2017 @Dan Munns hmmm... can you please have a look in "h_cmdb_assets table", do you have the "h_asset_urn" field values populated for the assets associated to 6313? Link to comment Share on other sites More sharing options...
Dan Munns Posted December 4, 2017 Author Share Posted December 4, 2017 @Victor Ahh that makes sense as the field is blank. We imported the assets with your asset import tool so how do we get the field to populate? Or is there another field we can use for the join? Asset ID > ID doesn't seem to work EDIT: So the fields are like this (the only missing column is Request ID) Link to comment Share on other sites More sharing options...
Victor Posted December 4, 2017 Share Posted December 4, 2017 11 minutes ago, Dan Munns said: Or is there another field we can use for the join? Asset ID > ID doesn't seem to work @Dan Munns no, it wouldn't work... the only way we can link requests and assets in the report is via the "h_asset_urn" field... I see the same situation in my test instance whereby some assets don't have a value for this field so I have to think of two actions: one, if there is a quick and painless way to populate this field (I think it is but I need to check with dev team) and two, why is this value missing in the first place... Link to comment Share on other sites More sharing options...
Dan Munns Posted December 4, 2017 Author Share Posted December 4, 2017 @Victor I will await your results. Thanks for all your help so far. Once this disposal process is sorted I am going on annual leave......lol Link to comment Share on other sites More sharing options...
Victor Posted December 4, 2017 Share Posted December 4, 2017 8 minutes ago, Dan Munns said: I am going on annual leave ... no !? ... That's it! No more help !! Link to comment Share on other sites More sharing options...
Dan Munns Posted December 4, 2017 Author Share Posted December 4, 2017 3 minutes ago, Victor said: ... no !? ... That's it! No more help !! To be fair I am going anyway Link to comment Share on other sites More sharing options...
Victor Posted December 4, 2017 Share Posted December 4, 2017 <jedi waving hand>You will stay here with the rest of us ... </jedi waving hand> Link to comment Share on other sites More sharing options...
Dan Munns Posted December 4, 2017 Author Share Posted December 4, 2017 @Victor thanks for all your help today. Used the updated CSV importer and all works as it should. I even managed to join two other tables to show type name (rather than the ID number in the first table) and serial number, without breaking it completely. Apparently the weekend has finally worn off! (Just in time for annual leave to start tomorrow) See you all on Thursday!! Thanks again. Dan Link to comment Share on other sites More sharing options...
Victor Posted December 4, 2017 Share Posted December 4, 2017 @Dan Munns happy to hear... we'll be updating the missing information in that field sometime later today... if the info was already populated there won't be any update so nothing to worry about! Enjoy the time off! Link to comment Share on other sites More sharing options...
Guest Paul Alexander Posted October 16, 2018 Share Posted October 16, 2018 Hi @Victor I realise this is a really old post....but I'm trying to see why so many of our assets don't have info in the h_asset_urn field (which is stopping us from being able to run reports about which assets are linked to other assets etc). You mentioned there was an update (above)....is this something that has to be run on each instance, or is it something which SHOULD now have happened? The created dates of our assets are all around May this year (2018) so have I done something wrong on the import? thanks Link to comment Share on other sites More sharing options...
Victor Posted October 22, 2018 Share Posted October 22, 2018 On 10/16/2018 at 8:55 AM, Paul Alexander said: why so many of our assets don't have info in the h_asset_urn field How were the assets imported? Is it the via CSV upload in Assets interface or did you use any of the import tools we provide for this? If a tool was used it might be you are using an old version of it which had a defect in this regard... On 10/16/2018 at 8:55 AM, Paul Alexander said: You mentioned there was an update (above)....is this something that has to be run on each instance, or is it something which SHOULD now have happened? Theoretically, an update can be performed to fix this but I would think of this as a last option... (because updates are usually a bad idea) On 10/16/2018 at 8:55 AM, Paul Alexander said: The created dates of our assets are all around May this year (2018) so have I done something wrong on the import? I cannot say ... but is unlikely you did something wrong. Most likely it was the tool not doing something right... Link to comment Share on other sites More sharing options...
Guest Paul Alexander Posted October 22, 2018 Share Posted October 22, 2018 Hi @Victor @ArmandoDM is now dealing with this here: Thanks for the update though....and welcome back! Link to comment Share on other sites More sharing options...
Victor Posted October 22, 2018 Share Posted October 22, 2018 11 minutes ago, Paul Alexander said: and welcome back! Link to comment Share on other sites More sharing options...
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