topic Re: JSON Fields Extraction using REX in Splunk Search

JSON Fields Extraction using REX

rczone — Thu, 26 Aug 2021 21:46:48 GMT

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"}]}

Re: JSON Fields Extraction using REX

ITWhisperer — Thu, 26 Aug 2021 22:57:50 GMT

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?

Re: JSON Fields Extraction using REX

rczone — Fri, 27 Aug 2021 02:03:58 GMT

@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"}]}

Re: JSON Fields Extraction using REX

ITWhisperer — Fri, 27 Aug 2021 07:00:23 GMT

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?

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

Re: JSON Fields Extraction using REX

PickleRick — Fri, 27 Aug 2021 07:04:31 GMT

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