9.2 Using Nsure Audit Report

NOTE:Nsure Audit Report is included with NetWare 6.5 for evaluation purposes only. It displays a licensing notice and terminates after 10 minutes of use until you install a valid license.

Nsure Audit Report is a Windows-based, ODBC-compliant application that can use SQL query statements or Crystal Decisions Reports to query Oracle and MySQL data stores (or any other database that has ODBC driver support). You can define your own SQL query statements or import existing query statements and reports. Some logging applications might also include their own predefined queries or reports. Query results are returned in simple data tables; rows represent individual records and columns represent fields within those records.

This section provides the information you need to use Nsure Audit Report to generate queries and reports. It includes

9.2.1 Installing Nsure Audit Report

Nsure Audit Report is available only in the Nsure Audit Windows installation. For information on the Windows install, see Installing Nsure Audit on Windows in the Novell Nsure Audit Installation Guide.

The Nsure Audit Report program file, lreport.exe, is installed to the \program files\novell\nsure audit directory.

9.2.2 Launching Nsure Audit Report

During installation, Nsure Audit Report is added to the Start menu. To start Nsure Audit Report from the Start menu, click Start > Programs > Nsure Audit Reporting Application.

9.2.3 Nsure Audit Report Interface

Figure 9-3 Nsure Audit Report Interface

The Workspace window includes four panes:

  • The Queries pane lists your defined queries. You can create, delete, or run queries in this pane.
  • The Reports pane lists the predefined Crystal Reports. You can import or run reports in this pane.
  • The Events pane lists your logging applications and their associated events. You can run distribution reports or count an application’s overall events in this pane.

    You must import the applications’ log schemas before they appear in the Events pane. For more information, see Importing Log Schemas.

  • The Databases pane lists the databases that you can query.

    You must define your databases before they appear in the Database pane. For more information, see Defining Your Databases in Nsure Audit Report.

To move between panes, simply click the tabs at the bottom of the Workspace window. You can also open each pane from the View menu.

The status bar displays the currently selected database and table. You can click these fields to select another database or table.

9.2.4 Defining Your Databases in Nsure Audit Report

Nsure Audit Report allows you to run queries and reports on any ODBC Data Source defined in your Windows registry.

To define your Windows Data Sources, go to the Windows Start menu and click Settings > Control Panel > Administrative Tools > Data Sources. There you can add, remove, and configure your ODBC Data Sources. For more information, see Windows Help.

After you have defined an ODBC Data Source in Windows, you can add the Data Source to your list of available databases in Nsure Audit Report.

  1. Click File > New > Database.

    You can also right-click in the Database pane and select Insert.

  2. In the Name field, specify the name you want to use to refer to this database.

  3. Click Browse.

  4. In the Select Data Source window, click Machine Data Source.

  5. Select the Windows Data Source Name (DSN) you want to be able to query in Nsure Audit Report.

  6. Specify the username and password Nsure Audit Report can use to authenticate with the database.

    If you leave this field blank, Nsure Audit Report uses the username and password defined in the Data Source.

    IMPORTANT:The only time you need to specify a username and password is if your Data Source driver does not define the username and password or if the username defined in the Data Source does not have rights to log in from the current workstation.

  7. When finished, click OK.

The Data Source now appears in the Database pane and can be selected for queries and reports.

The following table provides further information on the options in the Define Database menu.

Table 9-7 Define Database Menu Options

Option

Description

Name

The name you want to use to refer to this Data Source.

This name appears in the Databases pane.

DSN

The Windows Data Source Name (DSN) you want to add to your database list.

For information on defining your Windows Data Sources, refer to Windows Help.

Username

The username Nsure Audit Report uses to authenticate with the database.

If you leave this field blank, Nsure Audit Report uses the username and password defined in the Data Source.

The only time you need to provide a username and password is if your Data Source driver does not define the username and password or if the username defined in the Data Source does not have rights to log in from the current workstation.

In some cases, the username and password defined in the Data Source might not have rights to log in from the current workstation. For example, in Secure Mode, the default MySQL administrative account, Root, only has rights to log in at the database server. Therefore, if MySQL is running in Secure Mode, you either need to create a new user account with rights to log in from the current workstation or you must modify the rights for the Root account. (By default, MySQL installs in Secure Mode on NetWare 6.5.)

The default username for the NetWare 6.5 data store is auditusr. (This default can be changed during the installation of Nsure Audit.) This account has all privileges to the default database (naudit) and can log in from any IP address.

