Split json message into a new column in kibana table

ARoy · March 22, 2020, 3:08pm

Hi, I need to add new column in kibana from the json message.
The message is as follows :
{"results":[{"gender":"female","name":{"title":"Ms","first":"Eden","last":"Morel"},"location":{"street":{"number":1412,"name":"Rue Abel-Hovelacque"},"city":"Angers","state":"Pyrénées-Atlantiques","country":"France","postcode":21446,"coordinates":{"latitude":"-3.8242","longitude":"87.5006"},"timezone":{"offset":"-12:00","description":"Eniwetok, Kwajalein"}},"email":"eden.morel@example.com","login":{"uuid":"4be0250c-2848-4db5-a6a2-c0cf9e6730c3","username":"bluerabbit132","password":"nipper","salt":"FsH1RuQB","md5":"d25604b4f525093a171bf9f9c0f16268","sha1":"76c392c6a13bb191ec219a8a9907e7cb914fcf92","sha256":"8d9f2fbe1e9174d8a42d2cacde36dab5feda235b377f43ec5f3f9022de67ab1f"},"dob":{"date":"1988-04-29T06:59:05.741Z","age":32},"registered":{"date":"2019-09-15T22:27:53.874Z","age":1},"phone":"05-44-20-72-74","cell":"06-83-85-66-62","id":{"name":"INSEE","value":"2NNaN47984362 17"},"picture":{"large":"https://randomuser.me/api/portraits/women/57.jpg","medium":"https://randomuser.me/api/portraits/med/women/57.jpg","thumbnail":"https://randomuser.me/api/portraits/thumb/women/57.jpg"},"nat":"FR"}],"info":{"seed":"61e2198c825b6740","results":1,"page":1,"version":"1.3"}}

Currently the message filed has all the data, and I need them into separate columns when discovered in kibana. I tried to add a new column in the logstash file as follows but no luck :
filter {
split {
add_field => {
"foo_%{somefield}" => "Hello world, from %{host}"
"new_field" => "new_static_value"
}
}
}

I am new to elk, can you please help.

Thanks,
A Roy.

Badger · March 22, 2020, 4:42pm

Try mutate instead if split.

ARoy · March 22, 2020, 5:09pm

Hi Badger,

I tried to mutate as follows although the applicationID is set, but there is no newId :

filter {
grok {
match => { "message" => "%{GREEDYDATA:message}"}
}

mutate {
        split => ["source", "/"]
        add_field => { "applicationID" => "11" }
		add_field => { "newID" => "%{source[0]}" }
       }            
}

is there any change i need to do in the parse ?
Basically I need to display any field like name, gender location etc from the message field.

Thanks,
A Roy

Badger · March 22, 2020, 5:13pm

Have you looked at an event and verified that it has a source field and not, for example, a [log][file][path] field?

ARoy · March 22, 2020, 5:36pm

Yes, here is the list of available fields in kibana

It did not have a source field so used message instead.
Also i modified the earlier filter as follows but still no new field :
filter {
grok {
match => { "message" => "%{GREEDYDATA:message}"}
}

mutate {
        split => ["message", "/"]
        add_field => { "applicationID" => "11a" }
		add_field => { "newID" => "%{message[0]}" }
       }            
}

Badger · March 22, 2020, 11:27pm

OK, so the source field does not exist. Also a grok that consumes the whole of the message field in one GREEDYDATA and assigns it to the message field makes no sense.

Try using a json filter.

json { source => "message" }

ARoy · March 23, 2020, 7:48am

My Json conf file is as follows :

input
{
http_poller {
urls => {
urlname => "https://randomuser.me/api"
}
request_timeout => 60
schedule => { every => "20s"}
codec => "json"
}
}

filter
{
json { source => "message" }

mutate 
{     
    add_field => { "applicationID" => "12" }
	add_field => { "newfield" => "[message][results][0]"}
}

}

output
{
elasticsearch
{
hosts => "http://localhost:9200"
index => "logstash_http_poller_61"
document_type => "npoller"
}

stdout 
{
	codec => dots
}

}

But although i can add applicationID in new columns but newfield is not added.

Badger · March 23, 2020, 2:51pm

You need to look at your data. Either in Kibana, in the JSON tab on an expanded event in the Discover pane, or else use

output { stdout { codec => rubydebug } }

I would not expect the field to be called [message][results][0], just [results][0].

ARoy · March 23, 2020, 4:58pm

Hi, have added filter like this :

   filter {
             json { source => "message" }  
             mutate {
                      add_field => { "applicationID" => "12" }
    	              add_field => { "newID" => "[results][0]"}
                    }            
           }

But the debug output as follows does not set for newId still :

