/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Questions on creating a KACE custom report.

03/21/2017 1132 views
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

Comments


All Answers

0
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.

Answered 03/22/2017 by: chucksteel
Red Belt

0
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...")

Answered 03/22/2017 by: JasonEgg
Red Belt

 
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