Password

The password Nsure Audit Report uses to authenticate with the database.

NOTE:The default password for the NetWare 6.5 data store is auditpwd. (This default can be changed during the installation of Nsure Audit.)

Do Not Store Password

By default, Nsure Audit Report stores the password the logging server uses to authenticate with the database. This enables Nsure Audit Report to automatically log in to the database.

IMPORTANT:If you select the Do Not Store Password option, you must specify the password each time you run a query on the current database.

Editing and Deleting Data Sources

To modify or delete a Data Source in Nsure Audit Report:

  1. In the Databases pane, select the Data Source you want to modify or delete.

  2. Right-click to bring up the shortcut menu.

  3. Modify or delete the Data Source.

    1. Select Properties to modify the Data Source.

    2. Select Delete to delete the Data Source

      NOTE:Deleting a Data Source does not affect the Data Source definition in the Windows registry. It only removes the Data Source from the Database list, which means Nsure Audit Report can no longer query or run reports on the database.

Defining Your Default Database and Table

Your default database is automatically used for all queries and reports unless a specific database is designated in the query statement or another database is selected in the Database pane.

To define your default database:

  1. Click the Database field on the status bar.

  2. Select the default database from the list.

You can also select the database in the Database pane, right-click, and select Set As Default Database or you can choose Query > Default Database from the menu bar.

To define your default table:

  1. Click the Table field on the status bar.

  2. Type the name of the default table you want to use, or select it if it is already present in the list, then press Enter.

    The default table name defined by the Oracle and MySQL channels is NAUDITLOG.

    IMPORTANT:If you are using an Oracle database, the default table must correspond to the name of the table view, NAUDITLOG. For more information, see Section D.9, Creating a View in Oracle.

You can also click Query > Default Table from the menu.

9.2.5 Setting Default Options in Nsure Audit Report

The Options menu in Nsure Audit Report allows you to set your default sort order, query limits, date/time format, and Clipboard options.

To bring up the Options menu, click View > Options from the menu bar.

The following table reviews the options in the Options menu.

Table 9-8 Nsure Audit Report Options Menu Options

Option

Description

General

 

Default Sort Order

The order in which records are sorted in the query results window.

If you select ascending or descending, the records are sorted by the first column in the output which is the source IP address by default.

Result

 

Clipboard

The following Clipboard settings determine how query data is copied to the Clipboard.

Copy All If No Entries Selected

If no entries are selected when you press Ctrl+C in the Query Results window, all of the query information is copied to the Clipboard.

Copy ’Raw’ Instead of Translated Data

Information is copied to the Clipboard exactly as it appears in the database.

This means that all translated data (including IP addresses, signatures, dates, Severity level, Event ID, and other numeric values) are copied in their raw format. For example, a Severity level of Emergency would be copied as a 1.

Copy Field Headers

The field titles defined in the log schema files are copied to the Clipboard along with the data in their associated fields.

IMPORTANT:This option requires that you import each logging application’s log schema. For more information, see Importing Log Schemas .

Column Separation String

When records are copied to the Clipboard, the text string provided in this field is used to delineate the fields within each record.

This option facilitates cut and paste functions. For example, if you wanted to cut and paste data into an Excel spreadsheet, you would use a comma ( , ) delimiter. If you wanted to paste the data into tabular columns, you would use a tab delimiter.

Limits

 

Limit Results To __ Rows

This option limits the number of rows (that is, records) that are returned from a database query.

This is a global setting. This means that Nsure Audit Report automatically adds this parameter to all database queries unless the parameter is expressly defined in the query statement.

Printing

The following options determine which fonts are used to print query results.

Header Font

The font used to print the column headers.

Data Font

The font used to print the query data (that is, the fields within each record).

Footer Font

The font used to print the footer in the query results page. The footer contains the page number and query string.

Translation

 

Date/Time Format

The following options determine how Nsure Audit Report presents date and time information in the query results.

This is a global setting. This means that Nsure Audit Report automatically adds this parameter to all database queries unless the parameter is expressly defined in the query statement.

RFC822 UTC

All time and date information is formatted in RFC-822 format, which is the Internet standard format for electronic mail message headers.

All values are expressed in UTC.

RFC822

All time and date information is formatted in RFC-822 format.

All values are expressed in local time as defined in the workstation’s Windows settings.

Locale

All time and date information is formatted according to the standards and formats selected in the workstation’s Windows settings.

All values are expressed in local time as defined in the workstation’s Windows settings.

