Getting syntax error for JOIN applied between two metrics

Question

Writing first MQL query to achieve below:

1. reads log from a metric "logging.googleapis.com/user/count_verify_country" which has a field COUNTRY
2. reads log from another metric "logging.googleapis.com/user/verify_completed" which has a field MESSAGE
3. Now, count all occurences with MESSAGE as "VERIFY COMPLETED" for all different COUNTRY

Note: both logs have a common field flowExecutionId whose value remains same for each transaction.

fetch logging.googleapis.com/user/count_verify_country
| filter has(jsonPayload.requestBody.actors.client.verify.country)
| group_by [jsonPayload.requestBody.actors.client.daVinciFlow.flowExecutionId], [country: any(jsonPayload.requestBody.actors.client.verify.country)]
| join (
 fetch logging.googleapis.com/user/verify_completed
 | filter jsonPayload.requestBody.success.message == "Verify Completed"
 | group_by [jsonPayload.requestBody.actors.client.daVinciFlow.flowExecutionId], [success_count: count_true()]
) on jsonPayload.requestBody.actors.client.daVinciFlow.flowExecutionId
| outer_join
| group_by [country], [total_success: sum(success_count)]```
Getting error: Line 5: Expected: ')'. Instead found: 'logging'.

Answer 1

You mentioned you want to count all occurrences of MESSAGE as SUCCESS. But you are giving "Verify Completed" when you are filtering the verify_completed logs. So try using the SUCCESS in place of Verify Completed at the line filter jsonPayload.requestBody.success.message == "SUCCESS" and outer_join is not required.

fetch logging.googleapis.com/user/count_verify_country
| filter has(jsonPayload.requestBody.actors.client.verify.country)
| group_by [jsonPayload.requestBody.actors.client.daVinciFlow.flowExecutionId], [country: any(jsonPayload.requestBody.actors.client.verify.country)]
| join (
 fetch logging.googleapis.com/user/verify_completed
 | filter jsonPayload.requestBody.success.message == "SUCCESS"
 | group_by [jsonPayload.requestBody.actors.client.daVinciFlow.flowExecutionId], [success_count: count()]
) on jsonPayload.requestBody.actors.client.daVinciFlow.flowExecutionId
| group_by [country], [total_success: sum(success_count)]

Refer to this official MQL example document for more information.

• user9931563: Hey, thanks for your response. My bad, was trying to break the task into steps and wrote "SUCCESS" instead of "VERIFY COMPLETED" as latter appears in the logs. I tried removing outer join, but still getting same error for the second fetch statement. Line 6: Expected: ')'. Instead found: 'logging_bucket'
• Dharani Dhar Golladasari: Can you update the question with latest error and the changes you done to the query?
• user9931563: Unable to add whole query under the comment, but its same as above, only removed outer_join as you suggested. And got the error as mentioned in previous comment Line 5: Expected: ')'. Instead found: 'logging'.
• Dharani Dhar Golladasari: Can you try by giving count() instead of count_true()
• user9931563: Doesn't make any sense. The error is next to the join statement, somehow it is expecting braces to be closed. It is syntax issue, not variable definition