Join two Splunk queries without predefined fields

Question

I am trying to join 2 splunk queries. However in this case the common string between the 2 queries is not a predefined splunk field and is logged in a different manner. I have created the regex which individually identifies the string but when I try to combine using join, I do not get the result.

I have logs like this -

Logline 1 -

21-04-2019 11:01:02.001 server1 app1    1023456789 1205265352567565 1234567Z-1234-1234-1234-123456789123    Application Completed

Logline 2 -

21-04-2019 11:00:00.000 journey_ends server1    app1 1035625855585989 .....(lots of text) commonID:1234567Z-1234-1234-1234-123456789123 .....(lots of text) status(value) OK

the second Logline can be NOTOK as well

Logline 2 -

21-04-2019 11:00:00.000 journey_ends server1    app1 1035625855585989 .....(lots of text) commonID:1234567Z-1234-1234-1234-123456789123 .....(lots of text) status(value) NOTOK

I have tried multiple things but the best that I can come up with is -

index=test "journey_ends" | rex "status(value) (?<StatusType>[A-Z][A-Z]*)" | rex "commonID\:(?<commonID>[^\t]{37})" | table StatusType, commonID | join type=inner commonID [ search index=test "Application Completed" | rex "^(?:[^\t\n]*\t){7}(?P<commonID>[^\t]+)" | table _time, commonID] | chart count over StatusType by commonID

However the above query does not provide me the stats. In verbose mode, I can just see the events of query 1. Please note that the above 2 queries run correctly individually.

However currently I have to initially run the query to fetch the commonIDs from "Application Completed" logline and then in another query give the list of commonIDs found in the result first query as input and find the status value for each commonId from logline 2.

Expected Result (in a table):

StatusType commonID OK  1234567Z-1234-1234-1234-123456789123 NOTOK  1234567Z-1234-1234-1234-985625623541

Siddhartha Siddhartha · Accepted Answer · 2019-04-21T12:04:43

Can you try the below query,

index=main
AND "Application Completed" 
| rex "(?<common_id>[[:alnum:]]+-[[:alnum:]]+-[[:alnum:]]+-[[:alnum:]]+-[[:alnum:]]+)" 
| table _time, common_id
| join type=inner common_id [ 
    search index=main 
        | rex "status\(value\)\s+(?<status>.+)$" 
        | rex "(?<common_id>[[:alnum:]]+-[[:alnum:]]+-[[:alnum:]]+-[[:alnum:]]+-[[:alnum:]]+)" 
        | table status, common_id
    ]

Join two Splunk queries without predefined fields

1 Answers