Hi All,

I was curious as to how I need to modify the canned Dell report "Dell Warranty Expired" on the K1000, so that it also adds a LastSync column? Basically we want a report that shows us expired warranties, but we want to have the sync date to compare that too so we're only dealing with hardware that's still active. Any help would be appreciated. Thanks!

 

-Kurt

 

This is the code of the original report:

 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 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());

 

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

3

Here you go:

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, M.LAST_SYNC
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())
ORDER BY M.LAST_SYNC

If you are new to SQL queries, see if this might help:

http://www.itninja.com/blog/view/primer-for-writing-select-statement-queries-on-the-k1000-w-custom-sql-report-example

Hope that helps!

John

Answered 12/17/2012 by: jverbosk
Red Belt

Please log in to comment

Answers

1

Add LAST_SYNC to your SELECT statement. 

Answered 12/17/2012 by: dugullett
Red Belt

Please log in to comment
This content is currently hidden from public view.
Reason: Removed by member request
For more information, visit our FAQ's.

Answer this question or Comment on this question for clarity