Inventory Report needs to be modified, need suggestions
We have a report for each of our locations to list the machines per location that have Microsoft Office installed (my understanding but I could be off base on what exactly it's returning). We are ruling out other products from Microsoft; however, our kbox has been "hosing" up for the last few days requiring a reboot numerous times a day. Worked with tech support and found it was the reports peaking out the CPU and they advised they needed to be modified to be more efficient. I'm including the code and looking for suggestions. Tech support said the Display_Name NOT LIKE is probably the culprit. This is our first time working with MYSQL so I'm sure there is lots of room for improvement.
select DISTINCT LABEL.NAME as Location, DISPLAY_NAME AS Software, DISPLAY_VERSION AS Version, MACHINE.NAME AS Computer, MACHINE.USER_FULLNAME AS UserName, MACHINE.USER AS UserID, USER.LOCATION AS Department
from (SOFTWARE, MACHINE_SOFTWARE_JT, MACHINE, MACHINE_LABEL_JT, LABEL)
left join USER on MACHINE.USER = USER.USER_NAME
where
SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID and MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID AND MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID AND MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
and not IS_PATCH
and PUBLISHER Like '%Microsoft%'
and DISPLAY_NAME like '%office%'
and DISPLAY_NAME NOT LIKE '%compatibility%'
and DISPLAY_NAME NOT LIKE '%assemblies%'
and DISPLAY_NAME NOT LIKE '%components%'
and DISPLAY_NAME NOT LIKE '%access%'
and DISPLAY_NAME NOT LIKE '%mui%'
and DISPLAY_NAME NOT LIKE '%validation%'
and DISPLAY_NAME NOT LIKE '%add-in%'
and DISPLAY_NAME NOT LIKE '%live meeting%'
and DISPLAY_NAME NOT LIKE '%components%'
and DISPLAY_NAME NOT LIKE '%outlook%'
and DISPLAY_NAME NOT LIKE '%trial%'
and DISPLAY_NAME NOT LIKE '%proof%'
and DISPLAY_NAME NOT LIKE '%Visio%'
and DISPLAY_NAME NOT LIKE '%project%'
and DISPLAY_NAME NOT LIKE '%Toolbar%'
and DISPLAY_NAME NOT LIKE '%Language%'
and DISPLAY_NAME NOT LIKE '%accounting%'
and DISPLAY_NAME NOT LIKE '%click-to-run%'
and DISPLAY_NAME NOT LIKE '%converter%'
and DISPLAY_NAME NOT LIKE '%excel%'
and DISPLAY_NAME NOT LIKE '%frontpage%'
and DISPLAY_NAME NOT LIKE '%onenote%'
and DISPLAY_NAME NOT LIKE '%powerpoint%'
and DISPLAY_NAME NOT LIKE '%publisher%'
and DISPLAY_NAME NOT LIKE '%sounds%'
and DISPLAY_NAME NOT LIKE '%viewer%'
and DISPLAY_NAME NOT LIKE '%developer%'
and DISPLAY_NAME NOT LIKE '%word%'
and DISPLAY_NAME NOT LIKE '%visual studio%'
and DISPLAY_NAME NOT LIKE '%tools%'
and DISPLAY_NAME NOT LIKE '%compatib%'
and DISPLAY_NAME NOT LIKE '%animation%'
and DISPLAY_NAME NOT LIKE '%media%'
and DISPLAY_NAME NOT LIKE '%imess (%'
and DISPLAY_NAME NOT LIKE '%activation%'
and DISPLAY_NAME NOT LIKE '%custom ui%'
and DISPLAY_NAME NOT LIKE '%sr-1 disk%'
and DISPLAY_NAME NOT LIKE '%resource%'
and DISPLAY_NAME NOT LIKE '%ime (%'
and DISPLAY_NAME NOT LIKE '%web folder%'
and
(LABEL.NAME LIKE 'Computers - apcom%'
or LABEL.NAME LIKE 'Computers - Ashland City%'
or LABEL.NAME LIKE 'Computers - domain controllers%'
or LABEL.NAME LIKE 'Computers - unassigned%'
or LABEL.NAME LIKE 'Computers - takagi%'
or LABEL.NAME LIKE 'Computers - stratford%'
or LABEL.NAME LIKE 'Computers - renton%')
order by LABEL.NAME, USER.LOCATION, SOFTWARE.DISPLAY_NAME ASC
Any thoughts are greatly appreciated.
Answers (2)
I would check your software inventory and see what your version of Office is listed as. Our version is "Microsoft Office Professional Plus 2010". For this report I would then use DISPLAY_NAME = "Microsoft Office Professional Plus 2010". If you have multiple versions of Office in your environment then use:
and (DISPLAY_NAME = "Microsoft Office Professional Plus 2010"
or DISPLAY_NAME = "Other Microsoft Office version"
or DISPLAY_NAME = "Another version")
Comments:
-
I've modified the report and it returns when I use this:
select LABEL.NAME as Location, DISPLAY_NAME as Software, DISPLAY_VERSION as Version, MACHINE.NAME AS Computer, MACHINE.USER_FULLNAME AS UserName,
MACHINE.USER AS UserID/*, u4.LOCATION AS Department*/
from (SOFTWARE, MACHINE_SOFTWARE_JT, MACHINE,
MACHINE_LABEL_JT, LABEL)
/*LEFT JOIN USER u4 on MACHINE.USER_FULLNAME = u4.FULL_NAME */
where
LABEL.NAME LIKE '%Computers%' AND LABEL.NAME NOT LIKE '%Server%' AND
MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID AND
MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
AND
MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID and
MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
and SOFTWARE.DISPLAY_NAME LIKE '%Microsoft Office%' and SOFTWARE.DISPLAY_NAME NOT LIKE '%Microsoft Office Front%'
and (SOFTWARE.DISPLAY_NAME NOT RLIKE 'Image|Disc|EMC|Resource|Accounting|Integration|Toolbar|Update|Service|Activation|Custom|Web|Access|Excel|File|Groove|InfoPath|OneNote|Designer|Outlook|Powerpoint|Proof|Publisher|Shared|Visio|Word|Primary|Live|Add-in|Project|Components|Click|Sounds|Converter|System')
and LABEL.TYPE !='hidden'
order by LABEL.NAME, SOFTWARE.DISPLAY_NAME/*, u4.LOCATION, u4.FULL_NAME LIMIT 0,2 */
It does not return back when I remove the comments. I've tried left join, right join, join, and including the USER table in the select statement but everything runs until it either stops the system or disconnects my query browser/work bench from the server which causes us to have to reboot Kace to stop the mysqld process. - MAHull 12 years ago -
I would suggest trimming down your SQL to the bare minimum and slowly adding things. Start with a query that returns everything (e.g. all versions of office), then add the NOT LIKE statements to get just the Office versions that you want. Then add the order by statement. Then add the join to the user table, etc.
I would do all of this inside MySQL Workbench because it does have a button to stop the query if it starts running too long.
I would also suggest that instead of your DISPLAY_NAME LIKE '%Microsoft Office%' that you choose something more specific. Having three LIKE lines should be more efficient than using RLIKE with a dozen choices in it. That could by why your query is to slow. - chucksteel 12 years ago -
We've gone round and round with tech support on this issue. They now are reporting the report needs to be optimized and run after hours. The code was used from the report generator within Kace and we added the search criteria. I've been working with the workbench developing/rearranging the code. I can get it to run with workbench in less than 20 seconds but when plugged into Kace, it will run then not run. I changed the NOT LIKE's to NOT RLIKE since tech support first suggested the NOT LIKE's were the bulk of the problem.
Thanks for the suggestions and I will continue to work on getting the proper order of the flow. - MAHull 12 years ago
