Help with Creating an SQL report with an Asset Type for Location
I'm trying to create an SQL script that gives me a Dell Warranty report and then add a location of the device. The location is a separate Asset Type from the Device. For example I have all my locations listed as their own Asset.
The field is ASSET_DATA_1
The script below gives me the information I need I just don't know how to add the location information from ASSET_DATA_1.
SELECT M.NAME AS MACHINE_NAME,
M.CS_MODEL AS MODEL,
M.USER_LOGGED AS LAST_LOGGED_IN_USER,
DW.END_DATE AS EXPIRATION_DATE
FROM DELL_WARRANTY DW JOIN DELL_ASSET DA ON (DW.SERVICE_TAG = DA.SERVICE_TAG)
JOIN MACHINE M ON (M.BIOS_SERIAL_NUMBER = DA.PARENT_SERVICE_TAG OR M.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG)
LEFT JOIN DELL_WARRANTY DW2 ON DW2.SERVICE_TAG=DW.SERVICE_TAG and DW2.END_DATE > NOW()
WHERE M.CS_MANUFACTURER LIKE '%dell%'
AND DA.DISABLED != 1
AND DW.END_DATE < NOW()
AND DW2.SERVICE_TAG IS NULL;
Be the first to answer this question