This section discusses SQL usage in Custom Queries and provides a number of SQL code examples to illustrate valid syntax. RSA Security recommends that you review and understand the sample queries that are provided with RSA Authentication Manager.
Database Schema
The RSA Authentication Manager database is organized as a set of interrelated tables, or schema. To use the custom query feature effectively, you need to become familiar with the schema table names and the data they contain.
For example, the SDUser table contains over 30 fields relating to every user defined in the database—fields such as first and last name (chFirstName, chLastName), login (chDefaultLogin), and so on. SDUser is one of over 100 tables in the RSA Authentication Manager database schema.
9: Reports 197 Note: Refer to the Help for a comprehensive reference of all the schema tables and their contents. A schema section is also included in the Administration Toolkit Reference Guide (authmgr_admin_toolkit.pdf).
General Rules About Query Length and Syntax
In Custom Queries, for a query to be valid, it must meet the following conditions:
• There must be at least one SELECT statement. For more information, see the following section, “SELECT Statement Syntax.”
• Up to 32 statements in a single query are allowed, and a query can be up to 4,096 characters in length.
• If there is more than one SELECT statement in a query, they all must return the same set of fields in the same order. Selected fields in different SELECT statements can be identical. Alternatively, selected fields can be from different database tables if they have the same name, type and, in the case of character fields, the same size (for example, chDefaultLogin from the SDUser table and chDefaultLogin from the SDUserScope table). In addition, in all SELECT statements in a query, the fields must be in the same position.
• Custom queries are not case-sensitive and allow any combination of capital and lowercase letters.
• MESSAGE statements are optional and can only be used in conditional clauses (IF/THEN, ELSEIF, ELSE). For more information, see “Using Conditional Clauses to Validate User Input” on page 205.
SELECT Statement Syntax
In Custom Queries, you are limited to reading from the RSA Authentication Manager user and log databases, sdserv and sdlog. You cannot use queries to write to or modify the databases. Consequently, the sample queries primarily make use of the SELECT statement.
In Custom Queries, the syntax of the SELECT statement is as follows. Entries in square brackets ([ ]) are optional:
SELECT {*|column-list} FROM {table-name|explicit-join}
[WHERE search-condition]
[GROUP BY column[,column]...]
[HAVING search-condition]
[ORDER BY sort-criteria]
In a SELECT statement, you identify the column list, or fields of data, you want to search and the FROM clause to identify the database table containing the data. For example:
SELECT chDefaultLogin, chLastName, chFirstName FROM SDUser In this example, all users’ default logins, last and first names are retrieved from the SDUser table (which is in the sdserv database).
RSA Authentication Manager 6.1 Administrator’s Guide
198 9: Reports
Note: Because all table names in the sdserv (user) and sdlog (audit log) databases are unique, you do not need to include the database name in the query.
To retrieve all fields in a table, you can use the asterisk (*) as a wildcard for the column list in the SELECT statement: For example:
SELECT * FROM SDUser
The asterisk cannot be used as a wildcard in other constructions. For example, the following is not allowed and generates an error:
SELECT SDUser.*
Using Expressions to Retrieve Data
Another way to specify the column list in a SELECT statement is to use an
expression. Expressions provide ways to retrieve data by using a function. Custom Queries supports only functions that retrieve a numeric result. These include:
COUNT. This function provides a count of all rows in the results list. For example:
SELECT COUNT(DISTINCT chLastName) FROM SDUser
This example counts the number of distinct last names in the SDUser table.
MAX. This function retrieves the highest number from a particular field. For example:
SELECT MAX(iUserNum) FROM SDUser
This example retrieves the highest user number, or the last user added to the database.
MIN. This function retrieves the smallest number in a particular field. For example:
SELECT MIN(iUserNum) + 1 FROM SDUser
This example retrieves the second lowest active user number from the SDUser table.
Note: You can use operands (for example, “ + 1”) to modify functions. In such cases, be sure to include spaces to separate the operand from the function, or a syntax error appears.
Using Joins in SELECT Statements
When using the SELECT statement, you can retrieve data from a single table or from multiple tables. To retrieve data from multiple tables, use a table join. There are three types of joins:
INNER. An inner join returns the records selected for the table on the left side combined with the related records from the table on the right. (The first table specified in the SELECT statement is said to be on the left side.) With an inner join, only the fields that match the selection criteria are output. For example:
SELECT chDefaultLogin FROM SDUser JOIN SDToken ON SDUser.iUserNum=SDToken.iUserNum
9: Reports 199 This statement would output the login names of only those users who have tokens (including passwords) assigned to them. Note that using the JOIN clause by itself implies an inner join. You can also use the explicit INNER JOIN clause.
LEFT[OUTER]. With a left join, all records from the first (left) table are put into the result set and then they are joined by only those fields in the second (right) table that match the selection criteria. For example:
SELECT chLastName, chFirstName, chSerialNum FROM SDUser LEFT JOIN SDToken ON (SDUser.iUserNum = SDToken.iUserNum)
This statement would output the names of all users in the SDUser table. For users with an assigned token, the token serial number would also be output. Note that using LEFT JOIN by itself implies an outer join. You can also use the explicit LEFT OUTER JOIN clause.
RIGHT[OUTER]. With a right join, all records from the second (right) table are put into the result set, then they are joined by only those fields in the first (left) table that match the selection criteria. For example, in contrast to the preceding LEFT JOIN example, if you want to retrieve all serial numbers of tokens in the database, whether they are assigned or not, you can use this query:
SELECT chLastName, chFirstName, chSerialNum FROM SDUser RIGHT JOIN SDToken ON (SDUser.iUserNum = SDToken.iUserNum) Note that using RIGHT JOIN by itself implies an outer join. You can also use the explicit RIGHT OUTER JOIN clause. Also note that a RIGHT JOIN is limited to two tables.
To further define the column list in a SELECT statement, you can use multiple joins.
For example, to list all Group Administrators in the database, you can use this query:
SELECT chDefaultLogin, chLastName, chFirstName,
SDGroup.chName, SDSite.chName FROM SDAdministrativeRole JOIN SDUser ON SDUser.iUserNum =
SDAdministrativeRole.iUserNum
JOIN SDGroup ON SDAdministrativeRole.iGroupNum = SDGroup.iGroupNum
LEFT OUTER JOIN SDSite ON SDGroup.iSiteNum = SDSite.iSiteNum ORDER BY chDefaultLogin
Note: You can use inner joins and left joins together, or inner and right joins, but you should not use right and left joins in the same SELECT statement.
RSA Authentication Manager 6.1 Administrator’s Guide
200 9: Reports
In addition, in Custom Queries, the fields on which the table join is to occur must include the table name followed by a period followed by the column name. For example:
Correct:
JOIN SDAdministrativeRole ON SDUSer.iUserNum = SDAdministrativeRole.iUserNum
Incorrect:
JOIN SDAdministrativeRole ON iUserNum
The SELECT statement examples shown in this section use explicit joins (some form of the JOIN clause is included). Table joins can be also be implicit. For example:
SELECT chDefaultLogin FROM SDUser, SDToken WHERE SDUser.iUserNum=SDToken.iUserNum
Note: Implicit joins can take a long time to process and are not recommended. When you have the choice, use explicit joins, which are generally more efficient.
The following section goes into more detail about search conditions in table joins. For additional information about table joins, see “Best Practices for Table Joins” on page 209.
Using the ‘ON’ Search Condition
Explicit table joins must use the ON search condition. This conditional clause can serve one of two purposes in a join:
Relating tables. In the RSA Authentication Manager database, like most relational databases, tables often have relationships to other tables. For example, both the SDUser and SDToken tables have an iUserNum field, which relates users to their assigned tokens. An example of using this relationship in a SELECT statement is:
SELECT chLastName, chFirstName, chDefaultLogin FROM SDUser JOIN SDToken ON SDUser.iUserNum=SDToken.iUserNum
There are a number of other relationships among tables in the RSA Authentication Manager database. Relationship types are one-to-one, zero-to-one, one-to-many, and zero-to-many. For more information about relationships among tables, see the descriptions of the database schema in the Help.
Filtering tables. You can also use the ON search condition to select only those records that meet the search criteria. This sort of filter condition contains one or more logical expressions connected by a logical operator (AND, OR, NOT). For example:
SELECT chDefaultLogin, chLastName, chFirstName, chSerialNum FROM SDUser JOIN SDToken ON SDUser.iUserNum =
SDToken.iUserNum AND SDUser.chLastName BEGINS "A" AND SDUser.bTempUser = YES
This statement would output the login, last and first names, and token serial numbers of all temporary users whose last names begin with “A”.