Pattern or Filter to extract embedded JSON and Description in logfile


#1

Hi there
We have an app that writes a log file stored in Oracle as a blob object ( I am already connected via logstash jdbc to extract latest log file data ) and I have stripped down to the best of my ability the message to this which is a small sample from the output from logstash to a file.

"8/4/2015 9:31:36 AM {SOURCE : \"CP123\", IMPORTEDDATETIME : 8/4/2015 9:32:09 AM, DESCRIPTION : \"123456-10\", ORDERQUANTITY : 1, ARTICLECODE : \"123456-1234567\", ORDERCODE : \"123456\", GROSSSALESVALUE : 10.99, EXPORTEDDATETIME : 8/4/2015 9:32:09 AM, NAME : \"123456-10\", CODE : \"123456-10\", ORDERID : 19654, EXTRACTEDDATETIME : 8/4/2015 9:32:09 AM, }. Error Row is missing a value due to missing parent. column ARTICLEID {SOURCE : \"CP123\", IMPORTEDDATETIME : 8/4/2015 9:32:09 AM, DESCRIPTION : \"123456-1\", ORDERQUANTITY : 1, ARTICLECODE : \"123456-1234567\", ORDERCODE : \"123456\", GROSSSALESVALUE : 18.33, EXPORTEDDATETIME : 8/4/2015 9:32:09 AM, NAME : \"123456-1\", CODE : \"123456-1\", ORDERID : 49118, EXTRACTEDDATETIME : 8/4/2015 9:32:09 AM, }. Error Row is missing a value due to missing parent. column ARTICLEPRICE Errors: 25847 Inserted: 234308 Updated: 6091 Skipped: 6114"

You can see there is a date field at the start that I do not need

8/4/2015 9:31:36 AM

and then the JSON type fields

{SOURCE : \"CP123\", IMPORTEDDATETIME : 8/4/2015 9:32:09 AM, DESCRIPTION : \"123456-10\", ORDERQUANTITY : 1, ARTICLECODE : \"123456-1234567\", ORDERCODE : \"123456\", GROSSSALESVALUE : 10.99, EXPORTEDDATETIME : 8/4/2015 9:32:09 AM, NAME : \"123456-10\", CODE : \"123456-10\", ORDERID : 19654, EXTRACTEDDATETIME : 8/4/2015 9:32:09 AM, }

and then a description for the error .

. Error Row is missing a value due to missing parent. column ARTICLEID

At the end there is a summary of Errors, Inserted, Updated, Skipped which I will also not need and can discard

Errors: 25847 Inserted: 234308 Updated: 6091 Skipped: 6114

Yes, it is one big file.
Can you provide ideas on removing the data I do not need and preserving the JSON fields , some of which I will later remove, and keeping the description message.

Thanks in advance
Kb


(system) #2