Basic | Advanced | Filters | Summaries | SRM Archiver Integration | FAQ |
Generate a Report with Filters
Examples of Queries used in filters
Customized queries can be applied as additional reporting filters to retrieve specific SRM information from the clients and client groups in your CommCell. These queries are extremely useful if the provided report filters (clients, columns) do not provide the necessary data required for your environment. For example, you could apply SQL query filters to the Allocated Space column to display disks of a certain size, such as 2 GB. This ad hoc query further refines and filters the data to facilitate management of your storage infrastructure.
More examples of why you may want to use filters for SQL queries include:
Since the query will run against the CommServe database to retrieve the requested SRM data, which can be easily configured from the CommCell Console when specifying filter criteria of an SRM report. Any SQL query that is supported by a SQL Server can be used in Filters for SRM reporting. If desired, once the conditions have been defined, you can retrieve the full SQL query for use in third-party applications.
Filters in the form of SQL queries can be defined during the configuration of an SRM report. You can add a SQL query to the report to filter additional data for a specified column. To configure a report with filters, you must complete the following:
1. | From the CommCell Browser, click Reports, and then click SRM Report Manager. | |
2. | Expand the applicable tree to view your agent. Then select your agent and click a template to display the Options window. | |
3. | Click the Client Selection tab and select the client computers or client computer group. This is a required step. | |
4. |
Click Column Selection tab.
Select a category and Add or Remove columns between the Available and Selected boxes. The categories and columns differ for each type of report. Clicking Remove All will remove all previously selected filters for the reports. |
|
5. |
Click the Report Options tab to configure settings in the report.
Click the General tab to:
Click the Advanced tab to:
|
|
6. |
Select Filters tab to run real-time SQL queries can also be applied to the
data to provide further filtering.
See Examples of Queries used in filters for an explanation of the operators. |
|
7. |
|
|
8. |
Generate or save the report with one of these options:
|
The sections below explain the operators than can be used in a SQL query as well as real-world examples describing why and how to specify the condition and build the query.
The following explains the operators that can be used in filters using SQL queries. Examples for each are provided. Queries that include strings are case-sensitive.
Operator | Description | Example |
And |
Is a binary operator that operates on two operands
(data that is manipulated by the operator).
Works with both numeric and string queries. Displays a result if both operands are TRUE and no result is displayed if either is FALSE. |
Display a computer group that starts with B and ends with M
(e.g., Birmingham).
1. Select Client Computer Group column. 2. Enter this query: Starts With B And Ends With M
|
Or |
Is a binary operator that operates on two operands
(data that is manipulated by the operator).
Works with both numeric and string queries. Displays a result if either operand is TRUE and no result is displayed if both are FALSE. |
Display a computer that starts with B or starts with M (e.g.,
Birmingham, Milwaukee).
1. Select Computer column. 2. Enter this query: Starts With B Or Starts With M |
Not |
An operation on logical values that changes true to
false, and false to true. The NOT operator inverts the result of a
comparison expression or a logical expression.
The specified condition must be false in order to view a result. Example: Display a computer that does not have less that < 4 GB of used space. Therefore, only computers with 4 or more GB of used space will be displayed. |
Display a computer that does not have less that < 4 GB of used space. 1.Click Report Options tab and set data size unit to GB. 2. Select Used Space column. 3. Enter this query: Not < 4 |
Like |
The LIKE operator is used in character string comparisons
with pattern matching.
It can contain the special wild-card pattern matching characters percent -- '%' and underscore -- '_'. Underscore matches any single character. Percent matches zero or more characters. |
Display a computer that contains eng
characters in its name to identify computers in the engineering department.
1. Select Computer column. 2. Enter this query: %eng% |
Starts With | The Starts With operator is used in character string comparisons with pattern matching. |
All names of computers in the engineering department use the
convention of eng_. Display all computers
that start with E to list computers in the engineering department. 1. Select Computer column. 2. Enter this query: Starts With E |
Ends With | The Ends With operator is used in character string comparisons with pattern matching. |
All names of computers in the System Test department use the
convention of system_test. Display a
computer that ends with T to list computers in the system_test
department.
1. Select Computer column. 2. Enter this query: Ends With T |
> (Greater Than) |
A comparison operator that tests whether
two expressions are the same.
Works with only numeric queries. |
Display file sizes that are larger than 2 GB.
1. Click Report Options tab and set data size unit to GB. 2. Select Maximum Size column. 3. Enter this query: > 2 |
< (Less Than) |
A comparison operator that tests whether two
expressions are the same.
Works with only numeric queries. |
Display file sizes that are less than 4 GB.
1. Click Report Options tab and set data size unit to GB. 2. Select Maximum Size column. 3. Enter this query: < 4 |
>= (Greater Than or Equal To) |
A comparison operator that
tests whether two expressions are the same.
Works with only numeric queries. |
Display file sizes that are greater than or equal to 2 TB.
1. Click Report Options tab and set data size unit to TB. 2. Select Maximum Size column. 3. Enter this query: >= 2 |
<=(Less Than or Equal To) | A comparison operator that tests whether two expressions are the same. Works with only numeric queries. |
Display computers with less than or equal to 1 GB free space.
1. Click Report Options tab and set data size unit to GB. 2. Select Free Space column. 3. Enter this query: <= 1 |
= (Equal) | The equals comparison operator compares two values for equality. Works with both numeric and string queries. |
Display files that are located in the /usr/bin directory.
1. Select Path column. 2. Enter this query: =/usr/bin |
When constructing a filter using SQL query, adhere to these rules: