Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Socket Method


The collector reads data from the socket, parses it, and enters it into the database server. To use socket mode, you must set the port number. With this mode, log data processed by programs such as rsyslog, logstash, and nxlog can be received and processed.

Additional Value Configuration

Setting the SOCKET_PORT variable is necessary to receive data in SOCKET mode.

Option Name

Description

Remarks

SOCKET_PORT

Port number on which to receive data

You must specify a port that other programs are not using.



UI Text Box

Index


Table of Contents
maxLevel3
indent30px
exclude목차
classtoc




Example (1) Creating Regular Expression/Template File

This example is to collect data in SOCKET mode. SOCKET mode opens a socket and waits for an external program to enter data.

It is recommended to use rsyslog to collect syslogs. This example uses rsyslog to collect and analyze data.
Data transmission is performed by another program. In the following configuration file, only the configuration of the rgx file and the tpl file is handled.

UI Text Box
sizemedium
typeinfo

The syslog.rgx used in the FILE mode data processing example is still available.


In the example below, the socket port number is 33333. This port must allow access from the firewall.

Code Block
###############################################################################
# Copyright of this product 2013-2023,
# Machbase Corporation (Incorporation) or its subsidiaries.
# All Rights reserved
###############################################################################

# This file is for Machbase collector template file.

###################################################################
# Collect setting
###################################################################

COLLECT_TYPE=SOCKET

SOCKET_PORT=33333

###################################################################
# Process setting
###################################################################

REGEX_PATH=syslog.rgx

###################################################################
# Output setting
###################################################################

DB_TABLE_NAME = "socket_syslogtable"
DB_ADDR = "127.0.0.1"
DB_PORT = 5656
DB_USER = "SYS"
DB_PASS = "MANAGER"

# 0: Direct insert
# 1: Prepared insert
# 2: Append
APPEND_MODE=2

# 0: None, just append.
# 1: Truncate.
# 2: Try to create table. If table already exists, warn it and proceed.
# 3: Drop and create.
CREATE_TABLE_MODE=2


Example (2) Running Collector

Use the socket_syslog.tpl file in the example above to create a collector and run it. (Creation and execution are the same as FILE mode.)

Code Block
languagebash
Mach> create collector localhost.file_syslog from "/home/machbase_home/collector/socket_syslog.tpl";
Created successfully.
 
Mach> ALTER COLLECTOR localhost.file_syslog START;
Altered successfully.

Run the following command to verify that the collector is running normally and is connected.

Code Block
[mach@localhost ~]$ netstat -anp | grep "LISTEN " | grep 33333
(Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.)
tcp 0 0 0.0.0.0:33333 0.0.0.0:* LISTEN 20818/machcollecto
[mach@localhost ~]$

From the above results, you can see that the collector is waiting for data input on port 33333.


Example (3) Data

Entry

Insert

Enter the data using rsyslog.

Log in the rsyslog program as a user with root privileges and create the rsyslog configuration file (/etc/rsyslog.d/.conf) as follows.

Code Block
*.* @@127.0.0.1:33333

Or you can make the following detailed settings.

Create the following file as "/etc/rsyslog.d/127.0.0.1_syslog.conf" file. When rsyslog is restarted, it outputs data to socket every time syslog data is generated.

Code Block
$ModLoad imfile

$InputFileName /var/log/syslog
$InputFileTag syslog_file:
$InputFileStateFile stat-syslog
$InputFilePollInterval 1
$InputRunFileMonitor

if $programname == 'syslog_file' then @@127.0.0.1:33333


Then restart the rsyslog daemon.

Code Block
# service rsyslog restart
rsyslog stop/waiting
rsyslog start/running, process 22936

When rsyslog is restarted, /var/log/syslog data is written to the port 127.0.0.1:33333.

The collector gathers data through a socket connection, analyzes it, and enters it into the Machbase server. 
Since the $InputFilePollInterval variable is set to 1 in the rsyslog setting, the data input rate may be slow at the beginning.
If the data is entered normally, the database server can check the data in the corresponding table using the following SELECT query.

