# Elastic document count mismatch - SQL to ES

**URL:** <https://discuss.elastic.co/t/elastic-document-count-mismatch-sql-to-es/107648>\
**Category:** Elasticsearch\
**Created:** [November 15, 2017, 2:50am UTC](https://discuss.elastic.co/t/elastic-document-count-mismatch-sql-to-es/107648 "2017-11-15T02:50:31Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![RSundareswaran](https://avatars.discourse-cdn.com/v4/letter/r/9fc348/32.png) [@RSundareswaran](https://discuss.elastic.co/u/RSundareswaran)\
**Post date:** [November 15, 2017, 2:50am UTC](https://discuss.elastic.co/t/elastic-document-count-mismatch-sql-to-es/107648/1 "2017-11-15T02:50:31Z")

</div>

Hi,

I am trying to bulk index from MS-SQL data to ES (5.6.3). The SQL query in SSMS gives 16284 as total count but after getting indexed, Kibana gets the total count as 15768.However the stats in API gets shows both count in two differnt places. Please see below and let me what is the issue and how do I get the data count correctly in Kibana?

Also read some template mapping before indexing, still didnt help.

[https://discuss.elastic.co/t/bulk-index-cannot-change-type-for-index-with-more-than-one-shard/68560](https://discuss.elastic.co/t/bulk-index-cannot-change-type-for-index-with-more-than-one-shard/68560)

{  
"\_shards": {  
"total": 10,  
"successful": 5,  
"failed": 0  
},  
"\_all": {  
"primaries": {  
"docs": {  
"count":`15768`,  
"deleted": 0  
},  
"store": {  
"size\_in\_bytes": 69894322,  
"throttle\_time\_in\_millis": 0  
},  
"indexing": {  
"index\_total": `16284`,  
"index\_time\_in\_millis": 28164,  
"index\_current": 0,  
"index\_failed": 0,  
"delete\_total": 0,  
"delete\_time\_in\_millis": 0,  
"delete\_current": 0,  
"noop\_update\_total": 0,  
"is\_throttled": false,  
"throttle\_time\_in\_millis": 0  
},

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [November 15, 2017, 7:46am UTC](https://discuss.elastic.co/t/elastic-document-count-mismatch-sql-to-es/107648/2 "2017-11-15T07:46:12Z")

</div>

Strange. I’d say that may be some documents have been rejected or some documents are actually updates (using the same id)?

---

<div class="post-metadata">

**Author:** ![RSundareswaran](https://avatars.discourse-cdn.com/v4/letter/r/9fc348/32.png) [@RSundareswaran](https://discuss.elastic.co/u/RSundareswaran)\
**Post date:** [November 15, 2017, 1:41pm UTC](https://discuss.elastic.co/t/elastic-document-count-mismatch-sql-to-es/107648/3 "2017-11-15T13:41:20Z")

</div>

Thanks for the reply. Something weird I noticed. With `document_id` matched with a column in my SQL table, the count is mismatching, whereas if I leave it to ES with its own uuid, the count matches. I have attached the LS file here for your reference. Also i verified the `mtxnid` is a PK on the sql side, so there couldnt be a duplicate value.

```
input {
    jdbc {
        jdbc_connection_string => "jdbc:sqlserver://xxxx:1111;databaseName=udsihfuid"
        jdbc_user => "user"
	    jdbc_password => "passwd"
        jdbc_validate_connection => true
        jdbc_driver_library => "c:/users/downloads/sqljdbc4.jar"
        jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
        statement_filepath => "C:\ELK\5.6.3\logstash-5.6.3\config\jdbc_query7"
	#jdbc_page_size => 1000
    }
}
output {
    elasticsearch {
	hosts => "esserver:9200"
        index => "txns_qa_sample1"
        document_id => "%{mtxnid}"
    }

    stdout {
	codec => dots {}
    }

}

```

The query file is  
`SELECT Txn.TransactionID mtxnid FROM Transactions Txn INNER JOIN table1 (nolock) ON table1.TransactionID= Txn.TransactionID INNER JOIN table2 (nolock) ON table2.TransactionID = Txn.TransactionID`

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [November 15, 2017, 3:26pm UTC](https://discuss.elastic.co/t/elastic-document-count-mismatch-sql-to-es/107648/4 "2017-11-15T15:26:14Z")

</div>

> Also i verified the mtxnid is a PK on the sql side, so there couldnt be a duplicate value.

I think you need to double-check what your actual query is returning:

```auto
SELECT Txn.TransactionID mtxnid FROM Transactions Txn INNER JOIN table1 (nolock) ON table1.TransactionID= Txn.TransactionID INNER JOIN table2 (nolock) ON table2.TransactionID = Txn.TransactionID

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [December 13, 2017, 3:26pm UTC](https://discuss.elastic.co/t/elastic-document-count-mismatch-sql-to-es/107648/5 "2017-12-13T15:26:29Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.