This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(172.8, 91%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EYM%3C/text%3E%3C/svg%3E)
How can I update the following query to associate the department with each MDC recommendation?
securityresources
| where type == "microsoft.security/assessments"
| extend source = trim(' ', tolower(tostring(properties.resourceDetails.Source)))
| extend resourceId = trim(' ', tolower(tostring(case(
source =~ "azure", properties.resourceDetails.Id,
source =~ "aws" and isnotempty(tostring(properties.resourceDetails.ConnectorId)), properties.resourceDetails.Id,
source =~ "gcp" and isnotempty(tostring(properties.resourceDetails.ConnectorId)), properties.resourceDetails.Id,
source =~ 'aws', properties.resourceDetails.AzureResourceId,
source =~ 'gcp', properties.resourceDetails.AzureResourceId,
extract('^(.+)/providers/Microsoft.Security/assessments/.+$',1,id)
))))
| extend status = trim(" ", tostring(properties.status.code))
| extend cause = trim(" ", tostring(properties.status.cause))
| extend assessmentKey = tostring(name)
| where assessmentKey == "d57a4221-a804-52ca-3dea-768284f06bb7"
I can get the department name with the following query:
Resources
| where type == 'microsoft.compute/virtualmachines'
| extend
JoinID = toupper(id),
OSName = tostring(properties.osProfile.computerName),
OSType = tostring(properties.storageProfile.osDisk.osType),
VMSize = tostring(properties.hardwareProfile.vmSize),
departmentTag = tostring(tags['Department'])
| join kind=leftouter(
Resources
| where type == 'microsoft.compute/virtualmachines/extensions'
| extend
VMId = toupper(substring(id, 0, indexof(id, '/extensions'))),
ExtensionName = name
) on $left.JoinID == $right.VMId
| summarize Extensions = make_list(ExtensionName) by id, OSName, OSType, VMSize, name, resourceGroup, departmentTag
| order by tolower(OSName) asc
Hi @Yue Ma , you can modify your first query to join the results of the second query based on a common key. In this case, the resourceId from the first query and the JoinID from the second query can serve as the keys for the join operation. Here’s how you can update your query:
// First query to get MDC recommendations
securityresources
| where type == "microsoft.security/assessments"
| extend source = trim(' ', tolower(tostring(properties.resourceDetails.Source)))
| extend resourceId = trim(' ', tolower(tostring(case(
source =~ "azure", properties.resourceDetails.Id,
source =~ "aws" and isnotempty(tostring(properties.resourceDetails.ConnectorId)), properties.resourceDetails.Id,
source =~ "gcp" and isnotempty(tostring(properties.resourceDetails.ConnectorId)), properties.resourceDetails.Id,
source =~ 'aws', properties.resourceDetails.AzureResourceId,
source =~ 'gcp', properties.resourceDetails.AzureResourceId,
extract('^(.+)/providers/Microsoft.Security/assessments/.+$',1,id)
))))
| extend status = trim(" ", tostring(properties.status.code))
| extend cause = trim(" ", tostring(properties.status.cause))
| extend assessmentKey = tostring(name)
| where assessmentKey == "d57a4221-a804-52ca-3dea-768284f06bb7"
// Second query to get department names
| join kind=leftouter (
Resources
| where type == 'microsoft.compute/virtualmachines'
| extend JoinID = toupper(id)
| extend departmentTag = tostring(tags['Department'])
) on $left.resourceId == $right.JoinID
| project-away JoinID, JoinID1 // Remove duplicate columns after join
| extend department = departmentTag // Rename for clarity
Remember to replace the resourceId and JoinID with the actual column names used in your data if they are different.
Please let me know if you have any questions or if this doesn't work and I can help you further.
If this answer helps you please mark "Accept Answer" so other users can reference it.
Thank you,
James
thank you for the quick response. Here is the error message I got:
==
Please provide below info when asking for support: timestamp = 2024-03-21T17:39:23.6678663Z, correlationId = eded8614-07d6-4991-a1fe-236ad9637de7. (Code:BadRequest) Details: Query is invalid. Please refer to the documentation for the Azure Resource Graph service and fix the error before retrying. (Code:InvalidQuery) ParserFailure (Code:ParserFailure)
==
"Remember to replace the resourceId and JoinID with the actual column names used in your data if they are different." on which row(s) do I have to replace? Please note the content/format/parameter of the ID column in query 1 is different from the content/format/parameter of the ID column in query 2.
Hi @Yue Ma you only have to replace the resourceID or JoinID if you change anything that requires it, which your query doesn't. My apologies for the confusion. Is the error message giving you the line that the query fails at?
below is the "Raw Error" I got. I did not change anything. Here is an example of the "id" column from query 1 (company specific info is replaced by xxxx):
/subscriptions/0d7f7dfb-xxx-xxx-xxx-xxx/resourceGroups/RG-name/providers/Microsoft.Compute/virtualMachines/xxxxxxxxx/providers/Microsoft.Security/assessments/assessment-id
here is an example of the "id" column from query 2 (company specific info is replaced by xxxx):
/subscriptions/105849c7-xxx-xxx-xxxx-xxxx/resourceGroups/RG-name/providers/Microsoft.Compute/virtualMachines/VM-name
thank you!
{
"details": [
{
"code": "BadRequest",
"message": "Please provide below info when asking for support: timestamp = 2024-03-25T04:28:54.3230178Z, correlationId = 5e8e07e7-d474-4080-9208-5cd1f2cd14a5.",
"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": 2,
"token": "|"
}
]
}
]
}
This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Hi @Yue Ma , can you please send me an email at "azcommunity@microsoft.com" with subject "ATTN: James Hamil" and your subscription ID? I can open a free support ticket for you.
Best,
James
Just sent you an email as you suggested. Thank you,
Yue