I'm trying to create a report that among other things show if the PC in inventory has been scrapped. Our Asset Information has what I think is a custom field for "Scrapped?".  I'm not sure how to get that field to show in my report.  This is what I have so far.

SELECT DISTINCT(MACHINE.NAME), 
MACHINE.OS_NAME, 
MACHINE.CS_MODEL,
MACHINE.CHASSIS_TYPE,
MACHINE.SYSTEM_DESCRIPTION, 
MACHINE.CS_MANUFACTURER, 
DA.SHIP_DATE AS "Ship Date",
DA.SERVICE_TAG,
MAX(DW.END_DATE) AS "Warranty End Date",
DW.SERVICE_LEVEL_DESCRIPTION as "Service Level"
FROM MACHINE
LEFT JOIN DELL_ASSET DA on MACHINE.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG
LEFT JOIN DELL_WARRANTY DW on MACHINE.BIOS_SERIAL_NUMBER = DW.SERVICE_TAG
WHERE MACHINE.CS_MANUFACTURER like 'Dell%'
AND MACHINE.CHASSIS_TYPE != 'server'
GROUP BY MACHINE.NAME
ORDER BY "Ship Date", MACHINE.NAME
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1

You'll need to identify the table and field that contains the information in the database. The asset data ID for computers should be 5 so that data should be in ASSET_DATA_FIELD5, I think. The field ID will also be unique to your environment. I recommend using MySQL Workbench to connect to the database and looking at the ASSET_DATA tables to get the column names that you will then include in your report.

Answered 08/18/2016 by: chucksteel
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity