How to create a stored query that shows all incidents for which the last update was not made by a member of a particular group

Products

• CA Service Desk Manager
• CA Service Management - Service Desk Manager

Issue/Introduction

An example of a stored query that shows all incidents for which the last update was NOT made by a member of a particular group is as follows:

Type: Request

Where clause: (type = \'I\') AND (active=1) AND NOT (last_mod_by.[member]member_list.group IN U\'97A22CEE025B8D479C05DA528DE2C0CE\')

Note that the value provided for U need to be pre-determined.

Environment

Release: 12.6 or higher
Component:  CA Service Desk Manager

Resolution

Follow these steps to create and define the stored query:

1. Log in as ServiceDesk and set your role to the Administrator role.
   
   
2. Run a command similar to the following:
   pdm_extract -f "select id from ca_contact where last_name='grp1' and inactive=0"
   Note: In the above command, grp1 is the group you want to exclude.
   Results are similar to: { "97A22CEE025B8D479C05DA528DE2C0CE" }
   
   
3. Click on the Administration tab
   
   
4. Navigate to Service Desk, Application Data, Stored Queries
   
   
5. In the Stored Query List, click on Create New
   
   
6. Use the results from step#2 to enter the stored query definition. For example, enter:
   
   Type
   Request
   Description
   
   all incidents for which the last update was not made by a member of grp1
   
   Where Clause
   
   (type = \'I\') AND (active=1) AND NOT (last_mod_by.[member]member_list.group IN U\'97A22CEE025B8D479C05DA528DE2C0CE\')
   
   Figure 1: Stored query example