Remedy Advanced Searching and Macros

Table of Contents

Introduction

Search Types

Creating and Using Macros

Running a Macro

Editing and Deleting a Macro

Performing a Search Using a Variable Macro

Introduction

Searching within Remedy can often seem like a daunting challenge. But with some background knowledge about different search methods, Remedy users can query the database for just about any information needed.

Note: To avoid performance issues, the UNC-Request for Service archives tickets to a secondary form called UNC-RFS-Archive-2 13 months after the ticket Status is set to Closed. Remedy automatically modifies the status of Resolved tickets to a Closed status after 7 days if no modifications have been made to the request. All closed requests older than Aug 2, 2001 are stored in a secondary form called UNC-RFS-Archive. These secondary forms are copies of the Request for Service but do not have any active functionality such as menu options, etc.
Searching for Tickets during this period? Search in the Following Form
Open RFS tickets or tickets closed >13 months ago UNC-Request for Service
Closed tickets Aug 2, 2001 – 13 month rolling window UNC-RFS-Archive-2
Tickets closed before Aug 2, 2001 UNC-RFS-Archive

Search Types

When a form such as the UNC-Request for Service is displayed in Search mode, users can perform searches for a single request or set of requests they have permission to view or modify. To find specific requests, you must enter search parameters that define the type of requests you want to find.

You can search for requests in several ways:

Query by Example Searching (QBE)

The easiest way to specify search criteria is to fill in fields in the form to match the requests you want to find. You can specify values for more than one field. The more fields you fill in, the more specific your search becomes. The system searches for requests that meet all the criteria and displays them in the Results pane. QBE searches are generally simple searches requiring only one value for a given field. More complex searches may require using the Advanced Search Bar or a combination of a QBE search with the Advanced Search Bar.

Be careful constructing your searches. When you enter values into a field to search, Remedy will return only exact matches for your search unless you use qualifiers to define your search.

Advanced Search Bar Searching


The advanced search bar appears at the bottom of the main window. You can use the advanced search bar to define a more complex set of search criteria.

To view the Advanced Search Bar, from the File Menu, select View > Macro Bar .

Searching Menu Fields

When searching menu fields, you should distinguish between data-driven menus, indicated in Remedy by the drop-down menu arrow being located outside the field (Group Assigned, for example) and hard-coded menus, indicated by the drop-down menu arrow being located inside the field (Point of Contact, for example)

For example, you can search for all requests with two different values in the same field such as a search for RFS tickets that were created within a date range through the month of January, 2004. The example could be searched within the Advanced Search Bar by entering the following: ‘Create Date’>”01/01/04″ AND ‘Create Date’<”02/01/04″ , remembering that field labels are surrounded by single quotes and field values are surrounded by double quotes. You can use the advanced search bar together with fields in a form to specify search criteria.

Using Relational Operators in the Advanced Search Bar

Relational operators are useful especially in non-text fields such as date and time fields when you want to search for a value within a numerical range. You can use the following relational operators only in the advanced search bar. You cannot use them in a form.

Table 2. Relational Operators

Operator Example Query Action
( ) (‘Create Date’>”1/01/04″ AND ‘Create Date’<”2/01/04″) Use parentheses to control the order in which the expression is carried out. The resulting query will display all requests created in January, 2004
AND (‘Create Date’>”1/01/04″ AND ‘Create Date’<”2/01/04″) Both conditions must be true when using the AND Operator as the above example shows
OR ‘Person Assigned’=”began” OR ‘Person Assigned’=”tuna” Logical OR of the result of two conditions. The result is true if either condition is true. This query will return RFS tickets assigned to either began or tuna
! ‘Status1′!=”Assigned” Negates the condition that follows. If the condition is false, the result is true. This query will return requests that do not equal Assigned Status.
LIKE ‘Group Assigned’ LIKE “HRIS%” Performs a pattern search. This example will return requests assigned to any group with a name that begins with HRIS. The LIKE operator is useful only with character and diary fields.
+ ‘Create date’ > $DATE$ + (8*60*60)
  • Adds two numerical values (integer, real values, decimal).
  • Adds an integer interval to a date/time value.
  • Concatenates two character strings.
  • This example finds all requests that were created after 8:00 a.m. today. 8*60*60 is the number of seconds in 8 hours, where * is a multiplier. Date values are stored in Remedy in seconds.
