Error Parsing Multi-line KQL query while passing to Az Powershell Cmdlet

Rajesh Swarnkar 911 Reputation points
2023-03-13T15:10:30.16+00:00

I am trying to pass this multi-line KQL using here doc. But I keep getting parse error:

Search-AzGraph : {
  "error": {
    "code": "BadRequest",
    "message": "Please provide below info when asking for support: timestamp = 2023-03-13T14:54:05.1551439Z, correlationId = 
XXX.",
    "details": [
      {
        "code": "InvalidQuery",
        "message": "Query is invalid. Please refer to the documentation for the Azure Resource Graph service and fix the error before retrying."
      },
      {
        "code": "ParserFailure",
        "message": "ParserFailure",
        "line": 1,
        "characterPositionInLine": 3815,
        "token": "."
      }
    ]
  }
}

Here is what I am trying to do:

$the_query = @"
securityresources | where type == "microsoft.security/regulatorycompliancestandards/regulatorycompliancecontrols/regulatorycomplianceassessments" | extend complianceStandardId = replace( "-", " ", extract(@'/regulatoryComplianceStandards/([^/]*)', 1, id)) | where complianceStandardId == "Azure CIS 1.3.0" | extend failedResources = toint(properties.failedResources), passedResources = toint(properties.passedResources),skippedResources = toint(properties.skippedResources) | where failedResources + passedResources + skippedResources > 0 or properties.assessmentType == "MicrosoftManaged" | join kind = leftouter( securityresources | where type == "microsoft.security/assessments") on subscriptionId, name | extend complianceState = tostring(properties.state) | extend resourceSource = tolower(tostring(properties1.resourceDetails.Source)) | extend recommendationId = iff(isnull(id1) or isempty(id1), id, id1) | extend resourceId = trim(' ', tolower(tostring(case(resourceSource =~ 'azure', properties1.resourceDetails.Id, resourceSource =~ 'gcp', properties1.resourceDetails.GcpResourceId, resourceSource =~ 'aws' and isnotempty(tostring(properties1.resourceDetails.ConnectorId)), properties1.resourceDetails.Id, resourceSource =~ 'aws', properties1.resourceDetails.AwsResourceId, extract('^(.+)/providers/Microsoft.Security/assessments/.+$',1,recommendationId))))) | extend regexResourceId = extract_all(@"/providers/[^/]+(?:/([^/]+)/[^/]+(?:/[^/]+/[^/]+)?)?/([^/]+)/([^/]+)$", resourceId)[0] | extend resourceType = iff(resourceSource =~ "aws" and isnotempty(tostring(properties1.resourceDetails.ConnectorId)), tostring(properties1.additionalData.ResourceType), iff(regexResourceId[1] != "", regexResourceId[1], iff(regexResourceId[0] != "", regexResourceId[0], "subscriptions"))) | extend resourceName = tostring(regexResourceId[2]) | extend recommendationName = name | extend recommendationDisplayName = tostring(iff(isnull(properties1.displayName) or isempty(properties1.displayName), properties.description, properties1.displayName)) | extend description = tostring(properties1.metadata.description) | extend remediationSteps = tostring(properties1.metadata.remediationDescription) | extend severity = tostring(properties1.metadata.severity) | extend azurePortalRecommendationLink = tostring(properties1.links.azurePortal) | extend complianceStandardId = replace( "-", " ", extract(@'/regulatoryComplianceStandards/([^/]*)', 1, id)) | extend complianceControlId = extract(@"/regulatoryComplianceControls/([^/]*)", 1, id) | mvexpand statusPerInitiative = properties1.statusPerInitiative | extend expectedInitiative = statusPerInitiative.policyInitiativeName =~ "ASC Default" | summarize arg_max(expectedInitiative, *) by complianceControlId, recommendationId | extend state = iff(expectedInitiative, tolower(statusPerInitiative.assessmentStatus.code), tolower(properties1.status.code)) | extend notApplicableReason = iff(expectedInitiative, tostring(statusPerInitiative.assessmentStatus.cause), tostring(properties1.status.cause)) | project-away expectedInitiative | project complianceStandardId, complianceControlId, complianceState, subscriptionId, resourceGroup = resourceGroup1 ,resourceType, resourceName, resourceId, recommendationId, recommendationName, recommendationDisplayName, description, remediationSteps, severity, state, notApplicableReason, azurePortalRecommendationLink | join kind = leftouter (securityresources | where type == "microsoft.security/regulatorycompliancestandards/regulatorycompliancecontrols" | extend complianceStandardId = replace( "-", " ", extract(@'/regulatoryComplianceStandards/([^/]*)', 1, id)) | where complianceStandardId == "Azure Security Benchmark" | extend controlName = tostring(properties.description) | project controlId = name, controlName | distinct *) on $right.controlId == $left.complianceControlId | project-away controlId | distinct * | order by complianceControlId asc, recommendationId asc 
"@


$output = Search-AzGraph -Query $the_query 

Help asap.

Community Center | Not monitored
0 comments No comments
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.