[ElasticSearch] ELK 사용하여 로그분석 시스템 만들기 (MS-SQL 연동)

Mssql에 쌓이는 로그들을 분석하기 위한 시스템 구축

구성요소

설치 대상 서버 : Windows Server 2012 R2

VersionRole

Logstash	7.11	로그 수집, Parsing, Indexing
Elasticsearch	7.11	데이터 저장, 분석, 검색 엔진
Kibana	7.11	시각화 도구
MSSQL JDBC Driver	7.2	Java프로그램에서 MSSQL 접근을 위한 프로그램

ELK 7 Version 사용 이유

• ELK 7 Version은 OpenJDK가 Bundle로 구성되어 사용가능함Oracle JAVA 사용할 필요가 없음!!
• 무료버전에서 핵심 보안 기능 제공
  • 암호화된 통신
  • 역할 기반 액세스 제어
  • 자세한 내용은 Elastic Stack 구독 페이지 참고

Alert 기능의 부재

Elastalert 를 사용해 볼 수 있음

• python version 2.7을 사용해야 함 (아직까지 python 3 은 지원 X)
  python 3 지원 여부가 궁금하신 분들은 Add support for Python 3.5 #1210 PR이 종료 되는지 꾸준히 체크해 보세요~
• Open Source (Apache License 2.0 / 상업적 사용 가능, 제약사항 없음)
• 자세한 내용은 elastalert github 참고

Install

Elastic Stack 다운로드

https://www.elastic.co/kr/downloads/

Elasticsearch 설치

