Kusto Query using a bracket with a wildcard-CodePudding

Can you help me identifying what type of wildcard I need to use to find a certain email address in my properties field?

I know that the email I'm looking for is in the slot number 2 How can I find the email address without knowing the slot number? can I use a [*] instead of a [2]?

Here's my query:

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

I have the following data in my properties field:

{
    "enabled": true,
    "automationRunbookReceivers": [],
    "azureFunctionReceivers": [],
    "azureAppPushReceivers": [],
    "logicAppReceivers": [],
    "eventHubReceivers": [],
    "webhookReceivers": [],
    "armRoleReceivers": [],
    "emailReceivers": [
        {
            "name": "TED",
            "status": "Enabled",
            "useCommonAlertSchema": true,
            "emailAddress": "[email protected]"
        },
        {
            "name": "SevenOfNine",
            "status": "Enabled",
            "useCommonAlertSchema": true,
            "emailAddress": "[email protected]"
        },
        {
            "name": "PEAT",
            "status": "Enabled",
            "useCommonAlertSchema": true,
            "emailAddress": "[email protected]"
        }
    ],
    "voiceReceivers": [],
    "groupShortName": "eng-mon",
    "itsmReceivers": [],
    "smsReceivers": []
}

I've tried using [*] instead of [2] but it didn't work.

CodePudding user response：

where properties.emailReceivers has_cs "[email protected]" is theoretically not 100% safe ("[email protected]" 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":"[email protected]"'

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

id	name	resourceGroup	subscriptionId	properties	location
my_id	my_name	my_resourceGroup	my_subscriptionId	{"enabled":true,"automationRunbookReceivers":[],"azureFunctionReceivers":[],"azureAppPushReceivers":[],"logicAppReceivers":[],"eventHubReceivers":[],"webhookReceivers":[],"armRoleReceivers":[],"emailReceivers":[{"name":"TED","status":"Enabled","useCommonAlertSchema":true,"emailAddress":"[email protected]"},{"name":"SevenOfNine","status":"Enabled","useCommonAlertSchema":true,"emailAddress":"[email protected]"},{"name":"PEAT","status":"Enabled","useCommonAlertSchema":true,"emailAddress":"[email protected]"}],"voiceReceivers":[],"groupShortName":"eng-mon","itsmReceivers":[],"smsReceivers":[]}	my_location

Fiddle

CodePudding user response：

I found a way to do it using the keyword "contains".

In that way you don't need to specify in which slot it should find it, it could be [0],[1],[2]...[n]

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