One of the built-in reports in the K1000 is a report of Dell machines with expired warranties. This is a great report, except that all of our machines have multiple warranties applied (Complete Care + Keep your hard drive, etc). This results in double the number of machines in the listing, since each computer is listed for every warranty contract that has expired. Has anyone tackled this problem and written a report that will return a single listing for each computer? 

Answer Summary:
SELECT M.NAME AS MACHINE_NAME, M.CS_MODEL AS MODEL, DA.SERVICE_TAG, DA.SHIP_DATE, GROUP_CONCAT(DISTINCT DW.SERVICE_LEVEL_CODE SEPARATOR '\n') AS Service_Level_Code, GROUP_CONCAT(DISTINCT DW.SERVICE_LEVEL_DESCRIPTION SEPARATOR '\n') AS Service_Level_Desc, GROUP_CONCAT(DISTINCT DW.END_DATE SEPARATOR '\n') AS EXPIRATION_DATE FROM KBSYS.DELL_WARRANTY DW LEFT JOIN KBSYS.DELL_ASSET DA ON (DW.SERVICE_TAG = DA.SERVICE_TAG) LEFT 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 DW.END_DATE < NOW() GROUP BY M.NAME
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

4

This will work. Although the query seems to take a long time to run. I took out the logged in user to try and speed it up. You might have better luck with it left in. I have a ton of machines.

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,

GROUP_CONCAT(DISTINCT DW.SERVICE_LEVEL_CODE SEPARATOR '\n') AS Service_Level_Code,

GROUP_CONCAT(DISTINCT DW.SERVICE_LEVEL_DESCRIPTION SEPARATOR '\n') AS Service_Level_Desc, 

GROUP_CONCAT(DISTINCT DW.END_DATE SEPARATOR '\n') AS EXPIRATION_DATE  

FROM KBSYS.DELL_WARRANTY  DW

LEFT JOIN KBSYS.DELL_ASSET DA ON (DW.SERVICE_TAG = DA.SERVICE_TAG) 

LEFT 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

AND  DW.END_DATE < NOW()

AND  DW.SERVICE_TAG NOT IN ( SELECT SERVICE_TAG FROM KBSYS.DELL_WARRANTY WHERE END_DATE > NOW())

GROUP BY M.NAME
Answered 08/13/2012 by: dugullett
Red Belt

  • Here is the shortened one that I ran to make it run a little faster.

    SELECT M.NAME AS MACHINE_NAME, M.CS_MODEL AS MODEL, DA.SERVICE_TAG, DA.SHIP_DATE,
    GROUP_CONCAT(DISTINCT DW.SERVICE_LEVEL_CODE SEPARATOR '\n') AS Service_Level_Code,
    GROUP_CONCAT(DISTINCT DW.SERVICE_LEVEL_DESCRIPTION SEPARATOR '\n') AS Service_Level_Desc,
    GROUP_CONCAT(DISTINCT DW.END_DATE SEPARATOR '\n') AS EXPIRATION_DATE
    FROM KBSYS.DELL_WARRANTY DW
    LEFT JOIN KBSYS.DELL_ASSET DA ON (DW.SERVICE_TAG = DA.SERVICE_TAG)
    LEFT 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 DW.END_DATE < NOW()
    GROUP BY M.NAME
Please log in to comment
Answer this question or Comment on this question for clarity