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

 

 

 

 

 

 

 

 

Answer Summary:
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 ;
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

4

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'
Answered 09/05/2012 by: dugullett
Red Belt

  • 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 ;
  • Both of these reports work perfectly. Thank you each very much for your answers
Please log in to comment

Answers

0

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

 

 

Answered 01/08/2013 by: brucegoose03
Second Degree Black Belt

Please log in to comment
0

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

Answered 01/14/2013 by: brucegoose03
Second Degree Black Belt

Please log in to comment
Answer this question or Comment on this question for clarity