Incremental load not execute properly


(NETHAJI) #1

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 }	

}


(system) #2

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