DB mapping list fliter

Elastic Stack Logstash
1

Team-

can you help me in filter sections code,
index name is :-esp_test.
input is sql table(esp_details) and field names are hostname , BDS_name.

hostname      BDS_name
---------------------------------------
hostname1  sql23
hostname2  sql36
hostname1 sql47
hostname2 sql25

like
so i want to data be like in one row for hostname1 with all BDS_name associated.

example
hostname1 sql23 , sql 47 ---> in single row(single json)
hostname2 sql36 , sql25 ---> in single row(single json).

hope you understand my requirment .

please help me

2

i'm beginner of logstash , kindly help me on code

3

Welcome!

Please be patient in waiting for responses to your question and refrain from pinging multiple times asking for a response or opening multiple topics for the same question. This is a community forum, it may take time for someone to reply to your question. For more information please refer to the Community Code of Conduct specifically the section "Be patient". Also, please refrain from pinging folks directly, this is a forum and anyone that participates might be able to assist you.

If you are in need of a service with an SLA that covers response times for questions then you may want to consider talking to us about a subscription.

It's fine to answer on your own thread after 2 or 3 days (not including weekends) if you don't have an answer.

I can see that you posted that in Logstash. Is that on purpose?
Or do you want to do this in Elasticsearch?

4

in logstash only

5

What is your current logstash job file? I mean where do you want to send your data?

6

input is

input {
   jdbc {
         jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
         jdbc_connection_string => "jdbc:oracle:thin:@ldap://ldap-ldprd.com:5000/cn=OracleContext,dc=cisco,dc=com/SIAPRD"
         jdbc_user => "EDA_CHAIN11"
         jdbc_password => "****$"
         jdbc_validate_connection => true
         jdbc_fetch_size => "20000"
         statement => "select * from ESP_BUSINESS_DATABASE_SERVER_DBTS_V"

   }
}

output is

output {
elasticsearch {
        hosts => "url of elasticsearch with port number"
        index => "esp_test"
  }

  stdout {}

}
7

So what is the goal of changing from this:

hostname      BDS_name
---------------------------------------
hostname1  sql23
hostname2  sql36
hostname1 sql47
hostname2 sql25

to this:

hostname1 sql23 , sql 47
hostname2 sql36 , sql25
8

we have to list out the BDS names in one row for hostnames, so that we can easily identify that BDS names for every host.

example for below screenshot

image
image1554×684 57.2 KB

9

So you want to group the result by the hostnames?
You can do that at search time instead I believe.

10

yes, :slight_smile:

11

Just drag the hostname field on your Kibana lens visualization.

Or better, try the new ES|QL feature:

from esp_test
| stats count = count() by hostname
| limit 1000

See also Using ES|QL in Kibana | Elasticsearch Guide [8.13] | Elastic

12

this approach not suites my requirment @dadoonet .

i want to do it this in logstash filter section with code , i thought that we can use two indexes to achieve it .

13

May be you can... I'm just wondering what is the need for this if you can achieve the same thing without efforts?

What problem do you think you want to solve with your solution?