Locale Date

All date information is formatted according to the standards and formats selected in the workstation’s Windows settings. (Time information is not included.)

All values are expressed in local date format as defined in the workstation’s Windows settings.

Locale Time

All time information is formatted according to the standards and formats selected in the workstation’s Windows settings. (Date information is not included.)

All values are expressed in local time as defined in the workstation’s Windows settings.

Binary Data

Events logged to Nsure Audit include a data field that can contain up to 3072 bytes of data. The following options determine how Nsure Audit Report handles the information in the event’s data field.

Don’t Display

The data field is not included in the query results.

Display ASCII

The data field is included in the query results and displays in ASCII format.

Display hex

The data field is included in the query results and displays in hexadecimal format.

Display first __ bytes

Limits the amount of information that is included from the data field. The maximum is 3072 bytes.

9.2.6 Importing and Viewing Events in Nsure Audit Report

The Events pane allows you to view logging application properties as well as event data. The information provided on applications and their associated events can be used to define query statements, Notification Filters, and Heartbeat Notifications.

NOTE:For an explanation of event fields, see Section A.1, Event Structure. For information on defining Notification Filters and Heartbeat Notifications, see Section 8.0, Configuring Filters and Event Notifications.

Before you can view a logging application’s events, however, you must first import its log schema. The log schema catalogs the events that can be logged for a given application. It also provides the event descriptions and field labels that Nsure Audit Report uses in its reports. For more information, see Section A.4, Log Schema Files.

Importing Log Schemas

Nsure Audit stores each application’s log schema (LSC) file in its respective Application object. (English LSC files are stored under the NAuditAppSchemaEn attribute.) Therefore, when you import log schemas, Nsure Audit Report reads the information from the Application objects on the designated logging server.

To import a logging application’s log schema in Nsure Audit Report:

  1. Click File > Import > Application Schema.

  2. Specify the IP address or host name of the Secure Logging Server.

    If you have multiple logging servers, select the Secure Logging Server that loads the logging application associated Application object at startup.

    NOTE:To determine which Secure Logging Server loads the Application object, refer to the logging server’s Log Applications page. For more information, see Section 6.2, Creating Application Objects.

  3. From the drop-down list box, select the language version of the log schema you want to import.

    If an application does not have a log schema for the selected language, Nsure Audit Report imports the application’s English log schema.

    When you click OK, Nsure Audit Report connects to the logging server and imports the log schemas from all Application objects in the logging server’s supported Application containers. For more information, see Section 6.3, Application Objects.

    IMPORTANT:Nsure Audit Report writes the log schema files to its cache file, lreport.lsc, in the Windows Home Directory. Therefore, the account you use to log in to Windows XP must have right to the account’s Windows Home Directory. The Windows Home Directory is defined in the User Profile. For more information, see Home Directory in Windows Help.

  4. Click OK in the confirmation dialog box.

    The Import Confirmation dialog box notes that you must restart Nsure Audit Report before the new schemas appear in the Events tab.

  5. Restart Nsure Audit Report.

The new applications and their associated events now appear in the Events pane.

Viewing Application Properties and Events

IMPORTANT:To view events in the Events pane, the account you use to log in to Windows XP must have rights to the Windows Home Directory. The Windows Home Directory is defined in the User Profiles. For more information, see “Home Directory” in Windows Help.

To view a logging application’s properties in Nsure Audit Report:

  1. In the Events pane, select an application.

  2. Right-click, then select Properties.

The Application Properties window displays the following information:

Table 9-9 Application Properties Window Options

Attribute

Description

Application Identifier

The name assigned to the current application.

The Application Identifier is also stored in the application’s certificate. The Application Identifier is part of the Component string for every event logged from the current application. For more information, see Section 6.3, Application Objects.

Application ID

The four-digit hex value assigned to the current application.

The Application ID is part of the Event ID for every event logged from the current application. All Application IDs are assigned through Novell Developer Support and are maintained in the Nsure Audit central registry. For more information, see Section 6.3, Application Objects.

Description

The application description provided in the application’s log schema.

To view a logging application’s events in Nsure Audit Report:

  1. In the Events pane, expand the application’s folder.

    This exposes the application’s associated events. Only those events cataloged in the application’s log schema appear in this list.

  2. Right-click, then select Properties.

    You can also double-click an event to bring up the Event Properties window.

The Event Properties window displays the Event ID, description, and field information.

For more information on event fields, see Section A.1, Event Structure.

