Not able to read the data from external json file in logstash config

Elastic Stack Logstash
1

I'm trying to search the host value from current event and looking for same value in json file. If Json block has the host value I'm just converting the block into struct value and inserting as a new column in index.

output.json file (External json file)

[
  {
    "datacenter": "aa",
    "port": "444",
    "decommissioned_date": "",
    "environment": "int",
    "hostname": "hello.net",
    "provisioned_date": "01.01.2020",
    "current_status": "active",
    "Cluster_name": "blue",
    "ip": "10.44.44.44",
  },
  {
    "datacenter": "aws-e1",
    "port": "333",
	"decommissioned_date": "",
    "environment": "integration",
	"provisioned_date": "01.01.2020",
    "hostname": "google.com",
	"current_status": "active",
    "cluster_name": "black",
    "ip": "96.94.44.22",
  }
]

Logstash.config

input {
  #stdin { }
  tcp {
    codec => json_lines { charset => "UTF-8" }
    port => 4560
  }
}
filter {
    json {
        source => "payload_raw"
        target => "payload"
   }


  translate {
    dictionary_path => "/app/output.json"
    field => "hostname"
    destination => "external_host_data"
    refresh_interval => 3600
    override => true
    }

    if [external_host_data] {
    mutate {
      add_field => {
        "struct_field" => "%{[external_host_data][0]}"
      }
    }
  }

}
output {
   elasticsearch {
           hosts => ["host.net:9200"]
           index => "logstash-testyml-details-%{+YYYY.MM.dd}"
           pipeline => "query_default_pipeline"
           user => "admin"
           password => "****"
           }
stdout { codec => rubydebug }
}

error :

[2023-12-13T12:30:13,942][ERROR][logstash.javapipeline    ][main] Pipeline error {:pipeline_id=>"main", :exception=>#<LogStash::Filters::Dictionary::DictionaryFileError: Tr
anslate: no implicit conversion of Array into Hash when loading dictionary file at /app/output.json>
2

You have an error related to your dictionary file, please share how the file /app/output.json looks like:

3

Here it is

4

You need to share the content of the file, or at least some sample lines of it.

5

Below content from output.json file

[
  {
    "datacenter": "aa",
    "port": "444",
    "decommissioned_date": "",
    "environment": "int",
    "hostname": "hello.net",
    "provisioned_date": "01.01.2020",
    "current_status": "active",
    "Cluster_name": "blue",
    "ip": "10.44.44.44"
  },
  {
    "datacenter": "aws-e1",
    "port": "333",
	"decommissioned_date": "",
    "environment": "integration",
	"provisioned_date": "01.01.2020",
    "hostname": "google.com",
	"current_status": "active",
    "cluster_name": "black",
    "ip": "96.94.44.22"
  }
]
6

Oh I see, I thought this was your input.

Your dictionary cannot be in this format, the dictionaries for the translate filter needs to be a key-value pair file, this will not work, you will need to change your dictionary.

It needs to be something like this:

"google.com": '{"datacenter": "aws-e1","port": "333","decommissioned_date": "","environment": "integration","provisioned_date": "01.01.2020","current_status": "active","cluster_name": "black","ip": "96.94.44.22" }'
"hello.net": '{"datacenter": "aa","port": "444","decommissioned_date": "","environment": "int","provisioned_date": "01.01.2020","current_status": "active","Cluster_name": "blue","ip": "10.44.44.44"}'

I made an old post a couple of years ago explaining how to use the translate filter, you can check it here if you have any doubts.

7

Thanks @leandrojmp
If i have external file as this format it's working. I can see {"datacenter": "aws-e1","port": "333","decommissioned_date": "","environment": "integration","provisioned_date": "01.01.2020","current_status": "active","cluster_name": "black","ip": "96.94.44.22" } as separate column. But it's falling on varchar. How can i push it as object to in index (so that i can easily query the nested data (

8

It is not clear what you mean with that.

9

I'm able to see the below value in index as separate columns and the column type is varchar. How can i make the column type as object or rowtype ? Is it possible ?

Index name : testing
Column name in index : struct_field
Column type in index : varchar
Column value : 
{"datacenter": "aws-e1","port": "333","decommissioned_date": "","environment": "integration","provisioned_date": "01.01.2020","current_status": "active","cluster_name": "black","ip": "96.94.44.22" }

In any way i can query the value of datacenter directly ? (i.e select struct_field.datacenter from testing )

10

Where is this from? There is no such thing as varchar in Elasticsearch.

But your issue is that the value in the dictionary is added as a string, you need to parse it using the json filter.

Did you read the blog post I shared? There is an explanation and an example on how to do that when you have a json as the value of your dictionary.

You will need to use a json filter on the field external_host_data to parse the json value.

11

Sorry for the confusion I'm using trino elastic connector to read the data.
Elasticsearch connector — Trino 434 Documentation.
Between I read your blog that helps much :slight_smile: Thanks

12

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