Questions on creating a KACE custom report.
Hey guys,
Im looking to create another Dell Warranty report in KACE, with some custom columns.
In particular, I am looking to see what Dell Warranties are expiring by quarter, to help forecast costs etc.
So I need four reports, that show when a warranty expires by date. I just cant for the life of me find what data objects these are or if these is even possible, any help is appreciated.
I need to find out what Dell Warranties are expiring in these date ranges:
(Q1): January 1, 2017 - March 31, 2017
(Q2): April 1,2017 - June 30, 2017
(Q3): July 1, 2017 - September 30, 2017
(Q4): October 1, 2017 - December 31, 2017
Thanks in advance!!
0 Comments
[ + ] Show comments
Answers (2)
Please log in to answer
Posted by:
chucksteel
7 years ago
Here is a basic report that will show the quarter when the warranty will expire for each machine:
SELECT DISTINCT(MACHINE.NAME),
concat("Q", QUARTER(MAX(DW.END_DATE)), " ", YEAR(MAX(DW.END_DATE))) AS "Quarter",
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
Note that I concatenated the quarter and year to make it look nicer. The purchase and warranty data is stored in the DELL_ASSET and DELL_WARRANTY tables if you want to browse those for more custom reporting.
Posted by:
JasonEgg
7 years ago
Chuck's solution is a bit cleaner than mine, but here it is:
SELECT MACHINE.ID, MACHINE.NAME, MAX(DELL_WARRANTY.END_DATE) AS EXPIRATION_DATE,
CASE
WHEN MAX(DELL_WARRANTY.END_DATE) BETWEEN CAST('2017-01-01' AS DATE) AND CAST('2017-03-31' AS DATE)
THEN 'Q1'
WHEN MAX(DELL_WARRANTY.END_DATE) BETWEEN CAST('2017-04-01' AS DATE) AND CAST('2017-06-30' AS DATE)
THEN 'Q2'
WHEN MAX(DELL_WARRANTY.END_DATE) BETWEEN CAST('2017-07-01' AS DATE) AND CAST('2017-09-30' AS DATE)
THEN 'Q3'
WHEN MAX(DELL_WARRANTY.END_DATE) BETWEEN CAST('2017-10-01' AS DATE) AND CAST('2017-12-31' AS DATE)
THEN 'Q4'
ELSE 'other'
END AS 'Expiration Quarter'
FROM MACHINE
JOIN DELL_WARRANTY ON MACHINE.BIOS_SERIAL_NUMBER = DELL_WARRANTY.SERVICE_TAG
GROUP BY MACHINE.ID
HAVING `Expiration Quarter` != 'other'
ORDER BY `Expiration Quarter` ASC
You can view all computers' information by removing the second to last line (starts "HAVING...")
