0

I'm looking to create a report on all policies and initiatives in my Azure tenant. To do this, I'm using Search-AzGraph in PowerShell to pull from Azure Resource Graph Explorer. I'm running into an issue when I have a query that needs joined using $left or $right. I always get a badrequest error of ParserFailure.

The same query runs flawlessly in azure resource graph explorer. Need to use powershell to automate.

I am running a sample query from Microsoft Azure Resource Graph Explorer:

   // Policy assignments and information about each of its respective definitions
// Gets policy assignments in your environment with the respective assignment name, definition associated, category of definition (if applicable), as well as whether the definition type is an initiative or a single policy.
// Click the "Run query" command above to execute the query and see results.
policyResources
| where type =~'Microsoft.Authorization/PolicyAssignments'
| project policyAssignmentId = tolower(tostring(id)), policyAssignmentDisplayName = tostring(properties.displayName), policyAssignmentDefinitionId = tolower(properties.policyDefinitionId)
| join kind=leftouter(
 policyResources
 | where type =~'Microsoft.Authorization/PolicySetDefinitions' or type =~'Microsoft.Authorization/PolicyDefinitions'
 | project definitionId = tolower(id), category = tostring(properties.metadata.category), definitionType = iff(type =~ 'Microsoft.Authorization/PolicysetDefinitions', 'initiative', 'policy')
) on $left.policyAssignmentDefinitionId == $right.definitionId

Error: Powershell Error

I can remove the error by either putting a backtick prior to $left/$right or initializing the variables prior to $left = '$left'. When I do that, my join functions very strangely. The data from PolicyDefinitions is sporadic . I will sometimes get the type and category back and other times not. I would have expected the join to work or not.

CSV Output

using the here-string:

    $Policies = Search-AzGraph -Query @'
    policyResources
    | where type =~'Microsoft.Authorization/PolicyAssignments'
    | project policyAssignmentId = tolower(tostring(id)), policyAssignmentDisplayName = tostring(properties.displayName), policyAssignmentDefinitionId = tolower(properties.policyDefinitionId), policyDescription = tostring(properties.description), properties.scope
    | join kind=leftouter(
     policyResources
    | where type =~'Microsoft.Authorization/PolicySetDefinitions' or type =~'Microsoft.Authorization/PolicyDefinitions'
    | project definitionId = tolower(id), category = tostring(properties.metadata.category), definitionType = iff(type =~ 'Microsoft.Authorization/PolicysetDefinitions', 'initiative', 'policy')
    ) on $left.policyAssignmentDefinitionId == $right.definitionId
'@
user19700045
  • 11
  • 1
  • Generally, use single-quoted strings if the value is to be used _verbatim_ - then you won't have to worry about escaping `$` and `\``. Embedded `'` must then be escaped as `''`, but you can avoid that too if you use a verbatim [_here-string_](https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_Quoting_Rules#here-strings). – mklement0 Aug 05 '22 at 14:33
  • Thanks, trying that here-string avoided the error, but the join is still not working. Added the here-string to my original post at the bottom. – user19700045 Aug 05 '22 at 15:17
  • That suggests a separate problem that is unrelated to how you pass the query string (I can't help there). – mklement0 Aug 05 '22 at 15:19
  • The code works as is. – David דודו Markovitz Aug 05 '22 at 20:01
  • The join is not working correctly still. For instance, $policies.content returns no/limited data. I'm going to pursue a logic app instead of running this in powershell – user19700045 Aug 11 '22 at 21:32

0 Answers0