Code Block
Mach> select * from socket_syslogtable order by _arrival_time asc limit 10;
COLLECTOR_TYPE COLLECTOR_ADDR
-----------------------------------------------------
COLLECTOR_ORIGIN COLLECTOR_OFFSET
----------------------------------------------------------------------------------------------------------
TM HOST
---------------------------------------------------------------------------------------------------------------------
MSG
------------------------------------------------------------------------------------
SOCKET 127.0.0.1
NULL 1
2016-06-28 23:50:17 000:000:000 localhost
syslog_file: Jun 20 04:31:43 localhost kernel: imklog 5.8.10, log source = /proc
/kmsg started.
SOCKET 127.0.0.1
NULL 2
2016-06-28 23:50:17 000:000:000 localhost
syslog_file: Jun 20 04:31:43 localhost rsyslogd: [origin software="rsyslogd" swV
ersion="5.8.10" x-pid="15062" x-info="http://www.rsyslog.com"] start
SOCKET 127.0.0.1
NULL 3
2016-06-28 23:50:17 000:000:000 localhost
syslog_file: Jun 20 04:31:46 localhost kernel: imklog 5.8.10, log source = /proc/kmsg started.
SOCKET 127.0.0.1
NULL 4
2016-06-28 23:50:17 000:000:000 localhost
syslog_file: Jun 20 04:35:01 localhost CROND[15111]: (root) CMD (LANG=C LC_ALL=C
/usr/bin/mrtg /etc/mrtg/mrtg.cfg --lock-file /var/lock/mrtg/mrtg_l --confcache-
file /var/lib/mrtg/mrtg.ok)
SOCKET 127.0.0.1
NULL 5
2016-06-28 23:50:17 000:000:000 localhost
syslog_file: Jun 20 04:40:01 localhost CROND[15188]: (root) CMD (LANG=C LC_ALL=C
/usr/bin/mrtg /etc/mrtg/mrtg.cfg --lock-file /var/lock/mrtg/mrtg_l --confcache-
file /var/lib/mrtg/mrtg.ok)
SOCKET 127.0.0.1
NULL 6
2016-06-28 23:50:17 000:000:000 localhost
syslog_file: Jun 20 04:40:01 localhost CROND[15187]: (root) CMD (/usr/lib64/sa/s
a1 1 1)
SOCKET 127.0.0.1
NULL 7
2016-06-28 23:50:17 000:000:000 localhost
syslog_file: Jun 20 04:45:01 localhost CROND[15265]: (root) CMD (LANG=C LC_ALL=C
/usr/bin/mrtg /etc/mrtg/mrtg.cfg --lock-file /var/lock/mrtg/mrtg_l --confcache-
file /var/lib/mrtg/mrtg.ok)
SOCKET 127.0.0.1
NULL 8
2016-06-28 23:50:17 000:000:000 localhost
syslog_file: Jun 20 04:50:01 localhost CROND[15341]: (root) CMD (LANG=C LC_ALL=C
/usr/bin/mrtg /etc/mrtg/mrtg.cfg --lock-file /var/lock/mrtg/mrtg_l --confcache-
file /var/lib/mrtg/mrtg.ok)
SOCKET 127.0.0.1
NULL 9
2016-06-28 23:50:17 000:000:000 localhost
syslog_file: Jun 20 04:50:01 localhost CROND[15342]: (root) CMD (/usr/lib64/sa/s
a1 1 1)
SOCKET 127.0.0.1
NULL 10
2016-06-28 23:50:17 000:000:000 localhost
syslog_file: Jun 20 04:55:01 localhost CROND[15419]: (root) CMD (LANG=C LC_ALL=C
/usr/bin/mrtg /etc/mrtg/mrtg.cfg --lock-file /var/lock/mrtg/mrtg_l --confcache-
file /var/lib/mrtg/mrtg.ok)
[10] row(s) selected.

Log Collector Configuration


The socket input mode example is executed using rsyslog, logstash, and nxlog .

When these programs input log data through a socket, the collector collects them and inputs them to the database server.