9.2.7 Verifying Event Authenticity in Nsure Audit Report

To provide non-repudiable logs, Nsure Audit can digitally sign each event that is logged to the data store. To sign an event, the logging application or the Platform Agent hashes the event data and signs the hash with the Logging Application’s private key. The signature is then stored as part of the event. This signature allows the auditor or investigator to determine if an event has been changed.

To allow auditors to determine if an event has been deleted or the sequence of events has been changed, Nsure Audit can also chain its event signatures. That is, if event chaining is enabled, each event’s signature includes its own data as well as the signature from the previous event.

Event chaining is enabled in the Platform Agent’s configuration file, logevent. For information on configuring this option, see Logevent. It can also be configured through the Secure Logging Server object’s Sign Event attribute. For more information, see Section 4.2.2, Logging Server Objects .

If event chaining is enabled, Nsure Audit Report can verify that all the events logged to the data store for each logging application are authentic; that is, it can validate the event signatures to determine if an application’s events have been tampered with, deleted, or if the sequence of events has been changed.

NOTE:Future iterations of Nsure Audit Report will allow you to verify individual events.

To verify that an application’s logged events are authentic:

  1. Click Query > Verify Authenticity.

  2. Select the database where the events are logged.

  3. Specify the table where the events are logged.

  4. Select the logging application for the events you want to validate.

  5. If the application is running on multiple systems, specify the IP address or host name of the Platform Agent that logged the events you want to verify in the Source Address field.

  6. If you want to filter events, you can specify the component string for the events you want to validate in the Optional Filter field.

  7. Specify the path and filename for the Logging Application Certificate.

    Click Browse to locate the Logging Application Certificate in the directory tree.

  8. Click Verify.

The following table provides more information on the Verify options.

Table 9-10 Verify Options

Option

Description

Database

The database containing the events you want to verify.

Table

The table containing the events you want to verify.

Application

The logging application’s events you want to verify.

In most cases, each logging application has its own certificate. This means that event signatures are typically application-specific. Therefore, Nsure Audit Report verifies event signatures on a per application basis.

Source Address

The IP address or host name of the Platform Agent that logged the events you want to verify.

The only time you need to provide a source address is if the logging application is running on multiple systems. This allows Nsure Audit Report to identify which event chain to verify.

Optional Filter

If you only want to verify the events for a specific component or module within a logging application, you can specify the component’s component string.

For more information on component strings, see Section A.1, Event Structure and Section A.2, Component Strings.

App Certificate

The path and filename for the Logging Application Certificate used to sign the application’s events.

By default, the Logging Application Certificates are available in the following directories:

  • sys:\system\naudit (NetWare)
  • \program files\novell\nsure audit\logschema\ (Windows)
  • /opt/novell/naudit//logschema/ (Linux)
  • /opt/NOVLnaudit/logschema/ (Solaris)

After Nsure Audit Report verifies the application’s events, it returns the verification results.

Figure 9-4 Nsure Audit Report Verification Results

If the event chain is authentic, Nsure Audit Report returns a message that the table’s events have been verified as authentic. If the event chain is not authentic, Nsure Audit Report lists each problem and its associated event.

There following table provides an explanation for each signature error.

Table 9-11 Signature Errors

Signature Error

Explanation

Logging application restarted. This is the first event after the restart, but it cannot be verified if events have been removed at the end of the previous chain.

The logging application shut down and restarted, so the event count field (ClientMS) started again at 0; therefore, the event chain was broken.

You can determine if the application restart was malicious or not by looking at the last event in the previous event chain. Logging applications send an event when they are unloaded, so if the last event in the previous event chain is an application unload event, you know that no events have been deleted.

This is the first event in the database, but not the first event in the chain. Earlier events are missing.

The current event is the first event in the database, however, the event count field (ClientMS) indicates this is not the first event in the chain.

This message occurs if you have rolled or expired your data store. You can use the following methods to determine if any events are missing:

  • If you expired your data store, you can look at the current event’s time stamp to see if it occurred at the time you expired the data store.
  • If you rolled the data store, you can look at the event count field for the last event in the archived data store to determine if it preceded the current event.

The previous event is missing.

The current event’s signature does not include the signature from the previous event.

Using the event count field (ClientMS), Nsure Audit Report can determine that only the previous event is missing.

x previous events are missing.

The current event’s signature does not include the signature from the previous event.

Using the event count field (ClientMS), Nsure Audit Report can determine approximately how many previous events are missing.

Event has been tampered with.

