|< Day Day Up >|
In an active RT instance, with thousands of tickets, finding the ones you are interested in can quickly become a chore. Luckily, all of the ticket metadata we've been harping about is searchable. To start a new search, click on the Tickets link in the main menu. This takes you to an empty ticket search form, as shown in Figure 3-12, a very flexible and powerful search tool.
The Add Criteria section is less complicated than it looks. Each criterion you want to add is represented by a row of form elements. For example, the selected options in Figure 3-13 search for tickets where the queue is General, the status is open, the owner is Nobody, and the priority is greater than 90.
When you click the Add button, it adds all the criteria in Figure 3-13 to the query at once. The next screen clears the Add Criteria section, but it lists the criteria in the Query section toward the upper right of the page, as shown in Figure 3-14.
The query builder also allows you to choose the fields you want to display when listing your search results. The default display shows many fields, but you can limit the display to only those fields you want in the Display Columns box at the bottom of the search form page, shown in Figure 3-15.
Under Show Columns you can remove unwanted fields from the display or move a field forward "^" or back "^" in the display order. Under Add Columns you can
Figure 3-12. Search form
Figure 3-13. Search for tickets
Figure 3-14. Constructed query
Figure 3-15. Display Columns
select a new column to add to the display, set a link target, title text, and text style for it under Format, then add it to the display with the "->" button. At Order by, select which column to sort the results and whether to sort them ascending or descending. At Rows per page, select how many results to display at a time.
Once you've created your query and chosen the fields you want to display, execute the search by clicking the Search button at the bottom of the form.
3.10.1. Understanding TicketSQL
Ticket searches in RT use a special-purpose query language called TicketSQL. The graphical query builder above generates TicketSQL for the search you want to perform. The Advanced link in the Tickets menu lets you write your own TicketSQL query from scratch.
If you generate a query with the query builder, and then click Advanced, the text area will be populated with your query. This is useful for learning TicketSQL.
TicketSQL is a simple language geared toward selecting tickets from RT's database. It's a variation on SQL the Structured Query Language which is a standard language used to query relational databases. To understand how to create effective searches, you'll need to understand a little bit about how TicketSQL works. With TicketSQL, as with SQL, you create a series of zero or more name = value conditions (they are called predicates in SQL), and apply these limits to the data set. The set of items that match these limits is the result.
Here is a concrete example: to find all the tickets with a status of new, you would use the following TicketSQL:
status = 'new'
Simple enough. This limits the result set to only tickets with a status of new. Multiple clauses are joined with either AND or OR to indicate whether both conditions must be true or either one can be true. A search for all tickets with a status of new that live in the General queue would look like:
status = 'new' AND queue = 'General'
A ticket must match both of these conditions to be returned by the search. However, consider a search for tickets with a status of either new or open:
status = 'new' OR status = 'open'
This time, because OR is used, a ticket may match either one of the two conditions in order to be returned from the search. A ticket will be returned if it is new or if it is open.
You can have as many limits as you want. Use parentheses to group the conditions whenever they get a little hairy. Here is a search for tickets in either the General or Customer Support queues with a status of new or open that are owned by the user aml:
(status = 'new' OR status = 'open') AND (queue = 'General' OR queue = 'Customer Support') AND owner = 'aml'
You can search for custom field values in the same way as standard ticket attributes.[*] Simply prefix the name of the custom field with CF. to indicate to the TicketSQL parser that you are searching over a custom field:
(status = 'open' OR status = 'new') AND CF.Visibility != 'private'
3.10.2. Downloading Search Results
From the search results page, you have several display options. The most obvious is to choose the specific ticket you were looking for, and display that one. However, there are times when you might want to manipulate the tickets in an external program, so RT defines the standard export formats spreadsheet and RSS. The links to these exports are in the lower right corner of the search results page.
The RSS version of the search is useful for maintaining a list of tickets in a format usable by an RSS aggregator.[*] The spreadsheet version is really tab-separated values that most spreadsheet programs Excel or Gnumeric, for example can import. Many of these programs can be configured to retrieve results directly from the web, which may be even more convenient.
|< Day Day Up >|