Parsing JSON data

Hi Team,
New to ELK environment so please bear with me.
I'm trying to ingest data from json file and visualise in kibana. The ingest itself is working but the message is coming as: "message" => " "totalCount": 83\r",

    • how can i remove the \r" from the message
    • inside kibana the index needs to have value of 83 instead of being counted as 1
    • how can i format the json data to comeup as needAttention: 0, knownMalware: 37

JSON file:

{
    "data": {
        "malwareCountFilters": [
            {
                "count": 0,
                "filter": "needsAttention"
            },
            {
                "count": 37,
                "filter": "KnownMalware"
            },
            {
                "count": 0,
                "filter": "UnknownMalware"
            },
            {
                "count": 46,
                "filter": "FilelessMalware"
            },
            {
                "count": 0,
                "filter": "ApplicationControlMalware"
            }
        ],
        "totalCount": 83
    },
    "expectedResults": 0,
    "failedServersInfo": null,
    "failures": 0,
    "hidePartialSuccess": false,
    "message": "",
    "status": "SUCCESS"
}

logstash conf:


input {
    file {
        path => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
		start_position => "beginning"
	    sincedb_path => "NUL"
    }
}

filter {
     json { source => "message" }
			mutate { remove_field => [ "@version", "name", "host", "original", "file", "@timestamp", "path" ] }
		}	 

output{

elasticsearch{
		hosts => ["https://localhost:9200"]
		index => "cybereason"
		ssl => true
		ssl_certificate_verification => false
		user => "user"
    	password => "pwd@pwd"
	}
	
	stdout { codec => rubydebug }
}

logstash output:

