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.

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
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

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
Answered 08/22/2012 by: dugullett
Red Belt

  • Thank you very much this worked perfectly and saved me a weeks worth of work.
Please log in to comment
Answer this question or Comment on this question for clarity