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
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share