Jump to content

Report of assets linked to a call


Dan Munns
 Share

Recommended Posts

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

@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....

Capture2.PNG

Capture3.PNG

Link to comment
Share on other sites

@Dan Munns maybe you've done something to SQL in a previous life :P ... 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

@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

@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)

 

 

Capture2.PNG

Link to comment
Share on other sites

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

@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

  • 10 months later...

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

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

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
 Share

×
×
  • Create New...