Parsing a field to get multiple instances of a particular value

[Raghu_R]

Hi All,

I am working on Transaction Logs where I have a log field with the below data.

Below is an example of the data I have:
Transaction_Log :
{"message":"Some valid text", "Some valid text", "Some valid text"}
{"message":"Some valid text", "Some valid text", "Some valid text", "Some valid text"}
{"message":"Some valid text", "Some valid text", "Some valid text"}
{"message":"Printing data before Cart event-->(CartEvents__e:{CartId=A1234567, CartVolume=1034aef, CartCapacity=2000edc, CartExpiry=2nd Dec}), CartEvents__e:{CartId=A1234568, CartVolume=1045dkl, CartCapacity=2000lmn, CartExpiry=4rth Apr}), "levelNumber":"ER234", "logger":"ABCD"}
{"message":"Some valid text", "Some valid text", "Some valid text", "Some valid text", "Some valid text"}


In the above example, the CartEvents__e is a group of key value pairs where there are many fields apart from what I have given here. Also, there can multiple instances of CartEvents__e (two in my example data).
There can be as many as 10 instances of CartEvents__e. 

How can I write my query in such a way that the output looks as below:
(I have referred the documentation, but I am not able to figure this out correctly)
CartId                                CartVolume                           CartCapacity
A1234567                       1034aef                                  2000edc
A1234568                        1045dkl                                 2000lmn

I am not able to figure out how exactly this can be done.
Any help is greatly appreciated.

[to4kawa]

OK, check this please.

| makeresults 
| eval _raw="Transaction Log : {\"message\":\"Some valid text\", \"Some valid text\", \"Some valid text\"}
{\"message\":\"Some valid text\", \"Some valid text\", \"Some valid text\", \"Some valid text\"}
{\"message\":\"Some valid text\", \"Some valid text\", \"Some valid text\"}
{\"message\":\"Printing data before Cart event-->(CartEvents__e:{CartId=A1234567, CartVolume=1034aef, CartCapacity=2000edc, CartExpiry=2nd Dec}), CartEvents__e:{CartId=A1234568, CartVolume=1045dkl, CartCapacity=2000lmn, CartExpiry=4rth Apr}), \"levelNumber\":\"ER234\", \"logger\":\"ABCD\"}
{\"message\":\"Some valid text\", \"Some valid text\", \"Some valid text\", \"Some valid text\", \"Some valid text\"}" 
| rex max_match=0 "CartEvents__e:(?<jsons>.*?)\)" 
| stats count by jsons 
| rename jsons as _raw 
| kv

[Raghu_R]

@to4kawa  - I think I was not at all clear in framing my question.

Here's a better explanation:

1. There is a source table called Transaction_Log_Table which has various fields in it. 
2. One of the fields is Transaction_Log_Text which contains the messages as I have shown above which are populated from various backend sources. 
3. The number of messages in the Transaction_Log_Text is dynamic. The Transaction_Log_Text can have any number of messages.
4. Out of those many messages available, we have one particular message which will have the 'Printing data before cart event' prefix text.
5. After the prefix text, we can have any number of instances of CartEvents__e.
6. Each of these instances will have fields as shown in the post.
7. I have to extract these particular field values.

Let me know if you feel this has be explained better.

Thanks in advance!

[to4kawa]

Is there something wrong with my query?
It's able to extract the data, isn't it?

[Raghu_R]

@to4kawa  - Yes,  I wasn't getting proper data through the query.

Query:
--------
index=b2cforce sourcetype=sfdc:Transaction_Log__c
| rex field=Transaction_Log__c max_match=0 "CartEvents__e:(?<jsons>.*?)\)"
| stats count by jsons
| rename jsons as New_Field
| kv
| table New_Field

Above is the exact query I have used. 

More Sample Data:
------- ---------- ------
sourcetype=Transaction_Log_Table

Fields available in the events of the above source:
-------- ------------ -- ---- --------- -- ----- -------- ---------
1. Id
2. Warnings
3. Errors
4. Relation Id
5. Transaction_Log_Text

Data inside Transaction Log Text:
------ -------- ---------------- ----- ------

Sample 1:
"message":"Entering into log cart event method"
"message":"Printing data before Cart event--> (CartEvents__e:{CartId=A1234567, CartVolume=1034aef, CartCapacity=2000edc, CartExpiry=2nd Dec}), "levelNumber":"ER234", "logger":"ABCD"}
"message":"Exiting out of log cart event method"

Here, we have only one instance of CartEvents__e. Also, there is only one message before and after the CartEvents message.

Expected Output:
------------  ---------
Cart Id                             Cart Volume                         Cart Capacity
A1234567                     1034aef                                 2000edc

Sample 2:
"message":"Entering into fixed Cart event flow through AB_CD_Cartmethod","Logger-Utility"
"message":"Printing data after successful proposal"
"message":"Printing data before Cart event--> (CartEvents__e:{CartId=A1234567, CartVolume=1034aef, CartCapacity=2000edc, CartExpiry=2nd Dec}, CartEvents__e:{CartId=A1234568, CartVolume=1045dkl, CartCapacity=2000lmn, CartExpiry=4rth Apr}), "levelNumber":"ER234", "logger":"ABCD"}
"message":"Exiting out of log cart event method"
"message":"Exiting out of the main method","Exiting out of the main flow"

Here, there are two instances of the CartEvents__e. Also, there are three messages above the CartEvents__e message and two messages below.

Expected Output:
------------  ---------
Cart Id                             Cart Volume                         Cart Capacity
A1234567                     1034aef                                 2000edc
A1234568                      1045dkl                                2000lmn

Hopefully, this makes more sense.

[to4kawa]

please modify regex in rex.

[to4kawa]

The JSON format is so bad that I don't understand it. Is it really in this format?

[Raghu_R]

Apologies from my end. This is not JSON format. I have just found out that the type of the field is String by using the typeof function.