KACE Product Support

Hi,

I'm trying to create a report similar to the report "Dell Warranty Expires in the next 30 Days". But in my report I would like add OS Name and SP version and sort it by ship date and remove Service Label Code, Service Level Description, and Service Provider. Also prevent duplicate machine names from appearing.

I get close but I still get dups when I use the below syntax. I try to proceed further by removing and attemping to add what I want but it fails: 

SELECT M.NAME AS MACHINE_NAME,M.CS_MODEL AS MODEL, OS_NAME, SERVICE_PACK, DA.SERVICE_TAG, SHIP_DATE, M.USER_LOGGED AS LAST_LOGGED_IN_USER, DW.END_DATE AS EXPIRATION_DATE
FROM KBSYS.DELL_WARRANTY DW
LEFT JOIN KBSYS.DELL_ASSET DA ON (DW.SERVICE_TAG = DA.SERVICE_TAG)
LEFT JOIN MACHINE M ON (M.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG)
WHERE M.CS_MANUFACTURER LIKE '%dell%'
AND DA.DISABLED != 1
AND DW.END_DATE > NOW()
AND DW.END_DATE <= DATE_ADD(NOW(),INTERVAL 90 DAY)
AND DW.END_DATE = (SELECT MAX(END_DATE) FROM KBSYS.DELL_WARRANTY DW2 WHERE
ORDER BY SHIP_DATE;

The orignial syntax is this:

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 KBSYS.DELL_WARRANTY DW
LEFT JOIN KBSYS.DELL_ASSET DA ON (DW.SERVICE_TAG = DA.SERVICE_TAG)
LEFT 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 60 DAY)
AND DW.END_DATE = (SELECT MAX(END_DATE) FROM KBSYS.DELL_WARRANTY DW2 WHERE
DW2.SERVICE_TAG=DW.SERVICE_TAG AND DW2.SERVICE_LEVEL_CODE=DW.SERVICE_LEVEL_CODE);

Here is an image where i'm trying to get at:

Thanks much!!

Kirk