Auditor Help: Editing FFR SQL Queries

The View MS-SQL option in Auditor allows you to select the MS-SQL queries that are used to generate a specific report. To extract specific data from the Auditor database to a different database, the preferred method is to build an FFR report that contains the information of interest. Once the report is built, view and copy the SQL query, then execute it directly from the SQL Query Analyzer to extract the information from the Ecora database.

The Edit MS-SQL feature is used to customize a report, such as manipulating date/time information and for creating summary information, such as counting number of occurrences.

Edit SQL

In the process of generating a fact-finding report, you are using a SQL query to return the desired data from the configuration database. If you wish to see the SQL query, click on the Edit SQL... button in the FFR dialog box.
 Enable this feature under Settings... Preferences.

  1. Modify or verify the Table Name.
  2. Modify or verify the Description.
  3. Modify or verify the SQL Query.
  4. Enter the numbers of any columns you wish to merge.
  5. Click Check to verify that the query is still functional (no syntax errors).
  6. Click Save to record your changes (or Close to exit without saving).

Information needs to be converted to the appropriate (date/time) representation before it is manipulated. Do this using the Edit SQL feature. For example, consider the User Last Logon field (be sure you have collected user information.) Create a report showing the User Last Logon field. The goal is to display users with logon dates greater than 45 days from "today." You must edit the SQL command to convert the information.

 Note: A value of NULL in this example indicates the user has not logged-in. A separate report or table must be created to show these users or the existing report needs to be modified to handle the NULL value.