KQL/Search-AzGraph算子

Question

我刚刚开始使用AzGraph，我正在学习如何使用它的查询，当我试图将密钥库中的IP地址白化时，我遇到了一个问题，下面是我目前正在运行的查询：

Search-AzGraph -Query "resources
|where type == 'microsoft.keyvault/vaults'
|where properties.publicNetworkAccess == 'Enabled'
|mv-expand properties.networkAcls.ipRules
|project kvName = name, kvRule = properties.networkAcls.ipRules"

输出不提供每个保险库的地址列表，而是返回同一金库的一串重复行，这只发生在一定数量的白名单地址之后，我不确定数字：

kvName              kvRule                                                                                                           
------              ------                                                                                                           
Vault1                                                                                                                          
Vault2                                                                                                                  
Vault3              {@{value=1.1.1.1/32}}                                                                                     
Vault4              {@{value=1.1.1.1/32}, @{value=2.2.2.2/32}}                                                             
Vault5              {@{value=1.1.1.1/32}, @{value=2.2.2.2/32}}                                                             
Vault6              {@{value=1.1.1.1/32}}                                                                                      
Vault7                                                                                                                               
Vault8                                                                                                                               
Vault9  <--         {@{value=1.1.1.1/32}, @{value=2.2.2.2/32}, @{value=3.3.3.3/32}, @{value=4.4.4.4/32}...}
Vault9  <--         {@{value=1.1.1.1/32}, @{value=2.2.2.2/32}, @{value=3.3.3.3/32}, @{value=4.4.4.4/32}...}
Vault9  <--         {@{value=1.1.1.1/32}, @{value=2.2.2.2/32}, @{value=3.3.3.3/32}, @{value=4.4.4.4/32}...}
Vault9  <--         {@{value=1.1.1.1/32}, @{value=2.2.2.2/32}, @{value=3.3.3.3/32}, @{value=4.4.4.4/32}...}
Vault9  <--         {@{value=1.1.1.1/32}, @{value=2.2.2.2/32}, @{value=3.3.3.3/32}, @{value=4.4.4.4/32}...}   

我也尝试过扩展属性值，以查看这是否有帮助，但相反，格式更改为下面。

代码：

Search-AzGraph -Query "resources
|where type == 'microsoft.keyvault/vaults'
|where properties.publicNetworkAccess == 'Enabled'
|extend kvRule=parsejson(tostring(properties.networkAcls.ipRules))
|mv-expand kvRule 
|project kvName = name, kvRule.value"

输出：

kvName              kvRule            
------              ------                                     
Vault1                           
Vault2             1.1.1.1/32
Vault3             1.2.3.4/32   
Vault4             5.6.7.8/32   
Vault5             1.2.3.3/32 
Vault6                      
Vault7
Vault8                 
Vault9   <--       1.1.1.1/32   
Vault9   <--       2.2.2.2/32  
Vault9   <--       3.3.3.3/32 
Vault9   <--       4.4.4.4/32   

我遇到了连接运算符，并试图对我的查询使用这些示例，但是失败了，输出总是类似于上面的输出，或者我收到了一个错误：

此查询的输出类似于第二个示例：

Search-AzGraph -Query "Resources 
| join kind=leftouter (resources | where type=='microsoft.keyvault/vaults' | where properties.publicNetworkAccess == 'Enabled' | extend kvRule=parsejson(tostring(properties.networkAcls.ipRules)) | mv-expand kvRule | project id, kvName = name, kvURI = properties.vaultUri, kvRule) on id
| where type == 'microsoft.keyvault/vaults' 
| project id, name, kvType = type, kvLoc = location, kvSub = subscriptionId, kvURI, kvRule= properties.networkAcls.ipRules"

我还尝试了以下查询，该查询错误地指出ipRules是动态的：

Search-AzGraph -Query "Resources
|where type == 'microsoft.keyvault/vaults'
|where properties.publicNetworkAccess == 'Enabled'
|extend kvRule=parsejson(tostring(properties.networkAcls.ipRules))
|project kvID = id, name, kvLoc = location, kvSub = subscriptionId, kvURI = properties.vaultUri, kvRule
| join kind=leftouter (
    Resources
    |where type == 'microsoft.keyvault/vaults'
    |where properties.publicNetworkAccess == 'Enabled'
    |project name, kvRule = tolower(id))
on kvRule
| summarize by name" 

错误：

"code": "InvalidQuery",
"message": "Query is invalid. Please refer to the documentation for the Azure Resource Graph service and fix the error before retrying."

"code": "Default",
"message": "join key 'kvRule' is of a 'dynamic' type. Please use an explicit cast using extend operator in the join legs (for example, '... | extend kvRule = tostring(kvRule) | join (... | extend kvRule = tostring(kvRule)) on kvRule') as join on a 'dynamic' type is not supported."

我很难理解如何使这个查询工作，我真的认为我需要使用join操作符来正确地执行这个查询，但是我对KQL/DB查询没有足够的理解，我希望了解如何正确地执行这个查询。

我的目标是使输出成为一个单一的保险库名称，如果在一行中有所有地址，则kvRule在其白名单中包含一个完整的地址列表：

kvName              kvRule                                                                                                           
------              ------                                                                                                           
Vault1                                                                                                                          
Vault2               1.1.1.1/32, 2.2.2.2/32, 3.3.3.3/32                                                                                                    
Vault3               1.1.1.1/32, 2.2.2.2/32, 3.3.3.3/32, 1.1.1.3/32, 2.2.2.4/32, 3.3.3.1/32 

Answer 1

若要修复查询，而不是

|extend kvRule=parsejson(tostring(properties.networkAcls.ipRules))

搞定

|extend kvRule=tostring(parsejson(properties.networkAcls.ipRules))

为了更好地过滤，您可以考虑使用

| where isnotempty(kvRule)

为了取得明确的结果

kind=inner