/build/static/layout/Breadcrumb_cap_w.png

Modifying KACE K1000 Dell Warranty Expired Report

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

Answers (2)

Posted by: jverbosk 11 years ago
Red Belt
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

Posted by: dugullett 11 years ago
Red Belt
1

Add LAST_SYNC to your SELECT statement. 

Don't be a Stranger!

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

Sign up! or login

View more:

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