KACE Product Support

It depends on what the wizard report is lacking. A sample of the output you have and what you are trying to achieve would be helpful. - grayematter 10 years ago

A good example would be the built in Dell Warranty report by label. The way it presents the data is in 3 layers. I was hoping you can add items from different tables to the root line. - Zach_Lundberg 10 years ago

I totally agree. I'm trying to get a simple report showing Service Tag, System Name, and Warranty Start and End Date. The problem is that this information comes from DEVICE and DELL WARRANTY Topics. Apparently, when you add an additional topic, it does something to the layout which, as Zach noted above, makes no logical sense. - cottonakin 10 years ago

Cottonakin Attached you will find the exact report you need. I have built one in SQL, but not all of the technicians I work with know SQL so I want to make the Wizard work.

Option 1 will pull all computers. If you want to limit to a label use option 2.

Option 1:

SELECT DA.SERVICE_TAG, M.NAME AS System_Name, DW.START_DATE AS Warranty_Start_Date, DW.END_DATE AS Warranty_End_Date
FROM DELL_WARRANTY DW
JOIN DELL_ASSET DA ON (DW.SERVICE_TAG = DA.SERVICE_TAG)
JOIN MACHINE M ON (M.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG OR M.BIOS_SERIAL_NUMBER = DA.PARENT_SERVICE_TAG)
join MACHINE_LABEL_JT on M.ID = MACHINE_LABEL_JT.MACHINE_ID
WHERE M.BIOS_SERIAL_NUMBER!=''
AND DW.SERVICE_LEVEL_CODE like '%ND%'
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)
order by DW.END_DATE;

Option 2: Filter by Label (Change "WHERE MACHINE_LABEL_JT.LABEL_ID = 99" to the label ID you want to use. So it would state "...LABEL_ID = Your_Label_ID_Number"

SELECT DA.SERVICE_TAG, M.NAME AS System_Name, DW.START_DATE AS Warranty_Start_Date, DW.END_DATE AS Warranty_End_Date
FROM DELL_WARRANTY DW
JOIN DELL_ASSET DA ON (DW.SERVICE_TAG = DA.SERVICE_TAG)
JOIN MACHINE M ON (M.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG OR M.BIOS_SERIAL_NUMBER = DA.PARENT_SERVICE_TAG)
join MACHINE_LABEL_JT on M.ID = MACHINE_LABEL_JT.MACHINE_ID
WHERE MACHINE_LABEL_JT.LABEL_ID = 99
AND M.BIOS_SERIAL_NUMBER!=''
AND DW.SERVICE_LEVEL_CODE like '%ND%'
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)
order by DW.END_DATE; - Zach_Lundberg 10 years ago

I see what you are saying. I see that in my version (5.5), the 3-tier report does not allow SQL export. I would get the individual queries and link the SQL statements with appropriate joins. You can use a query browser such as MySQL Workbench to test your queries and browse the KACE database tables. - grayematter 10 years ago