Reporting customization in K1000
I'm just wondering if it's possible for Kace to generate a report showing total number of computers in each lab, together with computer warranty information.
Hope someone can help me.
Hope someone can help me.
2 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
chucksteel
6 years ago
I recommend placing your lab labels in a group, e.g. "Computer Labs". This report will then return what you requested:
SELECT LABEL.NAME as "Lab", COUNT(MACHINE.NAME) as Count,
MAX(DW.END_DATE) AS "Warranty End Date"
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
JOIN MACHINE_LABEL_JT on MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID
JOIN LABEL on LABEL.ID = MACHINE_LABEL_JT.LABEL_ID
JOIN LABEL_LABEL_JT on LABEL.ID = LABEL_LABEL_JT.CHILD_LABEL_ID
JOIN LABEL PARENT on PARENT.ID = LABEL_LABEL_JT.LABEL_ID
WHERE MACHINE.CS_MANUFACTURER like 'Dell%'
and PARENT.NAME = "Computer Labs"
GROUP BY LABEL.NAME
ORDER BY LABEL.NAME
If you use a different name for the label group, replace "Computer Labs" with "Your Label". The advantage to this method is that you can modify and add labels without needing to update the report. You can also create label groups for other groups. e.g. departments, locations, etc. And create reports for those groups, as well.
Comments:
-
Thank you so much for sharing the code with me. I will give it a try and let you know. - kzhangmit 6 years ago
Also, if you are reporting the count of computers in a lab, a list of all of the warranty information will have to be in one column, which will be pretty messy. You could report on the minimum warranty date and maximum warranty date in each group, however. - chucksteel 6 years ago