Problem with JSON file

[mblauw]

Hi all,

I've got some problems with by RegEx commands on a JSON file. I'm trying to do a linebreak on each },{ value and remove the header and footer. The last two seem to be working quite well. I can't, however, get te linebreak to work..

SEDCMD-removefooter = s/(\s*\],\"totalAc\”(.+[\r\n]*)+)//
SEDCMD-removeheader = s/^(\s*\{\s*+.+\"acList\":\[)//

Also, anybody knows good places to learn RegEx / SED?

{"src":1,"feeds":[{"id":1,"name":"From Consolidator","polarPlot":false}],"srcFeed":1,"showSil":true,"showFlg":true,"showPic":true,"flgH":20,"flgW":85,"acList":[{"Id":4735333,"Rcvr":1,"HasSig":false,"Icao":"484165","Bad":false,"Reg":"PH-BXM","FSeen":"\/Date(1489492025217)\/","TSecs":12,"CMsgs":3,"Alt":0,"GAlt":493,"InHg":30.4133873,"AltT":0,"Lat":52.306179,"Long":4.76435,"PosTime":1489492025217,"Mlat":false,"Tisb":false,"Spd":0.0,"TrkH":false,"Type":"B738","Mdl":"Boeing 737NG 8K2/W","Man":"Boeing","CNum":"30355","Op":"KLM Royal Dutch Airlines","OpIcao":"KLM","Sqk":"","VsiT":0,"Dst":0.33,"Brng":168.5,"WTC":2,"Species":1,"Engines":"2","EngType":3,"EngMount":0,"Mil":false,"Cou":"Netherlands","HasPic":false,"Interested":false,"FlightsCount":0,"Gnd":true,"SpdTyp":0,"CallSus":false,"Trt":2,"Year":"2000"},{"Id":4735513,"Rcvr":1,"HasSig":false,"Icao":"484219","Bad":false,"FSeen":"\/Date(1489492025217)\/","TSecs":12,"CMsgs":5,"Alt":0,"GAlt":493,"InHg":30.4133873,"AltT":0,"Call":"HVK1","Lat":52.318241,"Long":4.74571,"PosTime":1489492037420,"Mlat":false,"Tisb":false,"Spd":18.0,"Trak":267.0,"TrkH":false,"Sqk":"","VsiT":0,"Dst":1.58,"Brng":310.3,"WTC":0,"Species":0,"EngType":0,"EngMount":0,"Mil":false,"Cou":"Netherlands","HasPic":false,"Interested":false,"FlightsCount":0,"Gnd":true,"SpdTyp":0,"CallSus":false,"Trt":2},{"Id":4736693,"Rcvr":1,"HasSig":false,"Icao":"4846B5","Bad":false,"Reg":"","FSeen":"\/Date(1489491909202)\/","TSecs":128,"CMsgs":30,"Alt":0,"GAlt":493,"InHg":30.4133873,"AltT":0,"Call":"MQ","Lat":52.298538,"Long":4.75374,"PosTime":1489492037420,"Mlat":false,"Tisb":false,"Spd":0.0,"Trak":160.0,"TrkH":false,"Type":"-GND","Mdl":"Ground Vehicle","Man":"","Sqk":"","VsiT":0,"Dst":1.34,"Brng":209.3,"WTC":0,"Species":7,"EngType":0,"EngMount":0,"Mil":false,"Cou":"Netherlands","HasPic":false,"Interested":false,"FlightsCount":0,"Gnd":true,"SpdTyp":0,"CallSus":false,"Trt":2},{"Id":4739173,"Rcvr":1,"HasSig":true,"Sig":152,"Icao":"485065","Bad":false,"Reg":"PH-EZZ","FSeen":"\/Date(1489491894046)\/","TSecs":143,"CMsgs":104,"Alt":6600,"GAlt":7093,"InHg":30.4133873,"AltT":0,"Call":"KLM33N","Lat":52.320526,"Long":4.641017,"PosTime":1489492036076,"Mlat":true,"Tisb":false,"Spd":115.0,"Trak":26.6,"TrkH":false,"Type":"E190","Mdl":"Embraer EMB-190 STD","Man":"Embraer","CNum":"19000654","From":"EHAM Amsterdam Airport Schiphol, Netherlands","To":"EKBI Billund, Denmark","Op":"KLM Cityhopper","OpIcao":"KLC","Sqk":"0140","Help":false,"Vsi":-631,"VsiT":0,"Dst":8.42,"Brng":278.8,"WTC":2,"Species":1,"Engines":"2","EngType":3,"EngMount":0,"Mil":false,"Cou":"Netherlands","HasPic":false,"Interested":false,"FlightsCount":0,"Gnd":false,"SpdTyp":0,"CallSus":true,"Trt":2,"Year":"2013"},{"Id":4740238,"Rcvr":1,"HasSig":false,"Icao":"48548E","Bad":false,"Reg":"PH-EXL","FSeen":"\/Date(1489491890436)\/","TSecs":147,"CMsgs":13,"Alt":4750,"GAlt":5258,"InHg":30.4278164,"AltT":0,"TAlt":2016,"Call":"KLM1873","Lat":52.300861,"Long":4.759769,"PosTime":1489491890436,"Mlat":false,"PosStale":true,"Tisb":false,"Spd":23.0,"Trak":59.1,"TrkH":false,"Type":"E75S","Mdl":"ERJ-175STD (170-200)","Man":"Embraer","CNum":"17000633","From":"EHAM Amsterdam Airport Schiphol, Netherlands","To":"EDDS Stuttgart, Germany","Op":"KLM Cityhopper","OpIcao":"KLC","Sqk":"3432","Help":false,"Vsi":0,"VsiT":0,"Dst":0.95,"Brng":195.1,"WTC":0,"Species":0,"EngType":0,"EngMount":0,"Mil":false,"Cou":"Netherlands","HasPic":false,"Interested":false,"FlightsCount":0,"Gnd":true,"SpdTyp":0,"CallSus":false,"Trt":5,"Year":"2017"}

(....)

\/","TSecs":22318,"CMsgs":1407,"Alt":0,"GAlt":493,"InHg":30.4133873,"AltT":0,"Call":"C4","Lat":52.315102,"Long":4.76486,"PosTime":1489492034733,"Mlat":false,"Tisb":false,"Spd":32.0,"Trak":87.0,"TrkH":false,"Sqk":"","VsiT":0,"Dst":0.68,"Brng":8.5,"WTC":0,"Species":0,"EngType":0,"EngMount":0,"Mil":false,"Cou":"Netherlands","HasPic":false,"Interested":false,"FlightsCount":0,"Gnd":true,"SpdTyp":0,"CallSus":false,"Trt":2},{"Id":4735491,"Rcvr":1,"HasSig":false,"Icao":"484203","Bad":false,"Reg":"","FSeen":"\/Date(1489469002040)\/","TSecs":23035,"CMsgs":1850,"Alt":0,"GAlt":493,"InHg":30.4133873,"AltT":0,"Call":"KV1","Lat":52.322311,"Long":4.74203,"PosTime":1489492037404,"Mlat":false,"Tisb":false,"Spd":7.0,"Trak":298.0,"TrkH":false,"Type":"-GND","Mdl":"Ground Vehicle","Man":"","Sqk":"","VsiT":0,"Dst":2.07,"Brng":315.4,"WTC":0,"Species":7,"EngType":0,"EngMount":0,"Mil":false,"Cou":"Netherlands","HasPic":false,"Interested":false,"FlightsCount":0,"Gnd":true,"SpdTyp":0,"CallSus":false,"Trt":2}],"totalAc":4729,"lastDv":"636250573166210860","shtTrlSec":65,"stm":1489492037873}

[mblauw · Accepted solution]

I finally found a solution!

[json_flight_data]
SHOULD_LINEMERGE=false
NO_BINARY_CHECK=true
disabled=false
LINE_BREAKER=([.+,]+)(?=\{\"Id\")
SEDCMD-removeheader=s/^(\s*\{\s*+.+\"acList\":\[)//
SEDCMD-removefooter=s/(\s*\],\"totalAc\"(.+[\r\n]*)+)//
DATETIME_CONFIG=CURRENT
category=Structured
pulldown_type=true

[woodcock]

Was it the bad double-quote character?

[woodcock]

If this is really your exact text, then your problem is Windows: Take a VERY CLOSE look at all of your double-quote characters. One of them is invalid as far as Splunk is concerned. Fix that and see what happens. Test your RegEx @ http://www.RegEx101.com.

[mblauw]

It actually is a JSON reply from a REST API which is called every 5 seconds. When I parse my data through a JSON parser, I get a response from which I can extract multiple events with the following settings:

LINE_BREAKER=([\r\n]+)(?=\s*{\s*[\r\n]\s\"Id\")
SEDCMD-removeheader=s/^(\s*{\s*[\r\n]\"src\"(.+[\r\n])+)//
SEDCMD-removefooter=s/(\s*](.+[\r\n]*)+)//

[niketn]

@mblauw, can you please explain the reason for linebreak? Are you trying to parse/read JSON KV pairs?
If so, you can try spath command instead.

http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Spath#7:_Extract_and_expand_JSON_...

Also, as you have mentioned, if you are getting data file itself as json, Splunk should already do search time field extraction for you. Refer to KV_MODE settings for JSON data in props.conf.
http://docs.splunk.com/Documentation/Splunk/latest/Admin/Propsconf