Reporting on only a specific Label
I have the following report that shows all of the information that I need it to include, but I'm completely at a loss for how I can alter the SQL query to only display the information of the machines that have a specific Label. How would I alter it so that instead of showing the information of every machine in my inventory, it only shows the information of machines with a specific Label?
_______________________
SELECT M.NAME as MACHINE_NAME,
DATE_FORMAT(DA.SHIP_DATE,'%Y/%m/%d') as PURCHASE_DATE,
M.IP as IP_ADDRESS,
M.OS_NAME as OS_NAME,
M.SERVICE_PACK as OS_SERVICE_PACK,
M.BIOS_SERIAL_NUMBER as SERIAL_NUMBER,
MD.NAME as DISKS,
M.RAM_TOTAL as MEMORY,
M.PROCESSORS as PROCESSORS,
GROUP_CONCAT(DISTINCT DW.END_DATE ORDER BY 1 SEPARATOR '***') as WARRANTY_END,
GROUP_CONCAT(DISTINCT DW.SERVICE_LEVEL_DESCRIPTION ORDER BY 1 SEPARATOR '***') as SERVICE_LEVEL,
GROUP_CONCAT(DISTINCT DW.SERVICE_PROVIDER ORDER BY 1 SEPARATOR '***') as SERVICE_PROVIDER
FROM MACHINE M
LEFT JOIN KBSYS.DELL_ASSET DA on (DA.SERVICE_TAG = M.BIOS_SERIAL_NUMBER)
LEFT JOIN MACHINE_DISKS MD on (MD.ID = M.ID)
LEFT JOIN KBSYS.DELL_WARRANTY DW on (DW.SERVICE_TAG = DA.SERVICE_TAG)
LEFT JOIN MACHINE_SOFTWARE_JT MS on (MS.MACHINE_ID = M.ID)
LEFT JOIN SOFTWARE S on (S.ID = MS.SOFTWARE_ID)
LEFT JOIN MACHINE_NICS MN on (MN.ID = M.ID)
WHERE NOT S.IS_PATCH
AND DISPLAY_NAME rlike 'microsoft|sql'
GROUP BY MACHINE_NAME
ORDER BY M.NAME
Answers (1)
You need to add a JOIN for the label table. Add this in your JOIN area.
LEFT JOIN MACHINE_LABEL_JT MLJT ON MLJT.MACHINE_ID = M.ID LEFT JOIN LABEL L ON L.ID = MLJT.LABEL_ID
and then add this to your WHERE....
WHERE L.NAME LIKE '%<ENTER_LABEL_NAME>%' AND (NOT S.IS_PATCH)
Comments:
-
The %s are wildcards. If you want to be more specific you can use this.
WHERE L.NAME = '<ENTER_LABEL_NAME>'
I would also suggest changing your rlike to remove Microsoft. Since security patches reside in this table it would basically show every machine. - dugullett 11 years ago-
Awesome, this worked perfectly. Thanks for the quick response. - RGaspari 11 years ago
