Certain Canned Reports run slow or time out due to inefficient scoping in SQL queries

Products

• IT Management Suite

Issue/Introduction

The following canned reports are known to time out or run very slowly when sifting large database.

All Cost Types
All Purchase Requests
All Purchase Requests WP
Assets by Owner's Department
Count of Computers by Operating System
Count of Logical Disk by File System Type
Count of Software Packages Installed
Incomplete Procurement Items
Primary User by Computer
Primary User on Multiple Computers
Requestors - parameter
Suppliers - parameter
Total User Accounts by Computer
User Profiles Across Multiple Machines

Environment

SMP 8.5

Cause

The security scoping process in the SQL behind the reports is not optimized. They use inline select statements instead of a more efficient JOIN.

Resolution

1. Extract the zip file attached to this article to a local folder
2. Start the SMP Console and go to “Reports > All Reports”
3. Right click on a folder of your choice (or the root folder) and select “Import”
4. Browse to the saved file and import it.  A folder named “Revised Reports” will be created and inside of it will be the reports I revised.
5. Run the SQL in the file “MoveReportsToCorrectFolders.sql” against your database.

When your refresh the console the “Revised Reports” folder should be empty.  Go ahead and delete it.