# Using upsert to update existing record base on value in fields

**URL:** <https://discuss.elastic.co/t/using-upsert-to-update-existing-record-base-on-value-in-fields/313494>\
**Category:** Elasticsearch\
**Created:** [September 2, 2022, 2:56am UTC](https://discuss.elastic.co/t/using-upsert-to-update-existing-record-base-on-value-in-fields/313494 "2022-09-02T02:56:27Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![nookseal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nookseal/32/110368_2.png) [@nookseal](https://discuss.elastic.co/u/nookseal)\
**Post date:** [September 2, 2022, 2:56am UTC](https://discuss.elastic.co/t/using-upsert-to-update-existing-record-base-on-value-in-fields/313494/1 "2022-09-02T02:56:27Z")

</div>

Hi all,

Currently I'm trying to manage the Elasticsearch records from Filebeat which came from bash script and write to CSV file for each execute, then Filebeat collect log from CSV file.  
Below are example of header(not including in file)  
server\_ip,task\_number,task\_name,result

And the log file are as below. (came from bash script each execute/line)  
10.110.2.5, 1, check file A, False  
10.110.2.5, 2, check file B, True  
10.110.2.5, 1, check file A, True

I used Filebeat processor dissect to separate the field and directly ship to Elasicsearch without Logstash for now. The problem I'm facing now is record 1 and 3 are the same server\_ip and same task except the result that I would like to update instead and if server\_ip, task\_number, task\_name are new, so insert them.  
I know that Filebeat itself can't do this because it just shipping the log base on the message writing into CSV file

Currently in Elasticsearch  
record1 ---- 10.110.2.5, 1, check file A, False  
record2 ---- 10.110.2.5, 2, check file B, True  
record3 ---- 10.110.2.5, 1, check file A, True

The expected  
record1 ---- 10.110.2.5, 1, check file A, True  
record2 ---- 10.110.2.5, 2, check file B, True

The SQL query is like below  
**Update**  
`UPDATE dbName SET result = "True" WHERE server_ip = "10.110.2.5" AND task_number = "1" AND task_name = "check file A"`

Otherwise  
**Insert**  
`INSERT INTO dbNAME (server_ip, task_number, task_name, result) VALUES ("10.110.2.5", "1", "check file B", "True")`

Is Logstash be able to use upsert to update record base on value of multiple fields like this? Or any idea I can handle on this one?

---

<div class="post-metadata">

**Author:** ![nookseal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nookseal/32/110368_2.png) [@nookseal](https://discuss.elastic.co/u/nookseal)\
**Post date:** [September 2, 2022, 10:15am UTC](https://discuss.elastic.co/t/using-upsert-to-update-existing-record-base-on-value-in-fields/313494/2 "2022-09-02T10:15:43Z")

</div>

I've a query for \_update\_by\_query below, This help me achived what I want.

```auto
curl -X POST -k -u myusername:mypassword "https://localhost:9200/my-index/_update_by_query" -H 'Content-Type: application/json' -d'
{
  "script": {
    "inline": "ctx._source.result = 'True'",
    "lang": "painless"
  },
  "query": {
      "bool": {
            "must": [
                     {"match": {"server_ip": "10.110.2.5"}},
                     {"match": {"task_number": "1"}},
                     {"match": {"task_name": "check file A"}}
            ]
      }
    }
}
' 

```

but then I would like to convert them into logstash output so I tried but still doesn't work

```auto
output {
  elasticsearch {
    hosts => ["localhost:9200"]
    ssl_certificate_verification => 'false'
    index => 'my-index'
    user => 'myuser'
    password => 'mypassword'
    doc_as_upsert => true
    script => 'if(ctx._source.server_ip == "10.110.2.5" and ctx._source.task_number == "1" ctx._source.task_name == "check file A") { should update the result to "True" here}; else (ctx._source.item.add)'
    action => "create"
  }
}

```

---

<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:** [September 30, 2022, 10:16am UTC](https://discuss.elastic.co/t/using-upsert-to-update-existing-record-base-on-value-in-fields/313494/3 "2022-09-30T10:16:35Z")

</div>

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