Objective

EventLog Analyzer can audit PostgreSQL logs via log import feature, This article focuses on how to import the PostgreSQL logs via log import in EventLog Analyzer.

Prerequisites

• Availability of license purchased.
  
  For build lower than 13000 - EventLog Analyzer consumes one application license for each import and each unique log format identified during log file import - Importing the same file twice with different formats will consume two licenses.
  For build from 13000 & higher - Log import feature comes as built-in functionality when log source device is added in the application.

• The file to be imported has to be plain text and not encrypted.

• The necessary ports and permissions to perform log import are provided in this chart:

 Steps to follow 

1. To add the PostgreSQL for monitoring, configuration on the source is required.

Log format of PostgreSQL logs is determined by log_line_prefix parameter, set in postgresql.conf file.

The default format of PostgreSQL logs is '%m [%p] ' which logs a time stamp and the process ID.

log_line_prefix = '%m [%p] '

This format is supported by default in EventLog Analyzer.

Importing additional fields in EventLog Analyzer  :

If the user wants to add additional fields, log_line_prefix parameter in the postgresql.conf file must be changed.

The log_line_prefix parameter must follow the format(key- value pair) given below in the postgresql.conf file.

log_line_prefix format:  

log_line_prefix = 'time_stamp=%m or %t process_id=%p application_name=%a database_name=%d connection_from_with_port=%r connection_from=%h session_id=%c transaction_id=%x user_name=%u command_tag=%i sql_state_code=%e session_start_time=%s '

2. Import the PostgreSQL application logs from the remote path. Importing log files from a remote path in EventLog Analyzer is possible using either of one protocol as follows:

• SMB - Windows

• FTP

• SFTP
  

Importing the log file from a remote location requires authentication. This authentication can be achieved in two ways:

• Username and password - Used by SMB - Windows, FTP and SFTP
  

• SSH private key file sharing (specific to SFTP protocol)

 Case 1: Authentication type: Username and password 

• Step 1: Click the + Import Logs option in the top right corner of the screen.

• Step 2: In the Browse File(s) section, select Remote Path tab.

• Step 3: In the Device field, enter the device name from which you wish to import the log file or click the + icon to browse for and select the Windows device.

• Step 4: In the Protocol drop-down, choose the required protocol, SMB-Windows or FTP and SFTP, and enter the port number.

• Step 5: Provide the Username of the remote device, and the Password field will pop up.

• Step 6: Enter the Password in the field.

• Step 7: Use the Browse button to search for and select the file to be imported and click OK.

• Step 8: Select the Log format as PostgreSQL Logs .The Store Logs for Short-term option will retain the imported log data in EventLog Analyzer for two days. If the option is left unchecked, the logs will be stored as per your data retention configuration.

• Step 9: You can choose to schedule the log import at specific time intervals. You can also schedule the log collection by reading the same file on periodic manner or by creating a specific naming conventions for files. Refer How to Perform Scheduled Import Log Collection in EventLog Analyzer for more details.

• Step 10: Click Import to save the configuration.
  

 Case 2: Authentication type: SFTP-based SSH private key file sharing 

• Step 1: From the Browse File(s) options listed, select Remote Path.

• Step 2: In the Device field, enter the device name from which you wish to import the log file. Alternatively, you can click the + icon to browse for and select the Windows device.

• Step 3: Choose SFTP as the protocol and enter the port number (default port value is 22).

• Step 4: Provide the Username and choose Key File as the Authentication Type.

Note: EventLog Analyzer supports OpenSSH key file format only.

• Step 5: Click Browse and select the key file from the device. You can refer to this page to learn how to generate a key file with ssh-keygen, a standard component of the Secure Shell protocol.

• Step 6: If the key file is passphrase protected, select the Use Passphrase check box and enter the phrase in the field.

• Step 7: In the File field, click the Browse button to browse for and select the file to be imported.

• Step 8: Select the Log format as PostgreSQL Logs. The Store Logs for Short-term option will store the imported log data in EventLog Analyzer for two days. If the option is left unchecked, the logs will be stored as per your data retention configuration.

• Step 9: You can choose to schedule the log import at specific time intervals. You can also schedule the log collection by reading the same file on periodic manner or by creating a specific naming conventions for files. Refer How to Perform Scheduled Import Log Collection in EventLog Analyzer for more details.

• Step 10: Click Import to save the configuration.

For more details, refer to Importing log files from different locations.

 Tips 

1. Log import consumes one Application license for each import performed. If you are importing for analysis, you can select the Store Logs for Short-term option, which will store the imported log data in EventLog Analyzer for two days. If the option is left unchecked, the logs will be stored as per data retention configuration.
2. Log format will be set as Automatically Identify by default. You can select the PostgreSQL logs manually and extract new fields during log import. Refer How to Extract Fields in Log Import in EventLog Analyzer
3. The Scheduled import option is available in both Remote and S3 bucket imports. Refer How to perform scheduled import log collection in EventLog Analyzer.

 Related topics and articles 

• How to perform scheduled import log collection in EventLog Analyzer

• How to import logs in EventLog Analyzer from local path

• How to import logs in EventLog Analyzer from S3 Bucket