- ‘Create date’ > $TIMESTAMP$ – (7*24*60*60)
  • Subtracts two numerical values (integer, real values, decimal).
  • Subtracts two date/time values (resulting in an integer).
  • Subtracts an integer interval from a date/time value.
  • This example finds all requests that were created within the past week. 7*24*60*60 is the number of seconds in one week. This is useful to include in a custom report of all requests created in that week.
* 7*24*60*60 Multiplies two numeric values. This example defines the number of seconds in one week.
/ Divides two numeric values. For example, ‘Total Expenses’ / ‘Total Income’ = 2 finds all requests where the total amount spent for expenses is twice the total amount of income.
% ‘Group Assigned’ LIKE “HRIS%” When used as part of a search statement, it is interpreted as a wildcard symbol
< ‘Create date’ < ($TIMESTAMP$ – 24*60*60) Matches contents that are less than the value. For example, This example finds all requests created more than 24 hours ago. 24*60*60 or 86400, is the number of seconds in 24 hours.
> ‘Create date’ > “02/01/04 00:00:00″ Matches contents that are greater than the value. This example finds all requests with Create dates that are newer than midnight February, 1, 2004.
!= ‘Status’ != “Closed” Matches contents that are not equal to the value. This example finds all requests that are not closed.
<= ‘Create Date’<=”1/01/04″ Matches contents that are less than or equal to the value. This example finds all requests created on Jan 1, 2004 and earlier.
>= ‘Create Date’>=”1/01/04″ Matches contents that are greater than or equal to the value. This example finds all requests created on Jan, 1, 2004 and later.
= ‘Person Assigned’=”cdarring” Matches contents that are exactly equal to the value. This example finds all requests that are assigned to only cdarring.

Using Wildcard Symbols

Wildcard symbols can be used to search for a group of similar values or to group several values together in either a QBE search within a form of within the Advanced Search Bar.

Table 3. Wildcard Symbols

Wildcard Symbol Matches the following Characteristics:
%

Percent

Used in character fields to return values of like values for leading, trailing or other values within a result.

Example: ‘Group Assigned’ LIKE “KFBS-%” would return all KFBS groups

_

underscore

Used to match a single character in a search.

Example: B_b in a search would result in all word with b as the first and third letter such as Bob, Bub, Bab.

All fields within Remedy have a default search pattern of either leading, equal and anywhere based on the fields use and characteristics. Using wildcard symbols overrides the default values of fields when needed.

Using Keywords in the Advanced Search Bar

You can enter keywords in any field where you can enter character values. When you create or modify a request, keywords are replaced with their corresponding values. The most commonly used keywords are: $DATE$, $NULL$, $TIME$, $TIMESTAMP$, and $USER$.

Note: Keywords are case-sensitive. When entering keywords, use only UPPERCASE.

Table 4. Available Keywords within RFS Searches

Keyword Substituted Value
$DATE$ In a character field, the current date is displayed. In a date/time field, the time defaults to midnight (00:00:00).
$LASTCOUNT$ The number of matches found in the most recent search.
$LASTID$ The ID of the last successfully created request.
$TIME$ In a character field, the current time is displayed. In a date/time field, the date defaults to the current date.
$TIMESTAMP$ The current date/time stamp.
$USER$ The name of the user who is currently logged in.
$WEEKDAY$ The current day of the week.
$ENTER VALUE$ Used in a macro to define a variable to search on such as a date field or character field. Detailed information is described in the Macro section of this document.

Date Fields in General

Remedy does not interpret a date value as the entire day, it only interprets it as 12:00AM on that particular day. This affects how you write queries involving dates in two ways:

1. You cannot capture a single day in a query using the = (equal) operator. For example, if you know there are tickets that were created on July 1, 2002, and you run the query ‘Create Date’ = “07/01/02″ , you will get no returns. This is because Remedy is only looking for UNC-Request for Service tickets created at exactly 12:00AM on July 1, 2002. To solve this you must use a date range: ( ‘Create Date’ >= “07/01/02″ ) AND ( ‘Create Date’ < “07/02/02″ ) .

