I am new to the reporting on the KBOX.  I am trying to get a report ran that will give me info on how old devices are.  Dell machines, which are 99% of our workstations, show a "Ship Date" and warranty info within the Hardware info area.  Can someone help me to create a report that shows this?  Thanks for your help!
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1
Here is the query that I use:

SELECT DISTINCT(MACHINE.NAME), 
MACHINE.OS_NAME, 
MACHINE.CS_MODEL, 
MACHINE.CS_MANUFACTURER, 
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

Note that our machines typically ship with two warranties, the normal and the extended. This report only shows the later date.
Answered 01/16/2015 by: chucksteel
Red Belt

  • Hey Chuck, thanks for the fast reply. So, I thought I might be able to copy and paste your query but no luck. I even typed it out but still get a syntax error. What might I be missing? I, again, apologize for my ignorance, I am very new to the KACE reporting tool. Thanks again.
    • Does it tell you what the syntax error is?
      • mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE MACHINE.CS_MANUFACTURER like 'Dell%' GROUP BY MACHINE.NAME ORDER BY "Shi' at line 14] in EXECUTE( "SELECT DISTINCT(MACHINE.NAME), MACHINE.OS_NAME, MACHINE.CS_MODEL, MACHINE.CS_MANUFACTURER, 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 WHERE MACHINE.CS_MANUFACTURER like 'Dell%' GROUP BY MACHINE.NAME ORDER BY "Ship Date", MACHINE.NAME LIMIT 0")
      • You repeated part of the statement, it should end after the first ORDER BY "Ship Date", MACHINE.NAME.
      • Fixed and Working. Thanks again. Not sure how I copied it twice but, I sure did.
Please log in to comment
Answer this question or Comment on this question for clarity