How to split JSON nested arrays?

Elastic Stack Logstash
1

Hello,

I have quite a problem in attempt to split tags below is what I have:

message: {"fields":{"value":9936},"name":"sqlserver_performance","tags":{"counter":"Log File(s) Size (KB)","host":"WIN","instance":"Total","object":"MSSQL$DB:Databases","sql_instance":"WIN:DB"},"timestamp":1523617465000000000}

After parsing it with JSON in logstash by using:

json {
        source => "message"
    }

I get below results in elasticsearch:

{
  "_index": "logstash-sql-2018.04.13",
  "_type": "doc",
  "_id": "RuetvmIB7heNFYtXabpg",
  "_version": 1,
  "_score": null,
  "_source": {
    "offset": 1225848,
    "@timestamp": "2018-04-13T11:04:25.000Z",
    "fields": {
      "value": 9936
    },
    "tags": [
      [
        "host",
        "WIN"
      ],
      [
        "object",
        "MSSQL$DB:Databases"
      ],
      [
        "counter",
        "Log File(s) Size (KB)"
      ],
      [
        "instance",
        "Total"
      ],
      [
        "sql_instance",
        "WIN:DB"
      ]
    ],
    "beat": {
      "version": "6.2.3",
      "hostname": "WIN",
      "name": "WIN"
    },
    "name": "sqlserver_performance",
    "host": "WIN",
    "date": "1523617465000",
    "timestamp": 1523617465000000000,
    "@version": "1",
    "source": "c:\\temp\\sql\\sqlperf.out",
    "message": "{\"fields\":{\"value\":9936},\"name\":\"sqlserver_performance\",\"tags\":{\"counter\":\"Log File(s) Size (KB)\",\"host\":\"WIN\",\"instance\":\"Total\",\"object\":\"MSSQL$DB:Databases\",\"sql_instance\":\"WIN:DB\"},\"timestamp\":1523617465000000000}"
  },
  "fields": {
    "@timestamp": [
      "2018-04-13T11:04:25.000Z"
    ]
  },
  "sort": [
    1523617465000
  ]
}

Now this is almost perfect but I need to split tags array to separate fields with proper data types and I have no idea how to do it. Hopefully someone can help.

3

This reminds me of what you get without force_array => false in an xml filter :slight_smile:

If I understand the ask correctly then you could do it with this:

ruby { code => ' event.set("tagsAsHash", event.get("tags").to_h) ' }

"tagsAsHash" => {
            "host" => "WIN",
        "instance" => "Total",
         "counter" => "Log File(s) Size (KB)",
          "object" => "MSSQL$DB:Databases",
    "sql_instance" => "WIN:DB"
},
4

Really nice and such simple code, thank you a lot for help :slight_smile:

5

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