KACE Dell Warranty without Digital Support and max date
Hello - I've been able to get an SQL report that will spit out the max warranty date but sometimes that max date is the dell digital support which I don't want. I can't seem to figure out how to add the "not like %digital% or whatever I need. Thank you for any help.
SELECT DISTINCT M.NAME AS MACHINE_NAME
,M.CS_MODEL AS MODEL
, M.USER_LOGGED AS LAST_LOGGED_IN_USER
, DW.END_DATE AS EXPIRATION_DATE
, A20.NAME AS DEPT
FROM DELL_WARRANTY DW
LEFT JOIN 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)
LEFT JOIN ASSET ON ASSET.MAPPED_ID = M.ID AND ASSET.ASSET_TYPE_ID=5
LEFT JOIN ASSET_ASSOCIATION J20 ON J20.ASSET_ID = ASSET.ID AND J20.ASSET_FIELD_ID=20
LEFT JOIN ASSET A20 ON A20.ID = J20.ASSOCIATED_ASSET_ID
WHERE M.CS_MANUFACTURER LIKE '%dell%'
AND M.BIOS_SERIAL_NUMBER !=''
AND DA.DISABLED != 1
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)