design data explorer query for azure data pipeline and synapse pipeline failure results

Question

I am using a log analytics query for the data factory and synapse pipeline failure results. I am implementing the following code separately for datafactory pipelines and synapse pipelines:

let pipelines = ADFPipelineRun   
| where Status == 'Failed'   
and Category == "PipelineRuns"  
and End > datetime("2022-06-23T14:27:28.85Z")  
and End <= datetime("2022-06-29T17:00:28.85Z");  
let activities = ADFActivityRun  
| where Category == "ActivityRuns"  
and  Status in ( "Failed", "TimedOut")  
and ErrorMessage !contains "Activity failed because an inner activity failed"   
and ErrorMessage !contains "Operation on target";  
pipelines  
| join kind = inner   
activities  
on $left.RunId == $right.PipelineRunId  
|project DataFactory=substring(tostring(split(ResourceId, "/", 8)), 2, strlen(tostring(split(ResourceId, "/", 8)))-4) , TimeGenerated, PipelineName, ActivityName, RunId, Hash=hash_sha256(strcat(PipelineName,Parameters)), Parameters, ErrorMessage = case(notempty(ErrorMessage),ErrorMessage,OperationName1), FailureType, Start , End , Status   
|distinct DataFactory , TimeGenerated, PipelineName, ActivityName, RunId, Hash, Parameters, ErrorMessage, FailureType, Start , End , Status   
|summarize arg_max(ErrorMessage, *) by Hash  
|order by TimeGenerated desc;  

And the following for synapse pipeline results:

let syspipelines = SynapseIntegrationPipelineRuns  
| where Status == 'Failed'   
and Category == "IntegrationPipelineRuns"  
and End > datetime("2022-06-30T11:20:28.5638646Z")  
and End <= datetime("2022-06-30T11:58:57.8878486Z");  
let sysactivities = SynapseIntegrationActivityRuns  
| where Category == "IntegrationActivityRuns"  
and OperationName !contains "Queued"  
and OperationName !contains "InProgress";  
syspipelines  
| join kind = inner   
sysactivities  
on $left.RunId == $right.PipelineRunId  
|project DataFactory=substring(tostring(split(_ResourceId, "/", 8)), 2, strlen(tostring(split(_ResourceId, "/", 8)))-4) , TimeGenerated, PipelineName, ActivityName, RunId, Hash=hash_sha256(strcat(PipelineName,Parameters)), Parameters, OperationName = case(notempty(OperationName),OperationName,OperationName1),  Start , End , Status   
|distinct DataFactory, TimeGenerated, PipelineName, ActivityName, RunId, Hash, OperationName, Start , End , Status   
|summarize arg_max(OperationName, *) by Hash  
|order by TimeGenerated desc   

Is there a possibility where in I can frame the above two queries and make a consolidated query for both synapse and azure data factory (instead of two different queries) to execute under the same log analytics

Answer 1

Hi @azuresubcrp ,

Thanks for posting query in Microsoft Q&A Platform.

We can consider creating view using let statement for both the queries and then use union operator to combine results of both.

Click here to know about let statement.
Click here to know about creating view.
Click here to know about union operator.

Hope this helps. Please let us know if any further queries.

-----------

Please consider hitting Accept Answer button. Accepted answers help community as well.