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

Please log in to comment

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

Please log in to comment
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

Please log in to comment
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

Please log in to comment
Answer this question or Comment on this question for clarity