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

Comments

  • how to add Days Left to this query...
Please log in to comment

Answers

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.

Answered 09/10/2013 by: jknox
Red Belt

  • 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?
Please log in to comment
Answer this question or Comment on this question for clarity