[Logstash] ionenet 망연계 mysql db 연동

[INPUT]
input {
  jdbc {
    jdbc_validate_connection => true
    jdbc_driver_library => "/appdata/logstash/jdbc_driver_library/mysql-connector-java-8.0.16.jar"
    jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://10.25.1.200:3535/onenet?useUnicode=true&serverTimezone=Asia/Seoul"
    jdbc_user => "viewer_user"
    jdbc_password => "P@ssw0rd"
    jdbc_default_timezone => "Asia/Seoul"
    schedule => "5 9 * * *"
    statement => "SELECT * FROM view_file_trans_log_tab WHERE request_dt BETWEEN DATE_ADD(now(), INTERVAL -2 DAY) AND  DATE_ADD(now(), INTERVAL -1 DAY);" // 하루 전날 로그 가져오기
#    statement => "SELECT * FROM view_file_trans_log_tab WHERE request_dt BETWEEN DATE_ADD(now(), INTERVAL -150 DAY) AND  DATE_ADD(now(), INTERVAL -1 DAY);" // 현재일로부터 150일전까지 로그 가져오기
    jdbc_validation_timeout => 100000
  }
}



[FILTER]
filter{
  mutate {
    convert => {
      "file_cnt" => "integer"
    }
  }

  date {
    match => ["request_dt", "yyyy-MM-dd HH:mm:ss"]
    timezone => "Asia/Seoul"
    target => "request_dt"
  }

  mutate {
    rename => {
      "file_name" => "[file][name]"
      "private_info" => "[private][info]"
      "approval_dt" => "[approval][dt]"
      "approval_dept" => "[approval][dept]"
      "request_user_id" => "[user][id]"
      "group_name" => "[group][name]"
      "request_dt" => "[event][created]"
      "network" => "[request][dt]"
      "receive_mail" => "[mail][receive]"
      "title" => "[file][title]"
      "request_user_name" => "[user][name]"
      "gubun" => "[request][type]"
      "send_mail" => "[mail][send]"
      "approval_state" => "[approval][state]"
      "file_cnt" => "[file][count]"
      "approval_user" => "[approval][user]"
      "is_private" => "[is][private]"
    }
  }

  mutate {
    add_field => {
      "[event][category]" => "work_transfer"
    }
  }

  mutate {
    copy => {
      "[event][created]" => "@timestamp"
    }
  }
}



[OUTPUT]
output {
  elasticsearch {
    hosts => ["https://192.168.0.1:9200", "https://192.168.0.2:9200", "https://192.168.0.3:9200"]
    user => "elastic"
    password => "P@ssw0rd"
    index => "db_ionenet_work_transfer_log"
    ssl => true
    ssl_certificate_verification => false
    cacert => "/usr/share/logstash/elasticsearch-ca.pem"
  }
}