Get Data from SQL Database

Skip to end of metadata
Go to start of metadata

Getting data using Jdbc from SQL database

Settings in LOGalyze

To fech data periodically from a relational database or any Jdbc compatible data source you should define a Jdbc Collector in LOGalyze. See Collectors page what is a Jdbc type Collector.

As of version 4.1.2 you can only add a Jdbc collector manually by editing the conf/collectors.xml file. Here is a sample code for a Jdbc collector:

<collector id="a1b4d490-d631-431e-af01-01d717cc30dd">
  <name>Jdbc Collector t1</name>
  <weight>1</weight>
  <startup mode="automatic"/>
  <dtp>
   <type>jdbc</type>
   <parameter key="driver" value="com.mysql.jdbc.Driver"/>
   <parameter key="url" value="jdbc:mysql://localhost/jdbctest"/>
   <parameter key="username" value="root"/>
   <parameter key="password" value=""/>
   <parameter key="query" value="SELECT log_id, log_date, 'mysqlhost' AS loghostname, user, message FROM log WHERE log_id&gt;?"/>
   <parameter key="polling_interval" value="10"/>
   <parameter key="map.id.log_id" value="0"/>
   <parameter key="map.fields.timestamp" value="log_date"/>
   <parameter key="map.fields.loghostname" value="loghostname"/>
   <parameter key="map.fields.user_name" value="user"/>
   <parameter key="fetch_limit" value="100"/>
   <parameter key="timestamp_format" value=""/>
   <parameter key="reopen_time" value=""/>
  </dtp>
  <df>
   <type>identity</type>
  </df>
  <tag>jdbc</tag>
  <tag>test</tag>
 </collector>

Parameters:

  • driver: Jdbc driver. Driver jar must be in the classpath. The easiest way is to copy the jdbc driver jar into lib folder, it will be automatically added to the classpath.
  • url: Jdbc url. Contains the database type, hostname, port, schema (or database name)
  • username: Username for database connection
  • password: Password for database connection
  • query: SQL query to fetch data from database. WHERE clause is very important to avoid data duplication.
  • polling_interval: Polling interval in seconds.
  • map.id.<id_field_name_from_query>: initial value for ID field. Default is 0. In case of compound ID add more parameter with map.id prefix.
  • map.fields.timestamp: name of the timestamp field from query. In the sample above the field log_date contains the timestamp of the record.
  • timestamp_format: Date and time format string for map.fields.timestamp. Default value: yyyy-MM-dd HH:mm:ss Reference: [http://docs.oracle.com/javase/6/docs/api/java/text/SimpleDateFormat.html]
  • map.fields.loghostname: name of the source host name from query.
  • map.fields.<log_field_name>: map any field from query to an indexed log field. For example: map.fields.user_name will be the value from db field user.
  • fetch_limit: maximum number of rows fetched in one execution.

How it works?

Jdbc type collector stores the last ID value in a file under var/collector/ folder. The name of the file is the UUID of the collector (id parameter). If this file exists and contains a valid value, this will be the default map.id.<id_field_name_from_query> parameter and will be used in the WHERE clause of the SELECT query. After every fetch, the latest ID value will be saved into this file.

Jdbc Driver

The used jdbc driver class must be in the CLASSPATH. Practically put it into the lib folder and LOGalyze Engine startup script automatically puts it into the CLASSPATH.

Timestamp format

For parameter timestamp_format LOGalyze uses Java dateformat. Reference: [http://docs.oracle.com/javase/6/docs/api/java/text/SimpleDateFormat.html]

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.