JSON Fields Extraction using REX

[rczone]

Hello,

I have a requirement where i need to extract part of JSON code from splunk log and assign that field to spath for further results

My regex is working in regex101 but not in splunk 

below is log snippet --looking to grab the JSON code starting from {"unique_appcodes to end of line..i have shown the expected output below in the post

 cwmessage: 2021-08-26 17:14:10 araeapp INFO MRC: Unique AppCodes Report requested.
2021-08-26 17:14:10 araeapp INFO MRC_ARAE_I_042: (local) requesting uniq_appcodes report for KKA
2021-08-26 17:14:10 araeapp INFO {"unique_appcodes": [{"count": 2, "app_code": "XYZ", "group": "", "instance": "KKA"}, {"count": 2, "app_code": "QQQ", "group": "TSR05441", "instance": "KKA"}, {"count": 1, "app_code": "QQQ", "group": "", "instance": "KKA"}, {"count": 192, "app_code": "PPP", "group": "TSR05560", "instance": "KKA"}, {"count": 12, "app_code": "PPP", "group": "", "instance": "KKA"}, {"count": 12, "app_code": "GM9", "group": "TSR06083", "instance": "KKA"}, {"count": 139, "app_code": "ZZZ", "group": "TSR06103", "instance": "KKA"}, {"count": 6, "app_code": "GNA", "group": "TSR06085", "instance": "KKA"}, {"count": 803, "app_code": "SSS", "group": "MXXX0718", "instance": "KKA"}, {"count": 3, "app_code": "SSS", "group": "", "instance": "KKA"}]}

Rex using:  

| rex field=_raw (?msi)(?<json_field>\{\"unique_appcodes\".+\}$)

and this perfectly working in regex101.com which is extracting  the below required part but when i use this in SPlunk its not giving any results im thinking its the spaces between the JSON attributes

Please let me know your thoughts 

{"unique_appcodes": [{"count": 2, "app_code": "XYZ", "group": "", "instance": "KKA"}, {"count": 2, "app_code": "QQQ", "group": "TSR05441", "instance": "KKA"}, {"count": 1, "app_code": "QQQ", "group": "", "instance": "KKA"}, {"count": 192, "app_code": "PPP", "group": "TSR05560", "instance": "KKA"}, {"count": 12, "app_code": "PPP", "group": "", "instance": "KKA"}, {"count": 12, "app_code": "GM9", "group": "TSR06083", "instance": "KKA"}, {"count": 139, "app_code": "ZZZ", "group": "TSR06103", "instance": "KKA"}, {"count": 6, "app_code": "GNA", "group": "TSR06085", "instance": "KKA"}, {"count": 803, "app_code": "SSS", "group": "MXXX0718", "instance": "KKA"}, {"count": 3, "app_code": "SSS", "group": "", "instance": "KKA"}]}

[PickleRick]

Why don't you just extract the whole json and use spath?

For example

| rex field=_raw "(?<json>{.*})" 
| spath input=json path="unique_appcodes{}." output=some_field

Of course if you need to process each entry from unique_appcodes separately further down the stream you'd need to mvexpand

[ITWhisperer]

It works with makeresults

| makeresults 
| eval _raw=" cwmessage: 2021-08-26 17:14:10 araeapp INFO MRC: Unique AppCodes Report requested.
2021-08-26 17:14:10 araeapp INFO MRC_ARAE_I_042: (local) requesting uniq_appcodes report for KKA
2021-08-26 17:14:10 araeapp INFO {\"unique_appcodes\": [{\"count\": 2, \"app_code\": \"XYZ\", \"group\": \"\", \"instance\": \"KKA\"}, {\"count\": 2, \"app_code\": \"QQQ\", \"group\": \"TSR05441\", \"instance\": \"KKA\"}, {\"count\": 1, \"app_code\": \"QQQ\", \"group\": \"\", \"instance\": \"KKA\"}, {\"count\": 192, \"app_code\": \"PPP\", \"group\": \"TSR05560\", \"instance\": \"KKA\"}, {\"count\": 12, \"app_code\": \"PPP\", \"group\": \"\", \"instance\": \"KKA\"}, {\"count\": 12, \"app_code\": \"GM9\", \"group\": \"TSR06083\", \"instance\": \"KKA\"}, {\"count\": 139, \"app_code\": \"ZZZ\", \"group\": \"TSR06103\", \"instance\": \"KKA\"}, {\"count\": 6, \"app_code\": \"GNA\", \"group\": \"TSR06085\", \"instance\": \"KKA\"}, {\"count\": 803, \"app_code\": \"SSS\", \"group\": \"MXXX0718\", \"instance\": \"KKA\"}, {\"count\": 3, \"app_code\": \"SSS\", \"group\": \"\", \"instance\": \"KKA\"}]}"
| rex field=_raw (?msi)(?<json_field>\{\"unique_appcodes\".+\}$)
| spath input=json_field

Which version of splunk are you using?

[rczone]

@ITWhisperer  Appreciate the response yes the solution is exactly im looking at...but the field values changes every time in the log so i cant hardcode them

so i have to use either field name for rex  or _raw to get the values of  "unique_appcodes" 
again im using 

index=bwwm splunk_server_group=AWS sourcetype="app.log"  | rex field=_raw (?msi)(?<json_field>\{\"unique_appcodes\"[\s\S]+\}$)

So how can modify to use to |makeresults

Please suggest

Sample logsnippet--we will have different attributes each time so cant hardcode

appcode: ABC
   aws_acctid: 123456789
   aws_appshortname: beem
   aws_region: us-east-1b
   cwmessage: 2021-08-26 17:14:10 araeapp INFO MRC: Unique AppCodes Report requested.
2021-08-26 17:14:10 araeapp INFO MRC_ARAE_I_042: (local) requesting uniq_appcodes report for KKA
2021-08-26 17:14:10 araeapp INFO {"unique_appcodes": [{"count": 2, "app_code": "XYZ", "group": "", "instance": "KKA"}, {"count": 2, "app_code": "QQQ", "group": "TSR05441", "instance": "KKA"}, {"count": 1, "app_code": "QQQ", "group": "", "instance": "KKA"}, {"count": 192, "app_code": "PPP", "group": "TSR05560", "instance": "KKA"}, {"count": 12, "app_code": "PPP", "group": "", "instance": "KKA"}, {"count": 12, "app_code": "GM9", "group": "TSR06083", "instance": "KKA"}, {"count": 139, "app_code": "ZZZ", "group": "TSR06103", "instance": "KKA"}, {"count": 6, "app_code": "GNA", "group": "TSR06085", "instance": "KKA"}, {"count": 803, "app_code": "SSS", "group": "MXXX0718", "instance": "KKA"}, {"count": 3, "app_code": "SSS", "group": "", "instance": "KKA"}]}

[ITWhisperer]

I am not sure what you are trying to say here - are you wanting to extract each element of unique_appcodes separately so you can  use the values individually?

| makeresults 
| eval _raw=" cwmessage: 2021-08-26 17:14:10 araeapp INFO MRC: Unique AppCodes Report requested.
2021-08-26 17:14:10 araeapp INFO MRC_ARAE_I_042: (local) requesting uniq_appcodes report for KKA
2021-08-26 17:14:10 araeapp INFO {\"unique_appcodes\": [{\"count\": 2, \"app_code\": \"XYZ\", \"group\": \"\", \"instance\": \"KKA\"}, {\"count\": 2, \"app_code\": \"QQQ\", \"group\": \"TSR05441\", \"instance\": \"KKA\"}, {\"count\": 1, \"app_code\": \"QQQ\", \"group\": \"\", \"instance\": \"KKA\"}, {\"count\": 192, \"app_code\": \"PPP\", \"group\": \"TSR05560\", \"instance\": \"KKA\"}, {\"count\": 12, \"app_code\": \"PPP\", \"group\": \"\", \"instance\": \"KKA\"}, {\"count\": 12, \"app_code\": \"GM9\", \"group\": \"TSR06083\", \"instance\": \"KKA\"}, {\"count\": 139, \"app_code\": \"ZZZ\", \"group\": \"TSR06103\", \"instance\": \"KKA\"}, {\"count\": 6, \"app_code\": \"GNA\", \"group\": \"TSR06085\", \"instance\": \"KKA\"}, {\"count\": 803, \"app_code\": \"SSS\", \"group\": \"MXXX0718\", \"instance\": \"KKA\"}, {\"count\": 3, \"app_code\": \"SSS\", \"group\": \"\", \"instance\": \"KKA\"}]}"
| rex field=_raw (?msi)(?<json_field>\{\"unique_appcodes\".+\}$)
| spath input=json_field path=unique_appcodes{} output=unique_appcodes
| mvexpand unique_appcodes
| table unique_appcodes
| spath input=unique_appcodes
| fields - unique_appcodes

If not, perhaps you can share some more events showing the differences (which you can't hard code!) and some examples of expected output