Extended Battery Report
Anyone have SQL showing the computers (specifically laptops) that have battery warantys that are going to expire in the next 60 days.
Thanks,
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
SELECT M.NAME AS 'System Name', M.CS_MODEL AS MODEL, A.SERVICE_TAG as 'Service Tag', DATE_FORMAT(D.END_DATE,'%m/%d/%Y')AS 'End Date' FROM KBSYS.DELL_WARRANTY D LEFT JOIN KBSYS.DELL_ASSET A ON D.SERVICE_TAG = A.SERVICE_TAG LEFT JOIN MACHINE M ON M.BIOS_SERIAL_NUMBER = A.SERVICE_TAG where D.END_DATE > NOW() AND D.END_DATE <= DATE_ADD(NOW(), INTERVAL 60 DAY) AND D.SERVICE_LEVEL_DESCRIPTION LIKE '%BATTERY%' ORDER BY D.END_DATE
SELECT M.NAME AS 'System Name', M.CS_MODEL AS MODEL, A.SERVICE_TAG as 'Service Tag', DATE_FORMAT(D.END_DATE,'%m/%d/%Y')AS 'End Date' FROM KBSYS.DELL_WARRANTY D LEFT JOIN KBSYS.DELL_ASSET A ON D.SERVICE_TAG = A.SERVICE_TAG LEFT JOIN MACHINE M ON M.BIOS_SERIAL_NUMBER = A.SERVICE_TAG where D.END_DATE > NOW() AND D.END_DATE <= DATE_ADD(NOW(), INTERVAL 60 DAY) AND D.SERVICE_LEVEL_DESCRIPTION LIKE '%BATTERY%' ORDER BY D.END_DATE
Please log in to answer
Posted by:
dugullett
11 years ago
I'm not sure if this is something that is actually captured. It's been a while since I've been out of the purchasing game, but I believe the give you 90 day warranty on the batteries still? Something you could try is running a report for ship date and then taking 30 days off of that. This will return machines with a ship date older than 30 days, but less that 90.
SELECT NAME AS Computer_Name, CS_MODEL, D.SHIP_DATE FROM MACHINE M JOIN KBSYS.DELL_ASSET D ON D.SERVICE_TAG = M.BIOS_SERIAL_NUMBER WHERE D.SHIP_DATE < CURDATE() - INTERVAL 30 DAY AND D.SHIP_DATE > CURDATE() - INTERVAL 90 DAY AND M.CHASSIS_TYPE LIKE '%LAPTOP%' ORDER BY D.SHIP_DATE
Comments:
-
Dell laptop batteries should have a 1 year warranty by default, or at least for business models. There is a new extended battery warranty that extends coverage up to 3 years I believe.
I can't find anything specific on time frames at the moment, but here is a general description: http://content.dell.com/us/en/enterprise/d/services/support-services-extended-battery - jknox 11 years ago-
Wow I was way off. In that case use this for the dates. This will get everything less than a year, but greater than 305 days. So this will cover that 60 day range. You'll have to adjust based on your warranty.
WHERE D.SHIP_DATE < CURDATE() - INTERVAL 305 DAY AND
D.SHIP_DATE > CURDATE() - INTERVAL 1 YEAR AND - dugullett 11 years ago
-
Well we buy 3 year battery warranty for our laptops it shows up in kace as its own warranty line - stagebooms 11 years ago
-
Under the Dell Services Info? Maybe I'm not seeing that because we don't have it, which will make it hard for me to write a query for that. If it is there it will show in the KBSYS.DELL_WARRANTY table. - dugullett 11 years ago
-
I found some of my machines with the battery warranty. They were expired so test this in your environment first.
SELECT M.NAME AS 'System Name', M.CS_MODEL AS MODEL, A.SERVICE_TAG as 'Service Tag',
DATE_FORMAT(D.END_DATE,'%m/%d/%Y')AS 'End Date'
FROM KBSYS.DELL_WARRANTY D
LEFT JOIN KBSYS.DELL_ASSET A ON D.SERVICE_TAG = A.SERVICE_TAG
LEFT JOIN MACHINE M ON M.BIOS_SERIAL_NUMBER = A.SERVICE_TAG
where D.END_DATE > NOW()
AND D.END_DATE <= DATE_ADD(NOW(), INTERVAL 60 DAY) AND
D.SERVICE_LEVEL_DESCRIPTION LIKE '%BATTERY%'
ORDER BY D.END_DATE - dugullett 11 years ago-
That looks good. Wish I had one to test it against. - jknox 11 years ago
-
I'll give it a try tomorrow. Thanks. - stagebooms 11 years ago
-
Works Like a Champ; I had to change to 120 days because we are 3 months shy of when we started buying battery warranty; will change back to 60 or 45 depends on need. - stagebooms 11 years ago
