Stored queries

Stored queries enable users to retrieve and display current information efficiently by using predefined search parameters. Depending on permissions, users can run predefined queries, modify predefined queries before running them, or store a personal list of modified queries and run a query from their list.

Administrators can use stored queries to display lists of specific records or populate dynamic display objects such as charts and marquees. They can add new queries and fine tune the out-of-box queries to decrease response time or implement best practices in their environment.

Stored query option definitions

Option key	Function	Definition
F1	Append using the & character	Appends an additional query to the end of your original query using the word AND to separate the two queries.
F2	Append using the | character	Appends an additional query to the end of your original query using the word OR to separate the two queries.
F3	Insert using the & character	Inserts an additional query at the beginning of your original query using the word AND to separate the two queries.
F4	Insert using the | character	Inserts an additional query at the beginning of your original query using the word OR to separate the two queries.

Add a stored query

You can add, edit, or run stored queries from the Query window, which is accessible from the form in Database Manager or from the Application search form. Data entered into the form is automatically entered into the Query window to form the query. Depending on your permissions, you can edit and run that query, or select a query from a list of stored queries.

In this example, a query is performed on a specific assignee for all contracts in Configuration Management. Once stored, operators or groups of operators can access the query and automatically generate a list of records without reentering the detailed search criteria.

To add a stored query:

1. Click Configuration Management > Contracts > Contracts.
2. Use the Fill function in the Assignee field to choose an assignee. For this example, Carlton Hulman was chosen.

3. Click the More Actions icon and choose Expert Search.

   The Query form opens. The new query is displayed in the Query field. For this example, the query for Carlton Hulman is displayed as follows: assignee#"Carlton.Hulman"

   Note In Service Manager, you can use field name information in field help to identify the field names you want to use in your stored queries. To display field name information in field help, make sure one of the following conditions is fulfilled:

   • In the Windows client, the “Show context-sensitive help debug information” preference is enabled.
   • In the Web client, “viewcontexthelp=true” is appended to the URL before you log in.

4. On the Query form, click Keys.

   Note: Do not modify the automatically-generated query string, assignee#"Carlton.Hulman", as it appears in the Query field.

5. On The Key Window (keylist form), type a key number to select the number of positions for the assignee.name key in the query. For example, type the number 3 to select 3 positions.

6. Click OK.

   The Sort Fields field in the Query Window is now modified to agree with the key definition you selected.

7. On the Query form, click Store to create the querystored file.

8. Fill in the following fields:

   • In the Access List field, type in the Query Groups or Operator Names that you want to have access to this query. If you want all users to have access, leave the list blank.
   • In the Name field, type a unique name for your query.
   • In the Description field, type an explanatory description.

9. Click Add.

   The following message confirms your entry was successful: Query added to querystored file.

10. Continue to further refine your search criteria, or click Search. You can also click any record in the list of returned records to view the details.

Update a stored query

You can add, edit, or run stored queries from the Query window, which is accessible from the form in Database Manager or from the Application search form. Data entered into the from is automatically entered into the Query window to form the query. Depending on your permissions, you can edit and run that query or select a query from a list of stored queries.

In this example, we update and save a stored query for Incident Management (the probsummary file).

To update a stored query:

1. Open the query window.

   • To open the query window from an Service Manager application:

     1. Open the application Search form. For this example, click Incident Management > Search Incidents.
     2. Open the More Actions menu.
     3. Select Expert Search.

   • To open the query window from a Database Manager search form:

     1. Open the form in Database Manager. For this example, type =probsummary in the Form field, and then press Enter.
     2. Open the More Actions menu.

     3. Select Advanced Search.

        The Query window opens.

2. Click Select to display the list of stored queries for the associated file.

3. Double-click a query to select it. For this example, select status.resolved.

   The Stored Query record opens.

4. Click Select to edit the query as desired. In this example, change the query to flag=true and problem.status<>"Resolved".

   Note In Service Manager, you can use field name information in field help to identify the field names you want to use in your stored queries. To display field name information in field help, make sure one of the following conditions is fulfilled:

   • In the Windows client, the “Show context-sensitive help debug information” preference is enabled.
   • In the Web client, “viewcontexthelp=true” is appended to the URL before you log in.
