1

I've been struggling in KQL to come up with a query for detecting keyvaults which are not accessed in a certain time range for example (30days) and empty keyvaults no secrets, no certificates etc. further a newbie to KQL here, any suggestions?

below the kql statement but not getting the desired output (empty)

 let OneDayAgo = ago(30d);
 AzureActivity
 | where TimeGenerated <= ago(30d)
 | where ResourceProvider == "Microsoft.KeyVault"
 | where OperationName in ("Microsoft.KeyVault/vaults/write", "Microsoft.KeyVault/vaults/delete")
 | extend KeyVaultName = tostring(split(Resource, '/')[8])
 | summarize LatestActivityTime = max(TimeGenerated) by KeyVaultName
 | where isnull(LatestActivityTime) or LatestActivityTime <= ago(30d)
 | project KeyVaultName, LatestActivityTime
ferdy
  • 11
  • 3

1 Answers1

1

Your approach is quite flawed if your target is to identify unused Key Vaults. You are currently looking for events where a Key Vault is created, modified, or deleted. This is a bad approach because:

  1. These do not tell you if a Key Vault is used. These only tell you if the vault itself has been modified, not about the contents (secrets, certificates etc.)
  2. You cannot detect Vaults that have been created before the beginning of your logging period. You most probably do not have everything logged from the beginning.

So how could you reach your goal instead? Well, I would start with the following:

  1. Get a list of all Key Vaults by using Az CLI or Az Powershell (my examples are based on Az CLI) az keyvault list

  2. Check if the KeyVault contains anything with
    az keyvault secret list --vault-name <VAULTNAME>
    az keyvault certificate list --vault-name <VAULTNAME>
    az keyvault key list --vault-name <VAULTNAME>

  3. Finally you can check if the Vaults are in use by using your audit logging (this needs to be enabled, preferably by Azure Policy). The query should look something like

AzureDiagnostics 
| where Resource in ("<VAULTNAME>")
| where OperationName !in 
("VaultGet", "VaultList",
 "SecretList", "SecretListVersions", "KeyList",
 "CertificateList", "CertificateListVersions")

Note that you need to decide what operations mean the Vault is "in-use". In my example, I ignored listing operations that might be created by other monitoring scripts or similar.

Finally, note that Key Vaults can use two different access control methods: RBAC or Access Policies. Getting sufficient rights to run scripts like this is quite easy in the RBAC model: you just need the Key Vault Reader role to all Key Vaults you want to monitor. However, Vaults with Access Policies are much more complicated: you need to assign a reader role to your identity to each and every Vault.

bursson
  • 495
  • 4
  • 14