{
       "tags" => [
        [0] "_jsonparsefailure"
    ],
        "log" => {
        "file" => {
            "path" => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
        }
    },
    "message" => "{\r",
      "event" => {
        "original" => "{\r"
    }
}
{
       "tags" => [
        [0] "_jsonparsefailure"
    ],
        "log" => {
        "file" => {
            "path" => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
        }
    },
    "message" => "    \"data\": {\r",
      "event" => {
        "original" => "    \"data\": {\r"
    }
}
{
       "tags" => [
        [0] "_jsonparsefailure"
    ],
        "log" => {
        "file" => {
            "path" => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
        }
    },
    "message" => "        \"malwareCountFilters\": [\r",
      "event" => {
        "original" => "        \"malwareCountFilters\": [\r"
    }
}
{
       "tags" => [
        [0] "_jsonparsefailure"
    ],
        "log" => {
        "file" => {
            "path" => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
        }
    },
    "message" => "            {\r",
      "event" => {
        "original" => "            {\r"
    }
}
{
       "tags" => [
        [0] "_jsonparsefailure"
    ],
        "log" => {
        "file" => {
            "path" => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
        }
    },
    "message" => "                \"count\": 0,\r",
      "event" => {
        "original" => "                \"count\": 0,\r"
    }
}
{
       "tags" => [
        [0] "_jsonparsefailure"
    ],
        "log" => {
        "file" => {
            "path" => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
        }
    },
    "message" => "                \"filter\": \"needsAttention\"\r",
      "event" => {
        "original" => "                \"filter\": \"needsAttention\"\r"
    }
}
{
       "tags" => [
        [0] "_jsonparsefailure"
    ],
        "log" => {
        "file" => {
            "path" => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
        }
    },
    "message" => "            },\r",
      "event" => {
        "original" => "            },\r"
    }
}
{
       "tags" => [
        [0] "_jsonparsefailure"
    ],
        "log" => {
        "file" => {
            "path" => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
        }
    },
    "message" => "            {\r",
      "event" => {
        "original" => "            {\r"
    }
}
{
       "tags" => [
        [0] "_jsonparsefailure"
    ],
        "log" => {
        "file" => {
            "path" => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
        }
    },
    "message" => "                \"count\": 37,\r",
      "event" => {
        "original" => "                \"count\": 37,\r"
    }
}
{
       "tags" => [
        [0] "_jsonparsefailure"
    ],
        "log" => {
        "file" => {
            "path" => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
        }
    },
    "message" => "                \"filter\": \"KnownMalware\"\r",
      "event" => {
        "original" => "                \"filter\": \"KnownMalware\"\r"
    }
}
{
       "tags" => [
        [0] "_jsonparsefailure"
    ],
        "log" => {
        "file" => {
            "path" => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
        }
    },
    "message" => "            },\r",
      "event" => {
        "original" => "            },\r"
    }
}
{
       "tags" => [
        [0] "_jsonparsefailure"
    ],
        "log" => {
        "file" => {
            "path" => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
        }
    },
    "message" => "            {\r",
      "event" => {
        "original" => "            {\r"
    }
}
{
       "tags" => [
        [0] "_jsonparsefailure"
    ],
        "log" => {
        "file" => {
            "path" => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
        }
    },
    "message" => "                \"count\": 0,\r",
      "event" => {
        "original" => "                \"count\": 0,\r"
    }
}
{
       "tags" => [
        [0] "_jsonparsefailure"
    ],
        "log" => {
        "file" => {
            "path" => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
        }
    },
    "message" => "                \"filter\": \"UnknownMalware\"\r",
      "event" => {
        "original" => "                \"filter\": \"UnknownMalware\"\r"
    }
}
{
       "tags" => [
        [0] "_jsonparsefailure"
    ],
        "log" => {
        "file" => {
            "path" => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
        }
    },
    "message" => "            },\r",
      "event" => {
        "original" => "            },\r"
    }
}
{
       "tags" => [
        [0] "_jsonparsefailure"
    ],
        "log" => {
        "file" => {
            "path" => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
        }
    },
    "message" => "            {\r",
      "event" => {
        "original" => "            {\r"
    }
}
{
       "tags" => [
        [0] "_jsonparsefailure"
    ],
        "log" => {
        "file" => {
            "path" => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
        }
    },
    "message" => "                \"count\": 46,\r",
      "event" => {
        "original" => "                \"count\": 46,\r"
    }
}
{
       "tags" => [
        [0] "_jsonparsefailure"
    ],
        "log" => {
        "file" => {
            "path" => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
        }
    },
    "message" => "                \"filter\": \"FilelessMalware\"\r",
      "event" => {
        "original" => "                \"filter\": \"FilelessMalware\"\r"
    }
}
{
       "tags" => [
        [0] "_jsonparsefailure"
    ],
        "log" => {
        "file" => {
            "path" => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
        }
    },
    "message" => "            },\r",
      "event" => {
        "original" => "            },\r"
    }
}
{
       "tags" => [
        [0] "_jsonparsefailure"
    ],
        "log" => {
        "file" => {
            "path" => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
        }
    },
    "message" => "            {\r",
      "event" => {
        "original" => "            {\r"
    }
}
{
       "tags" => [
        [0] "_jsonparsefailure"
    ],
        "log" => {
        "file" => {
            "path" => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
        }
    },
    "message" => "                \"count\": 0,\r",
      "event" => {
        "original" => "                \"count\": 0,\r"
    }
}
{
       "tags" => [
        [0] "_jsonparsefailure"
    ],
        "log" => {
        "file" => {
            "path" => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
        }
    },
    "message" => "                \"filter\": \"ApplicationControlMalware\"\r",
      "event" => {
        "original" => "                \"filter\": \"ApplicationControlMalware\"\r"
    }
}
{
       "tags" => [
        [0] "_jsonparsefailure"
    ],
        "log" => {
        "file" => {
            "path" => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
        }
    },
    "message" => "            }\r",
      "event" => {
        "original" => "            }\r"
    }
}
{
       "tags" => [
        [0] "_jsonparsefailure"
    ],
        "log" => {
        "file" => {
            "path" => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
        }
    },
    "message" => "        ],\r",
      "event" => {
        "original" => "        ],\r"
    }
}
{
       "tags" => [
        [0] "_jsonparsefailure"
    ],
        "log" => {
        "file" => {
            "path" => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
        }
    },
    "message" => "        \"totalCount\": 83\r",
      "event" => {
        "original" => "        \"totalCount\": 83\r"
    }
}
{
       "tags" => [
        [0] "_jsonparsefailure"
    ],
        "log" => {
        "file" => {
            "path" => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
        }
    },
    "message" => "    },\r",
      "event" => {
        "original" => "    },\r"
    }
}
{
       "tags" => [
        [0] "_jsonparsefailure"
    ],
        "log" => {
        "file" => {
            "path" => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
        }
    },
    "message" => "    \"expectedResults\": 0,\r",
      "event" => {
        "original" => "    \"expectedResults\": 0,\r"
    }
}
{
       "tags" => [
        [0] "_jsonparsefailure"
    ],
        "log" => {
        "file" => {
            "path" => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
        }
    },
    "message" => "    \"failedServersInfo\": null,\r",
      "event" => {
        "original" => "    \"failedServersInfo\": null,\r"
    }
}
{
       "tags" => [
        [0] "_jsonparsefailure"
    ],
        "log" => {
        "file" => {
            "path" => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
        }
    },
    "message" => "    \"failures\": 0,\r",
      "event" => {
        "original" => "    \"failures\": 0,\r"
    }
}
{
       "tags" => [
        [0] "_jsonparsefailure"
    ],
        "log" => {
        "file" => {
            "path" => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
        }
    },
    "message" => "    \"hidePartialSuccess\": false,\r",
      "event" => {
        "original" => "    \"hidePartialSuccess\": false,\r"
    }
}
{
       "tags" => [
        [0] "_jsonparsefailure"
    ],
        "log" => {
        "file" => {
            "path" => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
        }
    },
    "message" => "    \"message\": \"\",\r",
      "event" => {
        "original" => "    \"message\": \"\",\r"
    }
}
{
       "tags" => [
        [0] "_jsonparsefailure"
    ],
        "log" => {
        "file" => {
            "path" => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
        }
    },
    "message" => "    \"status\": \"SUCCESS\"\r",
      "event" => {
        "original" => "    \"status\": \"SUCCESS\"\r"
  

  }
}

