/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


KACE Warranty Report - Filtered by Service Level

06/13/2017 726 views
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

Comments


All Answers

1
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.
Answered 06/14/2017 by: chucksteel
Red Belt

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