topic Extracting field from JSON and XML response in Splunk Search

Extracting field from JSON and XML response

dbautist — Mon, 28 Sep 2020 13:23:18 GMT

Depending on the content-type, the response that is logged is either in JSON or XML. I want to create a single query that would extract the REQUEST and DETAIL regardless of what the content-type is. I'm thinking I'd have to use spath, but I'm having a hard time grabbing the response between the "------------" and "<<<<<<<<<<<". I tried escaping it but no luck. Any help would be appreciated.

Note that these are 2 different log events.

2013-02-23 22:36:11,900 ID=[12345] >>>>>>>>>> > HTTP STATUS (400) POST https://myapi/test > content-type: application/xml > accept: application/xml > host: test.com > content-length: 249 > connection: Keep-Alive > <?xml version="1.0" encoding="utf-8"?>johndoe12345 ++++++++++ > Content-Type: application/xml > Cache-Control: no-cache, no-transform > = <?xml version="1.0" encoding="UTF-8"?> 400 myapi INVALID_LASTNAME TEST EXPECTED: LENGTH TOO SHORT ------------ <?xml version="1.0" encoding="UTF-8"?> 400 myapi INVALID_LASTNAME TEST EXPECTED: LENGTH TOO SHORT <<<<<<<<<<<

2013-02-23 22:36:11,900 ID=[12345] >>>>>>>>>> > HTTP STATUS (400) POST https://myapi/test > content-type: application/json; charset=utf-8 > accept: application/json > host: test.com > content-length: 74 > Expect: 100-continue > connection: Keep-Alive > {"firstname":"john","lastname":"doe","id":"12345"} ++++++++++ > Content-Type: application/json > Cache-Control: no-cache, no-transform > = { "status" : 400, "source" : "myapi", "code" : "INVALID_LASTNAME", "detail" : [ { "type" : "TEST", "annotation" : "EXPECTED: LENGTH TOO SHORT" } ] } ------------ { "status" : 400, "source" : "myapi", "code" : "INVALID_LASTNAME", "detail" : [ { "type" : "TEST", "annotation" : "EXPECTED: LENGTH TOO SHORT" } ] } <<<<<<<<<<<

Re: Extracting field from JSON and XML response

martin_mueller — Mon, 25 Feb 2013 20:56:40 GMT

I was bored...

| gentimes start=-1 increment=5m | head 2 | eval tmp = if(starttime%600==0,"2013-02-23 22:36:11,900 ID=[12345] >>>>>>>>>> > HTTP STATUS (400) POST https://myapi/test > content-type: application/xml > accept: application/xml > host: test.com > content-length: 249 > connection: Keep-Alive > <?xml version=\"1.0\" encoding=\"utf-8\"?><request><firstname>john</firstname><lastname>doe</lastname><id>12345</id></request> ++++++++++ > Content-Type: application/xml > Cache-Control: no-cache, no-transform > = <?xml version=\"1.0\" encoding=\"UTF-8\"?> <error> <status>400</status> <source>myapi</source> <code>INVALID_LASTNAME</code> <detail> <type>TEST</type> <annotation>EXPECTED: LENGTH TOO SHORT</annotation> </detail> </error> ------------ <?xml version=\"1.0\" encoding=\"UTF-8\"?> <error> <status>400</status> <source>myapi</source> <code>INVALID_LASTNAME</code> <detail> <type>TEST</type> <annotation>EXPECTED: LENGTH TOO SHORT</annotation> </detail> </error> <<<<<<<<<<<","2013-02-23 22:36:11,900 ID=[12345] >>>>>>>>>> > HTTP STATUS (400) POST https://myapi/test > content-type: application/json; charset=utf-8 > accept: application/json > host: test.com > content-length: 74 > Expect: 100-continue > connection: Keep-Alive > {\"firstname\":\"john\",\"lastname\":\"doe\",\"id\":\"12345\"} ++++++++++ > Content-Type: application/json > Cache-Control: no-cache, no-transform > = { \"status\" : 400, \"source\" : \"myapi\", \"code\" : \"INVALID_LASTNAME\", \"detail\" : [ { \"type\" : \"TEST\", \"annotation\" : \"EXPECTED: LENGTH TOO SHORT\" } ] } ------------ { \"status\" : 400, \"source\" : \"myapi\", \"code\" : \"INVALID_LASTNAME\", \"detail\" : [ { \"type\" : \"TEST\", \"annotation\" : \"EXPECTED: LENGTH TOO SHORT\" } ] } <<<<<<<<<<<") | rex field=tmp "(?<req_xml>\<\?xml .+?)\s+\+{8}.*?-{8,}\s+(?<resp_xml>\<\?xml .*?)\s+\<{8,}" | rex field=tmp "\>\s+(?<req_json>\{.*?)\s+\+{8,}.*?-{8,}\s+(?<resp_json>\{.*?)\s+\<{8,}" | spath input=req_xml output=firstname path=request.firstname | spath input=req_xml output=lastname path=request.lastname | spath input=req_xml output=id path=request.id | spath input=req_json output=firstname path=firstname | spath input=req_json output=lastname path=lastname | spath input=req_json output=id path=id | spath input=resp_xml output=detail_type path=error.detail.type | spath input=resp_xml output=detail_annotation path=error.detail.annotation | spath input=resp_json output=detail_type path=detail{}.type | spath input=resp_json output=detail_annotation path=detail{}.annotation | fields - *xml *json

The juicy bits are in the bottom nine or so lines.

Re: Extracting field from JSON and XML response

dbautist — Tue, 26 Feb 2013 00:00:08 GMT

Thanks martin. The two logs above are actually 2 different events.

Re: Extracting field from JSON and XML response

martin_mueller — Tue, 26 Feb 2013 07:01:15 GMT

I am aware of that - if you run the above query you'll see two events treated equally.