Is there a script to only show a dell device ship date that is older than 2 years old?
We have the current script but want to create a monthly reoccuring script that shows devices that older than 2 years old from when the script is ran. Is there any way to edit the current one we have to do that?
Our current script:
SELECT DISTINCT(MACHINE.NAME),
MACHINE.OS_NAME,
MACHINE.IP,
MACHINE.CS_MODEL,
DA.SHIP_DATE AS "Ship Date"
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%'
GROUP BY MACHINE.NAME
ORDER BY "Ship Date", MACHINE.NAME
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
Nico_K
3 years ago
Just add a calculation in the WHERE clause so your query looks like:
MACHINE.OS_NAME,
MACHINE.IP,
MACHINE.CS_MODEL,
DA.SHIP_DATE AS "Ship Date"
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 DA.SHIP_DATE< DATE_SUB(NOW(), INTERVAL 720 DAY)
GROUP BY MACHINE.NAME
ORDER BY "Ship Date", MACHINE.NAME
