[Logstash] secuve tos mariadb 연동

# tos_master: 마스터 뷰
[INTPUT]
input {
 jdbc {
  jdbc_validate_connection => true
  jdbc_driver_library => "/appdata/logstash/jdbc_driver_library/mariadb-java-client-2.7.4.jar"
  jdbc_driver_class => "org.mariadb.jdbc.Driver"
  jdbc_connection_string => "jdbc:mariadb://10.103.240.63:3306/tosms8"
  jdbc_user => "TOSMS8"
  jdbc_password => "TOSMS8"
  jdbc_default_timezone => "Asia/Seoul"
  schedule => "40 1 * * *"
  statement => "SELECT * FROM tos_master WHERE issuedate BETWEEN DATE_ADD(now(), INTERVAL -2 DAY) AND  DATE_ADD(now(), INTERVAL -1 DAY);"
  jdbc_validation_timeout => 100000
  last_run_metadata_path => "/tmp/.logstash_jdbc_last_run"
 }
}



[FILTER]
filter {
  date {
    match => ["issuedate","yyyy-MM-dd HH:mm:ss"]
    timezone => "Asia/Seoul"
    locale => "ko"
    target => "issuedate"
  }

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

  date {
    match => ["lastpwdchange","yyyy-MM-dd","yyyy-MM-dd HH:mm:ss.SSS"]
    timezone => "Asia/Seoul"
    locale => "ko"
    target => "lastpwdchange"
  }

  mutate {
    rename => {
      "empnum" => "[user][id]"
      "servername" => "[server][name]"
      "username" => "[user][account]"
      "certdn" => "[cert][type]"
      "issuedate" => "[event][created]"
      "lastpwdchange" => "[change][pwdate]"
      "expiredate" => "[expire][date]"
      "starttime" => "[start][time]"
      "endtime" => "[end][time]"
      "empname" => "[user][name]"
    }
  }

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

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

  if [change][pwdate] == "" {
    mutate {
      copy => {
        "@timestamp" => "[change][pwdate]"
      }
    }
  }

  ruby {
    code => "
      event.set('epoch1', event.get('[change][pwdate]').to_i)
      event.set('epoch2', event.get('[event][created]').to_i)
      event.set('epoch3', event.get('[event][created]').to_i-event.get('[change][pwdate]').to_i)
      event.set('epoch4', (event.get('[event][created]').to_i-event.get('[change][pwdate]').to_i)/86400)
      "
  }

  mutate {
    remove_field => ["epoch1","epoch2","epoch3"]
    rename => {
      "epoch4" => "[duration][pwchange]"
    }
  }
}



[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_tos_master"
    ssl => true
    ssl_certificate_verification => false
    cacert => "/usr/share/logstash/elasticsearch-ca.pem"
  }
}