rsyslog

rsyslog is often included by default in recent Linux distributions. 

So there is no need to install it any more. Just add the configuration file to the /etc/rsyslog.d/ directory and restart the rsyslog daemon.
rsyslog does not only transmit log data that is already recorded, but also transmits data whenever a new log data is recorded.
Below is a list of settings.


Simple Setting: Set Log Forwarding Address

This is the only way to specify the address to be forwarded when the log is created. It is simpler than other methods. 

Restart rsyslog after writing the following in /etc/rsyslog.d/.conf file.

Code Block
*.* @@<Collector host>:<Collector port>

The syslog data is then sent to collector host: port  .


Complex Setting: Set Input Log File / Transmission Frequency

A more complex method is to set the input log file and transmission frequency.

Code Block
$ModLoad imfile

$InputFileName /var/log/syslog
$InputFileTag syslog_file:
$InputFileStateFile stat-syslog
$InputFilePollInterval 1
$InputRunFileMonitor

if $programname == 'syslog_file' then @@<Collector host>:<Collector port>

Create a file that ends in .conf in the /etc/rsyslog.d/ folder, and then restart rsyslog.

See the rsyslog description for more details .

logstash

To install logstash, refer to Getting Started with Logstash .

You can modify the logstash conf file to send the desired data to the socket.
See the example below.

Code Block
input {
   file {
      path => "<Absolute path of log file>"
      }
   }
output {
   tcp {
      host => "<Collector host>"
      port => "<Collector port>"
        }
      }
  • Set the location of the input data file in the "input" section. If you want to enter syslog, set /var/log/syslog.
  • In the "output" section, you need to enter the collector's tcp socket, so set tcp and set ip and port number.


Nxlog

Nxlog is a log collector for Windows.

The configuration of the rgx, tpl file for the collector for the socket input mode is the same, and an example of the configuration file for nxlog is as follows.
Usually nxlog is installed in "C:\Program Files\nxlog" or "C:\Program Files (x86)\nxlog".
Create a configuration file located in the above path as follows.

Code Block
## This is a sample configuration file. See the nxlog reference manual about the
## configuration options. It should be installed locally and is also available
## online at http://nxlog-ce.sourceforge.net/nxlog-docs/en/nxlog-reference-manual.html


## Set the ROOT to the folder your nxlog was installed,
## otherwise it will not start.


#define ROOT C:\Program Files\nxlog
define ROOT C:\Program Files\nxlog


Moduledir %ROOT%\modules
CacheDir %ROOT%\data
Pidfile %ROOT%\data\nxlog.pid
SpoolDir %ROOT%\data
LogFile %ROOT%\data\nxlog.log


<Input in>
Module im_msvistalog
# For windows 2003 and earlier use the following:
# Module im_mseventlog
</Input>


<Output out>
Module om_tcp
Host <Collector host>
Port <Collector port>
</Output>


<Route 1>
Path in => out
</Route>

In the example above, when data is written to the im_msvistalog file, data transmission through the socket is set to <collector ip>: <collector port>.

Changing the configuration file and restarting the service will send the data to the collector via the socket.
Refer to the nxlog manual for details.


ODBC Method


ODBC mode is a method of collecting data from a database that can be accessed by an ODBC connection to a collector.

In Linux environments, you need to install the unixODBC package. 
The following example shows how to collect data from a MySQL database through unixODBC.
Please refer to the respective websites for how to install unixODBC and MyODBC .

Additional Value Configuration

The following variables must be set.

Option Name

Description

Remarks

ODBC_DSN

DSN for accessing the database

You must use the DSN described in the ODBC configuration.

ODBC_QUERY

Query for data retrieval


ODBC_SEQ_COLUMN

Column name of incremental value

Must be one of the columns to be queried. Only numeric types are supported.

Example (1) Generating Data

You must first enter the MySQL data. Enter data as follows.

