Modifying a custom report to include additional data
I need to produce a report of all Archived Assets that includes Make, Model & Serial number. I know the data still exists in the asset archive because I can drill down and manually gather that data. From the Assets menu I can View by Archived and the create a report that will show ID, Asset Name, Asset type etc. Based off some searches here I should be able to modify this query KACE created to include Make, Model & Serial Number.
IF anyone can point me int the the right direction on how to modify this please. Here is the query KACE created. Thank you in advance.
SELECT ASSET.ID, ASSET.NAME AS ASSET_NAME, T.NAME AS ASSET_TYPE, COALESCE(AC.NAME, 'None') AS ASSET_SUBTYPE,
IF(U.FULL_NAME != '', U.FULL_NAME, U.USER_NAME) AS USER,
T.MAPPED_TABLE, T.DETAIL_PAGE, ASSET.ASSET_DATA_ID, ASSET.MAPPED_ID,
M.NAME AS MACHINE_NAME,
L.NAME AS LOCATION,
GROUP_CONCAT(DISTINCT D.NAME) AS DEPARTMENT,
GROUP_CONCAT(DISTINCT C.NAME) AS COST_CENTER,
ARCHIVE_ASSET_DEVICE.NAME AS ARCHIVED_MACHINE_NAME,
ASSET_STATUS.NAME AS ASSET_STATUS FROM ASSET
INNER JOIN ASSET_TYPE T ON T.ID = ASSET.ASSET_TYPE_ID
LEFT JOIN ASSET_CLASS AC ON AC.ID = ASSET.ASSET_CLASS_ID
LEFT JOIN USER ASSET_OWNER ON ASSET_OWNER.ID = ASSET.OWNER_ID
LEFT JOIN ASSET ASSET_OWNER_LOCATION ON ASSET_OWNER_LOCATION.ID = ASSET_OWNER.LOCATION_ID
LEFT JOIN USER_ROLE ASSET_OWNER_ROLE ON ASSET_OWNER_ROLE.ID = ASSET_OWNER.ROLE_ID
LEFT JOIN KBSYS.LOCALE_BROWSER ASSET_OWNER_LOCALE ON ASSET_OWNER_LOCALE.ID = ASSET_OWNER.LOCALE_BROWSER_ID
LEFT JOIN USER U ON U.ID = ASSET.OWNER_ID
LEFT JOIN ASSET L ON L.ID = ASSET.LOCATION_ID
LEFT JOIN MACHINE M ON ASSET.MAPPED_ID = M.ID and ASSET.ASSET_TYPE_ID = 5
LEFT JOIN ARCHIVE_ASSET_DEVICE ON ASSET.ID = ARCHIVE_ASSET_DEVICE.ASSET_ID and ASSET.ASSET_TYPE_ID = 5
LEFT JOIN ASSET_FIELD_DEFINITION F5 ON F5.ASSET_TYPE_ID = ASSET.ASSET_TYPE_ID AND F5.FIELD_TYPE = 'ASSET_2'
LEFT JOIN ASSET_ASSOCIATION J5 ON J5.ASSET_FIELD_ID = F5.ID AND J5.ASSET_ID = ASSET.ID
LEFT JOIN ASSET D ON D.ID = J5.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_FIELD_DEFINITION F6 ON F6.ASSET_TYPE_ID = ASSET.ASSET_TYPE_ID AND F6.FIELD_TYPE = 'ASSET_3'
LEFT JOIN ASSET_ASSOCIATION J6 ON J6.ASSET_FIELD_ID = F6.ID AND J6.ASSET_ID = ASSET.ID
LEFT JOIN ASSET C ON C.ID = J6.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_FIELD_DEFINITION F1 ON F1.ASSET_TYPE_ID = ASSET.ASSET_TYPE_ID AND F1.FIELD_TYPE = 'ASSET_1'
LEFT JOIN ASSET_ASSOCIATION J1 ON J1.ASSET_FIELD_ID = F1.ID AND J1.ASSET_ID = ASSET.ID
LEFT JOIN ASSET LA ON LA.ID = J1.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_BARCODE_JT ABJT ON ABJT.ASSET_ID = ASSET.ID
LEFT JOIN BARCODE ON BARCODE.ID = ABJT.BARCODE_ID
LEFT JOIN ASSET_STATUS ON ASSET_STATUS.ID = ASSET.ASSET_STATUS_ID
WHERE 1=1 AND ASSET.ID != 1 AND ASSET.ASSET_TYPE_ID NOT IN (10000,10001,10200,7,10002) AND ASSET.ARCHIVE IN('PENDING', 'COMPLETED') GROUP BY ASSET.ID order by ASSET_NAME
Make and Model are in the ASSET_DATA_5 table, so you will need to add a join to that table:
LEFT JOIN ASSET_DATA_5 DATA on DATA.ID = ASSET.ASSET_DATA_ID
I'm fairly certain that the make a model fields are part of the standard device asset type and on my appliance those fields are FIELD_34 and FIELD_35, respectively. You need to add those to the select portion of the query:
DATA.FIELD_34 as 'Make',
DATA.FIELD_35 as 'Model',
On my appliance, the asset's name is the serial number, if that isn't the case on yours, then did you add a custom field to store it? If so, then you need to determine what the field ID is for your serial number column in the database. This query will show you the fields for the device asset type:
SELECT * FROM ORG1.ASSET_FIELD_DEFINITION WHERE ASSET_TYPE_ID = 5;
Once you have identified the column name, e.g. FIELD_12345, you can add that to the select portion of the main report query:
DATA.FIELD_12345 as 'Serial Number'