You will need a multiline codec to consume pretty-printed JSON. You can then use a ruby filter to flatten the [data][malwareCountFilters] event.

Hi Badger, thanks for replying.
So i included the multiline with pattern of "]," to exclude everything else not stuck at ruby part. The json field are coming as one line message, need to unpack or flatten it but this is the current output.

[2022-09-01T11:37:34,657][WARN ][logstash.filters.json    ][main][fe8d5d0b0b7aeaa49878c1d5ba7d2cb399c9de78ce95f3c1d3cb61d2370395a1] Error parsing json {:source=>"message", :raw=>"{\r\n    \"data\": {\r\n        \"malwareCountFilters\": [\r\n            {\r\n                \"count\": 0,\r\n                \"filter\": \"needsAttention\"\r\n            },\r\n            {\r\n                \"count\": 37,\r\n                \"filter\": \"KnownMalware\"\r\n            },\r\n            {\r\n                \"count\": 0,\r\n                \"filter\": \"UnknownMalware\"\r\n            },\r\n            {\r\n                \"count\": 46,\r\n                \"filter\": \"FilelessMalware\"\r\n            },\r\n            {\r\n                \"count\": 0,\r\n                \"filter\": \"ApplicationControlMalware\"\r\n            }\r\n        ],\r", :exception=>#<LogStash::Json::ParserError: Unexpected end-of-input within/between Object entries
 at [Source: (byte[])"{
    "data": {
        "malwareCountFilters": [
            {
                "count": 0,
                "filter": "needsAttention"
            },
            {
                "count": 37,
                "filter": "KnownMalware"
            },
            {
                "count": 0,
                "filter": "UnknownMalware"
            },
            {
                "count": 46,
                "filter": "FilelessMalware"
            },
            {
                "[truncated 94 bytes]; line: 25, column: 1]>}
[2022-09-01T11:37:34,857][ERROR][logstash.filters.ruby    ][main][8eea8eaeb6fd8cafd8b6fe7513e04db6bcc9f8161d8eaf137c0490b8e4e93a4a] Ruby exception occurred: undefined method `each' for nil:NilClass {:class=>"NoMethodError", :backtrace=>["(ruby filter code):3:in `block in filter_method'", "C:/ELK/logstash/vendor/bundle/jruby/2.5.0/gems/logstash-filter-ruby-3.1.8/lib/logstash/filters/ruby.rb:96:in `inline_script'", "C:/ELK/logstash/vendor/bundle/jruby/2.5.0/gems/logstash-filter-ruby-3.1.8/lib/logstash/filters/ruby.rb:89:in `filter'", "C:/ELK/logstash/logstash-core/lib/logstash/filters/base.rb:159:in `do_filter'", "C:/ELK/logstash/logstash-core/lib/logstash/filters/base.rb:178:in `block in multi_filter'", "org/jruby/RubyArray.java:1821:in `each'", "C:/ELK/logstash/logstash-core/lib/logstash/filters/base.rb:175:in `multi_filter'", "org/logstash/config/ir/compiler/AbstractFilterDelegatorExt.java:134:in `multi_filter'", "C:/ELK/logstash/logstash-core/lib/logstash/java_pipeline.rb:300:in `block in start_workers'"]}
{
          "host" => {
        "name" => "THIRU"
    },
       "message" => "{\r\n    \"data\": {\r\n        \"malwareCountFilters\": [\r\n            {\r\n                \"count\": 0,\r\n                \"filter\": \"needsAttention\"\r\n            },\r\n            {\r\n                \"count\": 37,\r\n                \"filter\": \"KnownMalware\"\r\n            },\r\n            {\r\n                \"count\": 0,\r\n                \"filter\": \"UnknownMalware\"\r\n            },\r\n            {\r\n                \"count\": 46,\r\n                \"filter\": \"FilelessMalware\"\r\n            },\r\n            {\r\n                \"count\": 0,\r\n                \"filter\": \"ApplicationControlMalware\"\r\n            }\r\n        ],\r",
      "@version" => "1",
    "@timestamp" => 2022-09-01T01:37:34.436392400Z,
          "tags" => [
        [0] "multiline",
        [1] "_jsonparsefailure",
        [2] "_rubyexception"
    ],
         "event" => {
        "original" => "{\r\n    \"data\": {\r\n        \"malwareCountFilters\": [\r\n            {\r\n                \"count\": 0,\r\n                \"filter\": \"needsAttention\"\r\n            },\r\n            {\r\n                \"count\": 37,\r\n                \"filter\": \"KnownMalware\"\r\n            },\r\n            {\r\n                \"count\": 0,\r\n                \"filter\": \"UnknownMalware\"\r\n            },\r\n            {\r\n                \"count\": 46,\r\n                \"filter\": \"FilelessMalware\"\r\n            },\r\n            {\r\n                \"count\": 0,\r\n                \"filter\": \"ApplicationControlMalware\"\r\n            }\r\n        ],\r"
    },
           "log" => {
        "file" => {
            "path" => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
        }
    }
}

Current logstash config:

input {
    file {
        path => "C:/ELK/logstash/Cyberreason/cybereasonoutput.json"
		start_position => "beginning"
	    sincedb_path => "NUL"
		codec => multiline { pattern => "]," negate => true what => "next" }
	    #everything that doesnot start/end with the pattern belong to the next line, so only thos inside the bracket remains.
    }
}

filter {
    json {
       source => "message"
    }
 	ruby {
	   code => '
	      event.get("[malwareCountFilters]").each { |a|
		  count = a["count"]
		  filter = a["filter"]
		  event.set( "[malwareCountFilters]#{count}", filter)
		  }
	   '
	}
}

output{
    stdout {}
}


Or perhaps another way by converting the file to ndjson

That will not work. Consider a file that contains

{
"anArray" : [ 
"foo"
]
}

If your multiline patterns matches "]" then your message will be

{ "anArray" : [ "foo" ]

which is not valid JSON. As Stephen says, perhaps it would be easier to convert pretty-printed JSON to line oriented outside of logstash.

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