# From logstash how to lookup on a data in elastic index?

**URL:** <https://discuss.elastic.co/t/from-logstash-how-to-lookup-on-a-data-in-elastic-index/281387>\
**Category:** Logstash\
**Created:** [August 13, 2021, 4:41pm UTC](https://discuss.elastic.co/t/from-logstash-how-to-lookup-on-a-data-in-elastic-index/281387 "2021-08-13T16:41:20Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![sathish.mtech01](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sathish.mtech01/32/85207_2.png) [@sathish.mtech01](https://discuss.elastic.co/u/sathish.mtech01)\
**Post date:** [August 13, 2021, 4:41pm UTC](https://discuss.elastic.co/t/from-logstash-how-to-lookup-on-a-data-in-elastic-index/281387/1 "2021-08-13T16:41:21Z")

</div>

Need a support,

i have created a lookup index in elastic search  
index - lookup  
Username;Identifier;First name;Last name  
booker12;l9012;Rachel;Booker  
grey07;l2070;Laura;Grey  
johnson81;l4081;Craig;Johnson  
jenkins46;l9346;Mary;Jenkins  
smith79;l5079;Jamie;Smith

In logstash

Input data  
{"Identifier":"l9012"}  
I have to use in filter the elastic index - lookup to transform the output to  
{"Identifier":"l9012","Username":"booker12"}

Store to elastic logstash index

Questions / suggestions are welcome

---

<div class="post-metadata">

**Author:** ![rugenl](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rugenl/32/12887_2.png) [@rugenl](https://discuss.elastic.co/u/rugenl)\
**Post date:** [August 13, 2021, 5:51pm UTC](https://discuss.elastic.co/t/from-logstash-how-to-lookup-on-a-data-in-elastic-index/281387/2 "2021-08-13T17:51:25Z")

</div>

You would use the [elasticsearch filter plugin](https://www.elastic.co/guide/en/logstash/current/plugins-filters-elasticsearch.html). Your use case seems pretty simple, am I missing something more?

---

<div class="post-metadata">

**Author:** ![sathish.mtech01](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sathish.mtech01/32/85207_2.png) [@sathish.mtech01](https://discuss.elastic.co/u/sathish.mtech01)\
**Post date:** [August 17, 2021, 2:10pm UTC](https://discuss.elastic.co/t/from-logstash-how-to-lookup-on-a-data-in-elastic-index/281387/3 "2021-08-17T14:10:30Z")

</div>

Hey thanks @rugenl ... Yes its looks simple simple but not able to implement though

Let me put in detail the logstash conf and data used

elastic index : lookup1

```auto
Identifier:l9012 Username:booker12 First name:Rachel Last name:Booker _id:R_ESQHsB2a-Go4z9V2H3 _type:_doc _index:lookup1 _score:0
Identifier:l2070 Username:grey07 First name:Laura Last name:Grey _id:SPESQHsB2a-Go4z9V2H3 _type:_doc _index:lookup1 _score:0
Identifier:l4081 Username:johnson81 First name:Craig Last name:Johnson _id:SfESQHsB2a-Go4z9V2H3 _type:_doc _index:lookup1 _score:0
Identifier:l9346 Username:jenkins46 First name:Mary Last name:Jenkins _id:SvESQHsB2a-Go4z9V2H3 _type:_doc _index:lookup1 _score:0
Identifier:l5079 Username:smith79 First name:Jamie Last name:Smith _id:S_ESQHsB2a-Go4z9V2H3 _type:_doc _index:lookup1 _score:0

```

data : sample.log

```auto
Testlog-{"name":"csk","opid":"l9012"}
Testlog-{"name":"csk1","opid":"l9013"}
Testlog-{"name":"csk2","opid":"l9014"}
Testlog-{"name":"csk2","opid":"l9014"}
Testlog-{"name":"csk2","opid":"l9012"}

```

logstash conf : logstash-elastic-lookup.conf

```auto
input {
    file {
    path => "/home/csk/elk/logstash/sample.log"
    start_position => "beginning"
  }
}
filter{
mutate{
gsub=> ["message","Testlog-",""]
}
if [message] =~ /^{.*}$/
    {
        json { 
		source => "message" 
		target => "message_extract" 
	     }
	elasticsearch {
            hosts => ["localhost:9200"]
	    index => "lookup1"
            query => "Identifier:%{[message_extract][opid]}"
            fields => { "Username" => "test" }
         }
    }
else{
mutate{
add_tag=> ["_testlogparsefailure"]
}
}
}
output {
file {
    path => "/home/csk/elk/logstash/sample_look.log"
    codec => json
  }
      stdout { codec => rubydebug }
    }

```

Output write to file sample\_look.log

```auto
{"message_extract":{"opid":"l9012","name":"csk"},"message":"{\"name\":\"csk\",\"opid\":\"l9012\"}","path":"/home/csk/elk/logstash/sample.log","@timestamp":"2021-08-17T13:51:33.198Z","tags":["_elasticsearch_lookup_failure"],"@version":"1","host":"csk-ai-revolution"}
{"message_extract":{"opid":"l9013","name":"csk1"},"message":"{\"name\":\"csk1\",\"opid\":\"l9013\"}","path":"/home/csk/elk/logstash/sample.log","@timestamp":"2021-08-17T13:51:33.246Z","tags":["_elasticsearch_lookup_failure"],"@version":"1","host":"csk-ai-revolution"}
{"message_extract":{"opid":"l9014","name":"csk2"},"message":"{\"name\":\"csk2\",\"opid\":\"l9014\"}","path":"/home/csk/elk/logstash/sample.log","@timestamp":"2021-08-17T13:51:33.248Z","tags":["_elasticsearch_lookup_failure"],"@version":"1","host":"csk-ai-revolution"}
{"message_extract":{"opid":"l9014","name":"csk2"},"message":"{\"name\":\"csk2\",\"opid\":\"l9014\"}","path":"/home/csk/elk/logstash/sample.log","@timestamp":"2021-08-17T13:51:33.249Z","tags":["_elasticsearch_lookup_failure"],"@version":"1","host":"csk-ai-revolution"}
{"message_extract":{"opid":"l9012","name":"csk2"},"message":"{\"name\":\"csk2\",\"opid\":\"l9012\"}","path":"/home/csk/elk/logstash/sample.log","@timestamp":"2021-08-17T13:51:33.249Z","tags":["_elasticsearch_lookup_failure"],"@version":"1","host":"csk-ai-revolution"}

```

Expected output

```auto
{"message_extract":{"opid":"l9012","name":"csk2"},"message":"{\"name\":\"csk2\",\"opid\":\"l9012\"}","path":"/home/csk/elk/logstash/sample.log","@timestamp":"2021-08-17T13:51:33.249Z","tags":["_elasticsearch_lookup_failure"],"@version":"1","host":"csk-ai-revolution","test":"booker12"}

```

Am I doing anything wrong please suggest..

---

<div class="post-metadata">

**Author:** ![rugenl](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rugenl/32/12887_2.png) [@rugenl](https://discuss.elastic.co/u/rugenl)\
**Post date:** [August 17, 2021, 4:20pm UTC](https://discuss.elastic.co/t/from-logstash-how-to-lookup-on-a-data-in-elastic-index/281387/4 "2021-08-17T16:20:26Z")

</div>

The tag "\_elasticsearch\_lookup\_failure" shows you're getting to the elasticsearch filter, but it's not working. Unless it's a typo here, you are looking for "index1", but said you created "index".

Is there anything in the logstash or elastic logs? Check the elastic audit log too if you have it. You can reproduce this query with curl to see if it works. Something like this:

```auto
curl -XGET "http://localhost:9200/index1/_search" -H 'Content-Type: application/json' -d'
{
  "match": {
    "terms": {
      "Identifier": "19012"
    }
  }
}'

```

Does your Elastic stack require https?

---

<div class="post-metadata">

**Author:** ![sathish.mtech01](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sathish.mtech01/32/85207_2.png) [@sathish.mtech01](https://discuss.elastic.co/u/sathish.mtech01)\
**Post date:** [August 18, 2021, 8:12am UTC](https://discuss.elastic.co/t/from-logstash-how-to-lookup-on-a-data-in-elastic-index/281387/5 "2021-08-18T08:12:00Z")

</div>

Thanks @rugenl ..it works on http only..

Also executed the command in console: getting error  
Data is available in the index : lookup1

 ![kibana3](https://us1.discourse-cdn.com/elastic/original/3X/8/5/8587eb1958061f367657bfadf3893225812f160c.png)

In kibana also i can see the data

 ![kibana](https://us1.discourse-cdn.com/elastic/original/3X/0/3/03fde3b373909ee11652781065415ac21b7d0c59.png)

My query is  
When a data comes to logstash need to use a lookup table which is a index (lookup1) in elastic search and add a new column and update the user name.

Could you please help here, hope the logic is clear.

---

<div class="post-metadata">

**Author:** ![rugenl](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rugenl/32/12887_2.png) [@rugenl](https://discuss.elastic.co/u/rugenl)\
**Post date:** [August 18, 2021, 2:00pm UTC](https://discuss.elastic.co/t/from-logstash-how-to-lookup-on-a-data-in-elastic-index/281387/6 "2021-08-18T14:00:21Z")

</div>

I don't use curl queries often and construction one I can't test is harder. Try this query, I'm pretty sure my first one is wrong. You can try it in devtools to get it debugged too. I still think you are close.

> [@rugenl](#):
>
> ```auto
> curl -XGET "http://localhost:9200/index1/_search" -H 'Content-Type: application/json' -d'
> {
> "query": {
> "term": {
> "Identifier": "l9012"
> }
> }
> }'
> 
> ```

---

<div class="post-metadata">

**Author:** ![sathish.mtech01](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sathish.mtech01/32/85207_2.png) [@sathish.mtech01](https://discuss.elastic.co/u/sathish.mtech01)\
**Post date:** [August 18, 2021, 2:11pm UTC](https://discuss.elastic.co/t/from-logstash-how-to-lookup-on-a-data-in-elastic-index/281387/7 "2021-08-18T14:11:08Z")

</div>

> [@rugenl](#):
>
> ```auto
> curl -XGET "http://localhost:9200/index1/_search" -H 'Content-Type: application/json' -d'
> {
> "query": {
> "term": {
> "Identifier": "l9012"
> }
> }
> }'
> 
> ```

Thanks @rugenl ...Yes i got the response for the query but how to incorporate same in logstash..

 ![test](https://us1.discourse-cdn.com/elastic/original/3X/9/f/9fbae351f16c496bf672252305913d002d11dfc6.png)

---

<div class="post-metadata">

**Author:** ![rugenl](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rugenl/32/12887_2.png) [@rugenl](https://discuss.elastic.co/u/rugenl)\
**Post date:** [August 18, 2021, 3:15pm UTC](https://discuss.elastic.co/t/from-logstash-how-to-lookup-on-a-data-in-elastic-index/281387/8 "2021-08-18T15:15:26Z")

</div>

Are you sure that there is nothing in the logs for either logstash or elasticsearch? We need to know what query the logstash filter is sending, so (I hope this is a linux host) you should be able to capture the net traffic with something like this:

```auto
tcpdump -i lo -s 1500 -w filter.pcap port 9200

```

Start tcpdump, run your logstash test, stop tcpdump and look at the captured file with something like wireshark. You should see a http request similar to the curl test. Hopefully there isn't a lot of other traffic to localhost:9200.

---

<div class="post-metadata">

**Author:** ![sathish.mtech01](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sathish.mtech01/32/85207_2.png) [@sathish.mtech01](https://discuss.elastic.co/u/sathish.mtech01)\
**Post date:** [August 18, 2021, 3:25pm UTC](https://discuss.elastic.co/t/from-logstash-how-to-lookup-on-a-data-in-elastic-index/281387/9 "2021-08-18T15:25:03Z")

</div>

> [@rugenl](#):
>
> `tcpdump -i lo -s 1500 -w filter.pcap port 9200`

Logstash i see some error

 ![data out](https://us1.discourse-cdn.com/elastic/original/3X/0/1/01fb1cc3d597e64b3260f1a214a680a5e9871bd0.png)

```auto
021-08-18T20:51:03,117][WARN][logstash.filters.elasticsearch] Failed to query elasticsearch for previous event {:index=>"lookup1", :error=>"[400] {\"error\":{\"root_cause\":[{\"type\":\"query_shard_exception\",\"reason\":\"No mapping found for [@timestamp] in order to sort on\",\"index_uuid\":\"t3tT7cyoRF6IXoG0WzBdew\",\"index\":\"lookup1\"}],\"type\":\"search_phase_execution_exception\",\"reason\":\"all shards failed\",\"phase\":\"query\",\"grouped\":true,\"failed_shards\":[{\"shard\":0,\"index\":\"lookup1\",\"node\":\"TzAW1Nu9REufdUwdXFjXlQ\",\"reason\":{\"type\":\"query_shard_exception\",\"reason\":\"No mapping found for [@timestamp] in order to sort on\",\"index_uuid\":\"t3tT7cyoRF6IXoG0WzBdew\",\"index\":\"lookup1\"}}]},\"status\":400}"}

```

---

<div class="post-metadata">

**Author:** ![rugenl](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rugenl/32/12887_2.png) [@rugenl](https://discuss.elastic.co/u/rugenl)\
**Post date:** [August 18, 2021, 3:50pm UTC](https://discuss.elastic.co/t/from-logstash-how-to-lookup-on-a-data-in-elastic-index/281387/10 "2021-08-18T15:50:57Z")

</div>

You'll have to look in the filter.pcap file to see what logstash sent to elasticsearch.

---

<div class="post-metadata">

**Author:** ![rugenl](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rugenl/32/12887_2.png) [@rugenl](https://discuss.elastic.co/u/rugenl)\
**Post date:** [August 18, 2021, 6:36pm UTC](https://discuss.elastic.co/t/from-logstash-how-to-lookup-on-a-data-in-elastic-index/281387/11 "2021-08-18T18:36:15Z")

</div>

Ok, I finally see the error "no mapping found for @timestamp". The [doc](https://www.elastic.co/guide/en/logstash/current/plugins-filters-elasticsearch.html) section for templates seems to imply that the simple query is adding size = 1 and a sort on @timestamp. You don't have @timestamp in your lookup index.

I think you may need to create a template and omit the sort.

---

<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 15, 2021, 6:36pm UTC](https://discuss.elastic.co/t/from-logstash-how-to-lookup-on-a-data-in-elastic-index/281387/12 "2021-09-15T18:36:52Z")

</div>

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