i've a case_number which has a field "task_id". i need to use that id to get all task details. i tried to get the value , it is empty. please help me out.
Here is the Elasticsearch pulled data
{
"_index": "logstash_itsm_incidents_parent",
"_id": "7xV-X4oBCe2ml7OsvNHn",
"_score": 1,
"_source": {
"tags": [
"_jdbcstreamingdefaultsused"
],
"incident_number": "INC0055453",
"case_number_id": "CS0017821",
"task_id": "case_number.task_id",
"sub_account_id": "ACCT7",
"@version": "1",
"tasks": {},
"sub_site_id": "",
"account_id": "ACCT6",
"@timestamp": "2023-09-04T09:36:29.104550Z",
"case_number": {
"case_number": "CS21",
"device_name": "",
"ticket_classification": "Reactive",
"device_id": "ACCTRUR7GOSBB",
"configuration_item": "A0",
"task_id": "CS0017821",
"alert_id": ""
},
"transaction": {
"asset_serial_number": "",
"reporting_sensor_count": null,
"impacted_device_count": 1
},
"state": "New",
"closed_at": null,
"short_description": "Rouge AP",
"description": "Rouge AP",
"site_id": "ACCT0011318",
"updated_at": "2022-04-12T10:00:05.000Z",
"incident_parent_id": null
}
Here my task id comes as "task_id": "case_number.task_id".
and tasks as {} .
From the below case number detail, i've to get the task_id and assign to task_id variable and use that task_id to pull task details and store it in tasks.
"case_number": {
"case_number": "CS21",
"device_name": "",
"ticket_classification": "Reactive",
"device_id": "ACSBB",
"configuration_item": "A0",
"task_id": "CS0017821",
"alert_id": ""
},
The configuration is being used as per below one. i tried add_field{"task_id" => [case_number][task_id]} as well as add_field{"task_id" => case_number.task_id} both failed . how to set the task_id field value from case_number
input {
jdbc {
jdbc_driver_library => "/usr/local/Cellar/logstash/8.9.0/libexec/logstash-core/lib/jars/postgresql-jdbc.jar"
jdbc_connection_string => "jdbc:postgresql://localhost:5432/pl_itsm_stg"
jdbc_user => "postgres"
jdbc_password => "root"
jdbc_driver_class => "org.postgresql.Driver"
tracking_column_type => "timestamp"
schedule => "0 * * * *" # cronjob schedule format (see "Helpful Links")
statement => "SELECT incident_number, site_id, sub_site_id, account_id, sub_account_id, closed_at, state, short_description, description, incident_parent_id, updated_at, case_number_id from customerdata_incident"
jdbc_fetch_size => "100000"
jdbc_paging_enabled => "true"
jdbc_page_size => "300"
}
}
filter {
if [case_number_id] {
jdbc_streaming {
jdbc_driver_library => "/usr/local/Cellar/logstash/8.9.0/libexec/logstash-core/lib/jars/postgresql-jdbc.jar"
jdbc_connection_string => "jdbc:postgresql://localhost:5432/pl_itsm_stg"
jdbc_user => "postgres"
jdbc_password => "root"
jdbc_driver_class => "org.postgresql.Driver"
statement => "select case_number,device_id, device_name, alert_id, ticket_classification, configuration_item, task_id from customerdata_case WHERE case_number = :case_id"
parameters => { "case_id" => "case_number_id"}
target => "case_number"
}
ruby {
code => '
cases = event.get("case_number")
if cases.is_a? Array
event.set("case", cases[0])
end
'
}
if [case] {
mutate {
rename => {"case" => "case_number"}
add_field =>{"task_id" => case_number.task_id}
}
}
if [task_id] {
jdbc_streaming {
jdbc_driver_library => "/usr/local/Cellar/logstash/8.9.0/libexec/logstash-core/lib/jars/postgresql-jdbc.jar"
jdbc_connection_string => "jdbc:postgresql://localhost:5432/pl_itsm_stg"
jdbc_user => "postgres"
jdbc_password => "root"
jdbc_driver_class => "org.postgresql.Driver"
statement => "select task_number, task_type from customerdata_task WHERE task_number = :task_num"
parameters => { "task_num" => "task_id"}
target => "tasks"
}
ruby {
code => '
task_info = event.get("tasks")
if task_info.is_a? Array
event.set("task_info", task_info[0])
end
'
}
mutate {
rename => {"task_info" => "tasks"}
}
}
}
jdbc_streaming {
jdbc_driver_library => "/usr/local/Cellar/logstash/8.9.0/libexec/logstash-core/lib/jars/postgresql-jdbc.jar"
jdbc_connection_string => "jdbc:postgresql://localhost:5432/pl_itsm_stg"
jdbc_user => "postgres"
jdbc_password => "root"
jdbc_driver_class => "org.postgresql.Driver"
statement => "select asset_serial_number, impacted_device_count, reporting_sensor_count from incident_transaction WHERE incident_id = :inc_id"
parameters => { "inc_id" => "incident_number"}
target => "transaction"
}
if [transaction] {
ruby {
code => '
transactions = event.get("transaction")
if transactions.is_a? Array
event.set("transaction", transactions[0])
end
'
}
}
if [incident_parent_id] {
jdbc_streaming {
jdbc_driver_library => "/usr/local/Cellar/logstash/8.9.0/libexec/logstash-core/lib/jars/postgresql-jdbc.jar"
jdbc_connection_string => "jdbc:postgresql://localhost:5432/pl_itsm_stg"
jdbc_user => "postgres"
jdbc_password => "root"
jdbc_driver_class => "org.postgresql.Driver"
statement => "select incident_number, company, sub_site_id, site_id, account_id, sub_account_id, issue_type, incident_state, resolved_at, resolved_by, impact, state, urgency from customerdata_incident WHERE incident_number = :parent_id"
parameters => { "parent_id" => "incident_parent_id"}
target => "incident_parent_id"
}
}
ruby {
code => '
incident_parent = event.get("incident_parent_id")
if incident_parent.is_a? Array
event.set("parent_id", incident_parent[0])
end
'
}
if [parent_id] {
mutate {
rename => {"parent_id" => "incident_parent_id"}
}
}
mutate {
copy => {"id" => "[@metadata][_id]"}
}
}
output {
stdout { codec => "json" }
elasticsearch {
hosts => ["https://localhost:9200"]
ssl => true
ssl_certificate_verification => false
cacert => "/Users/dev/ca_logstash.cer"
user => "elastic"
password => "+K"
index => "logstash_itsm_incidents_parent"
ilm_enabled => true
}
}
