KACE Warranty Report - Filtered by Service Level
Hi guys,
Is there a way I can filter my warranty report to just show when Client Gold Support / ProSupport expired, instead of the last service level. This is what I have for my SQL report.
SELECT DISTINCT(MACHINE.NAME),
MACHINE.USER_FULLNAME,
MACHINE.OS_NAME,
MACHINE.CS_MODEL,
MACHINE.CS_MANUFACTURER,
MACHINE.LAST_SYNC,
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
Not sure what I need to add to it.
Thanks for the help,
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
chucksteel
7 years ago
You need to change the MAX(DW.END_DATE) to just DW.END_DATE and add a statement to the WHERE clause to just include Gold support in the description:
SELECT DISTINCT(MACHINE.NAME),
MACHINE.USER_FULLNAME,
MACHINE.OS_NAME,
MACHINE.CS_MODEL,
MACHINE.CS_MANUFACTURER,
MACHINE.LAST_SYNC,
DA.SHIP_DATE AS "Ship Date",
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%'
and SERVICE_LEVEL_DESCRIPTION like "%Gold%"
GROUP BY MACHINE.NAME
ORDER BY "Ship Date", MACHINE.NAME
Note that I just include descriptions that contain the word Gold, that seems to be sufficient in my environment.
