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

Products

• CA Service Catalog
• CA Service Management - Asset Portfolio Management
• CA Service Management - Service Desk Manager

Issue/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

Resolution

The following SQL statements can be executed 

 

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