need sql query for warranty
can anybody share me a sql query to generate list machines whose warranty expiring in 100 days,
fields required asset name, start & end date,service tag, model, user name,domain
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
8 years ago
Top Answer
Here is the report I have setup. The fields returned are a little different from what you asked:
SELECT M.NAME AS MACHINE_NAME, M.CS_MODEL AS MODEL, DA.SERVICE_TAG, DA.SHIP_DATE,M.USER_LOGGED AS LAST_LOGGED_IN_USER, DW.SERVICE_LEVEL_CODE,
DW.SERVICE_LEVEL_DESCRIPTION, MAX(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)
WHERE M.CS_MANUFACTURER LIKE '%dell%'
AND M.BIOS_SERIAL_NUMBER!=''
AND DA.DISABLED != 1
GROUP BY MACHINE_NAME
HAVING EXPIRATION_DATE BETWEEN NOW() and DATE_ADD(NOW(), INTERVAL 100 DAY)
ORDER BY EXPIRATION_DATE DESC
