KACE Product Support

Here's a few versions for you. 

The first does what you asked for (with a few more columns I find useful in my own environment). 

The second one is sorted and grouped by machine name, which is useful when a machine has more than 1 warranty tagged to it (fairly common in my environment). 

The third one only pulls warranties for systems in the "server" label group (an LDAP label I have setup, but you could use any type).  Just change the next to the last line from "%server%" to whatever machine label you want to target and it should work (thanks to dchristian for the JOIN statements targetting a label in that one - the built-in report's statements for doing the same thing weren't working well for me in this report, probably due to my own MySQL ignorance).

To create any of these, go to Reporting > Reports > Choose Action > Add New SQL Report and populate it with this info and then hit Save.

Hope that helps!

John

_______________________________________

*Title*
Warranties by Expiration Date

*Category:
Warranty (Custom)

*Description*
Machine warranty report (sorted by exipration date ascending)

SELECT DISTINCT M.NAME AS MACHINE_NAME, M.CS_MODEL AS MODEL, DA.SERVICE_TAG, DW.SERVICE_LEVEL_DESCRIPTION, DW.SERVICE_PROVIDER, DW.END_DATE AS EXPIRATION_DATE 
FROM KBSYS.DELL_WARRANTY  DW
JOIN KBSYS.DELL_ASSET DA ON (DW.SERVICE_TAG = DA.SERVICE_TAG)
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
ORDER BY EXPIRATION_DATE, DW.SERVICE_LEVEL_DESCRIPTION

Show Line Number Column: x
_______________________________________

*Title*
Warranties by Machine

*Category:
Warranty (Custom)

*Description*
Machine warranty report (sorted by machine

SELECT DISTINCT M.NAME AS MACHINE_NAME, M.CS_MODEL AS MODEL, DA.SERVICE_TAG, DW.SERVICE_LEVEL_DESCRIPTION, DW.SERVICE_PROVIDER, DW.END_DATE AS EXPIRATION_DATE 
FROM KBSYS.DELL_WARRANTY  DW
JOIN KBSYS.DELL_ASSET DA ON (DW.SERVICE_TAG = DA.SERVICE_TAG)
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
ORDER BY MACHINE_NAME, EXPIRATION_DATE DESC, DW.SERVICE_LEVEL_DESCRIPTION

*Break on Columns*
MACHINE_NAME

Show Line Number Column: x

_______________________________________

*Title*
Warranties for Server Label

*Category:
Warranty (Custom)

*Description*
Server warranty report (only pulls machines in "server" label group)

*SQL Select Statement*
SELECT DISTINCT M.NAME AS MACHINE_NAME, M.CS_MODEL AS MODEL, DA.SERVICE_TAG, DW.SERVICE_LEVEL_DESCRIPTION, DW.SERVICE_PROVIDER, DW.END_DATE AS EXPIRATION_DATE 
FROM KBSYS.DELL_WARRANTY  DW
JOIN KBSYS.DELL_ASSET DA ON (DW.SERVICE_TAG = DA.SERVICE_TAG)
JOIN MACHINE M ON (M.BIOS_SERIAL_NUMBER = DA.PARENT_SERVICE_TAG OR M.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG)
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE M.CS_MANUFACTURER LIKE '%dell%'
AND M.BIOS_SERIAL_NUMBER!=''
AND DA.DISABLED != 1
AND L.NAME LIKE '%server%'
ORDER BY MACHINE_NAME, EXPIRATION_DATE DESC, DW.SERVICE_LEVEL_DESCRIPTION

*Break on Columns*
MACHINE_NAME

Show Line Number Column: x
_______________________________________