Splunk Search

Joining two files with derived field

tva000
Observer

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.

Labels (1)
0 Karma

PickleRick
SplunkTrust
SplunkTrust

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)
0 Karma
Career Survey
First 500 qualified respondents will receive a $20 gift card! Tell us about your professional Splunk journey.

Can’t make it to .conf25? Join us online!

Get Updates on the Splunk Community!

Community Content Calendar, September edition

Welcome to another insightful post from our Community Content Calendar! We're thrilled to continue bringing ...

Splunkbase Unveils New App Listing Management Public Preview

Splunkbase Unveils New App Listing Management Public PreviewWe're thrilled to announce the public preview of ...

Leveraging Automated Threat Analysis Across the Splunk Ecosystem

Are you leveraging automation to its fullest potential in your threat detection strategy?Our upcoming Security ...