# Incremental load not execute properly

**URL:** <https://discuss.elastic.co/t/incremental-load-not-execute-properly/157364>\
**Category:** Elasticsearch\
**Created:** [November 19, 2018, 1:23pm UTC](https://discuss.elastic.co/t/incremental-load-not-execute-properly/157364 "2018-11-19T13:23:46Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Nethaji](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nethaji/32/72803_2.png) [@Nethaji](https://discuss.elastic.co/u/Nethaji)\
**Post date:** [November 19, 2018, 1:23pm UTC](https://discuss.elastic.co/t/incremental-load-not-execute-properly/157364/1 "2018-11-19T13:23:46Z")

</div>

Hi

I have logstash config file like below, but i couldn't able to get the correct count whenever i inserted new record the duplication is occur

is there any possible to delete the elasticsearch index first and then execute the statement?

input {  
jdbc {  
jdbc\_connection\_string =\> "jdbc:mysql://ipaddress:3306/sam\_sit2"  
# The user we wish to execute our statement as  
jdbc\_user =\> "_**"  
jdbc\_password =\> "**_\*"  
# The path to our downloaded jdbc driver  
jdbc\_driver\_library =\> "D:\ELK\com.mysql.jdbc\_5.1.5.jar"  
jdbc\_driver\_class =\> "com.mysql.jdbc.Driver"  
# our query  
last\_run\_metadata\_path =\> "C:/Users/srv\_spadmin/.logstash\_jdbc\_last\_run"  
clean\_run =\> true  
use\_column\_value =\> true  
tracking\_column =\> ref\_id  
tracking\_column\_type =\> "numeric"  
statement=\>"  
select distinct(invtbl.SoftwareLincenseInvetory\_Id) , concat( IFNULL(sistbl.status\_id,' '), ' ',invtbl.SoftwareLincenseInvetory\_Id,'',ifnull(dtbl.DomainName,''))as ref\_id  
,(sistbl.Non\_ComplianceCost)

, invtbl.Version\_Id,invtbl.Edition\_Id,invtbl.Cost\_Id,invtbl.TotalLicense,dtbl.DomainName

,invtbl.year\_id,invtbl.manufacturer\_Id,invtbl.Licsensetype\_id

,invtbl.SoftwareCompliance,invtbl.SoftwareLicense\_Id,invtbl.overuseCost,invtbl.is\_softwareChecked,invtbl.Is\_Inventory

,sntbl.softwareName,vtbl.softwareVersion,etbl.editionName,lttbl.licensename,yrtbl.Year

,sistbl.softwareinv\_id,sistbl.status\_id,ltbl.LicenseAllocated,ltbl.LicenseAvailiable

,lstbl.LicenseStatus, ctbl.Cost, smtbl.manufacturerName, srstbl.SoftwareReconcileStatus,sttbl.sofwaretype

,concat(sntbl.softwareName,' ', vtbl.softwareVersion,' ',etbl.editionName) as softwarename\_ve

,(case when ( Is\_Inventory=1 )then invtbl.SoftwareLincenseInvetory\_Id  
else null  
end) as software\_assets\_kpi  
,(case when (is\_softwareChecked=1) then invtbl.SoftwareLincenseInvetory\_Id  
else null  
end) as reconcilestatus\_kpi  
,(case when (SoftwareCompliance=2 and is\_softwareChecked=1) then invtbl.SoftwareLincenseInvetory\_Id  
else null  
end) as non\_comp\_kpi

,(case when(SoftwareCompliance=1 and is\_softwareChecked=1) then 'Compliance'  
when (SoftwareCompliance=2 and is\_softwareChecked=1) then 'Non-Compliance'  
end) as reconcilestatus

,(case when (invtbl.Is\_Inventory=1 and invtbl.is\_softwareChecked=1) then 'Inventory'  
when (invtbl.Is\_Inventory=2 and invtbl.is\_softwareChecked=1) then 'Non-Inventory'  
end) as Softwaretypechart

,(case when (invtbl.licsensetype\_id = 1 and invtbl.is\_inventory=1) then 'MPN'  
when (invtbl.licsensetype\_id = 2 and invtbl.is\_inventory=1) then 'Volume'  
when (invtbl.licsensetype\_id = 6 and invtbl.is\_inventory=1) then 'OEM'  
end) as lictype  
,a.totalcost

from sam\_sit2.tbl\_softwarelicenseinventory invtbl

left join tbl\_softwareinvstatus sistbl on invtbl.SoftwareLincenseInvetory\_Id=sistbl.softwareinv\_id

left join tbl\_softwarelicensemaster sntbl on invtbl.SoftwareLicense\_Id =sntbl.software\_Id

left join tbl\_softwareversion vtbl on invtbl. Version\_Id = vtbl.version\_Id

left join tbl\_softwareeditionmaster etbl on invtbl.Edition\_Id =etbl.softwareEdition\_Id

left join tbl\_softwarelicensedetail ltbl on ltbl.SoftwareLicenseInventory\_Id=invtbl.SoftwareLincenseInvetory\_Id

left join tbl\_licensetype lttbl on invtbl.Licsensetype\_id=lttbl.license\_Id

left join tbl\_licensestatus lstbl on lstbl.LicenseStatus\_id=sistbl.status\_id

left join tbl\_cost ctbl on invtbl.Cost\_Id=ctbl.Cost\_Id

left join tbl\_softwaremanufacturer smtbl on invtbl.manufacturer\_Id=smtbl.manufacturer\_Id

left join tbl\_softwarereconcilestatus srstbl on invtbl.SoftwareCompliance=srstbl.SoftwareReconcileStatus\_Id

left join tbl\_sofwaretype sttbl on invtbl.Is\_Inventory=sttbl.sofwaretype\_Id

left join tbl\_year yrtbl on invtbl.Year\_id=yrtbl.Year\_id

left join tbl\_softwaretransaction ststbl on invtbl.SoftwareLincenseInvetory\_Id=ststbl.SoftwareLicenseInventory\_id

left join tbl\_domainname dtbl on ststbl.Domain\_id =dtbl.DomainName\_id

join sam\_sit2.sumtotalcost a

"  
}}  
output {

```
    elasticsearch {
	hosts => "http://ipaddress:9200"
	 index => "samdashboard2"
	   document_id => "%{ref_id}"   
	}
    stdout { codec => rubydebug }	

```

}

---

<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 17, 2018, 1:23pm UTC](https://discuss.elastic.co/t/incremental-load-not-execute-properly/157364/2 "2018-12-17T13:23:53Z")

</div>

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