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
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
Please log in to answer
Posted by:
dugullett
12 years ago
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 12 years ago
