/build/static/layout/Breadcrumb_cap_w.png

Custom Reporting

Hello,

I'm Fairly new to the Kbox still and I need to have a report done Right away for Meaningful Use for our Hospital.  So i'm looking for a report that will give me Computer Name/ Mac Address/  IP/ Last User Logged in/ Manufacturer/ System Model.  I need to also have the report break them out as to what Label they are in.  So i don't have to go through the report and try to figure out where the computers are located.  Any kind of help would be great.


0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
SELECT M.NAME AS SYSTEM_NAME,IP, MAC,CS_MANUFACTURER AS Manufacturer,CS_MODEL as Model, GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR '\n') AS 'LABEL NAMES' FROM MACHINE M LEFT JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = M.ID) LEFT JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID) GROUP BY M.ID ORDER BY SYSTEM_NAME
Posted by: dugullett 11 years ago
Red Belt
5
This will list what you need with the labels associated.
 
SELECT M.NAME AS SYSTEM_NAME,IP, MAC,CS_MANUFACTURER AS Manufacturer,CS_MODEL as Model,
GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR '\n') AS 'LABEL NAMES'
FROM MACHINE M
LEFT JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = M.ID)
LEFT JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID)
GROUP BY M.ID
ORDER BY SYSTEM_NAME
 
If you are looking for specific labels you can use this.
 
SELECT M.NAME AS SYSTEM_NAME,IP, MAC,CS_MANUFACTURER AS Manufacturer,CS_MODEL as Model,
GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR '\n') AS 'LABEL NAMES'
FROM MACHINE M
LEFT JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = M.ID)
LEFT JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID)
#ENTER LABEL NAME HERE IN BETWEEN THE '% %'
WHERE LABEL.NAME LIKE '%LABEL_NAME%'
GROUP BY M.ID
ORDER BY SYSTEM_NAME

Comments:
  • Thank you very much this worked perfectly and saved me a weeks worth of work. - bkoldy 11 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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