Hello,

To preface, I'm brand new to KACE and brand new to using SQL to make reports.  I'm looking to create a report that displays only machines applied to a specific label.  In my case the label name is "WRK_ACTIVE".  My issue with the reports that exist, is that they all display multiple lines for each type of support/warranty that the machines have.  I did find the below code on this forum which does give me just one line of detail, but I can't seem to add the write code to make sure it only prints for the label I want.  Below is what I'm using (I've tried to add L.NAME = 'WRK_ACTIVE in the appropriate section but it doesn't work for me).  Any help or links to other reports would be appreciated.

SELECT DISTINCT M.NAME AS MACHINE_NAME,M.CS_MODEL AS MODEL, OS_NAME, DA.SERVICE_TAG, DA.SHIP_DATE,

M.USER_LOGGED AS LAST_LOGGED_IN_USER, DW.END_DATE AS EXPIRATION_DATE

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 MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID) 
LEFT JOIN LABEL L ON (ML.LABEL_ID = L.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)
ORDER BY EXPIRATION_DATE, DW.SERVICE_LEVEL_DESCRIPTION
2 Comments   [ + ] Show Comments

Comments

  • Sorry... I should have mentioned the report I'm looking to print just shows warranty information :-)
  • Sorry, I should have mention this is for a Warranty Listing on specific machines.
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity

Share