CODEWITHSUNDEEP
azurepowershellazure-resource-managerkqlazure-resource-graph
learner
learner

Reputation: 1007

Graph Query to extract Azure resources without a backup, unable to get the user who created the resource

I have got the query below, works correctly however its not returning the createdBy field i.e the user that created the resource. The field comes up as blank. I would appreciate it if I anyone can help alter the query to include the user who created the resource.

    $startTime = [datetime]::UtcNow.AddHours(-$ThresholdHours).ToString('o')

    $searchAzGraphSplat = @{
            Query = "
            resources
            | where type in (
                'microsoft.compute/virtualmachines',
                'microsoft.storage/storageaccounts/fileServices/shares',
                'microsoft.compute/disks',
                'microsoft.sql/servers/databases'
            )
            | extend backupPolicy = properties.backupPolicyId,
                      createdBy = coalesce(tostring(properties.createdBy.userPrincipalName), tostring(identity.principalId))
            | extend timeCreated = todatetime(properties.timeCreated)
            | where timeCreated > todatetime('$startTime')
            | where isnull(backupPolicy) or backupPolicy == ''
            | project name, type, location, resourceGroup, subscriptionId, timeCreated, createdBy
            "
        }


    $result = @()
    do {
        $response = Search-AzGraph @searchAzGraphSplat
        $searchAzGraphSplat['SkipToken'] = $response.SkipToken
        if ($response.Data) {
            $result += $response.Data
        }
    } while ($response.SkipToken)

** EDIT **

New version

Try {


    $startTime = [datetime]::UtcNow.AddHours(-$ThresholdHours).ToString('o')


    $query = @'
resources
| where type in (
    'microsoft.compute/virtualmachines',
    'microsoft.storage/storageaccounts/fileServices/shares',
    'microsoft.compute/disks',
    'microsoft.sql/servers/databases'
)
| extend timeCreated = todatetime(coalesce(
    properties.timeCreated,
    properties.creationDate))
| where timeCreated > todatetime('{0}')
    and isempty(properties.backupPolicyId)
| join kind = leftouter (
    resourcechanges
    | where properties.changeType == 'Create'
    | extend createdTime = todatetime(properties.changeAttributes.timestamp)
    | where createdTime > todatetime('{0}')
    | project
        resourceId = tostring(properties.targetResourceId),
        createdBy = tostring(properties.changeAttributes.changedBy),
        createdByType = tostring(properties.changeAttributes.changedByType)
) on $left.id == $right.resourceId
| project
    name, type, location, resourceGroup,
    subscriptionId, timeCreated, createdBy, createdByType
'@ -f $startTime

$searchAzGraphSplat = @{ Query = $query }
$result = do {
    $response = Search-AzGraph @searchAzGraphSplat
    $searchAzGraphSplat['SkipToken'] = $response.SkipToken
    if ($response.Data) { $response.Data }
}
while ($response.SkipToken)

Upvotes: 0

Views: 33

Answers (1)

Santiago Squarzon
Santiago Squarzon

Reputation: 60838

The reason why the createdBy property comes empty is because the resources of the type you're filtering for don't have such property. In addition, identity.principalId has nothing to do with the principal that created the resource, it refers to the id of the Managed Identity in case the resource has it enabled.

Your best bet if you want to do it all via KQL query is a join with the resourcechanges table, however depending on how old the resource was created, the properties may come empty. Otherwise, the way to go will be by a direct API call to ARM API.

The KQL query joining both tables would be:

$ThresholdHours = # set here ...
$startTime = [datetime]::UtcNow.AddHours(-$ThresholdHours).ToString('o')

$query = @'
resources
| where type in (
    'microsoft.compute/virtualmachines',
    'microsoft.storage/storageaccounts/fileServices/shares',
    'microsoft.compute/disks',
    'microsoft.sql/servers/databases'
)
| extend timeCreated = todatetime(coalesce(
    properties.timeCreated,
    properties.creationDate))
| where timeCreated > todatetime('{0}')
    and isempty(properties.backupPolicyId)
| join kind = leftouter (
    resourcechanges
    | where properties.changeType == 'Create'
    | extend createdTime = todatetime(properties.changeAttributes.timestamp)
    | where createdTime > todatetime('{0}')
    | project
        resourceId = tostring(properties.targetResourceId),
        createdBy = tostring(properties.changeAttributes.changedBy),
        createdByType = tostring(properties.changeAttributes.changedByType)
) on $left.id == $right.resourceId
| project
    name, type, location, resourceGroup,
    subscriptionId, timeCreated, createdBy, createdByType
'@ -f $startTime

$searchAzGraphSplat = @{ Query = $query }
$result = do {
    $response = Search-AzGraph @searchAzGraphSplat
    $searchAzGraphSplat['SkipToken'] = $response.SkipToken
    if ($response.Data) { $response.Data }
}
while ($response.SkipToken)

Upvotes: 0

Related Questions