Hi
I couldn't able to perform incremental load in logstash config file because of i'm having multiple join query in my statement.
the only possible thing is i want to delete the elasticsearch index first and run the statement then i can achieve the exact count in my data.
if i delete the index and running the same query means i got the correct output, but i can't able to add the delete command in logstash config file
i tried like below but the delete command was not running
please provide some solution and how to achieve this
input {
exec{
type => "curl"
command => 'curl -XDELETE "http://ipaddress:9200/schedule"'
codec => json
interval => 120
}
jdbc {
jdbc_connection_string => "jdbc:mysql://192.168.7.231:3306/sam_sit2"
# The user we wish to execute our statement as
jdbc_user => "Test"
jdbc_password => "Admin123"
# 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://192.168.7.231:9200"
index => "samdashboard2"
document_id => "%{ref_id}"
}
stdout { codec => rubydebug }
}