/build/static/layout/Breadcrumb_cap_w.png

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


0 Comments   [ + ] Show comments

Answers (2)

Posted by: Hobbsy 3 weeks ago
Red Belt
0

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 weeks ago
Posted by: KevinG 3 weeks ago
10th Degree Black Belt
0

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 weeks ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