Code Block
0,2015-05-20 06:00:00,16.194.51.72,6790,183.103.50.46,5281,20,GET /twiki/bin/view/TWiki/KlausWriessnegger HTTP/1.1,200,3848
1,2015-05-20 06:00:02,96.40.75.42,11011,31.224.72.52,12069,55,GET /twiki/bin/search/Main/SearchResult?scope=text®ex=on&search=Office%20*Locations[notA-Za-z] HTTP/1.1,200,7771
2,2015-05-20 06:00:02,174.47.129.59,6032,96.40.75.42,6442,72,GET /twiki/bin/edit/Main/WebSearch?t=1078669682 HTTP/1.1,401,12846
3,2015-05-20 06:00:02,153.199.166.54,4220,86.45.186.17,2245,1,GET /twiki/bin/oops/TWiki/RichardDonkin?template=oopsmore¶m1=1.2¶m2=1.2 HTTP/1.1,200,11281
4,2015-05-20 06:00:02,226.7.237.25,10805,50.230.44.173,179,70,GET /twiki/bin/oops/TWiki/AppendixFileSystem?template=oopsmore¶m1=1.12¶m2=1.12 HTTP/1.1,200,11382
5,2015-05-20 06:00:02,183.103.50.46,7175,96.128.212.177,7175,73,GET /twiki/bin/view/TWiki/DontNotify HTTP/1.1,200,4140
6,2015-05-20 06:00:02,123.198.82.192,6784,63.214.191.124,10825,21,GET /twiki/bin/view/TWiki/DontNotify HTTP/1.1,200,4140
7,2015-05-20 06:00:02,214.153.107.182,5562,85.183.139.166,1367,8,GET /twiki/bin/oops/TWiki/RichardDonkin?template=oopsmore¶m1=1.2¶m2=1.2 HTTP/1.1,200,11281
8,2015-05-20 06:00:02,245.13.24.17,7451,69.99.246.62,4497,20,GET /twiki/bin/view/Main/SpamAssassin HTTP/1.1,200,4081
9,2015-05-20 06:00:02,239.81.105.222,2245,71.129.68.118,1641,59,GET /twiki/bin/view/Main/TokyoOffice HTTP/1.1,200,3853

 The table columns to be collected are seq, at, srcip, srcport, dstip, dstport, protocol, eventlog, eventcode, and eventsize. Set seq as a sequential increment column. To create a table with this structure, perform the following query in mysql:

Code Block
mysql> create table odbc_seq_int_10 (seq int(9), at timestamp, srcip varchar(20), srcport int(6), dstip varchar(20), dstport int(6), protocol int(3), eventlog varchar(1024), eventcode int(9), eventsize int(9));

After successfully creating the table, enter the data into the MySQL database with the following command:

Code Block
mysql> load data infile '<File Path>' into table <Table Name> fields terminated by ',';
Query OK, 10 rows affected (0.00 sec)
Records: 10 Deleted: 0 Skipped: 0 Warnings: 0

For more information about running MySQL databases, see the MySQL manual.


Example (2) ODBC Configuration

Create an ODBC configuration file to access the MySQL database.

Code Block
[MYSQL]
Driver=MySQL
Server=<Database host address>
Port=<Database host port>
Database=<Name of default database to access>
UID=<User ID>
PWD=<User password>

In unixODBC, USER DATA SOURCES is used first, so make the above contents in the .odbc.ini file in the user home directory that runs collector.

Example (3) Checking ODBC Settings

Use the unixODBC isql program to verify that the ODBC configuration is working properly. Execute the following as a parameter of MYSQL which is the set DSN.

Code Block
$ isql -v MYSQL

If it is installed and configured normally, you will get the following results.

Code Block
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>


You can query the input data through isql.

