johnwrite johnwrite - 4 months ago 32
MySQL Question

Azure Application Insights Analytics Query on Joining

I have 2 custom events A and B . A can have values either "X" or "Y" . B will have a unique value Z . Some rows of event A has both values "X" and "Y" but has a unique event B value "Z" and I want to filter this event A having values "X" and "Y" .

I want the result set to be having event A with only value X and event B with value Z . Basically I doesn't want to show the entry of event A having Y value when it already has an entry having "X" with the same unique value Z that it has with Y.

I basically did an inner join between values of X and Y based on unique value Z

customEvents
| extend Column1 = tostring(customDimensions.['A']) , column2 = tostring(customDimensions.['B'])
| where (Column1 == "X" )
| project Column1 ,column2
| join kind= inner (
customEvents | extend Column1 = tostring(customDimensions.['A']) , column2 = tostring(customDimensions.['B']) | where (Column1 == "Y"
)| project Column1 , column2
) on column2 | project Column1 , column2


By this I am able to get the common rows on unique value Z in column2 and X in column1 .

Now how to show the remaining rows of column1 having Y value alone in event A and add them to the result set that I got from the above inner join query ? I tried using a full outer with event A having Y value alone with the above result i got . But it doesn't gave me desired results. Need some help on this .

Answer

I think there are several ways to achieve the desired outcome:

  • Use Union operator that just "glues" two data sets together without joining on any field - (Query1) | union (Query2). This is suitable if you can have two data sets in two queries as an input to produce the union.

  • Use (Query1) | join kind=anti (Query2) on Field to get everything from the left side that is not represented in the right side in Field. This is suitable if you have a data set you want to "subtract" from the other richer data set to get the required result.

Comments