# Sql Server to Logstash
**URL:** https://discuss.elastic.co/t/sql-server-to-logstash/136608
**Category:** Logstash
**Created:** [June 20, 2018, 5:58am UTC](https://discuss.elastic.co/t/sql-server-to-logstash/136608 "2018-06-20T05:58:16Z")
**Posts on this page:** 17
**Page:** 1
### Author:  [@kartheek91](https://discuss.elastic.co/u/kartheek91)
#### Post date: [June 20, 2018, 5:58am UTC](https://discuss.elastic.co/t/sql-server-to-logstash/136608/1 "2018-06-20T05:58:17Z")
I'm trying to index records that are fetched from sql server and inserted into elasticsearch.Actually now I have 25k records and while I'm running logstash.conf file it is indexing upto 2000 records and logstash is terminating. So, can anyone tell what might be the reason so I need to index 1 miilion records in future.
Thanks in advance
Kartheek
---
### Author:  [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)
#### Post date: [June 20, 2018, 6:19am UTC](https://discuss.elastic.co/t/sql-server-to-logstash/136608/2 "2018-06-20T06:19:26Z")
Have you looked in the Logstash log for clues about the shutdown? What does your configuration look like?
---
### Author:  [@kartheek91](https://discuss.elastic.co/u/kartheek91)
#### Post date: [June 20, 2018, 6:22am UTC](https://discuss.elastic.co/t/sql-server-to-logstash/136608/3 "2018-06-20T06:22:12Z")
Thanks @magnusbaeck for your reply and I will post the configuration file.
input {
jdbc {
jdbc\_driver\_library =\> "c:\drivers\sqljdbc4"
jdbc\_driver\_class =\> "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc\_connection\_string =\> "jdbc:sqlserver://localhost\SAILS-DM29:1433;databasename=ePrimeRx\_New"
jdbc\_paging\_enabled =\> "true"
jdbc\_pool\_timeout =\> "300"
jdbc\_paging\_enabled =\> "true"
jdbc\_fetch\_size =\> 10000
connection\_retry\_attempts\_wait\_time =\> 5
connection\_retry\_attempts =\> 10
```
jdbc_user => "sa"
jdbc_password => "sails123"
statement => " SELECT DRG.[TenantId] AS [TENANTID],DRG.[Id] AS [id],DRG.[DrugCode] AS [drugcode],DRG.[BRANDNAME] AS [DrugBandName], DRG.[QuickCode] AS [quickcode],
DRG.[Name] AS [drugname],DRG.[BillingNDC] AS [ndc],[DRGTYPE].Remarks AS [DRUGTYPE],
DRG.[GenericName] AS [GenericName], DRG.[Strength] AS [Strength], DRG.[ManufactId] AS [ManufactId], DRG.[AWPPack] AS [AWPPack],
DRG.[DirectUnitPrice] AS [DirectUnitPrice],DRG.[CostPack] AS [CostPack],DRG.[UnitPriceCost] AS [UnitPriceCost],DRG.[QtyPack] AS [QUANTYPACK],
[DRG].[DrugFormId] AS [DrugFormId], [DRG].[DrugUnitId] AS [DrugUnitId], [DRG].[DrugClass] AS [DrugClass],
[DRGCAT].[Name] AS [DrugCategory],
[DRG].[IsDeleted] AS [IsDeleted], [DRG].[IsActive] AS [IsActive],
[DRG].[CreatedDtTm] AS [DrugCreatedDate],[DRG].[ModifiedDtTm] AS [DrugModifiedDate]
FROM [Drug] AS [DRG]
LEFT OUTER JOIN [dbo].[DrugForm] AS [DRGFROM] ON [DRG].[DrugFormId] = [DRGFROM].[Id]
LEFT OUTER JOIN [dbo].[DrugUnit] AS [DRGUNIT] ON [DRG].[DrugUnitId] = [DRGUNIT].[Id]
LEFT OUTER JOIN [dbo].[DrugType] AS [DRGTYPE] ON [DRG].[DrugTypeId] = [DRGTYPE].[Id]
LEFT OUTER JOIN [DBO].[Drug_DrugCat] as [DRGCATEGORY] on [DRG].[ID] = [DRGCATEGORY].[DrugId]
LEFT OUTER JOIN [DBO].[DrugCat] as [DRGCAT] on [DRGCATEGORY].[DrugCatId] = [DRGCAT].[Id] where [DRG].[ModifiedDtTm] >= :sql_last_value"
use_column_value => false
tracking_column => "ModifiedDtTm"
tracking_column_type => "timestamp"
record_last_run => true
clean_run => true
```
}
}
filter {
mutate {
add\_field =\> { "ndcsuggest" =\> "%{ndc}"
"drugnamesuggest" =\> "%{drugname}"
"quickcodesuggest" =\> "%{quickcode}" }
}
}
# The filter part of this file is commented out to indicate that it is
# optional.
output {
elasticsearch{
index =\> "micromerchant1"
document\_type =\> "drug"
action =\>"update" #if want to update existing index data based on ID column
#ssl=\>true # if node is on SSL
hosts =\> ["localhost:9200"]
doc\_as\_upsert =\> true
action =\> "update"
document\_id =\> "%{id}"
}
stdout { codec =\> json\_lines }
}
---
### Author:  [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)
#### Post date: [June 20, 2018, 6:25am UTC](https://discuss.elastic.co/t/sql-server-to-logstash/136608/4 "2018-06-20T06:25:25Z")
How many unique `DRG.[Id]` values are there in the database?
---
### Author:  [@kartheek91](https://discuss.elastic.co/u/kartheek91)
#### Post date: [June 20, 2018, 6:25am UTC](https://discuss.elastic.co/t/sql-server-to-logstash/136608/5 "2018-06-20T06:25:41Z")
23026
---
### Author:  [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)
#### Post date: [June 20, 2018, 6:25am UTC](https://discuss.elastic.co/t/sql-server-to-logstash/136608/6 "2018-06-20T06:25:49Z")
> [@kartheek91](#):
>
> document\_id =\> "%{id}"
Is the `id` field unique across all 25000 documents?
---
### Author:  [@kartheek91](https://discuss.elastic.co/u/kartheek91)
#### Post date: [June 20, 2018, 6:27am UTC](https://discuss.elastic.co/t/sql-server-to-logstash/136608/7 "2018-06-20T06:27:28Z")
yes this is the query
SELECT COUNT(DRG.[Id] )
FROM [Drug] AS [DRG]
LEFT OUTER JOIN [dbo].[DrugForm] AS [DRGFROM] ON [DRG].[DrugFormId] = [DRGFROM].[Id]
LEFT OUTER JOIN [dbo].[DrugUnit] AS [DRGUNIT] ON [DRG].[DrugUnitId] = [DRGUNIT].[Id]
LEFT OUTER JOIN [dbo].[DrugType] AS [DRGTYPE] ON [DRG].[DrugTypeId] = [DRGTYPE].[Id]
LEFT OUTER JOIN [DBO].[Drug\_DrugCat] as [DRGCATEGORY] on [DRG].[ID] = [DRGCATEGORY].[DrugId]
LEFT OUTER JOIN [DBO].[DrugCat] as [DRGCAT] on [DRGCATEGORY].[DrugCatId] = [DRGCAT].[Id]
and the result count is 23026
---
### Author:  [@kartheek91](https://discuss.elastic.co/u/kartheek91)
#### Post date: [June 20, 2018, 6:28am UTC](https://discuss.elastic.co/t/sql-server-to-logstash/136608/8 "2018-06-20T06:28:23Z")
yes this is the query
SELECT COUNT(DRG.[Id] )
FROM [Drug] AS [DRG]
LEFT OUTER JOIN [dbo].[DrugForm] AS [DRGFROM] ON [DRG].[DrugFormId] = [DRGFROM].[Id]
LEFT OUTER JOIN [dbo].[DrugUnit] AS [DRGUNIT] ON [DRG].[DrugUnitId] = [DRGUNIT].[Id]
LEFT OUTER JOIN [dbo].[DrugType] AS [DRGTYPE] ON [DRG].[DrugTypeId] = [DRGTYPE].[Id]
LEFT OUTER JOIN [DBO].[Drug\_DrugCat] as [DRGCATEGORY] on [DRG].[ID] = [DRGCATEGORY].[DrugId]
LEFT OUTER JOIN [DBO].[DrugCat] as [DRGCAT] on [DRGCATEGORY].[DrugCatId] = [DRGCAT].[Id]
and the result count is 23026
---
### Author:  [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)
#### Post date: [June 20, 2018, 6:31am UTC](https://discuss.elastic.co/t/sql-server-to-logstash/136608/9 "2018-06-20T06:31:42Z")
If you look at a few of the documents that were indexed, what is the value of `_version`?
---
### Author:  [@kartheek91](https://discuss.elastic.co/u/kartheek91)
#### Post date: [June 20, 2018, 6:33am UTC](https://discuss.elastic.co/t/sql-server-to-logstash/136608/10 "2018-06-20T06:33:08Z")
> [@Christian\_Dahlqvist](#):
>
> \_version
Actually this is first run and I'm dropping index everytime and I'm checking whether the document count and rows count are equal
---
### Author:  [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)
#### Post date: [June 20, 2018, 6:34am UTC](https://discuss.elastic.co/t/sql-server-to-logstash/136608/11 "2018-06-20T06:34:30Z")
What is the value? Does it vary?
---
### Author:  [@kartheek91](https://discuss.elastic.co/u/kartheek91)
#### Post date: [June 20, 2018, 6:35am UTC](https://discuss.elastic.co/t/sql-server-to-logstash/136608/12 "2018-06-20T06:35:55Z")
This is the count
---
### Author:  [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)
#### Post date: [June 20, 2018, 6:37am UTC](https://discuss.elastic.co/t/sql-server-to-logstash/136608/13 "2018-06-20T06:37:06Z")
That was not what I asked. Please look at a few documents and check what the value `_version` is.
If the `id` field is unique across all records, this value should never really be greater than 1, as a higher value indicates that documents have been updated.
---
### Author:  [@kartheek91](https://discuss.elastic.co/u/kartheek91)
#### Post date: [June 20, 2018, 6:38am UTC](https://discuss.elastic.co/t/sql-server-to-logstash/136608/14 "2018-06-20T06:38:00Z")
"@version": "1" So all indexed with @version :1 i.e total count is 2522
---
### Author:  [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)
#### Post date: [June 20, 2018, 6:41am UTC](https://discuss.elastic.co/t/sql-server-to-logstash/136608/15 "2018-06-20T06:41:18Z")
> yes this is the query
> SELECT COUNT(DRG.[Id] )
> FROM [Drug] AS [DRG]
> LEFT OUTER JOIN [dbo].[DrugForm] AS [DRGFROM] ON [DRG].[DrugFormId] = [DRGFROM].[Id]
> LEFT OUTER JOIN [dbo].[DrugUnit] AS [DRGUNIT] ON [DRG].[DrugUnitId] = [DRGUNIT].[Id]
> LEFT OUTER JOIN [dbo].[DrugType] AS [DRGTYPE] ON [DRG].[DrugTypeId] = [DRGTYPE].[Id]
> LEFT OUTER JOIN [DBO].[Drug\_DrugCat] as [DRGCATEGORY] on [DRG].[ID] = [DRGCATEGORY].[DrugId]
> LEFT OUTER JOIN [DBO].[DrugCat] as [DRGCAT] on [DRGCATEGORY].[DrugCatId] = [DRGCAT].[Id]
>
> and the result count is 23026
No, that only counts the number of rows. To count the number of unique `DRG.[Id]` values you need:
```
SELECT DISTINCT COUNT(DRG.[Id]) ...
```
---
### Author:  [@kartheek91](https://discuss.elastic.co/u/kartheek91)
#### Post date: [June 20, 2018, 6:43am UTC](https://discuss.elastic.co/t/sql-server-to-logstash/136608/16 "2018-06-20T06:43:56Z")
> [@magnusbaeck](#):
>
> .
SELECT DISTINCT(COUNT(DRG.[Id]) )
FROM [Drug] AS [DRG]
LEFT OUTER JOIN [dbo].[DrugForm] AS [DRGFROM] ON [DRG].[DrugFormId] = [DRGFROM].[Id]
LEFT OUTER JOIN [dbo].[DrugUnit] AS [DRGUNIT] ON [DRG].[DrugUnitId] = [DRGUNIT].[Id]
LEFT OUTER JOIN [dbo].[DrugType] AS [DRGTYPE] ON [DRG].[DrugTypeId] = [DRGTYPE].[Id]
LEFT OUTER JOIN [DBO].[Drug\_DrugCat] as [DRGCATEGORY] on [DRG].[ID] = [DRGCATEGORY].[DrugId]
LEFT OUTER JOIN [DBO].[DrugCat] as [DRGCAT] on [DRGCATEGORY].[DrugCatId] = [DRGCAT].[Id]
and the count is same as above i.e. 23026
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [July 18, 2018, 6:43am UTC](https://discuss.elastic.co/t/sql-server-to-logstash/136608/17 "2018-07-18T06:43:56Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.