CODEWITHSUNDEEP
azurepower-automateazure-data-explorerkql
Adam S
Adam S

Reputation: 13

How to check if a KQL query returns null or not in Power Automate

In Power Automate I am running a KQL query titled 'cmdletsForEmail' using the action 'Run KQL Query'. The query returns a row with a couple of columns if there is a problem.

I try to check if the query body is empty using this:

empty(body('cmdletsForEmail')?['value'])

in this condition

enter image description here

However, it is always evaluated to False even if there are rows returned. { "expressionResult": false }

How can I change the condition to check if the query returns no rows?

Here is a screenshot of the condition: condition screenshot

I also tried this value for the condition but now it always resolves as true:

empty(outputs('cmdletsForEmail')?['body/body'])

But then the condition always returns true. The Condition json input is: { "expressionResult": true }

Attempted Solutions: The Run KQL Query action in my Flow is returning an Html Table.

The output of the Run KQL Query action is (edited for brevity):

{
    "statusCode": 200,
    "headers": {
        "Pragma": "no-cache",
        "Transfer-Encoding": "chunked",
        "Vary": "Accept-Encoding",
        "Cache-Control": "no-store, no-cache",
        "Set-Cookie": "…",
        "x-ms-request-id": "…",
        "Strict-Transport-Security": "max-age=’…’; includeSubDomains",
        "X-Content-Type-Options": "nosniff",
        "X-Frame-Options": "DENY",
        "Timing-Allow-Origin": "*",
        "x-ms-apihub-cached-response": "false",
        "x-ms-apihub-obo": "false",
        "Date": "Wed, 08 Mar 2023 16:28:20 GMT",
        "Content-Type": "application/json; charset=utf-8",
        "Expires": "-1",
        "Content-Length": "5487"
    },
    "body": {
        "attachmentName": "kusto_flow_table_...",
        "attachmentContent": "…",
        "body": "…",
        "bodyHtml": "<table border=\"1\" cellpadding=\"5\" cellspacing=\"1\" bordercolor=\"Black\" style=\"font-family:&quot;Segoe UI&quot;;font-size:12px;border-collapse:collapse;\"><tr bgcolor=\"DarkGray\"><td nowrap=\"nowrap\">Command</td><td nowrap=\"nowrap\">failRateCurrentRounded</td><td nowrap=\"nowrap\">failRateLastWeekRounded</td><td nowrap=\"nowrap\">failRateLastWeekPercent</td><td nowrap=\"nowrap\">failRateCurrentPercent</td><td nowrap=\"nowrap\">generateEmail</td></tr></table><a href=’<kusto cluster link>’ style='color:green; font-family: Calibri; font-size: 8pt;'>’<Kusto cluster link>’</a> [<a href=’kusto cluster and database and query link' style='color:blue; font-family: Calibri; font-size: 8pt;'>Open on Web</a>] [<a href=’kusto cluster and query link’ style='color:blue; font-family: Calibri; font-size: 8pt;'>Open on Desktop</a>]",
        "kustoDeepLink": "<a href=’kusto cluster link' style='color:green; font-family: Calibri; font-size: 8pt;'>Kusto cluster link</a> [<a href=’kusto cluster and query link' style='color:blue; font-family: Calibri; font-size: 8pt;'>Open on Web</a>] [<a href=’kusto cluster and query link’ style='color:blue; font-family: Calibri; font-size: 8pt;'>Open on Desktop</a>]"
    }
}

I tried changing the condition to check if length(outputs('cmdletsForEmail')?['body/body']) is not equal to 0, however this resulted in an email sent with an empty table.

I tried changing the condition to check if length(outputs('cmdletsForEmail')?['body']) is not equal to 0, but that resulted in an error stating

Unable to process template language expressions for action 'Condition_2' at line '0' and column '0': 'The template language function 'length' expects its parameter to be an array or a string. The provided value is of type 'Object'. Please see https://aka.ms/logicexpressions#length for usage details.'.

I tried changing the condition to check if length(outputs('cmdletsForEmail')?['body/value']) is not equal to 0 but that resulted in an error stating

Unable to process template language expressions for action 'Condition_2' at line '0' and column '0': 'The template language function 'length' expects its parameter to be an array or a string. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#length for usage details.'.

Upvotes: 0

Views: 1571

Answers (1)

RitikaNalwaya
RitikaNalwaya

Reputation: 590

Here is the way to check whether query returned any rows or not.

length(outputs('Run_KQL_query')?['body/value'])

Explanation:- The KQL query in power automate returns output in json object format and value is an array property of the json object which holds collection of rows returned by the query. Please refer same here.

Using length() we can check whether the value array is empty or has any value. If array is empty then code mentioned above will return 0 else it will return number of records.

Screenshots for sample run

Condition checking the length of value array

Query returning records

Query returning no records

Upvotes: 0

Related Questions