CODEWITHSUNDEEP
joinmergeunionazure-log-analytics
H4p7ic
H4p7ic

Reputation: 1913

Log Analytics - Is it possible to merge rows into single row on same column id

Hi so what i'm trying to achieve is t merge the results of a union between two tables into single rows in Log Analytics.

I am creating a union between two tables with different operations names.

That is i'm trying to get the rows from a specific action in a run united/merged with with the rows of the correlated run.

this is what i want to achieve: enter image description here

Here is my query so far:

//Logic App Runs
AzureDiagnostics
| where OperationName in ( "Microsoft.Logic/workflows/workflowRunCompleted" )
| where workflowId_s == "/SUBSCRIPTIONS/[GUID PLACEHOLDER]/RESOURCEGROUPS/[NAME PLACEHOLDER]/PROVIDERS/MICROSOFT.LOGIC/WORKFLOWS/[NAME PLACEHOLDER]"
| where status_s == "Failed"
| extend ["Duration [sec]"]=(todouble(endTime_t - startTime_t) / 10000000)
| union (
    //Logic App Actions 
    AzureDiagnostics
    | where OperationName in ( "Microsoft.Logic/workflows/workflowActionCompleted" )
    | where workflowId_s == "/SUBSCRIPTIONS/[GUID PLACEHOLDER]/RESOURCEGROUPS/[NAME PLACEHOLDER]/PROVIDERS/MICROSOFT.LOGIC/WORKFLOWS/[NAME PLACEHOLDER]"
    | where resource_actionName_s == "Log_Exception"
    | where status_s == "Succeeded"  
    | project resource_runId_s , trackedProperties_PassId_g, trackedProperties_AccessCodes_s, trackedProperties_BookingNumber_s, trackedProperties_FlowId_g , trackedProperties_LicensePlates_s, trackedProperties_BlobUrl_s
)
| project LogicApp=resource_workflowName_s, Status=status_s, StartTime=startTime_t, EndTime=endTime_t, ["Duration [sec]"],  ResourceGroup=resource_resourceGroupName_s, RunId=resource_runId_s, PassId=trackedProperties_PassId_g, FlowId=trackedProperties_FlowId_g, AccessCodes=trackedProperties_AccessCodes_s, BookingNumber=trackedProperties_BookingNumber_s, LicensePlates=trackedProperties_LicensePlates_s, ExceptionInformation=trackedProperties_BlobUrl_s

Upvotes: 1

Views: 3114

Answers (1)

H4p7ic
H4p7ic

Reputation: 1913

I Found the solution, i think :P

I just changed the the combination technique from a union to a join and also shifted the order in which the join is made.

//Tallin_Failed
AzureDiagnostics
    | where OperationName in ( "Microsoft.Logic/workflows/workflowActionCompleted" )
    | where workflowId_s == "/SUBSCRIPTIONS/[GUID PLACEHOLDER]/RESOURCEGROUPS/[NAME PLACEHOLDER]/PROVIDERS/MICROSOFT.LOGIC/WORKFLOWS/[NAME PLACEHOLDER]"
    | where resource_actionName_s == "Log_Exception"
    | where status_s == "Succeeded"
| join kind=rightouter  (
    //Exception_Tallin
    AzureDiagnostics
    | where OperationName in ( "Microsoft.Logic/workflows/workflowRunCompleted" )
    | where workflowId_s == "/SUBSCRIPTIONS/[GUID PLACEHOLDER]/RESOURCEGROUPS/[NAME PLACEHOLDER]/PROVIDERS/MICROSOFT.LOGIC/WORKFLOWS/[NAME PLACEHOLDER]"
    | where status_s == "Failed"
    | extend ["Duration [sec]"]=(todouble(endTime_t - startTime_t) / 10000000)
    | project LogicApp=resource_workflowName_s, resource_runId_s, workflowRunStatus=status_s, StartTime=startTime_t, EndTime=endTime_t, ["Duration [sec]"],  ResourceGroup=resource_resourceGroupName_s
) on resource_runId_s 
| project LogicApp, Status=workflowRunStatus , StartTime, EndTime, ["Duration [sec]"],  ResourceGroup, RunId=resource_runId_s , PassId=trackedProperties_PassId_g, FlowId=trackedProperties_FlowId_g, AccessCodes=trackedProperties_AccessCodes_s, BookingNumber=trackedProperties_BookingNumber_s, LicensePlates=trackedProperties_LicensePlates_s, ExceptionInformation=trackedProperties_BlobUrl_s

I cant mark this as an answer yet (until 2 days from now) though... :P

Upvotes: 1

Related Questions