json 使用带通配符的括号的Kusto查询

ghg1uchk  于 2022-12-01  发布在  其他
关注(0)|答案(2)|浏览(171)

您能否帮助我确定在属性字段中查找某个电子邮件地址时需要使用哪种类型的通配符?
我知道我要查找的电子邮件地址在2号插槽中。如果不知道插槽号,我如何找到电子邮件地址?我可以使用[*]代替[2]吗?
以下是我的疑问:

resources
| where type == 'microsoft.insights/actiongroups'
| where properties["enabled"] in~ ('true')
| where properties['emailReceivers'][2]['emailAddress'] == "DevSecOps@pato.com"
| project id,name,resourceGroup,subscriptionId,properties,location
| order by tolower(tostring(name)) asc

我的属性字段中有以下数据:

{
    "enabled": true,
    "automationRunbookReceivers": [],
    "azureFunctionReceivers": [],
    "azureAppPushReceivers": [],
    "logicAppReceivers": [],
    "eventHubReceivers": [],
    "webhookReceivers": [],
    "armRoleReceivers": [],
    "emailReceivers": [
        {
            "name": "TED",
            "status": "Enabled",
            "useCommonAlertSchema": true,
            "emailAddress": "tedtechnicalengineeringdesign@pato.com"
        },
        {
            "name": "SevenOfNine",
            "status": "Enabled",
            "useCommonAlertSchema": true,
            "emailAddress": "sevenofnine@pato.com"
        },
        {
            "name": "PEAT",
            "status": "Enabled",
            "useCommonAlertSchema": true,
            "emailAddress": "DevSecOps@pato.com"
        }
    ],
    "voiceReceivers": [],
    "groupShortName": "eng-mon",
    "itsmReceivers": [],
    "smsReceivers": []
}

我尝试使用[*]代替[2],但没有效果。

dfty9e19

dfty9e191#

where properties.emailReceivers has_cs "DevSecOps@pato.com" is theoretically not 100% safe ("DevSecOps@pato.com" might appear in fields other than "emailAddress"), but in your case it might be enough and if you have a large data set it will also be fast.
If you need a 100% guarantee, then also add the following:
where dynamic_to_json(properties.emailReceivers) matches regex '"emailAddress":"DevSecOps@pato.com"'
It's not pretty, but Azure Resource Graph uses just a subset of the KQL supported by Azure Data Explorer.

let resources = datatable(id:string, name:string, resourceGroup:string, subscriptionId:string, location:string, type:string, properties:dynamic)
[
    "my_id"
   ,"my_name"
   ,"my_resourceGroup"
   ,"my_subscriptionId"
   ,"my_location"
   ,"microsoft.insights/actiongroups"
   ,dynamic
    (
        {
            "enabled": true,
            "automationRunbookReceivers": [],
            "azureFunctionReceivers": [],
            "azureAppPushReceivers": [],
            "logicAppReceivers": [],
            "eventHubReceivers": [],
            "webhookReceivers": [],
            "armRoleReceivers": [],
            "emailReceivers": [
                {
                    "name": "TED",
                    "status": "Enabled",
                    "useCommonAlertSchema": true,
                    "emailAddress": "tedtechnicalengineeringdesign@pato.com"
                },
                {
                    "name": "SevenOfNine",
                    "status": "Enabled",
                    "useCommonAlertSchema": true,
                    "emailAddress": "sevenofnine@pato.com"
                },
                {
                    "name": "PEAT",
                    "status": "Enabled",
                    "useCommonAlertSchema": true,
                    "emailAddress": "DevSecOps@pato.com"
                }
            ],
            "voiceReceivers": [],
            "groupShortName": "eng-mon",
            "itsmReceivers": [],
            "smsReceivers": []
        }
    )
];
resources
| where type == "microsoft.insights/actiongroups"
| where properties.enabled == true
| where properties.emailReceivers has_cs "DevSecOps@pato.com"
| where dynamic_to_json(properties.emailReceivers) matches regex '"emailAddress":"DevSecOps@pato.com"'
| project id,name,resourceGroup,subscriptionId,properties,location
| order by tolower(name) asc
idnameresourceGroupsubscriptionIdpropertieslocation
my_idmy_namemy_resourceGroupmy_subscriptionId{"enabled":true,"automationRunbookReceivers":[],"azureFunctionReceivers":[],"azureAppPushReceivers":[],"logicAppReceivers":[],"eventHubReceivers":[],"webhookReceivers":[],"armRoleReceivers":[],"emailReceivers":[{"name":"TED","status":"Enabled","useCommonAlertSchema":true,"emailAddress":"tedtechnicalengineeringdesign@pato.com"},{"name":"SevenOfNine","status":"Enabled","useCommonAlertSchema":true,"emailAddress":"sevenofnine@pato.com"},{"name":"PEAT","status":"Enabled","useCommonAlertSchema":true,"emailAddress":"DevSecOps@pato.com"}],"voiceReceivers":[],"groupShortName":"eng-mon","itsmReceivers":[],"smsReceivers":[]}my_location

Fiddle

vyswwuz2

vyswwuz22#

我找到了一种方法,使用关键字“包含”。
这样就不需要指定它应该在哪个插槽中找到它,它可以是[0]、[1]、[2]...[n]

resources
| where type == 'microsoft.insights/actiongroups'
| where properties["enabled"] in~ ('true')
| where properties['emailReceivers'] contains "DevSecOps@pato.com"
| project id,name,resourceGroup,subscriptionId,properties,location
| order by tolower(tostring(name)) asc

相关问题