• Elasticsearch 다운로드 후 압축해제
• Elasticsearch의 경우 압축해제한 폴더를 복사하여 elasticsearch-7.1.1-master와 elasticsearch-7.1.1-node 2개 폴더로 구성
• Elasticsearch master Home 경로 (E:\Elastic\elasticsearch-7.1.1-master
• Elasticsearch node Home 경로 (E:\Elastic\elasticsearch-7.1.1-node)

Elasticsearch Master TLS(암호화 프로토콜) 구성

• Elasticsearch master Home으로 이동
• 인증서 생성Enter password for elastic-certificates.p12 : 메시지가 보이면 Enter
  config 폴더에 elastic-certificates.p12 파일이 생겼는지 확인
•  

E:\Elastic\elasticsearch-7.1.1-master> .\bin\elasticsearch-certutil cert -out .\config\elastic-certificates.p12

This tool assists you in the generation of X.509 certificates and certificate
signing requests for use with SSL/TLS in the Elastic stack.

The 'cert' mode generates X.509 certificate and private keys.
  * By default, this generates a single certificate and key for use
  on a single instance.
  * The '-multiple' option will prompt you to enter details for multiple
  instances and will generate a certificate and key for each one
  * The '-in' option allows for the certificate generation to be automated by describing
  the details of each instance in a YAML file

  * An instance is any piece of the Elastic Stack that requires a SSL certificate.
  Depending on your configuration, Elasticsearch, Logstash, Kibana, and Beats
  may all require a certificate and private key.
  * The minimum required value for each instance is a name. This can simply be the
  hostname, which will be used as the Common Name of the certificate. A full
  distinguished name may also be used.
  * A filename value may be required for each instance. This is necessary when the
  name would result in an invalid file or directory name. The name provided here
  is used as the directory name (within the zip) and the prefix for the key and
  certificate files. The filename is required if you are prompted and the name
  is not displayed in the prompt.
  * IP addresses and DNS names are optional. Multiple values can be specified as a
  comma separated string. If no IP addresses or DNS names are provided, you may
  disable hostname verification in your SSL configuration.

  * All certificates generated by this tool will be signed by a certificate authority (CA).
  * The tool can automatically generate a new CA for you, or you can provide your own with the
      -ca or -ca-cert command line options.

By default the 'cert' mode produces a single PKCS#12 output file which holds:
  * The instance certificate
  * The private key for the instance certificate
  * The CA certificate

If you specify any of the following options:
  * -pem (PEM formatted output)
  * -keep-ca-key (retain generated CA key)
  * -multiple (generate multiple certificates)
  * -in (generate certificates from an input file)
then the output will be be a zip file containing individual certificate/key files

Enter password for elastic-certificates.p12 :

Certificates written to E:\Elastic\elasticsearch-7.1.1-master\config\elastic-certificates.p12

This file should be properly secured as it contains the private key for your instance.

This file is a self contained file and can be copied and used 'as is'
For each Elastic product that you wish to configure, you should copy
this '.p12' file to the relevant configuration directory
and then follow the SSL configuration instructions in the product guide.

• Enter password for elastic-certificates.p12 : 메시지가 보이면 Enter
  config 폴더에 elastic-certificates.p12 파일이 생겼는지 확인
• elasticsearch.yml 설정

xpack.security.enabled: true
xpack.security.transport.ssl.enabled: true
xpack.security.transport.ssl.verification_mode: certificate
xpack.security.transport.ssl.keystore.path: elastic-certificates.p12
xpack.security.transport.ssl.truststore.path: elastic-certificates.p12

• Elasticsearch Master 실행

E:\Elastic\elasticsearch-7.1.1-master> .\bin\elasticsearch.bat

• 비밀번호 설정 (새 명령창 열어서 실행)

E:\Elastic\elasticsearch-7.1.1-master> .\bin\elasticsearch-setup-passwords.bat auto
Initiating the setup of passwords for reserved users elastic,apm_system,kibana,logstash_system,beats_system,remote_monitoring_user.
The passwords will be randomly generated and printed to the console.
Please confirm that you would like to continue [y/N]y


Changed password for user apm_system
PASSWORD apm_system = 57cO0uOZlo6DbAdhaWxe

Changed password for user kibana
PASSWORD kibana = oWc8DXXcp850o0gBGHjl

Changed password for user logstash_system
PASSWORD logstash_system = eNfDiTsMUAIzLEGY8n7C

Changed password for user beats_system
PASSWORD beats_system = 9gsRg0ESWFm1HVv4A5ZT

Changed password for user remote_monitoring_user
PASSWORD remote_monitoring_user = PygrS7zpedXN3XBt73No

Changed password for user elastic
PASSWORD elastic = LOBSuRW2eHAy1MGY5zhS

비밀번호는 잘 기록해두어야 함
interactive Command Argument를 통해 수동으로 설정도 가능!!

 

Elasticsearch Node TLS(암호화 프로토콜) 구성

• Elasticsearch node Home으로 이동
• Master 의 config 폴더 복사

E:\Elastic\elasticsearch-7.1.1-node> cp ..\elasticsearch-7.1.1-master\config\* .\config\

• elasticsearch.yml 설정

node.master: false
xpack.security.enabled: true
xpack.security.transport.ssl.enabled: true
xpack.security.transport.ssl.verification_mode: certificate
xpack.security.transport.ssl.keystore.path: elastic-certificates.p12
xpack.security.transport.ssl.truststore.path: elastic-certificates.p12

• Elasticsearch Node 실행

E:\Elastic\elasticsearch-7.1.1-node> .\bin\elasticsearch.bat

Kibana 설정 및 실행

• Kibana 다운로드 후 압축해제
• Kibana Home 경로 (E:\Elastic\kibana-7.1.1)
• config\kibana.yml 파일 수정

elasticsearch.username: "kibana"
elasticsearch.password: "setup-passwords 를 통해 생성된 비밀번호 입력"

• Kibana 실행

E:\Elastic\kibana-7.1.1> .\bin\kibana.bat

Kibana 접속 확인

• Username : elastic / Password : setup-passwords 를 통해 생성된 비밀번호 입력

Logstash Install

• Logstash와 MSSQL JDBC Driver 다운로드 후 압축해제
• Logstash Home 경로 (E:\Elastic\Logstash-7.1.1)
• MSSQL JDBC Driver 경로 (E:\Elastic\sqljdbc_7.2\en)MSSQL JDBC Driver를 kor 버전으로 받으면 logstash 실행 시 일부 로그가 깨져서 나옴

Logstash Config

• config폴더 밑에 logstash.conf 파일 생성
• 2개 DB에 접근하여 로그를 가져와서 인덱싱 작업 후 elasticsearch로 데이터를 전달하는 설정

input {
    jdbc {
        jdbc_driver_library => "E:\Elastic\sqljdbc_7.2\enu\mssql-jdbc-7.2.2.jre8.jar"
        jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
        jdbc_connection_string => "jdbc:sqlserver://12.34.56.78;databaseName=DORY;"
        jdbc_user => "nemo"
        jdbc_password => "dorynemo1!"
        statement => "SELECT TOP 5000 * FROM [gtm].[T_USER_LOG] WHERE SEQ > :sql_last_value ORDER BY seq"
        last_run_metadata_path => "{last_run_path}"

        schedule => "* * * * *"
        tracking_column_type => "numeric"
        use_column_value => true
        tracking_column => "seq"
        type => "dory"
    }
    jdbc {
        jdbc_driver_library => "E:\Elastic\sqljdbc_7.2\enu\mssql-jdbc-7.2.2.jre8.jar"
        jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
        jdbc_connection_string => "jdbc:sqlserver://12.34.56.78;databaseName=TAYO;"
        jdbc_user => "gani"
        jdbc_password => "tayogani1!"

        statement => "SELECT * FROM [dbo].[T_USER_LOG] WHERE SEQ > :sql_last_value"
        last_run_metadata_path => "{last_run_path}"
        schedule => "* * * * *"
        tracking_column_type => "numeric"
        use_column_value => true
        tracking_column => "seq"
        type => "tayo"
    }
}
filter {
}
output {
    elasticsearch {
        hosts => "localhost:9200"
        user => "elastic"
        password => "setup-passwords 를 통해 생성된 비밀번호 입력"
        index => "%{type}"
    }
    stdout { codec => rubydebug }
}

Logstash 실행

E:\Elastic\logstash-7.1.1> .\bin\logstash.bat -f .\config\logstash.conf

Trouble Shooting

ELK가 설치된 서버 ip가 12.34.56.99라고 가정하겠습니다~

elasticsearch가 local에서만 접속이 되고 remote 접속이 안됨

Problem

http://12.34.56.99:9200 접속 안됨

Solution

elasticsearch.yaml 파일에 아래 설정 추가

network.host: 0.0.0.0

network.host 설정 후 Elasticsearch 에러

Problem

• elasticsearch.yaml 파일의 network.host: 0.0.0.0 으로 설정했더니 아래와 같은 에러 발생

[2019-06-21T16:33:33,511][ERROR][o.e.b.Bootstrap          ] [SKH-MISDWEB02] node validation exception
[1] bootstrap checks failed
[1]: the default discovery settings are unsuitable for production use; at least one of [discovery.seed_hosts, discovery.seed_providers, cluster.initial_master_nodes] must be configured

Solution

• elasticsearch.yaml 파일에 아래 설정 추가

discovery.seed_hosts: ["localhost"]

Kibana가 local에서만 접속이 되고 remote 접속이 안됨

Problem

http://12.34.56.99:5601 접속 안됨

Solution

• kibana.yaml 파일에 아래 설정 추가

server.host: "12.34.56.99"

Index 확인 및 삭제 API

Kibana Dev Tools나 Post Man 을 사용해서 API를 실행해 볼 수 있습니다

• Index 확인

GET /_cat/indices?v
health status index                   uuid                   pri rep docs.count docs.deleted store.size pri.store.size
green  open   kibana_sample_data_logs Uerdn8Q8TYuqrpLzEpqcpQ   1   0      14005            0     11.5mb         11.5mb
yellow open   gtms                    Pge0zSxmSGu_E2QLz9GLWQ   1   1      11075            0      1.7mb          1.7mb
green  open   .kibana_1               IMxCKibwQY6dn-DKiqPPBw   1   0         39            1    113.8kb        113.8kb
green  open   .kibana_task_manager    SeWnHEK3T260ctU-O8XZ8g   1   0          2            0     13.4kb         13.4kb
green  open   .security-7             0Kc9CSaLTGeqeO_FBs7I9Q   1   0         12            0     29.2kb         29.2kb

• Index 삭제

DELETE /gtms?pretty
{
    "acknowledged" : true
}

 

Elasticsearch Cluster Shards 상태 확인

http://12.34.56.99:9200/_cluster/health?level=shards

{
  "cluster_name": "elasticsearch",
  "status": "yellow",
  "timed_out": false,
  "number_of_nodes": 2,
  "number_of_data_nodes": 2,
  "active_primary_shards": 4,
  "active_shards": 4,
  "relocating_shards": 0,
  "initializing_shards": 0,
  "unassigned_shards": 4,
  "delayed_unassigned_shards": 0,
  "number_of_pending_tasks": 0,
  "number_of_in_flight_fetch": 0,
  "task_max_waiting_in_queue_millis": 0,
  "active_shards_percent_as_number": 50.0,
  "indices": {
    ".kibana_task_manager": { "status": "yellow", "number_of_shards": 1, "number_of_replicas": 1, "active_primary_shards": 1, "active_shards": 1, "relocating_shards": 0, "initializing_shards": 0, "unassigned_shards": 1, "shards": { "0": { "status": "yellow", "primary_active": true, "active_shards": 1, "relocating_shards": 0, "initializing_shards": 0, "unassigned_shards": 1 } } },
    ".security-7": { "status": "yellow", "number_of_shards": 1, "number_of_replicas": 1, "active_primary_shards": 1, "active_shards": 1, "relocating_shards": 0, "initializing_shards": 0, "unassigned_shards": 1, "shards": { "0": { "status": "yellow", "primary_active": true, "active_shards": 1, "relocating_shards": 0, "initializing_shards": 0, "unassigned_shards": 1 } } },
    "kibana_sample_data_logs": { "status": "yellow", "number_of_shards": 1, "number_of_replicas": 1, "active_primary_shards": 1, "active_shards": 1, "relocating_shards": 0, "initializing_shards": 0, "unassigned_shards": 1, "shards": { "0": { "status": "yellow", "primary_active": true, "active_shards": 1, "relocating_shards": 0, "initializing_shards": 0, "unassigned_shards": 1 } } },
    ".kibana_1": { "status": "yellow", "number_of_shards": 1, "number_of_replicas": 1, "active_primary_shards": 1, "active_shards": 1, "relocating_shards": 0, "initializing_shards": 0, "unassigned_shards": 1, "shards": { "0": { "status": "yellow", "primary_active": true, "active_shards": 1, "relocating_shards": 0, "initializing_shards": 0, "unassigned_shards": 1 } } }
  }
}

• Master와 Node 1개로 구성된 Cluster
• status가 red로 나오면 안됨 (Kibana 실행 시 에러남)

Logstash 실행 안됨

Problem

• JAVA 경로 설정이 제대로 안되어 있어서 아예 프로세스 시작도 못함

Solution

• bin\logstash.bat 파일에 JAVA_HOME PATH 설정

@echo off
setlocal enabledelayedexpansion
set params='%*'
set JAVA_HOME=E:\Elastic\elasticsearch-7.1.1-master\jdk

• bin\setup.bat 파일 JAVA PATH 설정

if defined JAVA_HOME (
set JAVA="E:\Elastic\elasticsearch-7.1.1-master\jdk\bin\java.exe"
) else (
for %%I in (java.exe) do set JAVA="%%~$PATH:I"
)

MSSQL JDBC Driver + Open JDK 사용 시 문제

Problem

• TypeError : jdk.internal.loader.ClassLoaders$AppClassLoader to java.net.URLClassLoader

Solution

• logstash-core\lib\jars\ 폴더 밑에 강제로 mssql-jdbc-7.2.2.jre8.jar 파일 추가
• config\logstash.conf 파일에서 jdbc_driver_library 값을 없애줘야 함

jdbc {
    jdbc_driver_library => ""
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    ...
}

참고문서

Getting Started with Free Elasticsearch Security Features
색인 삭제-Elasticsearch 설명서 [5.4]
[logstash] JDBC input plugin
Connection refused error on Elastic Search-Stack Overflow

 

 

출처 : https://wooyoung85.tistory.com/52

ELK 사용하여 로그분석 시스템 만들기 (MS-SQL 연동)