# Index data from MSSQL server to Elasticsearch using Logstash as nested documents
**URL:** https://discuss.elastic.co/t/index-data-from-mssql-server-to-elasticsearch-using-logstash-as-nested-documents/217179
**Category:** Logstash
**Created:** [January 30, 2020, 12:33pm UTC](https://discuss.elastic.co/t/index-data-from-mssql-server-to-elasticsearch-using-logstash-as-nested-documents/217179 "2020-01-30T12:33:00Z")
**Posts on this page:** 9
**Page:** 1
### Author:  [@Mohan\_vel](https://discuss.elastic.co/u/Mohan_vel)
#### Post date: [January 30, 2020, 12:33pm UTC](https://discuss.elastic.co/t/index-data-from-mssql-server-to-elasticsearch-using-logstash-as-nested-documents/217179/1 "2020-01-30T12:33:00Z")
Hi All i am new to elasticsearch i am trying to index data from MSSQL server to elasticsearch using logstash as nested documents i am having some issues while creating a topic so i have created the issue in stack overflow and shared the link below.
[link posted in stackoverflow](https://stackoverflow.com/questions/59983126/nested-document-to-elasticsearch-using-logstash)
Could any one help me to sort out the issue?
---
### Author:  [@inhinyera16](https://discuss.elastic.co/u/inhinyera16)
#### Post date: [January 30, 2020, 1:01pm UTC](https://discuss.elastic.co/t/index-data-from-mssql-server-to-elasticsearch-using-logstash-as-nested-documents/217179/2 "2020-01-30T13:01:02Z")
I believe you can do this with jdbc\_streaming filter
you can check it out ๐
[https://www.elastic.co/guide/en/logstash/current/plugins-filters-jdbc\_streaming.html](https://www.elastic.co/guide/en/logstash/current/plugins-filters-jdbc_streaming.html)
Ps: Please mark as "Solution" if you think this helps ๐
---
### Author:  [@Mohan\_vel](https://discuss.elastic.co/u/Mohan_vel)
#### Post date: [January 30, 2020, 3:57pm UTC](https://discuss.elastic.co/t/index-data-from-mssql-server-to-elasticsearch-using-logstash-as-nested-documents/217179/3 "2020-01-30T15:57:47Z")
Hi thanks for the reply Archelle. I gone through the given link since i was new to this logstash env i was unable to get through it sample code attached below. You have attached a screen shot of the result which is needed. If you have any sample code kindly post it it will be really helpful parallely i will look into the link and try to create a solution. Thanks
Note : i have pasted the jdbc driver with in logstash-7.2.0\logstash-core\lib\jars that is why my
Jdbc\_driver\_libirary is empty
input {
jdbc {
jdbc\_driver\_library =\> ""
jdbc\_driver\_class =\> "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc\_connection\_string =\> "jdbc:sqlserver://DESKTOP-VHDBS6E\SQLEXPRESS:1433;databaseName=sa;"
jdbc\_user =\> "sa"
jdbc\_password =\> "sa"
statement =\> "Select id,name from department"
}
}
filter{
jdbc\_streaming {
jdbc\_driver\_library =\> ""
jdbc\_driver\_class =\> "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc\_connection\_string =\> "jdbc:sqlserver://DESKTOP-VHDBS6E\SQLEXPRESS:1433;databaseName=sa;"
jdbc\_user =\> "sa"
jdbc\_password =\> "sa"
statement =\> "select Id as empid,emp\_Name as empname from Employee where dept\_Id = :code"
```
parameters => { "code" => "id"}
target => "employee_details"
```
}
}
output {
stdout { codec =\> rubydebug }
}
---
### Author:  [@inhinyera16](https://discuss.elastic.co/u/inhinyera16)
#### Post date: [January 31, 2020, 11:54am UTC](https://discuss.elastic.co/t/index-data-from-mssql-server-to-elasticsearch-using-logstash-as-nested-documents/217179/4 "2020-01-31T11:54:26Z")
There you have it, just a few points though.
1. Use deptid as alias of id field in input block.
2. In your filter block, replace :code with :deptid, then under parameter,
parameters =\> { "deptid =\> "deptidโ}
target =\> "employee\_details"
---
### Author:  [@Mohan\_vel](https://discuss.elastic.co/u/Mohan_vel)
#### Post date: [February 1, 2020, 7:40pm UTC](https://discuss.elastic.co/t/index-data-from-mssql-server-to-elasticsearch-using-logstash-as-nested-documents/217179/5 "2020-02-01T19:40:17Z")
Thanks Archelle. I have tried the above code but now i am getting error related to driver so the error is quite different so i have opened another topic i have pasted the link below for your reference
[invalid setting jdbc\_streaming](https://discuss.elastic.co/t/invalid-setting-for-jdbc-streaming-filter-plugin/217498/3)
Once the issue get resolved i will mark it as solution. As of now both topic is open. Hopefully it will be resolved. Kindly help me to close this. Thanks in advance.
---
### Author:  [@inhinyera16](https://discuss.elastic.co/u/inhinyera16)
#### Post date: [February 3, 2020, 6:45am UTC](https://discuss.elastic.co/t/index-data-from-mssql-server-to-elasticsearch-using-logstash-as-nested-documents/217179/6 "2020-02-03T06:45:40Z")
Hi @Mohan_vel
Here's my config
```
input {
jdbc {
jdbc_driver_library => "/tmpBIS/sqljdbc4.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://localhost:1433;database=db;user=arc;password=mipw"
jdbc_user => "arc"
statement => "select id as deptid, Name as deptname from department"
}
}
filter {
jdbc_streaming {
jdbc_driver_library => "/tmpBIS/sqljdbc4.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://localhost:1433;database=db;user=arc;password=mipw"
jdbc_user => "arc"
statement => "SELECT ID as emp_id, emp_name FROM Employee where dept_id = :deptid"
parameters => { "deptid" => "deptid"}
target => "employee_details"
}
mutate {
remove_field => ["@version","@timestamp"]
}
}
output {
stdout {codec => rubydebug}
}
```
---
### Author:  [@Mohan\_vel](https://discuss.elastic.co/u/Mohan_vel)
#### Post date: [February 3, 2020, 10:47am UTC](https://discuss.elastic.co/t/index-data-from-mssql-server-to-elasticsearch-using-logstash-as-nested-documents/217179/7 "2020-02-03T10:47:47Z")
Hi @inhinyera16 ,
Driver issue not resolved so i go upgraded my logstash from 7.2.0 to 7.5.2 now the above code is working fine. i have given my config below for your reference.
```
input {
jdbc {
jdbc_driver_library => "D:/Users/mmurugesan/Desktop/driver/mssql-jdbc-7.4.1.jre12-shaded.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://EC2AMAZ-J90JR4A\SQLEXPRESS:1433;databaseName=xxx;"
jdbc_user => "xxxx"
jdbc_password => "xxx"
statement => "Select Policyholdername,Age,Policynumber,Dob,Client_Address from policy"
}
}
filter{
jdbc_streaming {
jdbc_driver_library => "D:/Users/mmurugesan/Desktop/driver/mssql-jdbc-7.4.1.jre12-shaded.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://EC2AMAZ-J90JR4A\SQLEXPRESS:1433;databaseName=xxxx;"
jdbc_user => "xxxx"
jdbc_password => "xxxx"
statement => "select claimnumber,claimtype from claim where policynumber = :policynumber"
parameters => {"policynumber" => "policynumber"}
target => "claim_details"
}
}
output {
stdout { codec => rubydebug }
}
```
Thanks a lot. i will notify you if i raise any other topic related to my work thanks a lot.
---
### Author:  [@Mohan\_vel](https://discuss.elastic.co/u/Mohan_vel)
#### Post date: [February 4, 2020, 7:23am UTC](https://discuss.elastic.co/t/index-data-from-mssql-server-to-elasticsearch-using-logstash-as-nested-documents/217179/8 "2020-02-04T07:23:33Z")
Hi @inhinyera16 ,
I have created another topic Fyi
[remove nested documents based on column flag](https://discuss.elastic.co/t/remove-nested-documents-from-index-using-logstash/217606)
Kindly look into this. Thank you.
As discussed i have marked solution for this topic.
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [March 3, 2020, 7:23am UTC](https://discuss.elastic.co/t/index-data-from-mssql-server-to-elasticsearch-using-logstash-as-nested-documents/217179/9 "2020-03-03T07:23:37Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.