Forum Discussion
EtienneFiset
Apr 02, 2025Brass Contributor
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:
- Retrieving IPs from incident entities in Microsoft Sentinel.
- Enriching each IP using an external API.
- Filtering malicious IPs based on their score and risk level.
- Storing those IPs in an array variable (MaliciousIPs).
- 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:
- 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