How to collect data and correlate into a table?

[ritesh14]

|>TYPE|2022-04-25 18:38:40|2d7e908bo82cb8|1725357403659|HERE|TYPE/272|1,856|1.2.0|ABC|351c481f2de|NONE<|
|>TYPE|2022-04-25 18:38:19|8e61ty7ebd2c25|1725357403659|THERE|TYPE/272||1.2.0|ABCD|4552aa7f9ebd704a91c8|{authType}|{ "message": { "number": "1856345" }, "transaction": { "sample1": "value1", "sample2": "value2" }}<|<|

I am looking for collecting data from both of above message and correlate between two.
I am looking for numbers 272 and 1,856 from HERE
and looking for sample1 and sample2 from THERE

both HERE and THERE will have 272 common and that is the only one.

build a table between those two with

sample1, sample2, 1,856

[ritesh14]

appologies for the delay. not able to login for few days. but above answer give me guidance and it worked now.

[richgalloway · Accepted solution]

Perhaps this will get you started.

| makeresults | eval data="|>TYPE|2022-04-25 18:38:40|2d7e908bo82cb8|1725357403659|HERE|TYPE/272|1,856|1.2.0|ABC|351c481f2de|NONE<|
|>TYPE|2022-04-25 18:38:19|8e61ty7ebd2c25|1725357403659|THERE|TYPE/272||1.2.0|ABCD|4552aa7f9ebd704a91c8|{authType}|{ \"message\": { \"number\": \"1856345\" }, \"transaction\": { \"sample1\": \"value1\", \"sample2\": \"value2\" }}<|<|" | eval data=split(data,"
") | mvexpand data | eval _raw=data | fields - data
```Lines above just set up test data.  Omit in production.```
```Extract needed fields```
| rex "(?:\|[^\|]+){4}\|(HERE|THERE)\|(?<TYPE>[^\|]+)\|(?<number>[\d,]*)(?:\|[^\|]+){4}\|(?<json>[^\<]*)"
```Join events of the same type```
| stats values(*) as * by TYPE
```Convert the json field to single-value```
| nomv json
```Extract sample1 and sample2 values from the json field.```
| rex field=json "sample1\\\": \\\"(?<sample1>[^\\\"]+)"
| rex field=json "sample2\\\": \\\"(?<sample2>[^\\\"]+)"
| table sample1 sample2 number