Code Block
SQL> select * from odbc_seq_int_10;
+-----+---------------------+-----------------+---------+----------------+---------+----------+---------------------------------------------------------------------------------------------------------+-----------+-----------+
| seq | at                  | srcip           | srcport | dstip          | dstport | protocol | eventlog                                                                                                | eventcode | eventsize |
+-----+---------------------+-----------------+---------+----------------+---------+----------+---------------------------------------------------------------------------------------------------------+-----------+-----------+
| 0   | 2015-05-20 06:00:00 | 16.194.51.72    | 6790    | 183.103.50.46  | 5281    | 20       | GET /twiki/bin/view/TWiki/KlausWriessnegger HTTP/1.1                                                    | 200       | 3848      |
| 1   | 2015-05-20 06:00:02 | 96.40.75.42     | 11011   | 31.224.72.52   | 12069   | 55       | GET /twiki/bin/search/Main/SearchResult?scope=text®ex=on&search=Office%20*Locations[notA-Za-z] HTTP/1.1 | 200       | 7771      |
| 2   | 2015-05-20 06:00:02 | 174.47.129.59   | 6032    | 96.40.75.42    | 6442    | 72       | GET /twiki/bin/edit/Main/WebSearch?t=1078669682 HTTP/1.1                                                | 401       | 12846     |
| 3   | 2015-05-20 06:00:02 | 153.199.166.54  | 4220    | 86.45.186.17   | 2245    | 1        | GET /twiki/bin/oops/TWiki/RichardDonkin?template=oopsmore¶m1=1.2¶m2=1.2 HTTP/1.1                        | 200       | 11281     |
| 4   | 2015-05-20 06:00:02 | 226.7.237.25    | 10805   | 50.230.44.173  | 179     | 70       | GET /twiki/bin/oops/TWiki/AppendixFileSystem?template=oopsmore¶m1=1.12¶m2=1.12 HTTP/1.1                 | 200       | 11382     |
| 5   | 2015-05-20 06:00:02 | 183.103.50.46   | 7175    | 96.128.212.177 | 7175    | 73       | GET /twiki/bin/view/TWiki/DontNotify HTTP/1.1                                                           | 200       | 4140      |
| 6   | 2015-05-20 06:00:02 | 123.198.82.192  | 6784    | 63.214.191.124 | 10825   | 21       | GET /twiki/bin/view/TWiki/DontNotify HTTP/1.1                                                           | 200       | 4140      |
| 7   | 2015-05-20 06:00:02 | 214.153.107.182 | 5562    | 85.183.139.166 | 1367    | 8        | GET /twiki/bin/oops/TWiki/RichardDonkin?template=oopsmore¶m1=1.2¶m2=1.2 HTTP/1.1                        | 200       | 11281     |
| 8   | 2015-05-20 06:00:02 | 245.13.24.17    | 7451    | 69.99.246.62   | 4497    | 20       | GET /twiki/bin/view/Main/SpamAssassin HTTP/1.1                                                          | 200       | 4081      |
| 9   | 2015-05-20 06:00:02 | 239.81.105.222  | 2245    | 71.129.68.118  | 1641    | 59       | GET /twiki/bin/view/Main/TokyoOffice HTTP/1.1                                                           | 200       | 3853      |
+-----+---------------------+-----------------+---------+----------------+---------+----------+---------------------------------------------------------------------------------------------------------+-----------+-----------+
SQLRowCount returns 10
10 row(s) fetched.
SQL>

If you can not get these results, check the names of unixODBC and DSN. Refer to the unixODBC documentation for related details.

Example (4) Collector Configuration

Create a tpl file using the query, DSN, username and password used above. 

In the ODBC mode input method, REGEX_PATH is not needed because data is provided separately for each column.

Code Block
###############################################################################
# Copyright of this product 2013-2023,
# Machbase Corporation (Incorporation) or its subsidiaries.
# All Rights reserved
###############################################################################

#
# This file is for Machbase collector template file.
#

###################################################################
# Collect setting
###################################################################

COLLECT_TYPE=ODBC
ODBC_DSN=MYSQL <= Name of driver specified in "odbc.ini"
ODBC_QUERY="select * from sample_seq_int_10" <= Not required to input other queries except select <Columns> from <Table name>.
ODBC_SEQ_COLUMN=seq <= The base column name that determines the order in the query results

###################################################################
# Process setting
###################################################################

#PREPROCESS_PATH=Python script file path

###################################################################
# Output setting
###################################################################