{
            "newID" => "[results][0]",
             "info" => {
        "results" => 1,
        "version" => "1.3",
           "seed" => "04dd7c27f572a217",
           "page" => 1
    },
         "@version" => "1",
       "@timestamp" => 2020-03-23T16:53:40.712Z,
          "results" => [
        [0] {
                 "login" => {
                     "md5" => "8d15f677a7b7ac0b06fe9f5c5e5e5a47",
                    "sha1" => "f6b850f227785b3040c94493514cfa260400d1d4",
                    "salt" => "zddTLVFc",
                "password" => "virgil",
                    "uuid" => "706d5358-600b-49c8-8fd9-c60f8a08afa8",
                "username" => "ticklishmouse829",
                  "sha256" => "f4f6842e4241471e95c8a329a42216663ee6200f1093da14e28566fdb20d5d2f"
            },
                    "id" => {
                 "name" => "",
                "value" => nil
            },
              "location" => {
                    "country" => "Germany",
                "coordinates" => {
                    "longitude" => "-38.3661",
                     "latitude" => "-37.7415"
                },
                     "street" => {
                    "number" => 8714,
                      "name" => "Gartenweg"
                },
                   "postcode" => 56273,
                      "state" => "Hamburg",
                   "timezone" => {
                    "description" => "Mid-Atlantic",
                         "offset" => "-2:00"
                },
                       "city" => "Ellwangen (Jagst)"
            },
                 "phone" => "0560-5696538",
                   "dob" => {
                 "age" => 53,
                "date" => "1967-11-28T07:34:30.260Z"
            },
                "gender" => "male",
                  "name" => {
                "first" => "Ilhan",
                "title" => "Mr",
                 "last" => "Angerer"
            },
                  "cell" => "0177-1314699",
            "registered" => {
                 "age" => 17,
                "date" => "2003-01-19T18:56:32.652Z"
            },
                   "nat" => "DE",
               "picture" => {
                "thumbnail" => "https://randomuser.me/api/portraits/thumb/men/98.jpg",
                   "medium" => "https://randomuser.me/api/portraits/med/men/98.jpg",
                    "large" => "https://randomuser.me/api/portraits/men/98.jpg"
            },
                 "email" => "ilhan.angerer@example.com"
        }
    ],
    "applicationID" => "12"
}

Following are the results field value in kibana :

 {
  "login": {
    "uuid": "efb3b528-9346-486d-aa8b-5c1d25f55553",
    "salt": "oqAHx3jP",
    "md5": "2c5d872419f1fea6371b1966c316610e",
    "sha256": "b0969c257980ce2879d3f25094f0c2de66ceb553b29c0a1a73df1f2c1c519471",
    "username": "sadfrog808",
    "password": "lisalisa",
    "sha1": "951a7c417b960db9bf173a9ab7bb9fdfbff92f91"
  },
  "registered": {
    "date": "2017-03-13T03:16:58.686Z",
    "age": 3
  },
  "location": {
    "postcode": 2349,
    "timezone": {
      "description": "Central Time (US & Canada), Mexico City",
      "offset": "-6:00"
    },
    "city": "Bendigo",
    "state": "South Australia",
    "country": "Australia",
    "street": {
      "number": 7073,
      "name": "Hamilton Ave"
    },
    "coordinates": {
      "latitude": "-86.5667",
      "longitude": "95.8325"
    }
  },
  "name": {
    "title": "Ms",
    "last": "Williams",
    "first": "Sue"
  },
  "nat": "AU",
  "id": {
    "value": "707048521",
    "name": "TFN"
  },
  "email": "sue.williams@example.com",
  "gender": "female",
  "dob": {
    "date": "1949-04-17T11:37:50.272Z",
    "age": 71
  },
  "phone": "03-9333-3244",
  "picture": {
    "large": "https://randomuser.me/api/portraits/women/16.jpg",
    "thumbnail": "https://randomuser.me/api/portraits/thumb/women/16.jpg",
    "medium": "https://randomuser.me/api/portraits/med/women/16.jpg"
  },
  "cell": "0474-839-219"
}

Badger · March 23, 2020, 7:01pm

Please edit your post, select the configuration and click on </> in the tool bar above the edit pane. You will see the preview pane on the right change from

filter {
json { source => "message" }
mutate {
add_field => { "applicationID" => "12" }
add_field => { "newID" => "[results][0]"}
}
}

to

filter {
    json { source => "message" }
    mutate {
        add_field => { "applicationID" => "12" }
        add_field => { "newID" => "[results][0]"}
    }
}

Then do the same for the blocks of JSON.

ARoy · March 24, 2020, 10:02am

Hi Badger,
Sorry for the unformulated text earlier, I have reformatted them.
Please let me know where I am going wrong here, since i am not able to add any column/field from the results field in kibana. I need it urgently.

Thanks again.

A Roy.

Badger · March 24, 2020, 12:42pm

For a sprintf reference you need the %{}. Try

add_field => { "newID" => "%{[results][0]}"}

ARoy · March 24, 2020, 3:07pm

Thank you so much @Badger, it is working as expected now.

Regards,
Abhardeep

system · April 21, 2020, 3:07pm

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