LogLogic General Database Collector for Microsoft SQL Server Log Configuration Guide

(1)

LogLogic General Database Collector 

for Microsoft SQL Server

(2)

Proprietary Information

This document contains proprietary and confidential information of LogLogic, Inc. and its licensors.  In accordance with the license, this document may not be copied, disclosed, modified, transmitted, or translated except as permitted in writing by LogLogic, Inc.

Trademarks

LogLogic and the LogLogic logo are trademarks or registered trademarks of LogLogic, Inc. in the United States and/or foreign countries. All other company or product names are trademarks or registered trademarks of their respective owners.

Notice

The information contained in this document is subject to change at any time without notice. All

warranties with respect to the software and accompanying documentation are set our exclusively in the Software License Agreement or in the Product Purchase Agreement that covers the documentation.

LogLogic, Inc.

(3)

LogLogic is committed to the success of our customers and to ensuring our products improve customers' ability to maintain secure, reliable networks. Although LogLogic products are easy to use and maintain, occasional assistance might be necessary. LogLogic provides timely and comprehensive customer support and technical assistance from highly knowledgeable,

experienced engineers who can help you maximize the performance of your LogLogic Appliances. To reach LogLogic Customer Support:

Telephone: Toll Free—1-800-957-LOGS Local—1-408-834-7480

EMEA or APAC: + 44 (0) 207 1170075 or +44 (0) 8000 669970 Email: [email protected]

You can also visit the LogLogic Support website at: http://www.loglogic.com/services/support.  When contacting Customer Support, be prepared to provide:

Your name, email address, phone number, and fax number Your company name and company address

Your machine type and release version

A description of the problem and the content of pertinent error messages (if any)

Documentation Support

Your feedback on LogLogic documentation is important to us. Send e-mail to

[email protected] if you have questions or comments. Your comments will be reviewed and addressed by the LogLogic technical writing team.

(6)

Conventions

LogLogic documentation uses the following conventions to highlight code and command-line elements:

A monospace font is used for programming elements (such as code fragments, objects,

methods, parameters, and HTML tags) and system elements (such as filenames, directories, paths, and URLs).

A monospace bold font is used to distinguish system prompts or screen output from

user responses, as in this example: username: system

home directory: home\app

A monospace italic font is used for placeholders, which are general names that you

replace with names specific to your site, as in this example:  LogLogic_home_directory\upgrade\

Straight brackets signal options in command-line syntax. For example:

(7)

Chapter 1 – Configuring LogLogic’s General

Database Collector for Microsoft SQL Server

This chapter describes the configuration steps involved to enable a LogLogic Appliance to collect log records that are stored in a Microsoft SQL Server database table.

Prerequisites . . . 7

Enabling the LogLogic Appliance to Capture Log Data . . . 7

Testing Connectivity . . . . 10

Verifying the Configuration . . . 10

Prerequisites

Prior to configuring the General Database Collector for Microsoft SQL Server and LogLogic Appliance, ensure that you meet the following prerequisites:

Microsoft SQL Server 2000 SP4 running on Windows Server 2000/2003. Microsoft SQL Server 2005 SP1 running on Windows Server 2003/2008. Microsoft SQL Server 2008 running on Windows Server 2008/R2. Name of database table from which log data will be retrieved.

Database table must contain a Timestamp field that specifies the chronological order of the

table rows.

Note: LogLogic uses the General Database Collector for Microsoft SQL Server to retrieve log data directly from the specified table within the SQL server database.

LogLogic appliance 4.9.1 or higher with log source package for General Database Collector

for Microsoft SQL Server.

A Microsoft SQL Server User account with db_datareader and public database role access at

the minimum.

Administrator access on the LogLogic Appliance.

Enabling the LogLogic Appliance to Capture Log Data

(8)

To add General Database Collector for Microsoft SQL Server Device:

1. Log in to the LogLogic Appliance.

2. From the navigation menu, select Management > Devices. The Devices tab appears.

3. Click Add New.

The Add Device tab appears.

4. Type in the following information for the device:

Name—Name for the General Database Collector for Microsoft SQL Server device Description (optional)—Description of the General Database Collector for Microsoft

SQL Server device

Device Type—Select General Database Collector for Microsoft SQL Server from the

drop-down menu

Host IP—IP address of the General Database Collector for Microsoft SQL Server

appliance

Enable Data Collection—Select the Yes radio button

Refresh Device Name through DNS Lookups (optional)—Select this checkbox to

enable the Name field to be automatically updated. The name is obtained using a reverse DNS lookup on the configured refresh interval. The DNS name overrides any manual name you assign.

5. Configure:

Server Port—Port number for the database. UserID—Login userid for the database.

Password/Confirm Password—Use password to access the database.

Start Collection From Date—Specifies the start point for the first collection only; not

