Comprehensive Dell warranty report?
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?:
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
Answers (3)
Please log in to answer
Posted by:
chucksteel
9 years ago
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
Posted by:
anonymous_9363
9 years ago
>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.
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.
Posted by:
chucksteel
9 years ago
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.
