/build/static/layout/Breadcrumb_cap_w.png

K1000: Warranty Expired Query shows multiple computer listings

Im using this query to find all the machines on the network that have an expired warranty according to their service tag.

However, when I run the query, some of the machines are listed twice but should only be listed once.

Here is an example of the output where machine example3 is correctly listed but example1 is listed twice.

#      Machine Name     Service Tag

1       example1           abcd123

2       example1           abcd123

3       example3            abcd124

Code:

 

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, DW.SERVICE_LEVEL_CODE, DW.SERVICE_LEVEL_DESCRIPTION, DW.END_DATE AS EXPIRATION_DATE

FROM

 DELL_WARRANTY DW

JOIN

DELL_ASSET DA ON (DW.SERVICE_TAG = DA.SERVICE_TAG)

JOIN

                MACHINE M

ON (M.BIOS_SERIAL_NUMBER = DA.PARENT_SERVICE_TAG OR M.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG)

LEFT JOIN

DELL_WARRANTY DW2 ON DW2.SERVICE_TAG=DW.SERVICE_TAG and DW2.END_DATE > NOW()

WHERE

M.CS_MANUFACTURER LIKE '%dell%'

AND

M.BIOS_SERIAL_NUMBER!=''

AND

 DA.DISABLED != 1

AND

 DW.END_DATE < NOW()

AND

DW2.SERVICE_TAG IS NULL;


1 Comment   [ + ] Show comment
  • how to add Days Left to this query... - rahimpal 7 years ago

Answers (1)

Posted by: jknox 10 years ago
Red Belt
0

Check the system at support.dell.com.  I think you will find systems have two listings under warranty there as well, and that is what is causing two entries in the report.  This would be true if the warranties were upgraded after initial purchase or if the systems had something like complete care added.


Comments:
  • I checked on dell support to see if there were any other listings like you suggested but there was only one. Regardless, I'm more so interested in just narrowing down my search since I'm returned 300 some computers warranties have expired. Any idea on how to modify the SQL to only list the machine once instead of multiple times? - removedbyuser95897 10 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