New to SQL, We are running Microsoft SQL for the server. I would like to write a script that will list software and the pc's they are installed on, excluding software that is installed on no machines, nor is the software labeled 'Approved Software' or 'Drivers and System Software'. The ideal is basically to generate a report of computers containing unapproved software. Near as I can tell I will have to do some joins on machine_software, software, software_label_jt, and label

help

1 Comment   [ + ] Show Comment

Comments

  • Just to be clear, you're intending to label all approved software as either "Approved Software" or "Drivers and System Software" and you want a report showing computers that have software installed that does not have one of those labels installed.
Please log in to comment

Answers

0

Here is a variation I use. I prefer it broken out like this because to me it looks cleaner. It can be modified to fit what you need.

SELECT DISPLAY_NAME, DISPLAY_VERSION,

GROUP_CONCAT(DISTINCT M.NAME SEPARATOR '\n') AS MACHINE_NAME,

GROUP_CONCAT(DISTINCT M.USER_LOGGED SEPARATOR '\n') AS User_Logged

FROM SOFTWARE S

LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON MSJT.SOFTWARE_ID = S.ID

LEFT JOIN MACHINE M ON M.ID = MSJT.MACHINE_ID

LEFT JOIN SOFTWARE_LABEL_JT SLJT ON SLJT.SOFTWARE_ID=S.ID

LEFT JOIN LABEL L ON L.ID=SLJT.LABEL_ID

WHERE (NOT S.IS_PATCH)

AND (L.NAME != 'APPROVED SOFTWARE'

OR L.NAME != 'DRIVERS AND SYSTEM SOFTWARE')

GROUP BY S.DISPLAY_NAME, M.NAME

ORDER BY S.DISPLAY_NAME
Answered 07/03/2013 by: dugullett
Red Belt

  • hmm I tried that and I got an error " the query does not contain the specified break field Software_title
    • I don't specify a break field in mine. I would remove the break.
Please log in to comment
Answer this question or Comment on this question for clarity