Kusto Query using a bracket with a wildcard

Question

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'] == "DevSecOps@pato.com"
| 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": "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": []
}

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

It seems you are using Azure Resource Graph, not Azure Data Explorer. — David דודו Markovitz, Nov 30 '22 at 15:30

score 1 · Answer 1 · answered Jan 09 '23 at 18:46

Do you need to find a certain email address from properties? can you please explain a little more why you need wildcards? can this query work for you? basically expand the 'emailReceivers' list and find out where emailAddress contains the value you are searching for.

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 "DevSecOps@pato.com"
| project id,name,resourceGroup,subscriptionId,properties,location
| order by tolower(tostring(name)) asc

score 0 · Answer 2 · answered Nov 30 '22 at 16:03

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

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":"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

I gave your query a try, but it didn't work. It brought 0 results. Thanks for your intention to help though! — Heriberto Martinez, Nov 30 '22 at 18:55
This is a tested code on both ADX and Azure Resource Graph (with slight variation due to the lack of support in datatable), **created based on the sample data you have provided**. The difference in results is probably due to the use of the case-sensitive operator `has_cs`. Try `has` instead. P.S. `contains` also has its case-sensitive variation - `contains_cs` — David דודו Markovitz, Nov 30 '22 at 19:03

score 0 · Answer 3 · answered Nov 30 '22 at 18:13

0

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 "DevSecOps@pato.com"
| project id,name,resourceGroup,subscriptionId,properties,location
| order by tolower(tostring(name)) asc

answered Nov 30 '22 at 18:13

Heriberto Martinez

1
1

You found a way to force a full scan on the data and risk false positives results, as explained in my answer. If you post a question on SO, you might as well read the answers. – David דודו Markovitz Nov 30 '22 at 18:51
According to your example `properties["enabled"]` is boolean, not string. `true` is not qualified. – David דודו Markovitz Nov 30 '22 at 19:07
`name` in `resources` is already of type `string`. – David דודו Markovitz Nov 30 '22 at 19:08

Kusto Query using a bracket with a wildcard

3 Answers3