/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Comprehensive Dell warranty report?

09/16/2015 2898 views
I like the format of the "Dell warranty expiring in 365 days". How would I tweak that to simply list all Dell products and their corresponding warranty expiration regardless of when it expires/expired?

I THINK I have it correct... The report runs at least and LOOKS like it is correct. 

Would I simply take this code and remove everything after the line?:
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.SERVICE_PROVIDER, 
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.SERVICE_TAG OR M.BIOS_SERIAL_NUMBER = DA.PARENT_SERVICE_TAG) 
WHERE M.CS_MANUFACTURER LIKE '%dell%'  
AND M.BIOS_SERIAL_NUMBER!=''
AND DA.DISABLED != 1
---------------------------------------------------
AND DW.END_DATE > NOW() 
AND DW.END_DATE <= DATE_ADD(NOW(),INTERVAL 365 DAY)
AND DW.END_DATE = (SELECT MAX(END_DATE) FROM DELL_WARRANTY DW2 WHERE DW2.SERVICE_TAG=DW.SERVICE_TAG AND DW2.SERVICE_LEVEL_CODE=DW.SERVICE_LEVEL_CODE);

And what does "AND DA.DISABLED !=1" do? 
0 Comments   [ + ] Show comments

Comments


All Answers

1
Yes, that should return warranty information for all computers. If your machines have extended warranties then this will result in multiple rows per computer (there will be a row per warranty). Here is the report that I use to show the maximum warranty date and ship date.
SELECT DISTINCT(MACHINE.NAME), 
MACHINE.OS_NAME, 
MACHINE.CS_MODEL, 
MACHINE.CS_MANUFACTURER, 
DA.SHIP_DATE AS "Ship Date",
MAX(DW.END_DATE) AS "Warranty End Date",
DW.SERVICE_LEVEL_DESCRIPTION as "Service Level"
FROM MACHINE
LEFT JOIN DELL_ASSET DA on MACHINE.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG
LEFT JOIN DELL_WARRANTY DW on MACHINE.BIOS_SERIAL_NUMBER = DW.SERVICE_TAG
WHERE MACHINE.CS_MANUFACTURER like 'Dell%'
GROUP BY MACHINE.NAME
ORDER BY "Ship Date", MACHINE.NAME

Answered 09/16/2015 by: chucksteel
Red Belt

1
>And what does "AND DA.DISABLED !=1" do?
I know nothing about KBoxes but the SQL is pretty standard.

'DA' is the name of a table in the database. 'DISABLED' is a column in that table. '!=' means 'not equal to'

So, that part of the query says 'any record where the 'DISABLED' column in the table named 'DA' has any value that's not 1.
Answered 09/17/2015 by: VBScab
Red Belt

1
Computers with DISABLED = 1 don't have any warranty information listed. If you view them in the Inventory they will say there is no warranty information available and there is a refresh button to try and fetch it.
Answered 09/17/2015 by: chucksteel
Red Belt

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