Return only unique machines w/ expired warranty
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?
0 Comments
[ + ] Show comments
Answers (1)
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
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 answer
Posted by:
dugullett
11 years ago
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
Comments:
-
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 - dugullett 11 years ago