used for subsequent collections.

Polling Interval—The time between collection cycles in minutes.

Note: User can collect log data from multiple database tables, to specify different database tables use “Add Row” button and provide the following input data for each database table.

Database Name—The database from which to collect logs. Database Table—The name of the table which contains the logs.

Query Columns—to restrict which columns are collected, a comma-separated list of

columns to collect, which must include the timestamp column.

Timestamp Column—The name of the column which holds the timestamp of the log.

Note: If more then one timestamp column is present in the table being collected from, the collector will use the first timestamp column for its cursor. To order the timestamp columns you will need to specify the table column order in the 'Query Columns' dialog box, by listing out order of the columns to be collected.

Max Rows per Query—The max number of result rows to be retrieved during each

(9)

Figure 1 Adding General Database Collector for Microsoft SQL Server as a Device to the LogLogic Appliance

6. Click Add.

(10)

Testing Connectivity

After configuring the General Database Collector for Microsoft SQL Server and the LogLogic Appliance, you should test the connectivity between the Microsoft SQL Server server's database and the Appliance.

To test connectivity:

1. Select from the left navigation pane, Management > Devices. The Devices tab appears.

2. Select the device name for General Database Collector for Microsoft SQL Server as added earlier.

3. Click the Test button to verify that a valid connection to the database has been obtained and the count for the number of logs eligible for collection.

Figure 2 Connection Test

If the connection fails, an error displays and in some cases a potential diagnosis. Also, the number of eligible log records to be collected is displayed.

Verifying the Configuration

This section describes how to verify that the configuration changes made to General Database Collector for Microsoft SQL Server and the LogLogic Appliance are applied correctly.

To verify the configuration:

1. Log in to the LogLogic Appliance.

2. From the navigation menu, select Dashboards > Log Source Status. The Log Source Status tab appears.

(11)

Figure 3 Verification of the General Database Collector for Microsoft SQL Server Configuration

4. To further verify that data is being collected you can view the data in the search reports to check if the logs were obtained.

If the device does not appear in the Log Source Status tab, check the Microsoft SQL Server database table for events that should have been collected. If events were detected and are still not appearing on the LogLogic Appliance, verify the General Database Collector for Microsoft SQL Server configuration, and the LogLogic Appliance configuration.

If the device name appears in the list of devices but log data for the device is not

appearing within your search reports, you need to verify that your database connection is up and running properly. For more information, see Testing Connectivity on page 10 and Troubleshooting on page 13.

(12)

Chapter 2 – How LogLogic Supports General

Database Collector for Microsoft SQL Server

This chapter describes LogLogic's support for General Database Collector for Microsoft SQL Server. LogLogic enables you to collect log records that are stored in a Microsoft SQL Server database table.

How LogLogic Appliance Captures Log Records Stored in a Microsoft SQL Server Database Table . . . 12

How LogLogic Appliance Captures Log Records Stored in a

Microsoft SQL Server Database Table

A collector is required to fetch the log data from a Microsoft SQL Server database table. The General Database Collector for Microsoft SQL Server collects the log data from a Microsoft SQL Server database table in a sequential manner and sends database logs to appliance which will generate logs in Key-Value pair format.

The collector maintains a progress cursor, which is the timestamp of the first table row not yet collected. The timestamp of each row is found in the specified timestamp column. Before the first collection cycle, the progress cursor is the configured start time.

(13)

Chapter 3 – Troubleshooting and FAQ

This chapter contains troubleshooting regarding the configuration and/or use of log collection for General Database Collector for Microsoft SQL Server. It also contains an FAQ, providing quick answers to common questions.

Troubleshooting . . . 13

Frequently Asked Questions (FAQ) . . . 13

Troubleshooting

Problems Retrieving Log Files Using Configured Collector

If you are having general problems retrieving log files using your configured collector, you may need to verify that your LogLogic Appliance is receiving logs from the Microsoft SQL Server table as scheduled.

1. Log in to the LogLogic application managing the General Database Collector for Microsoft SQL Server log data.

2. Select from the navigation pane, Management > Devices. The Devices tab appears.

3. Select the device name for General Database Collector for Microsoft SQL Server as added earlier.

4. Click the Test button to verify that a valid connection to the database has been obtained and the count for the number of logs eligible for collection.

Alternatively, you can run an Index Search against as follows:

1. In the navigation menu, click Search > Index Search.

2. Specify LogLogic Appliance as the Device Type and choose the appropriate Source Device.

3. Enter a Boolean Search. For example, to return file collector-related logs, type: engine_filecollector.

Frequently Asked Questions (FAQ)

How does the LogLogic appliance obtain the data from the Microsoft SQL Server database

table?

(14)

LogLogic General Database Collector for Microsoft SQL Server Log Configuration Guide