Forum Discussion

Anonymous's avatar
Anonymous
Apr 02, 2025
Solved

Logic app - Escaped Characters and Formatting Problems in KQL Run query and list results V2 action

I’m building a Logic App to detect sign-ins from suspicious IP addresses. The logic includes:

  1. Retrieving IPs from incident entities in Microsoft Sentinel.
  2. Enriching each IP using an external API.
  3. Filtering malicious IPs based on their score and risk level.
  4. Storing those IPs in an array variable (MaliciousIPs).
  5. Creating a dynamic KQL query to check if any of the malicious IPs were used in sign-ins, using the in~ operator.

Problem:

When I use a Select and Join action to build the list of IPs (e.g., "ip1", "ip2"), the Logic App automatically escapes the quotes. As a result, the KQL query is built like this:

IPAddress in~ ([{"body":"{\"\":\"\\\"X.X.X.X\\\"\"}"}])

Instead of the expected format:

IPAddress in~ ("X.X.X.X", "another.ip")

This causes a parsing error when the Run Query and List Results V2 action is executed against Log Analytics.

------------------------

Here's the For Each action loop who contain the following issue:

  1. Dynamic compose to formulate the KQL query in a concat, since it's containing the dynamic value above :

concat('SigninLogs | where TimeGenerated > ago(3d) | where UserPrincipalName == \"',variables('CurrentUPN'),'\" | where IPAddress in~ (',outputs('Join_MaliciousIPs_KQL'),') | project TimeGenerated, IPAddress, DeviceDetail, AppDisplayName, Status')

The Current UPN is working as expected, using the same format in a Initialize/Set variable above (Array/String(for IP's)).

The rest of the loop : 

Note: Even if i have a "failed to retrieve" error on the picture don't bother with that, it's just about the dynamic value about the Subscription, I've entered it manually, it's working fine.

What I’ve tried:

  • Using concat('\"', item()?['ip'], '\"') inside Select (causes extra escaping).
  • Removing quotes and relying on Logic App formatting (resulted in object wrapping).
  • Flattening the array using a secondary Select to extract only values.
  • Using Compose to debug outputs.

Despite these attempts, the query string is always malformed due to extra escaping or nested JSON structure.

I would like to know if someone has encountered or have the solution to this annoying problem ?

Best regards

  • Anonymous's avatar
    Anonymous
    May 05, 2025

    Solution: Extract the object type and convert it to a string with an initialization variable, append the string variable, and call it in the DynamicUPNKQL.

    Root cause: The escaping was due to object extraction, which is incompatible with KQL, even in a concat.

1 Reply

  • Anonymous's avatar
    Anonymous

    Solution: Extract the object type and convert it to a string with an initialization variable, append the string variable, and call it in the DynamicUPNKQL.

    Root cause: The escaping was due to object extraction, which is incompatible with KQL, even in a concat.

Resources