Azure Question posted in
The official documentation to get you started can be found here.

Azure – Application Insights KQL query – not in sub query

How can I return results of a query that aren’t in the results of another query? For example, I want to return the results that are returned from the first query and are not also in the second query:

First query:

customEvents
| extend Product = tostring(customDimensions.["Is Successful"])
| extend TransactionNumber = tostring(customDimensions.["Transaction Number"])
| extend StoreCode = tostring(customDimensions.["StoreCode"])
| where name contains 'Transaction Posted' and customDimensions.['Transaction Number'] != ''
| where customDimensions.["Is Successful"] == "False"
| project TransactionNumber

e.g. if the above returned

145
167
100
94
56

Second query:

customEvents
| extend Product = tostring(customDimensions.["Is Successful"])
| extend TransactionNumber = tostring(customDimensions.["Transaction Number"])
| extend StoreCode = tostring(customDimensions.["StoreCode"])
| where name contains 'Transaction Posted' and customDimensions.['Transaction Number'] != ''
| where customDimensions.["Is Successful"] == "True"
| project TransactionNumber

e.g. if the above returned

94
95
96
97
167
100

I want the result set to be:

145
56

I’ve tried the following but that doesn’t appear to return the results from the failures query that aren’t in the results of the successes query:

let failures = (customEvents
    | extend Product = tostring(customDimensions.["Is Successful"])
    | extend TransactionNumber = tostring(customDimensions.["Transaction Number"])
    | extend StoreCode = tostring(customDimensions.["StoreCode"])
    | where name contains 'Transaction Posted' and customDimensions.['Transaction Number'] != ''
    | where customDimensions.["Is Successful"] == "False"
    | project TransactionNumber);
let successes = (customEvents
    | extend Product = tostring(customDimensions.["Is Successful"])
    | extend TransactionNumber = tostring(customDimensions.["Transaction Number"])
    | extend StoreCode = tostring(customDimensions.["StoreCode"])
    | where name contains 'Transaction Posted' and customDimensions.['Transaction Number'] != ''
    | where customDimensions.["Is Successful"] == "True"
    | project TransactionNumber);
failures
| join kind=fullouter (successes) on TransactionNumber
| where isnull(TransactionNumber)
| project TransactionNumber
Tags:

2

Answers


  1. 0

    Try doing an anti-join:

    let failures = (customEvents
    | extend Product = tostring(customDimensions.["Is Successful"])
    | extend TransactionNumber = tostring(customDimensions.["Transaction Number"])
    | extend StoreCode = tostring(customDimensions.["StoreCode"])
    | where name contains 'Transaction Posted' and customDimensions.['Transaction Number'] != ''
    | where customDimensions.["Is Successful"] == "False"
    | project TransactionNumber);
let successes = (customEvents
    | extend Product = tostring(customDimensions.["Is Successful"])
    | extend TransactionNumber = tostring(customDimensions.["Transaction Number"])
    | extend StoreCode = tostring(customDimensions.["StoreCode"])
    | where name contains 'Transaction Posted' and customDimensions.['Transaction Number'] != ''
    | where customDimensions.["Is Successful"] == "True"
    | project TransactionNumber);
failures
| join kind=anti(successes) on TransactionNumber
| project TransactionNumber

    Mark the whole code and execute it.

    Login or Signup to reply.
  2. 0

    You can integrate your query like below KQL Query:

    let failures = datatable(TransactionNumber: int)
[
 145,167,100,94,56
];
let successes = datatable(TransactionNumber: int)
[
94,95,96,97,167,100
];
failures
| join kind=leftanti (successes) on TransactionNumber

    Output:

    enter image description here

    Fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search