# Missing Data - SQL Server 2017 to Elasticsearch

**URL:** <https://discuss.elastic.co/t/missing-data-sql-server-2017-to-elasticsearch/191342>\
**Category:** Logstash\
**Created:** [July 19, 2019, 7:58am UTC](https://discuss.elastic.co/t/missing-data-sql-server-2017-to-elasticsearch/191342 "2019-07-19T07:58:04Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![martb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/martb/32/78185_2.png) [@martb](https://discuss.elastic.co/u/martb)\
**Post date:** [July 19, 2019, 7:58am UTC](https://discuss.elastic.co/t/missing-data-sql-server-2017-to-elasticsearch/191342/1 "2019-07-19T07:58:04Z")

</div>

Hi,

Im ingesting data from a SQL Server 2017 database to Elasticsearch using logstash v6.2.4. Im finding that although I have a last run identifier Im missing data (source compared to output).  
This source data is static data i.e not updated just added to. The counts vary from data sets but normal 5-2 missing i.e In 17,790 Kibana 16,788

Please see conf below. Your help would be much appreciated.

input {  
jdbc {  
jdbc\_connection\_string =\> "jdbc:sqlserver://xxx.xxx.xxx.xxx\MSSQLSERVER:1433;database=database;user=username;password=password"  
jdbc\_user =\> nil  
jdbc\_driver\_library =\> "/usr/share/java/sqljdbc\_6.2/enu/mssql-jdbc-6.2.2.jre8.jar"  
jdbc\_driver\_class =\> "com.microsoft.sqlserver.jdbc.SQLServerDriver"  
statement =\> "  
SELECT  
slm\_event.eventid AS eventid,  
slm\_event.eventtime AS eventtime,  
slm\_tag\_eventtype.tagvalue AS eventtype,  
slm\_tag\_platformhostname.tagvalue AS platformhostname,  
slm\_tag\_platformtype.tagvalue AS platformtype,  
slm\_tag\_program.tagvalue AS program,  
slm\_tag\_updatedescription.tagvalue AS updatedescription,  
slm\_tag\_outcome.tagvalue AS outcome,  
slm\_tag\_outofhours.tagvalue AS outofhours,  
slm\_tag\_eventdetail.tagvalue AS eventdetail,  
slm\_tag\_reason.tagvalue AS reason  
FROM  
slm\_event  
INNER JOIN  
slm\_tag slm\_tag\_type  
ON slm\_tag\_type.eventid = slm\_event.eventid  
AND slm\_tag\_type.tagname = 'TYPE-3-3'  
LEFT OUTER JOIN  
slm\_tag slm\_tag\_eventtype  
ON slm\_tag\_eventtype.eventid = slm\_event.eventid  
AND slm\_tag\_eventtype.tagname = 'Type'  
LEFT OUTER JOIN  
slm\_tag slm\_tag\_platformhostname  
ON slm\_tag\_platformhostname.eventid = slm\_event.eventid  
AND slm\_tag\_platformhostname.tagname = 'Platform hostname'  
LEFT OUTER JOIN  
slm\_tag slm\_tag\_platformtype  
ON slm\_tag\_platformtype.eventid = slm\_event.eventid  
AND slm\_tag\_platformtype.tagname = 'Platform type'  
LEFT OUTER JOIN  
slm\_tag slm\_tag\_program  
ON slm\_tag\_program.eventid = slm\_event.eventid  
AND slm\_tag\_program.tagname = 'Program'  
LEFT OUTER JOIN  
slm\_tag slm\_tag\_updatedescription  
ON slm\_tag\_updatedescription.eventid = slm\_event.eventid  
AND slm\_tag\_updatedescription.tagname = 'Update'  
LEFT OUTER JOIN  
slm\_tag slm\_tag\_outcome  
ON slm\_tag\_outcome.eventid = slm\_event.eventid  
AND slm\_tag\_outcome.tagname = 'Outcome'  
LEFT OUTER JOIN  
slm\_tag slm\_tag\_outofhours  
ON slm\_tag\_outofhours.eventid = slm\_event.eventid  
AND slm\_tag\_outofhours.tagname = 'Out of hours'  
LEFT OUTER JOIN  
slm\_tag slm\_tag\_eventdetail  
ON slm\_tag\_eventdetail.eventid = slm\_event.eventid  
AND slm\_tag\_eventdetail.tagname = 'Event detail'  
LEFT OUTER JOIN  
slm\_tag slm\_tag\_reason  
ON slm\_tag\_reason.eventid = slm\_event.eventid  
AND slm\_tag\_reason.tagname = 'Reason'  
WHERE slm\_event.eventtime \> '2019-3-01'  
AND slm\_event.eventid \> :sql\_last\_value  
ORDER BY eventid;  
"  
type =\> "TYPE-3-3"  
last\_run\_metadata\_path =\> "/etc/logstash/last\_run/type-3-3"  
schedule =\> "\*/5 \* \* \* \*"  
tracking\_column =\> "eventid"  
tracking\_column\_type =\> "numeric"  
use\_column\_value =\> true  
}  
}  
filter {  
if [type] == "TYPE-3-3" {  
mutate {  
convert =\> { "eventtime" =\> "string" }  
add\_field =\> {"acsctype" =\> "%{type}" }  
}  
date {  
match =\> ["eventtime", "ISO8601"]  
remove\_field =\> ["eventtime"]  
}  
translate {  
field =\> "acsctype"  
destination =\> "control"  
dictionary\_path =\> "/etc/logstash/controls.yml"  
}  
translate {  
field =\> "acsctype"  
destination =\> "controlcategory"  
dictionary\_path =\> "/etc/logstash/control-category.yml"  
}  
}  
}  
output {  
if [type] == "TYPE-3-3" {  
elasticsearch {  
hosts =\> "[http://127.0.0.1:9200](http://127.0.0.1:9200)"  
user =\> "user"  
password =\> "password"  
index =\> "acsc-main-controls-%{+YYYY.MM.dd}"  
}  
}  
}

---

<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:** [August 16, 2019, 7:58am UTC](https://discuss.elastic.co/t/missing-data-sql-server-2017-to-elasticsearch/191342/2 "2019-08-16T07:58:18Z")

</div>

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