How to find what offerings are being requested by users and the volume of these requests with SQL queries?

Document ID : KB000009399
Last Modified Date : 14/02/2018
Show Technical Document Details
Introduction:

For administrative or managerial purposes, knowing what is being requested and with what frequency said offerings are being requested is crucial to better streamline an environment and check the usage of offerings by individuals in an organization.

Environment:
Windows
Instructions:

The following SQL statements can be executed from query analyzer in both SQL 2000 and SQL 2005:

To know the total number of requested offerings by or for a single user:

SELECT 
req_for_user_id as UserID, 
count(request_id) as TotalRequests 
FROM 
usm_request 
group by 
req_for_user_id 
order by 
req_for_user_id asc

To know what type of offerings are being requested by or for a user:

SELECT 
req_for_user_id as UserID, 
name as ServiceName, 
count(request_id) as TotalRequests 
FROM
usm_request 
group by 
req_for_user_id, 
name 
order by 
req_for_user_id asc