The current event’s signature is not valid.

Although it includes the signature from the previous event, the event data in the signature does not match the current data.

9.2.8 Working with Reports in Nsure Audit Report

In Nsure Audit Report, the term “reports” refers specifically to Crystal Decisions Report Template Files (*.rpt). Crystal Decisions Reports graphically summarize specific sets of log data in pie charts, bar charts, and so forth.

Nsure Audit Report allows you to import and run Crystal Decisions Reports. Some logging applications might also include their own predefined reports. You do not need Crystal Reports to run reports; however, if you have Crystal Reports, you can customize the predefined reports or you can design your own reports and import those reports into Nsure Audit Report.

Nsure Audit Report stores the report name and path in the Windows registry under HKEY_CURRENT_USER\Software\Novell\Log Report Application\1.0\Reports; however, the actual report file is stored in the directory structure.

The following sections discuss working with reports in Nsure Audit Report:

Importing Reports

To import Crystal Decisions Reports:

  1. In the Reports pane right-click, then select Insert.

  2. In the Name field, specify the name you want to appear in the Reports pane.

  3. In the File field, specify the path and filename for the Crystal Decisions Report.

    Click Browse to locate the file in the directory tree.

  4. When finished, click OK.

    Nsure Audit Report adds the report to the Reports pane.

Nsure Audit Report stores the report name and path in the Windows registry under HKEY_CURRENT_USER\Software\Novell\Log Report Application\1.0\Reports; however, the actual report file is stored in the directory structure.

Deleting Reports

To delete a Report in Nsure Audit Report:

  1. In the Reports pane, select the report you want to delete.

  2. Right-click, then click Delete.

Running Reports

You do not need Crystal Reports to run Crystal Decisions Reports in Nsure Audit Report.

To run a report in Nsure Audit Report:

  1. In the Databases pane, select the database you want to run the report on.

    NOTE:If you do not select a database, the report runs against the default database. For more information, see Defining Your Default Database and Table .

  2. In the Reports pane, select the report you want to run.

  3. Right-click, then select Run.

    Nsure Audit Report opens the report in the Report window.

  4. To update the report with live data, click the Refresh Data button Novell Audit Refresh Data icon on the Report toolbar.

Drilling Down on Report Data

After you run a report, Nsure Audit Report allows you to drill down on a specific field value. A drill-down report returns all records that match the selected field value.

To run a drill-down report, simply double-click the field value you want to query.

HINT:The mouse pointer appears as a magnifying glass over drill-down fields.

When you run a drill-down report, Nsure Audit Report returns all records that match the given field value. For example, if you drill down on a SourceIP field that has a value of 192.65.102.159, Nsure Audit Report returns all records that have a value of 192.65.102.159 in their SourceIP field.

Exporting Reports

Nsure Audit Report can export reports in a variety of formats including Adobe* Acrobat*, HTML, Microsoft Excel, ODBC, Rich Text Format (RTF), Microsoft Word, text, comma-separated values (CSV), and XML.

To export a report in Nsure Audit Report:

  1. Run a report.

    For step-by-step instructions, see Running Reports .

  2. Click File > Export.

  3. Select the file format that you want to export the report to (for example, .pdf, .txt, .xml, and so forth).

  4. Select the export destination.

  5. Click OK.

    Nsure Audit Report brings up the Save As dialog box.

  6. Specify the export filename, then click Save.

Nsure Audit Report exports the file in the designated format to the designated path and filename.

Printing Reports

To print a report to your default printer:

  1. Run a report.

    For step-by-step instructions, see Running Reports .

  2. Click the Print button on the Report toolbar.

9.2.9 Working with Queries in Nsure Audit Report

Nsure Audit Report uses queries to request information from MySQL and Oracle databases. All Nsure Audit Report queries are defined in SQL. Although you must be familiar with the SQL language to create SQL query statements, this is the most powerful and flexible query method.

If you are unfamiliar with the SQL language, Nsure Audit Report includes a Query Expert to help you define basic query statements. You can also import existing query sets and run them within Nsure Audit Report.

Nsure Audit Report stores all queries in the Windows registry under HKEY_CURRENT_USER\Software\Novell\Log Report Application\1.0\Queries.

The following sections provide details on working with queries in Nsure Audit Report:

Importing Queries

