topic Extract fields from JSON array in Splunk Search

Extract fields from JSON array

rups260386 — Sat, 15 Jun 2019 17:12:02 GMT

We are moving log from AWS infrastructure to Splunk index via SQS service, but somehow JSON formatted logs breaking when it transferred to splunk index. Below are example of events, where tags field getting all contents as values instead of Key & value format i.e Key: Value.

Example of event1

{ [-] 
   @timestamp: 2019-06-15T16:41:49.303Z 
   duration: 897499 
   host: 10.216.8.0 
   id: f1d12e8909e8157a 
   kind: CLIENT 
   localEndpoint: { [+] 
   } 
   name: apix:dxl 
   parentId: 4ea4be35f8e2f4ba 
   tags: [ [-] 
     [ [-] 
       http.uri 
       /subscriptions/msisdn/{id-value}/eligibility 
     ] 
     [ [-] 
       X-VF-Trace-TransactionId 
       24965de5-b649-4218-9036-49c87f826f97 
     ] 
     [ [-] 
       http.method 
       GET 
     ] 
   ] 
   timestamp: 1560616907952081 
   topic: preprd_onenumber_portal 
   traceId: 5d051fcbc2c1b3744ea4be35f8e2f4ba 
}

it expected to come as Key and Value format i.e

tags: [ [-] 
      [ [-] 
        http.uri:  /subscriptions/msisdn/{id-value}/eligibility 
      ] 
      [ [-] 
        X-VF-Trace-TransactionId: 24965de5-b649-4218-9036-49c87f826f97 
      ] 
      [ [-] 
        http.method: GET 
      ] 
    ]

This issue we can't able to fix it on splunk administration level because we are not owning the splunk infrastructure, hence we need to fix using splunk query or splunk regex.

Also tags field is JSON array having multiple values not just http.uri,X-VF-Trace-TransactionId & http.method which seeing in example event1 . if you see below example event2, you will able to see few more value added in tags field apart from event1 example. It is totally depending event types.

Example of Event2

{ [-] 
   @timestamp: 2019-06-15T16:47:43.399Z 
   duration: 829826 
   host: 10.216.8.0 
   id: 3ec15beb5ab8cd3e 
   kind: CLIENT 
   localEndpoint: { [+] 
   } 
   name: apix:dxl 
   parentId: 06da0f555402d791 
   tags: [ [-] 
     [ [-] 
       error.description 
       Exception while retrieving data from APIX 
     ] 
     [ [-] 
       error 
       not_found 
     ] 
     [ [-] 
       X-VF-Trace-TransactionId 
       6de3d50c-083e-4565-8f2c-4cf417ff6d21 
     ] 
     [ [-] 
       error.message 
       Error when retrieving data: ApixServiceException 
     ] 
     [ [-] 
       http.method 
       GET 
     ] 
     [ [-] 
       http.uri 
       /subscriptions/msisdn/{id-value} 
     ] 
   ] 
   timestamp: 1560617261722840 
   topic: preprd_onenumber_portal 
   traceId: 5d05212dc4b910b006da0f555402d791 
}

To fix this we need to either convert all contents inside tags field as Key and value format i.e Key: Value OR need to extract content from tags field into different fields.

how can I write a regular expression to convert all contents inside tags field as Key and value format i.e Key: Value OR how to extract all content from tags field in separate fields

Thanks for the answers!

Re: Extract fields from JSON array

VatsalJagani — Sun, 16 Jun 2019 15:35:49 GMT

Hello @rups260386,
If you could provide one event's _raw text, that would be great.

Re: Extract fields from JSON array

rups260386 — Sun, 16 Jun 2019 15:49:36 GMT

Hello @VatsalJagani

Thank you for responding. please find example of raw event

{"timestamp":1560699735030660,"duration":1104711,"topic":"preprd_onenumber_portal","parentId":"478a2114fbc67b0f","localEndpoint":{"serviceName":"mdp","ipv4":"172.17.0.2"},"traceId":"5d066357b4dbb643478a2114fbc67b0f","tags":[["X-VF-Trace-TransactionId","3686e1ae-42f3-4a74-8c83-57ab4f58bccf"],["http.method","GET"],["http.uri","/subscriptions/msisdn/{id-value}/eligibility"]],"@timestamp":"2019-06-16T15:42:16.195Z","kind":"CLIENT","name":"apix:dxl","id":"5a5049617f89eed7","host":"10.216.8.6"} 
{"timestamp":1560699735030077,"duration":1100676,"topic":"preprd_onenumber_portal","parentId":"478a2114fbc67b0f","localEndpoint":{"serviceName":"mdp","ipv4":"172.17.0.2"},"traceId":"5d066357b4dbb643478a2114fbc67b0f","tags":[["http.path","/v1/multiDevice/subscriptions/msisdn/447741962817/eligibility"],["http.method","GET"]],"@timestamp":"2019-06-16T15:42:16.195Z","kind":"CLIENT","name":"get","id":"979af41494def56d","host":"10.216.8.6"} 
{"timestamp":1560699733690031,"duration":1057504,"topic":"preprd_onenumber_portal","localEndpoint":{"serviceName":"mdp","ipv4":"172.17.0.2"},"traceId":"5d066355d08bed483d83748158d6a180","tags":[["http.path","/product"],["mvc.controller.method","searchProducts"],["mvc.controller.class","ProductController"],["method","searchProducts"],["principal","f6d82074-b7f2-4197-816c-46fd5a311846"],["class","ProductController"],["msisdn","447741962817"],["http.method","GET"]],"@timestamp":"2019-06-16T15:42:15.487Z","kind":"SERVER","name":"get /product","id":"3d83748158d6a180","remoteEndpoint":{"ipv4":"47.73.7.94"},"host":"10.216.8.1"}

Re: Extract fields from JSON array

VatsalJagani — Sun, 16 Jun 2019 16:21:04 GMT

@rups260386,

Please try this query after searching your events:

| eval tag_s = substr(tags, 3, len(tags)-4) | makemv tag_s delim="],[" | mvexpand tag_s | eval tag_s = substr(tag_s, 2, len(tag_s)-2) | makemv tag_s delim="\",\"" | eval key=mvindex(tag_s,0) | eval value=mvindex(tag_s,1) | eval {key}=value | fields - tag_s, key, value

I've used mvexpand so one event might converted to muliple, you can group them again by some fields with stats command, if you want.

Hope this helps!!!

Re: Extract fields from JSON array

MuS — Sun, 16 Jun 2019 20:15:30 GMT

Hi rups260386,

You can use props.conf and transforms.conf on your search head to get the key value pairs after tags, try something like this:

props.conf

[SourceTypeNameHere]
REPORT-myUniqueClassName = myTransformToGetKVAfterTags

transforms.conf

[myTransformToGetKVAfterTags]
REGEX = (?:tags\":\[)?(?:\[\"([^"]+)\",\"([^"]+)\"\][,\]])+?
FORMAT = $1::$2

Tested on regex101.com and the captured groups look like this:

Hope this helps ...

cheers, MuS