CODEWITHSUNDEEP
logstashkibanaelastic-stackelasticsearch-5logstash-jdbc
Harish Gupta
Harish Gupta

Reputation: 402

Kibana Index Pattern showing wrong results

I am using ELK stack in which i have used jdbc input in logstash
I have created 2 indexes

  1. users
  2. employees

Both the indexes have one same column objid
Logstash config file

input {
  jdbc {
    jdbc_driver_library => "/opt/application/cmt/ELK/logstash-5.3.0/ojdbc14.jar"
    jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
    jdbc_connection_string => "jdbc:oracle:thin:@xx.xxx.xx.xx:xxxx:abc"
    jdbc_user => "xxxx"
    jdbc_password => "xxxxx"
    schedule => "*/2 * * * *"
    statement => "select * from table_employee"
  }
}
output {
  elasticsearch {
    index => "employees"
    document_type => "employee"
    document_id => "%{objid}"
    hosts => "xx.xxx.xxx.xx:9200"
    }
}

input {
  jdbc {
    jdbc_driver_library => "/opt/application/cmt/ELK/logstash-5.3.0/ojdbc14.jar"
    jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
    jdbc_connection_string => "jdbc:oracle:thin:@xx.xxx.xx.xx:xxxx:abc"
    jdbc_user => "xx"
    jdbc_password => "xxxxxxx"
    schedule => "*/2 * * * *"
    statement => "select A.OBJID,A.LOGIN_NAME,A.STATUS,A.USER_ACCESS2PRIVCLASS,A.USER_DEFAULT2WIPBIN,A.SUPVR_DEFAULT2MONITOR,A.USER2RC_CONFIG,A.OFFLINE2PRIVCLASS,A.WIRELESS_EMAIL from table_user a where A.STATUS=1"
  }
}
output {
  elasticsearch {
    index => "users"
    document_type => "user"
    document_id => "%{objid}%{login_name}"
    hosts => "xx.xxx.xxx.xx:9200"
    }
}

1st input jdbc 'employees' contains 26935 records

2nd input jdbc 'users' contains 10619 records

Common Records : 9635 ( objid matches )

1st problem is that when i create an index pattern in kibana as '

users

It's showing count of 37554 ,why ? it should show only 10619

2nd problem : when i create an index pattern as '

employees

It's showing count of 27919 ,why ? it should show only 26935

Also i have create different document Id for index 'users' %{objid}%{login_name}

Upvotes: 0

Views: 320

Answers (1)

cattastrophe
cattastrophe

Reputation: 291

If your users and employees input and output are in the same file/executed at the same time, as what your example shows, you need to use conditionals to route your data to the correct elasticsearch index. Logstash concatenates your files/file into one pipeline, so all your inputs run through all of the filters/outputs, which is likely why you're getting unexpected results. See this discussion.

You will need to do something like this:

input {
  jdbc {
    statement => "SELECT * FROM users"
    type => "users"
  }
}

input {
  jdbc {
    statement => "SELECT * FROM employees"
    type => "employees"
  }
}

output {
  if [type] == "users" {
    elasticsearch {
      index => "users"
      document_type => "user"
      document_id => "%{objid}%{login_name}"
      hosts => "xx.xxx.xxx.xx:9200"
    }
  }

  if [type] == "employees" {
    elasticsearch {
      index => "employees"
      document_type => "employee"
      document_id => "%{objid}"
      hosts => "xx.xxx.xxx.xx:9200"
    }
  }  
}

Upvotes: 1

Related Questions