Dell Expired Warranty Report - Multiple listing for each machine - need unique only
When we purchase our Dell PC's we receive the 1 yr warranty and then purchase an additional 4 year warranty on top of that. This causes our warranty information to appear as multiple warranty's. For example the first 1 year warranty expired in 2010 and the second warranty expires in 2014.
When I run the canned report I get a result declaring this PC to be out of warranty when it is covered until 2014. Any suggestion on how to adjust the report to take into account our PC's with more than one warranty would be appreciated.
thank you
Answers (3)
I had the same issue and here is what worked for me. it list Machin Name, Model, Service tag, Ship date, last logged in user and Expiration date. SELECT DISTINCT M.NAME AS MACHINE_NAME, M.CS_MODEL AS MODEL, DA.SERVICE_TAG, DA.SHIP_DATE,M.USER_LOGGED AS LAST_LOGGED_IN_USER, DW.END_DATE 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=(Select Max(DW2.END_DATE) FROM KBSYS.DELL_WARRANTY DW2 WHERE DW2.SERVICE_TAG=DA.SERVICE_TAG) 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()) Order by EXPIRATION_DATE ;
Something like this will work. Make sure to test in your environment. I don't have a ton of Dell's to test with.
SELECT M.NAME AS MACHINE_NAME, M.CS_MODEL AS MODEL, DW.SERVICE_TAG, DA.SHIP_DATE, DW.SERVICE_LEVEL_DESCRIPTION, DW.END_DATE 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()) AND ENTITLEMENT_TYPE LIKE 'EXTENDED'
Comments:
-
I had the same issue and here is what worked for me. it list Machin Name, Model, Service tag, Ship date, last logged in user and Expiration date.
SELECT DISTINCT M.NAME AS MACHINE_NAME, M.CS_MODEL AS MODEL, DA.SERVICE_TAG, DA.SHIP_DATE,M.USER_LOGGED AS LAST_LOGGED_IN_USER, DW.END_DATE 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=(Select Max(DW2.END_DATE) FROM KBSYS.DELL_WARRANTY DW2 WHERE DW2.SERVICE_TAG=DA.SERVICE_TAG)
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())
Order by EXPIRATION_DATE ; - bozadmin 12 years ago -
Both of these reports work perfectly. Thank you each very much for your answers - jhaste 12 years ago
Maybe not exactly what you're looking for, but this will show you all machines (be careful, it's only limiting the results by Manfucatrure like dell, so if you have more than 1,000 machines, you may want to limit this to a label, or run it over night)
Select
MACHINE.NAME,
MACHINE.IP,
MACHINE.CS_MODEL,
KBSYS.DELL_WARRANTY.START_DATE,
KBSYS.DELL_WARRANTY.END_DATE,
KBSYS.DELL_WARRANTY.ENTITLEMENT_TYPE,
KBSYS.DELL_WARRANTY.ITEM_NUMBER,
KBSYS.DELL_WARRANTY.SERVICE_LEVEL_CODE,
KBSYS.DELL_WARRANTY.SERVICE_LEVEL_GROUP,
KBSYS.DELL_WARRANTY.SERVICE_LEVEL_DESCRIPTION
From
MACHINE Left Join
KBSYS.DELL_WARRANTY On MACHINE.BIOS_SERIAL_NUMBER =
KBSYS.DELL_WARRANTY.SERVICE_TAG
Where
MACHINE.CS_MANUFACTURER LIKE '%dell%'
Group By
MACHINE.NAME
for 5.4:
Select
MACHINE.NAME,
MACHINE.IP,
MACHINE.CS_MODEL,
DELL_WARRANTY.START_DATE,
DELL_WARRANTY.END_DATE,
DELL_WARRANTY.ENTITLEMENT_TYPE,
DELL_WARRANTY.ITEM_NUMBER,
DELL_WARRANTY.SERVICE_LEVEL_CODE,
DELL_WARRANTY.SERVICE_LEVEL_GROUP,
DELL_WARRANTY.SERVICE_LEVEL_DESCRIPTION
From
MACHINE Left Join
DELL_WARRANTY On MACHINE.BIOS_SERIAL_NUMBER =
DELL_WARRANTY.SERVICE_TAG
Where
MACHINE.CS_MANUFACTURER LIKE '%dell%'
Group By
MACHINE.NAME
