How do I define a stored query that lists all of the active requests that have been reported by members of a particular group of users or contacts?

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

Description:

When defining a stored query that lists all of the active requests that have been reported by members of a particular group of users or contact, the UUID for the group must be specified in the where clause of the stored query.

Solution:

When a Service Desk ticket such as an incident is logged, the ticket detail includes a field named "Reported by". The user or contact specified in this field may be a member of a particular group of users or contacts.

It is possible to create a stored query that, when run, results in a list of active requests that have been reported by any member of a particular group of users. The stored query can be associated with a Service Desk scoreboard node.

The definition of the query must include the UUID of the group.

Finding the UUID

To find the UUID of the group contact, query the database for the contact_uuid in the ca_contact table for the particular group.

For example, when the particular group name of interest is 'grp1', when the following SQL statement is run, the result is as shown:

SQL statement: select contact_uuid from ca_contact where last_name='grp1'
Result: 0x12D91967B6A3344EB69087A240A1F5BD

Creating the stored query

Next, when creating the stored query, specify the UUID in the where clause.

Note that the "Reported by" field maps to the log_agent attribute of the cr object.

Relative to the previous example, set the where clause to:

log_agent.[member]member_list.group IN U\'12D91967B6A3344EB69087A240A1F5BD\')

Sample Screenshots:

Figure 1 - Defining the stored query

Figure 1

Figure 2 - Incident Detail showing the "Reported by" field

Figure 2