2. You cannot use the last day you want to capture as the closing date of your date range. You have to close the date range with the day AFTER the last day you want to capture. If you want to capture all tickets created in the month of July, 2002, you cannot use ( ‘Create Date’ >= “07/01/02″ ) AND ( ‘Create Date’ <= “07/31/02″ ) . This query omits any UNC-Request for Service tickets created on July 31, 2002. To get accurate results, you must use ( ‘Create Date’ >= “07/01/02″ ) AND ( ‘Create Date’ < “08/01/02″ ) .

Examples:

  • ‘Create date’ > $TIMESTAMP$ – (7*24*60*60) finds all requests that were created within the past week. (7*24*60*60 is the number of seconds in one week.) This is useful to include in a custom report of all requests created in that week.
  • ‘Create date’ < ($TIMESTAMP$ – 24*60*60) finds all requests created more than 24 hours ago. ([24*60*60] or 86400, is the number of seconds in 24 hours.)
  • ‘Create date’ > $DATE$ + (8*60*60) finds all requests that were created after 8:00 a.m. today. (8*60*60 is the number of seconds in 8 hours.)
Note: When searching within the Advanced Search Bar, always place field names in single quotes, such as ‘Create Date’, for the timestamp set when a request is created.

UNC-Request for Service Fields of Interest for Reporting

Field Name Definition Example Location
Create Date Date and time a request is submitted 3/29/2004 1:25:18 PM Ticket History Tab in RFS
Creator Onyen of the user who submitted the request cdarring Ticket History Tab in RFS
Group Assigned Remedy Group currently assigned to the request REMEDY ADMIN Ticket Summary Tab in RFS
Group Created Remedy Group that submitted the Request REMEDY ADMIN Ticket History Tab in RFS
Last Modified by Onyen of the user who last modified the request cdarring Ticket History Tab in RFS
Modified Date Date and time the request was last modified 3/29/2004 1:25:18 PM Ticket History Tab in RFS
Person Assigned Onyen of the Remedy user the request is currently assigned to cdarring Ticket Summary Tab in RFS
Point of Contact Source of the request Phone, Email, Web, Walk-in, Alarm, Chat RFS Customer Information Section
Request Number The six digit unique identifier for each request 648257 Upper-left corner of RFS application
Sys-Date Status Solved Date and time the request was last placed in Status of Solved. 3/29/2004 1:25:18 PM Hidden Field, available from the pull-right beside the Advanced Search Bar
Sys-Status Current Status of the request Assigned, Work in Progress, Pending, Solved, Closed, Cancelled Hidden Field, available from the pull-right beside the Advanced Search Bar
Sys-Priority Label SLA Flag used indicate if Resolution SLA has been exceeded Low, Medium, High, Urgent Hidden Field, available from the pull-right beside the Advanced Search Bar
Sys-Esc Interval SLA flag used to indicate if Response SLA has been exceeded 1, 2, 3 Hidden Field, available from the pull-right beside the Advanced Search Bar
Sys-Weekday Used to indicate the day of the week a request is created Sunday-Saturday Hidden Field, available from the pull-right beside the Advanced Search Bar
Sys-Creator Org Grp ID Indicated by a numeric value for the Primary group of the user Example: 63

For REMEDY ADMIN

Hidden Field, available from the pull-right beside the Advanced Search Bar
Sys-Org Grp ID The current Group Assigned displayed as a numeric value Example: 63

For REMEDY ADMIN

Hidden Field, available from the pull-right beside the Advanced Search Bar

My Searches

You can select and run a search that you have created previously and saved. You can define an unlimited number of searches.

If there is certain criteria that you think you will search on more than once, you can save that search for later use as a My Search:

From the File Menu within RFS, select Actions > Save Search .

  1. Name the search in the Save Search window.
  2. Select OK.
  3. To access the saved search, select the down arrow to the right of the SEARCH button on the search form.
  4. From the File Menu, select Actions > My Searches to display your saved searches.
  5. Choose a saved search to run it.
View My Searches: You may want to view a search you have saved without actually running it. A situation where this would be useful is if you want to run a query that is similar to one you have saved, but just need to make minor changes. From the File Menu in RFS, select Actions > View My Searches and select the one you want from the pull-right menu. This displays the search string in the Search window. You then have the option of making edits to the query before running it.

