SRM for Windows File System - Reports

Basic Advanced Filters Summaries SRM Archiver Integration FAQ  

Table of Contents

Overview

Generate a Report with Filters

Examples of Queries used in filters

Operators Explained

Overview

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.

Generate a Report with Filters

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. Select the type of SRM report you wish to generate. An SRM report can be selected from the SRM Report Manager node.
  2. Determine which client(s) and client computer group(s) from which you wish to collect data.
  3. Determine and select the type of data to collect for the report (filtering).
  4. Define the conditions for the SQL query. Queries that include strings are case-sensitive.
  5. Generate the report.

To generate a report with Filters:

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:
  • Set the time range of the report.
  • Enable forecasting by clicking Include Trending and specifying default trending interval and number of data points on which the trending interval is based.
  • Enter the Maximum number of Bar Points per chart or select Draw All Bar Points.
  • Either accept the default of 100 rows or increase or decrease the rows to display in the report by specifying a number in the Limit number of rows to output field.

Click the Advanced tab to:

  • Set the Data Size Unit to GB for the tables.
  • Set the Sorting order of the columns in either ascending or descending order or no sorting order.
6. Select Filters tab to run real-time SQL queries can also be applied to the data to provide further filtering.
  • Select a column in which you want to specify a condition (e.g., VM Datastore Details - Capacity) and click the Conditions field of Free Space and then click the ellipsis  button.
  • In the Select Filter Condition dialog box, double-click the operator and in the Condition field, enter the values of the condition, such as >2 and click OK.
  • Click Add.

See Examples of Queries used in filters for an explanation of the operators.

7.
  • The full SQL query can be generated if necessary after adding the condition and selecting the Get SQL Query tab.
  • The query that is displayed is Read-only and can be used in a third-party application, such as SQL Server Management Studio and used there to collect and report data.
8. Generate or save the report with one of these options:
  • Click Run to generate the report immediately and view the results in the CommCell Console.
  • Click Schedule to generate and schedule the report on a daily, weekly, monthly, yearly, or even one time basis. Specify the report language (see Languages - Support for the list of languages supported in Reports), format (HTML, EXCEL, PDF), and location. Optionally, notifications can be configured so the report can be emailed, sent to an SNMP trap, or RSS feed, etc.

    When scheduling, patterns can also be applied to the report so it runs on a specific time, repeated every so many days, or run for a particular number of times. Some reports allow enabling Forecasting by specifying default trending interval and number of data points on which the trending interval is based.

  • Click Save As... to save the report filter criteria, schedule information, notifications to a template so it can be used to generate a report at a later date. All report templates are saved under My Reports.
 

Examples of Queries used in filters

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.

Operators Explained

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

Rules to Follow

When constructing a filter using SQL query, adhere to these rules:

Back to Top