Json parse

Elastic Stack Logstash
1

hi all,
i want to parse my massage , it look like this below
so the columnName value will be the field name
and ColumnValue will be the value of the new field.
my code looks like :slight_smile:

input {
jdbc {
jdbc_driver_library => "/etc/logstash/drivers/sqljdbc_6.0/enu/jre8/sqljdbc42.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://XXX:1433;databasename=ELK"
jdbc_user => "ELuser"
jdbc_password => "PPPPPAa"
statement => " SELECT * FROM [dbo].[_tmp_elk] WHERE json_full_text IS NOT NULL "
tags => ["pct"]
clean_run => false
}
}
filter {
if "pct" in [tags]
{
json {
source => "json_full_text"
add_field => {"[Columns][ColumnName]","%{[Columns][ColumnValue]}"}

	}

date {
match => [ "RequestOpenDate", "dd-MM-yyyy HH:mm" ]
target => "RequestOpenDate"
}
date {
match => [ "RequestCloseDate", "dd-MM-yyyy HH:mm" ]
target => "RequestCloseDate"
}
date {
match => [ "Actions_InsertedDate", "dd-MM-yyyy HH:mm" ]
target => "Actions_InsertedDate"
}

mutate { remove_field => [ "json_full_text" ] }

}
}

output {
if "pct" in [tags] {
elasticsearch {
user => elastic
password => nnuuuyt
hosts => ["tlgdelkedb02.harel-office.com:9200"]
index => "pct2-%{+YYYY.MM.dd}"
}
}
}

see the document below :
"_index": "pro-2017.08.17",
"_type": "logs",
"_id": "AV60penuPiY1C8rIJQL9",
"_score": 1,
"_source": {
"ownerdepartment": "client",
"ownermobile": null,
"type": "logs",
"actions_inserteddate": "2010-10-27T15:06:00.000Z",
"itemid": 93091,
"actions_status": "yes",
"requesttype": "profile",
"@version": "1",
"requestessence": null,
"requestclosedate": "2010-10-27T15:06:00.000Z",
"requestcurrenthandler": "xxx",
"owner": "מתי קלמן",
"actions_station": "sss",
"Columns": [
{
"ColumnID": 2520,
"ColumnName": "geter",
"ColumnValue": "מmati glman",
"ColumnValueID": null
},
{
"ColumnID": 42083,
"ColumnName": "deparment",
"ColumnValue": "senior",
"ColumnValueID": null
},
{
"ColumnID": 2512,
"ColumnName": "phone",
"ColumnValue": "067547340",
"ColumnValueID": null
}
],
"requestparentitemid": 92853,
"requestopendate": "2010-10-20T11:03:00.000Z",
"actions_notes": null,
"tags": [
"pct"
],
"ownerposition": "xxx",
"requestsubject": "yyyyy",
"actions_id": 128528,
"@timestamp": "2017-08-17T10:55:16.875Z",
"ownerphone": 37547340,
"requeststatus": "close",
"actions_insertedby": "yaniv hav"
}
}

2

You need to write some Ruby code in a ruby filter to loop over all hashes in the Columns field and create fields for them. I'm pretty sure a solution to this has been posted here in the past.

3

You can send a keywords or link, so I can find it?

4

hi
i found somthing that you wrote in discuss
Parse JSON array to flat JSON with filter, or alternative solution

I understood i can not use it like this any more,
can you help me by where to put set and get in this code?

ruby {
code => "
event['contextMap'].each { |kv|
event[kv['key']] = kv['value'] unless kv['value'].nil?
}
"
}

5 
event.get('contextMap').each { |kv|
  event.set(kv['key'], kv['value']) unless kv['value'].nil?
}
6

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