Joining two files with derived field

[tva000]

Greetings,

I am very new to Splunk and I am sure my question may have been asked multiple times.  I went through multiple articles but unable to get the answers.  It may be very simple for experts.

I have two files and need to frame a query to join the log file with another log file, which are both *.json files

File1.json

"lvl": "DEBUG"
"msg": "JobID 123456789012345678901234567890123456789012345678901234567890 completed with state: Failed"
"ts": "2021-09-07T16:50:21.901Z"

File2.json

"JobName":"Lambda Handler"
"Ruuid": "123456789012345678901234567890123456789012345678901234567890"

My requirement is to parse File1.json and extract the JobID number alone, in this case 1234....0 and join this derived field with Ruuid in File2.json to form a end result like this

JobName,JobID,msg
Lambda Handler,123456789012345678901234567890123456789012345678901234567890,JobID 123456789012345678901234567890123456789012345678901234567890 completed with state: Failed

I used substring to extract the JobID from File1 with this, but I am not sure how to use this derived field "Ruuid" to join with file 2 Ruuid

index=* | source="File1.json" msg = *"completed with state:" | table msg | eval Ruuid = substr(msg,6,62) |

Any inputs would be really helpful to me.

Thanks.

[PickleRick]

You should get the events into one stream (a result of a search) and then perform a stats command.

| stats values(JobName) values(msg) by Ruuid

Since for every Ruuid you have only one msg and only one JobName you won't have to worry about multivalued fields in the results.

The trick is to get the data stream prepared. One solution (slightly more obvious to understand) is to append one search to another

index=whatever source=file1.json msg=* | eval Ruuid=substr(msg,6,62) | append [ search index=anything source=file2.json Ruuid=*]

Another way is to select all the events at once and selectively "fix" some of them

(index=something source=file1.json msg=*) OR (index=somewhere source=file2.json Ruuid=*) | eval Ruuid=if(isnull(Ruuid),substr(msg,6,62),Ruuid)