Splunk Search

JSON Fields Extraction using REX

rczone
Path Finder

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

 

 

 

 

 

 

 

Labels (3)
0 Karma

PickleRick
Motivator

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

0 Karma

ITWhisperer
Legend

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

ITWhisperer_0-1630018640369.png

Which version of splunk are you using?

0 Karma

rczone
Path Finder

 

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

 

 

 

 

 

 

 

 

 

0 Karma

ITWhisperer
Legend

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

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!