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

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

3

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)
 

 

Answered 02/13/2013 by: dugullett
Red Belt

  • 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.
    • Awesome, this worked perfectly. Thanks for the quick response.
Please log in to comment
Answer this question or Comment on this question for clarity