Splunk Dev

Problem with JSON file

mblauw
Path Finder

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}
Tags (1)
0 Karma
1 Solution

mblauw
Path Finder

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

View solution in original post

0 Karma

mblauw
Path Finder

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
0 Karma

woodcock
Esteemed Legend

Was it the bad double-quote character?

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

mblauw
Path Finder

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]*)+)//

0 Karma

niketn
Legend

@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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...