Split json message into a new column in kibana table

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.

Try mutate instead if split.

1 Like

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

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

1 Like

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]}" }
       }            
}
1 Like

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" }

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.

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].

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"
}

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.

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.

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

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

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

Regards,
Abhardeep