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
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

0
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

Answered 06/19/2015 by: chucksteel
Red Belt

Please log in to comment

Answers

Answer this question or Comment on this question for clarity

Share