Anyone have SQL showing the computers (specifically laptops) that have battery warantys that are going to expire in the next 60 days.

 

Thanks,

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

Comments

Please log in to comment

Answers

3

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
Answered 08/21/2012 by: dugullett
Red Belt

  • 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
    • 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
  • Well we buy 3 year battery warranty for our laptops it shows up in kace as its own warranty line
    • 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.
  • 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
    • That looks good. Wish I had one to test it against.
  • I'll give it a try tomorrow. Thanks.
  • 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.
Please log in to comment
Answer this question or Comment on this question for clarity