5. Specify any other controls you want.

6. Click Store to save your edits to the selected query.

   The Stored Query Maintenance form opens.

7. Give the query a unique Name. For this example, not.resolved.
8. Add a Description. For this example, type Unresolved records.

9. Click Add.

   Service Manager returns you to the Query window and displays the message: “Query added to querystored file”.

You can click Execute Search to run the saved query at this point, or click Back to exit.

Append stored queries

You can add, edit, or run stored queries from the Query window, which is accessible from the form in Database Manager or from the Application search form. Data entered into the from is automatically entered into the Query window to form the query. Depending on your permissions, you can edit and run that query or select a query from a list of stored queries.

Note: You must have the capability word QueryAdmin or query.stored.mod operator record to add or edit stored queries.

In this example, two stored queries for Incident Management (the probsummary file) are joined and saved.

To append stored queries:

1. To open the query window from a Service Manager application:

   1. Open the application Search form.

      For this example, click Incident Management > Search Incidents.

   2. Open the More Actions menu.

   3. Choose Expert Search.

      The query window opens.

2. To open the query window from a Database Manager search form:

   1. Open the form in Database Manager.

      For this example, type =probsummary in the Form field, and then press Enter.

   2. Open the More Actions menu.

   3. Choose Expert Search.

      The Query window opens.

3. Click Select to display the list of stored queries for the associated file, and then double-click a query to select it.

   For this example, select deadline. The Stored Query record opens.

4. Click Select.

   Service Manager returns you to the list of queries.

5. Select another query from the list.

   For this example, select pri.1. The Stored Query record opens with additional buttons on the toolbar.

6. Click one of these buttons to use this query in addition to the one you selected previously.
   • Click Append using & to add this query after the previous one using the and (&) operator.
   • Click Append using | to add this query after the previous one using the or (|) operator.
   • Click Insert using & to add this query before the previous one using the and (&) operator.
   • Click Insert using | to add this query before the previous one using the or (|) operator.

   For this example, click Append using &.

7. You can click Execute Search to run the query without saving it or click Store to save your modified query for future use.

   For this example, click Store. The Stored Query Maintenance form opens.

• The value in the Name field is that of the last appended or inserted stored query, pri.1.
• The value in the Query field is the new query you created. (flag=true and status#"DEADLINE" and problem.status~="Cleared" and flag=true and priority.code="1").

• For this example, enter the following information:
  • Give the query a unique Name. For this example, type pri.1&deadline.
  • Add a Description. For this example, type Priority 1 records in deadline alert.
  • Edit the Query, if necessary. For this example, delete the second and flag=true.
  • Specify any other controls you want.

• Click Add.

  Service Manager returns you to the Query window and displays the message: “Query added to querystored file”.

• You can click Execute Search to run the saved query at this point, or click Back to exit.

Run a stored query

Depending on your permissions, you can add, edit, or run stored queries from the Query window, which is accessible from the form in Database Manager or from the Application search form. Data entered into the from is automatically entered into the Query window to form the query. Depending on your permissions, you can edit and run that query, or select a query from a list of stored queries.

Example: Selecting and running a stored query.

In this example, a stored query for Incident Management (the probsummary file) is selected and run.

1. Open the query window.
   • To open the query window from a Service Manager application:
     1. Open the application Search form. For this example, click Incident Management > Search Incidents.
     2. Open the More Actions menu.
     3. Select Expert Search.
   • To open the query window from a Database Manager search form:
     1. Open the form in Database Manager. For this example, type =probsummary in the Form field, and then press Enter.
     2. Open the More Actions menu.
     3. Select Expert Search.

   The Query window opens.

2. Click Select to display the list of stored queries for the associated file.
3. Double-click a query to select it. For this example, select status.resolved. The Stored Query record opens.

4. Click Select.

   Service Manager returns you to the Query window.

5. Click Execute Search to run the stored query.

   Records matching the query display in a record list.

   • If only one record matches your search criteria, that record opens.
   • If no records match, the server displays the following message: No records found.

 

Related topics

Using stored queries in display objects
Menu option searches
Grant access to stored queries
Create stored queries from the Query Maintenance form