Forum Discussion

EtienneFiset's avatar
EtienneFiset
Brass Contributor
Apr 02, 2025

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

No RepliesBe the first to reply

Resources