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

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

Description:

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.

Solution:

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

    Figure 1