Collection Method (2) Socket/ODBC
- jake shin (Unlicensed)
Analytics
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.
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.
############################################################################### # 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.)
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.
[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 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.
*.* @@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.
$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.
# 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.
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.
*.* @@<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.
$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.
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.
## 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.
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:
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:
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.
[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.
$ isql -v MYSQL
If it is installed and configured normally, you will get the following results.
+---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL>
You can query the input data through isql.
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.
############################################################################### # 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.
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.