DB_TABLE_NAME = "sample_seq_int"
DB_ADDR = <Machbase Server Host>
DB_PORT = <Machbase Server Port>
DB_USER = "SYS"
DB_PASS = "MANAGER"

# 0: Direct insert
# 1: Prepared insert
# 2: Append
APPEND_MODE=2

# 0: None, just append.
# 1: Truncate.
# 2: Try to create table. If table already exists, warn it and proceed.
# 3: Drop and create.
CREATE_TABLE_MODE=2

Use the above tpl file to create and start the collector, and then check the results using machsql.

Code Block
Mach> select * from sample_seq_int limit 50;
COLLECTOR_TYPE COLLECTOR_ADDR
-----------------------------------------------------
COLLECTOR_ORIGIN COLLECTOR_OFFSET SEQ
-----------------------------------------------------------------------------------------------------------------------
AT SRCIP SRCPORT DSTIP DSTPORT PROTOCOL
-----------------------------------------------------------------------------------------------------------------------
EVENTLOG EVENTCODE EVENTSIZE
--------------------------------------------------------------------------------------------------------------
ODBC 192.168.122.1
MYSQL 9 9
1900-01-03 19:54:55 000:000:000 239.81.105.222 2245 71.129.68.118 1641 59
GET /twiki/bin/view/Main/TokyoOffice HTTP/1.1 200 3853
ODBC 192.168.122.1
MYSQL 8 8
1900-01-03 19:54:55 000:000:000 245.13.24.17 7451 69.99.246.62 4497 20
GET /twiki/bin/view/Main/SpamAssassin HTTP/1.1 200 4081
ODBC 192.168.122.1
MYSQL 7 7
1900-01-03 19:54:55 000:000:000 214.153.107.182 5562 85.183.139.166 1367 8
GET /twiki/bin/oops/TWiki/RichardDonkin?template=oopsmore¶m1=1.2¶m2=1.2 HTTP/1
.1 200 11281
ODBC 192.168.122.1
MYSQL 6 6
1900-01-03 19:54:55 000:000:000 123.198.82.192 6784 63.214.191.124 10825 21
GET /twiki/bin/view/TWiki/DontNotify HTTP/1.1 200 4140
ODBC 192.168.122.1
MYSQL 5 5
1900-01-03 19:54:55 000:000:000 183.103.50.46 7175 96.128.212.177 7175 73
GET /twiki/bin/view/TWiki/DontNotify HTTP/1.1 200 4140
ODBC 192.168.122.1
MYSQL 4 4
1900-01-03 19:54:55 000:000:000 226.7.237.25 10805 50.230.44.173 179 70
GET /twiki/bin/oops/TWiki/AppendixFileSystem?template=oopsmore¶m1=1.12¶m2=1.12
HTTP/1.1 200 11382
ODBC 192.168.122.1
MYSQL 3 3
1900-01-03 19:54:55 000:000:000 153.199.166.54 4220 86.45.186.17 2245 1
GET /twiki/bin/oops/TWiki/RichardDonkin?template=oopsmore¶m1=1.2¶m2=1.2 HTTP/1
.1 200 11281
ODBC 192.168.122.1
MYSQL 2 2
1900-01-03 19:54:55 000:000:000 174.47.129.59 6032 96.40.75.42 6442 72
GET /twiki/bin/edit/Main/WebSearch?t=1078669682 HTTP/1.1 401 12846
ODBC 192.168.122.1
MYSQL 1 1
1900-01-03 19:54:55 000:000:000 96.40.75.42 11011 31.224.72.52 12069 55
GET /twiki/bin/search/Main/SearchResult?scope=text®ex=on&search=Office%20*Locat
ions[notA-Za-z] HTTP/1.1 200 7771
ODBC 192.168.122.1
MYSQL 0 0
1900-01-03 19:54:55 000:000:000 16.194.51.72 6790 183.103.50.46 5281 20
GET /twiki/bin/view/TWiki/KlausWriessnegger HTTP/1.1 200 3848
[10] row(s) selected.

You can see that the data is output in reverse order to the MySQL output.