Archived Asset Report showing BIOS Serial Number
Hello,
Below is the SQL for a report that shows me a list of archived assets. The problem with this report is it's missing the BIOS serial number. If I go to the archived asset in Kace I can see the link to the device. Does anyone have any tips to modify the SQL below for archived assets and include detail from the device's BIOS Serial Number field?
SELECT ASSET.NAME AS ASSET_NAME, COALESCE(ASSET_CLASS.NAME, 'None') AS ASSET_SUBTYPE, ASSET_STATUS.NAME AS ASSET_STATUS, ASSET_LOCATION.NAME AS LOCATION,
CASE WHEN ASSET.ARCHIVE='COMPLETED' THEN 'Completed' WHEN ASSET.ARCHIVE='PENDING' THEN 'Pending' ELSE 'Not Archived' END AS ASSET_ARCHIVE FROM ASSET_DATA_5
LEFT JOIN ASSET ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID AND ASSET.ASSET_TYPE_ID=5
LEFT JOIN MACHINE M ON ASSET.MAPPED_ID = M.ID and ASSET.ASSET_TYPE_ID = 5
LEFT JOIN ASSET ASSET_LOCATION ON ASSET_LOCATION.ID = ASSET.LOCATION_ID
LEFT JOIN ASSET_CLASS ON ASSET_CLASS.ID = ASSET.ASSET_CLASS_ID
LEFT JOIN ASSET_STATUS ON ASSET_STATUS.ID = ASSET.ASSET_STATUS_ID
ORDER BY ASSET_ARCHIVE
Answers (2)
Just a thought, if you have archived the asset that would suggest the Inventory record for the device will have been deleted. Whilst you may see the asset linked to the device in inventory, for example MACHINE.ID, if there is no device there will be nothing on that ID.
I know that no one likes to be told to “shut the doors after the horse has bolted” but that is why we always recommend that you create a serial number field in the device asset type and then link BIOS serial number in Inventory to that serial asset field, within the Device Asset type.
This provides some advantages, not least that it reduces duplicated assets and also maintains the device name as the asset name.
It is most helpful as even when you archive the asset you still have the serial number of the device it was.
Comments:
-
You're absolutely correct in your assessment. Going forward I've added the BIOS serial number as a field in the asset type of device. I think this should allow me to more easily see that data on assets that are archived going forward. My mistake was in assuming since I could still see the device by clicking on the link in the archived asset that I'd still be able to query the device. - brianpink 3 years ago
SELECT ASSET.NAME AS ASSET_NAME, M.BIOS_SERIAL_NUMBER, COALESCE(ASSET_CLASS.NAME, 'None') AS ASSET_SUBTYPE, ASSET_STATUS.NAME AS ASSET_STATUS, ASSET_LOCATION.NAME AS LOCATION,
CASE WHEN ASSET.ARCHIVE='COMPLETED' THEN 'Completed' WHEN ASSET.ARCHIVE='PENDING' THEN 'Pending' ELSE 'Not Archived' END AS ASSET_ARCHIVE FROM ASSET_DATA_5
LEFT JOIN ASSET ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID AND ASSET.ASSET_TYPE_ID=5
LEFT JOIN MACHINE M ON ASSET.MAPPED_ID = M.ID and ASSET.ASSET_TYPE_ID = 5
LEFT JOIN ASSET ASSET_LOCATION ON ASSET_LOCATION.ID = ASSET.LOCATION_ID
LEFT JOIN ASSET_CLASS ON ASSET_CLASS.ID = ASSET.ASSET_CLASS_ID
LEFT JOIN ASSET_STATUS ON ASSET_STATUS.ID = ASSET.ASSET_STATUS_ID
ORDER BY ASSET_ARCHIVE
Comments:
-
Thanks for the reply, however this query only gives me the BIOS serial number for assets not archived and returns no results for BIOS serial number for assets that are archived. If I could somehow query the device that's linked in the asset that's archived, my problem here would be solved. - brianpink 3 years ago
