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

Question

你能帮我确定我需要使用什么类型的通配符来找到我的属性字段中的某个电子邮件地址吗?

我知道我要找的电子邮件在2号插槽中在不知道槽号的情况下如何找到邮箱地址?我能用[*]代替[2]吗?

这是我的查询:

resources
| where type == 'microsoft.insights/actiongroups'
| where properties["enabled"] in~ ('true')
| where properties['emailReceivers'][2]['emailAddress'] == "[e-mail]"
| 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": "[e-mail]"
},
{
"name": "SevenOfNine",
"status": "Enabled",
"useCommonAlertSchema": true,
"emailAddress": "[e-mail]"
},
{
"name": "PEAT",
"status": "Enabled",
"useCommonAlertSchema": true,
"emailAddress": "[e-mail]"
}
],
"voiceReceivers": [],
"groupShortName": "eng-mon",
"itsmReceivers": [],
"smsReceivers": []
}

我试过用[*]代替[2]，但是行不通。

Answer 1

您是否需要从属性中查找某个电子邮件地址?你能多解释一下为什么需要通配符吗?这个查询对你有用吗?基本上展开' emailreceiver '列表，找出emailAddress包含你正在搜索的值。

resources
| where type == 'microsoft.insights/actiongroups'
| where properties["enabled"] in~ ('true')
| mv-expand properties['emailReceivers'] | limit 100
| extend emailAddr = properties_emailReceivers['emailAddress']
| where emailAddr contains "[e-mail]"
| project id,name,resourceGroup,subscriptionId,properties,location
| order by tolower(tostring(name)) asc

Answer 2

where properties.emailReceivers has_cs "[e-mail]"理论上不是100%安全的("[e-mail]"可能会出现在&;emailaddress &;)以外的字段中，但在您的情况下，它可能已经足够了，如果您有一个大数据集，它也会很快。

如果您需要100%的保证，那么还可以添加以下内容:
where dynamic_to_json(properties.emailReceivers) matches regex '"emailAddress":"[e-mail]"'

这看起来并不漂亮，但是Azure资源图只使用了Azure数据浏览器支持的KQL的一个子集。

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": "[e-mail]"
},
{
"name": "SevenOfNine",
"status": "Enabled",
"useCommonAlertSchema": true,
"emailAddress": "[e-mail]"
},
{
"name": "PEAT",
"status": "Enabled",
"useCommonAlertSchema": true,
"emailAddress": "[e-mail]"
}
],
"voiceReceivers": [],
"groupShortName": "eng-mon",
"itsmReceivers": [],
"smsReceivers": []
}
)
];
resources
| where type == "microsoft.insights/actiongroups"
| where properties.enabled == true
| where properties.emailReceivers has_cs "[e-mail]"
| where dynamic_to_json(properties.emailReceivers) matches regex '"emailAddress":"[e-mail]"'
| project id,name,resourceGroup,subscriptionId,properties,location
| order by tolower(name) asc

propertiestbody> <<tr>

id	name	resourceGroup	subscriptionId	location
my_id	my_name	my_resourceGroup	my_subscriptionId	{"enabled"真的,"automationRunbookReceivers":[],"azureFunctionReceivers":[],"azureAppPushReceivers":[],"logicAppReceivers":[],"eventHubReceivers":[],"webhookReceivers":[],"armRoleReceivers":[],"emailReceivers":[{"name"TED","status":"enabled","useCommonAlertSchema":真的,"emailAddress"[e-mail]"},{"name"SevenOfNine","status":"Enabled","useCommonAlertSchema":真的,"emailAddress":"[e-mail]"},{"name"PEAT","status":"Enabled","useCommonAlertSchema":真的,"emailAddress":"[e-mail]"}],"voiceReceivers":[],"groupShortName":问uot; eng-mon"itsmReceivers":[],"smsReceivers": []}	my_location

我找到了一个方法，使用关键字"contains"。

这样你就不需要指定它应该在哪个槽中找到它，它可以是[0]，[1]，[2]…[n]

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