/bundles/itninjaweb/img/Breadcrumb_cap_w.png
I am trying to get a report built that will pull certain assets (defined by Asset.Name).  I used the report wizard a few different ways and it will not pull the asset name if it does not have an associated machine.id.  The following is one of the scripts.  I am sure it has something to do with the first Left Join but I do not know how to fix it.  I thought the wizard would give me a valid report but it does not seem to be able to create the correct report.  The PC in question, is an asset but it is offline so we don't have the Agent installed on it so it has never inventoried. 

SELECT ASSET.NAME AS ASSET_NAME, ASSET_LOCATION.NAME AS LOCATION, A110.NAME AS DEPARTMENT, ASSET_DATA_5.FIELD_63 AS MODEL, ASSET_DATA_5.FIELD_66 AS SERIAL_NUMBER, USER_111.FULL_NAME AS USER  

FROM MACHINE  

LEFT JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID=5

LEFT JOIN ASSET ASSET_LOCATION ON ASSET_LOCATION.ID = ASSET.LOCATION_ID 

LEFT JOIN ASSET_ASSOCIATION J110 ON J110.ASSET_ID = ASSET.ID AND J110.ASSET_FIELD_ID=110
                                 
LEFT JOIN ASSET A110 ON A110.ID = J110.ASSOCIATED_ASSET_ID 

LEFT JOIN ASSET_DATA_5 ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID 

LEFT JOIN USER USER_111 ON USER_111.ID = ASSET.OWNER_ID 

WHERE (ASSET.NAME like '%PC0157%') 
2 Comments   [ - ] Hide Comments

Comments

  • okay, that is great. Now what if I want to use that query and also add in the where clause

    OR (exists (select 1 from LABEL, MACHINE_LABEL_JT where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID AND MACHINE_LABEL_JT.LABEL_ID = LABEL.ID AND LABEL.TYPE <> 'hidden' and LABEL.NAME like '%Offline Devices%') )

    ORDER BY LOCATION
    • The asset doesn't match anything in the MACHINE table, so you can't reference machine labels.
  • So there is no way I can create a report that pulls machines in a label and machines in assets?
    • You can report on machines in the inventory and include asset information if a record exists in both places for a given machine. If you need to report on assets that don't have an inventory record, then you need to use another method to identify those machines. You could create a report that shows computer assets that don't have a matching inventory record, but if you never cleaned out your assets it will be a very long list.
Please log in to comment

Answer this question or Comment on this question for clarity

Answers

0
If the computer isn't in the inventory, then it won't exist in the MACHINE table, and therefore won't be mapped to any asset. That means you can skip the MACHINE table and just query the ASSET table:
SELECT ASSET.NAME AS ASSET_NAME, ASSET_LOCATION.NAME AS LOCATION, A110.NAME AS DEPARTMENT, ASSET_DATA_5.FIELD_63 AS MODEL, ASSET_DATA_5.FIELD_66 AS SERIAL_NUMBER, USER_111.FULL_NAME AS USER  
FROM ASSET  
LEFT JOIN ASSET ASSET_LOCATION ON ASSET_LOCATION.ID = ASSET.LOCATION_ID 
LEFT JOIN ASSET_ASSOCIATION J110 ON J110.ASSET_ID = ASSET.ID AND J110.ASSET_FIELD_ID=110
                                 
LEFT JOIN ASSET A110 ON A110.ID = J110.ASSOCIATED_ASSET_ID 
LEFT JOIN ASSET_DATA_5 ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID 
LEFT JOIN USER USER_111 ON USER_111.ID = ASSET.OWNER_ID 
WHERE (ASSET.NAME like '%PC0157%') 
Of course, if the machine has never been inventoried, then there may not be a corresponding asset, unless you manually create your assets separately (or import them). When a machine is inventoried the first time, the appliance will check if there is a matching asset (based on the settings in the computer asset type). If there is a matching asset, then the mapped ID is set to define the relationship. If there is not a matching asset, then the appliance creates it.


Answered 03/06/2018 by: chucksteel
Red Belt

Please log in to comment