Generate a K1000 report of computers containing unapproved software.
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
-
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. - chucksteel 11 years ago
-
That is correct - baddog1229 11 years ago
Answers (1)
Please log in to answer
Posted by:
dugullett
11 years ago
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
Comments:
-
hmm I tried that and I got an error " the query does not contain the specified break field Software_title - baddog1229 11 years ago
-
I don't specify a break field in mine. I would remove the break. - dugullett 11 years ago