To import existing queries, you must save the queries in a text-based query file. The query file format requires that you enclose the title of each query in square brackets [ ]. The query statement is on the line following the title. If you want to enable the Translate Column Titles option, enter Translate=1 on the line following the query statement. Empty lines are not legal and any line that starts with a hash (#) is commented out.

For information on the Translate Column Titles option, see Manually Creating Queries.

The following is a sample query file. It contains two queries: All Connection Cleared events and All Directory Remove events.

# Query File
#
[All ’Connection Cleared’ events]
SQL=SELECT * FROM log WHERE eventid=655622 
Translate=1
[All ’Directory Remove’ events]
SQL=SELECT * FROM log WHERE eventid=655368 
Translate=1

To import SQL queries in Nsure Audit Report:

  1. Click File > Import > Query Set.

  2. Select the query file in the directory tree, then click Open.

The queries contained in the query file now appear in the Query pane.

All imported queries are stored in the Windows registry under HKEY_CURRENT_USER\Software\Novell\Log Report Application\1.0\Queries.

Manually Creating Queries

All queries are stored in the Windows registry under HKEY_CURRENT_USER\Software\Novell\Log Report Application\1.0\Queries.

To manually create a query in Nsure Audit Report:

  1. Click Query > Manual.

    You can also right-click in the Queries pane and select Insert.

  2. In the Name field, specify the name you want to use to refer to this query.

  3. Define the query statement in the Query window.

  4. Select Translate Column Titles if you want Nsure Audit Report to label the query results with the field titles defined in the log schema.

  5. When finished, click OK.

NOTE:When using mySQL, use the LIMIT statement to prevent an excessively large number of records from being returned. Even with a maximum value set in Nsure Audit Report, mySQL returns up to LIMIT records.

The query now appears in the Queries pane.

The following table provides further information on the Direct Query options.

Table 9-12 Direct Query Options

Query Option

Description

Name

The name you want to use to refer to this query.

The query name is listed in the Queries pane and it appears as the title in the query results window.

Query

The query statement.

Queries are defined using the SQL query language. For basic information on SQL queries, see the MySQL Reference Manual.

If you want your query to dynamically build the FROM clause using the currently selected database and table, enter FROM $l in the query statement.

Translate Column Titles

Select Translate Column Titles if you want Nsure Audit Report to label the column headings with the field titles defined in the log schema.

We recommend that you select this option only for queries that return one type of event. If you select this option for queries that return multiple types of events, Nsure Audit Report labels the column headings with the field titles from the last event returned in the query.

IMPORTANT:For this option to work, you must import each application’s log schema. For information, see Importing Log Schemas .

Creating Queries Using the Query Expert

IMPORTANT:To use the Query Expert, the account you use to log in to Windows XP must have rights to the Windows Home Directory. The Windows Home Directory is defined in the User Profiles. For more information, see Home Directory in Windows Help.

If you are unfamiliar with the SQL query language, you can use the Query Expert to help you define basic database queries. The Query Expert simplifies the process of creating a query by allowing you to choose from lists of predefined parameters. The Query Expert then constructs the query statement from the parameters you select.

Figure 9-5 Nsure Audit Report Query Expert

Because the Query Expert can provide only a limited set of parameters, the queries it creates are very simple. However, it is the easiest way to create queries and it is capable of creating most base-level queries.

To create a query using the Query Expert in Nsure Audit Report:

  1. Click Query > Expert.

  2. Specify the name you want to use to refer to this query in the Name field.

  3. Define the query parameters.

    1. In the Event tab, define the condition and the event.

    2. In the Timeperiod tab, select a time parameter.

  4. When finished, click OK.

NOTE:When using mySQL, use the LIMIT statement to prevent an excessively large number of records from being returned. Even with a maximum value set in Nsure Audit Report, mySQL returns up to LIMIT records.

The query now appears in the Queries pane.

The following table provides further information on the Query Expert’s parameters.

Table 9-13 Query Expert Parameters

Parameter

Description

Event

 

matches is less than is more than is between

The query condition.

Events

The event parameter.

The drop-down menu includes all the events for every application listed in the Events pane.

If the application’s log schema provides event descriptions, Nsure Audit Report displays those descriptions in the Event list; however, the events are still sorted by their numeric Event ID. Therefore, the event descriptions are not listed in alphabetical order, but related events are grouped together.

Timeperiod

The time parameter.

The query returns only events that occurred in the designated time frame.

The basic structure of query statements created with the Query Expert is as follows:

SELECT * FROM table WHERE eventid condition event(s) AND clienttimestamp> $time_parameter
Shortcuts

The Query Expert provides some quick and easy ways to create queries for all the events in a single application or for a single event.

To create a query for all the events in a single application:

  1. Select an application in the Events pane.

  2. Right-click, then select Define Query.

  3. In the Timeperiod tab, select a time parameter.

  4. Click OK.

To create a query for a single event:

  1. Expand one of the application folders in the Events pane.

  2. Select an event.

  3. Right-click, then select Define Query.

  4. In the Timeperiod tab, select a time parameter.

  5. Click OK.

Modifying Queries

To modify a Query in Nsure Audit Report:

  1. In the Queries pane, select the query you want to modify or delete.

  2. Right-click, then select Properties.

  3. Modify the query.

    For information on the query options, see Creating Saved Queries .

Deleting Queries

To delete a Query in Nsure Audit Report:

  1. In the Queries pane, select the query you want to delete.

  2. Right-click, then select Delete.

Custom Query Macros

Nsure Audit Report has some powerful custom macros that simplify data queries. The following table lists the custom query macros that can used in Nsure Audit Report.

IMPORTANT:All query macros must be preceded by a dollar sign ($).

Table 9-14 Nsure Audit Report Custom Query Macros

Function

Description

l

The currently selected database and table.

If you want your query to dynamically build the FROM clause using the currently selected database and table, enter FROM $l in the query statement.

Now

The current date and time in local time as defined in the workstation’s Windows settings.

ThisMonth

The current month in local time as defined in the workstation’s Windows settings

Today

The current day in local time as defined in the workstation’s Windows settings.

LastWeek

The previous week in local time as defined in the workstation’s Windows settings.

Yesterday

Yesterday in local time as defined in the workstation’s Windows settings.

Date(mm-dd-yyyy)

The given date in local time.

Hex(x)

The decimal value of hex value x

IP(address)

An IP address in IPv4 dotted address form, or in host name form.

Prompt(message)

Prompts the user for a value. When a query that contains this variable is run, the user is prompted to input a value based on text.

For example, select * from $l where text2=’$Prompt(Please Provide the User Name for which you want to query:)’;, prompts the user to provide a value based on the prompt provided by message. This value is then used in the query.

PromptDate(mm-dd-yyyy)

Prompts the user for a date value, then converts it into seconds since 1970, making it consistent with the way dates are stored in the Nsure Audit database. The date must be in the form mm-dd-yyyy.

PromptHex(hex_value)

Prompts the user for a hex value, and converts it to an integer value.

PromptIP(host_or_ip)

Prompts the user for a host name or dot-formatted IP address, and converts it to an integer consistent to the way IP addresses are stored in the Nsure Audit database.

The following sample query statement illustrates how the query macros can be used:

SELECT * FROM $l WHERE eventid='$Prompt(Please provide the Event ID to query:)' AND clienttimestamp>$WEEK

Running Queries

To run a query in Nsure Audit Report:

  1. Select the database you want to query.

    1. Click the Database field on the status bar.

    2. Select the default database from the list.

  2. Select the table you want to query.

    1. Click the Table field on the status bar.

    2. Select the default table from the drop-down list, then press Enter.

  3. In the Queries pane, select the query you want to run.

  4. Right-click, then select Run.

Nsure Audit Report returns the query results in a data table; rows represent individual records and columns represent fields within those records. You can click any of the column headings to sort the results by that field.

Figure 9-6 Query Results in Nsure Audit Report

If you selected the Translate Column Titles option when you defined the query, Nsure Audit Report labels the query results with the field titles defined in the log schema. Nsure Audit Report also displays each event’s field titles as you mouse over the event fields.

NOTE:We recommend that you select only the Translate Column Titles option for queries that return one type of event. If you select this option for queries that return multiple types of events, Nsure Audit Report labels the column headings with the field titles from the last event returned in the query. For more information on the Translate Column Titles option, see Manually Creating Queries.

Running Event Distributions

Event Distributions tell you how many times each type of event has occurred for a given application. For example, if you run an Event Distribution for NetWare, Nsure Audit Report returns the number of times each event listed in the NetWare log schema occurred.

To run an Event Distribution in Nsure Audit Report:

  1. Select the database you want to query.

    1. Click the Database field on the status bar.

    2. Select the default database from the list.

  2. Select the table you want to query.

    1. Click the Table field on the status bar.

    2. Select the default table from the drop-down list, then press Enter.

  3. In the Events pane, select an application.

  4. Right-click, then select Distribution.

Nsure Audit Report returns the number of times each of the application’s events occurred in the selected database.

Figure 9-7 Event Distribution Report in Nsure Audit Report

The Distribution window lists the Event ID and how many times that Event ID occurred. To sort on the Event ID, click the Event ID column. To sort by the number of occurrences, click the Count column.

If the application’s log schema provides event descriptions, Nsure Audit Report displays those descriptions in the Event ID column. However, when you sort on the Event ID column, events are sorted by their numeric Event ID, not by their description. Consequently, the event descriptions are not listed in alphabetical order, but related events are grouped together.

Counting Events

If you want to know how many events have been logged for a given application, or the number of occurrences for a specific event, you can run an event count. An event count simply returns the number of events logged to the current database.

To run an event count for a logging application in Nsure Audit Report:

  1. Select the database you want to query.

    1. Click the Database field on the status bar.

    2. Select the default database from the list.

  2. Select the table you want to query.

    1. Click the Table field on the status bar.

    2. Select the default table from the drop-down list, then press Enter.

  3. Select an application in the Events pane.

  4. Right-click, then select Count.

Nsure Audit Report returns the total number of events that have been logged for the selected application.

To run an event count for a single event:

  1. Select the database you want to query.

    1. Click the Database field on the status bar.

    2. Select the default database from the list.

  2. Select the table you want to query.

    1. Click the Table field on the status bar.

    2. Select the default table from the drop-down list, then press Enter.

  3. Expand an application folder in the Events pane.

  4. Select one of the application’s events.

  5. Right-click, then select Count.

Nsure Audit Report returns the total occurrences of the selected event.

Managing Query Results in Nsure Audit Report

After it returns the query results, Nsure Audit Report allows you to further process the data by dynamically sorting records, running drill-down queries, copying specific records, and viewing event properties.

Sorting Records

To sort the query results by a specific field, click the corresponding field heading. Nsure Audit Report toggles between ascending and descending order. The first time you click, Nsure Audit Report sorts the records in ascending order. If you click again, it sorts the record in descending order, and so forth.

Drilling Down on Query Data

After you run a report, Nsure Audit Report allows you to drill down on a specific field value. A drill-down report returns all records that match the selected field value.

To run a drill-down query:

  1. Position your mouse pointer over the field value you want to query.

  2. Right-click, then select Drill-down.

Nsure Audit Report returns all records that match the selected field value. For example, if you drill-down on a SourceIP field that has a value of 192.65.102.159, Nsure Audit Report returns all records that have a value of 192.65.102.159 in the SourceIP field.

Copying Records

To copy specific records from the query results:

  1. Select the records you want to copy.

    1. Shift-click to select contiguous records.

    2. Ctrl+click to select non-contiguous records.

  2. Right-click, then select Copy.

    You can also press Ctrl+C.

The query data can be copied to the Windows Clipboard in raw format, it can have field delimiters, and it can include field headers. How information is copied to the Windows Clipboard is managed through the Clipboard settings in the Nsure Audit Report Options menu. For more information, see Section 9.2.5, Setting Default Options in Nsure Audit Report.

NOTE:If the Copy All if No Entries Selected option is selected in the Options menu, you can copy all the records in the query results window by not selecting any records and pressing Ctrl+C.

Viewing Individual Records

To view a specific a specific record’s properties:

  1. Select the record you want to view.

  2. Right-click, then select Properties.

    You can also double-click the record.

Exporting Query Results in Nsure Audit Report

Nsure Audit Report can export query results in the following formats:

  • HTML (*.htm)
  • Comma-separated values (*.csv)
  • Text (tab-delimited) (*.txt)

To export query results in Nsure Audit Report:

  1. Run a query.

    For step-by-step instructions, see Running Queries .

  2. Click File > Export.

  3. In the Export Results menu, define the export file’s path and filename.

  4. Click the Save As Type drop-down list, then select the export format.

  5. Click Save.

Exporting Specific Records
  1. Select the records you want to export.

    1. Shift-click to select contiguous records.

    2. Ctrl+click to select non-contiguous records.

  2. Click File > Export.

Printing Query Results in Nsure Audit Report

To print the query results to your default printer:

  1. Run a query.

    For step-by-step instructions, see Running Queries .

  2. Click File > Print.

    You can also right-click, then select Print.

Printing Specific Records

To print specific records from the query results:

  1. Select the records you want to print.

    1. Shift-click to select contiguous records.

    2. Ctrl+click to select non-contiguous records.

  2. Click File > Print.

    You can also right-click, then select Print.