Recent searches

The User Tool remembers previous searches you have performed. You can repeat a recent search by selecting it from the list of recent searches. The User Tool can list up to nine recent searches.

You can also access a recent search which is stored in memory. Select the down arrow to the right of the SEARCH button on the search form.

  1. From the File Menu, select Recent Searches.
  2. The most recent searches you have made will display.
View Recent Searches: You may want to view a recent search without actually running it. A situation where this would be useful is if you typed a long search string and after searching, you realized you needed to add more criteria. The View Recent Searches option opens the search window and displays the criteria of the recent search you select. From the File Menu, select Actions > View Recent Searches and select the one you want from the pull-right menu. This displays the search string in the Search window. You then have the option of making edits to the query before running it.

Creating and Using Macros

There may be some actions you perform in Remedy on a regular basis, such as a search on open tickets. If so, writing a macro to perform these actions will eliminate having to go through the steps every time.

Make the Macro bar visible by selecting View > Macro Bar from the File Menu if you do not see it located beside the File Menu at the top of the Remedy application window.

  1. Select the Start Recording button indicated by the red circle.
  2. After you open your form to search, always select Ctrl + E to null all hidden fields that are set by default when you open a form. Manually perform the actions you want to include as part of the macro. This action should include opening the form, such as RFS in a search mode and also any fields you wish to search. Reporting and file export will be discussed in another document, but any report style or file export can also be saved in your macro.
  3. Select the Stop Recording button indicated by the square located on the Macro bar.
  4. When the Save Macro window appears, give the macro a name in the Macro Name field.
  5. You have the option of entering a description in the Help Text field, but it is not required.
  6. Select Save to store the macro. If you decide there is more to be recorded, then just Select Continue Recording and repeat steps 3-8.
Note: If you make a mistake during the manual operations while recording, cancel the macro and restart your macro.

Running a Macro

After you record a macro, you are ready to run it.

Select the drop-down box on the Macro Bar to select the macro. This can also be done by going to Tools > Run Macro , selecting the macro and Selecting OK.

If you have saved a macro but is not appearing in either of those places, from the File Menu, select Tools > Options > General and make sure the Search Path is C:\Program Files\AR System\Home\arcmds or C:\Program Files\Remedy\Home\arcmds .

Editing and Deleting a Macro

Editing what actions a macro carries out isn’t an option, but you can edit a macro’s name and description as well as delete it.

From the File Menu, select Tools > Manage > Macros to bring up the Manage Macros window.

  1. To delete a macro, just select it and Select the Delete button.
  2. To change the name or description, select it and edit the text in the New Name and Help Text fields.
  3. Select Modify.
  4. Select Close.

Performing a Search Using a Variable Macro

You can record a macro that enables you to change certain values when you run the macro. That is, when the macro is running, you can enter variable values in the appropriate fields. This is useful if you are doing monthly reports where the dates or groups change. When you run this type of macro, you will be prompted with a dialog box where you can enter your dates or other variables specific to that report. There are two key components of the variable macro that you should remember:

  1. For any field value you want to be prompted for, you must enter a label name in double quotes in place of the actual value in the query. Whatever label name you put in double quotes is what will show as the label for the field when you are prompted.
  2. When you use a label name in place of an actual value, you must put a $ sign on both sides of the label name. Also, the $ signs must be within the double quotes.

Below are instructions for writing a macro where the variables are for a date range and for a particular group:

  1. If you do not have the Macro Bar set to display automatically, select View > Macro Bar.
  2. Select the Record button (w/ the red circle) on the Macro Bar.
  3. Go to File > Open. Select the application in which you want to run the macro and Select Search.
  4. If you do not have the Advanced Search Bar set to display automatically, Select on the Advanced button at the top, right corner of the form.
  5. In the Advanced Search Bar, type the following search string: ( ‘Create Date’ >= “$Beginning Date$” AND ‘Create Date’ < “$Ending Date$” ) AND ( ‘Group Assigned’ = “$Group Name$” )
    This type of string will not work unless it is used during the macro recording process.
  6. Select on the Search button.
  7. When prompted with the dialog box, enter the date range in the mm/dd/yy format and a group name, then Select on OK.