Report to pull certain assets
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
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
chucksteel
6 years ago
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.
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 - ellisha 6 years ago