CODEWITHSUNDEEP
azurekqlazure-resource-graph
Rajesh Rajamani
Rajesh Rajamani

Reputation: 539

KQL Query for Azure Resource Graph Explorer only returns 30 days of data

My KQL Query to retrieve resources is set to look for events in the last 1 hour upto 90 days in history. However it only returns data until the first day of the current month. Is this is a data limitation or am I setting the parameters incorrect ?

I set up the filter for date in this line changeTime <ago(1h) and changeTime > ago(1h)- 90d

resourcechanges
| extend 
     changeTime = todatetime(properties.changeAttributes.timestamp)
    ,changeType = tostring(properties.changeType)    
    ,changedBy = tostring(properties.changeAttributes.changedBy)
    ,targetResourceType = tostring(properties.targetResourceType)
    ,targetResourceId = tostring(properties.targetResourceId)
| where changeType in ('Create','Delete') and changeTime <ago(1h) and changeTime > ago(1h)- 90d
| join kind=inner (resources | project resources_Name = name, resources_Type = type, resources_Subscription= subscriptionId, resources_ResourceGroup= resourceGroup, id) on $left.targetResourceId == $right.id 
| join kind=inner (resourceContainers | where type=='microsoft.resources/subscriptions' | project SubName=name, subscriptionId, subproperties=properties) on $left.resources_Subscription == $right.subscriptionId
| project resources_Name, resources_Type, resources_Subscription,SubName, resources_ResourceGroup, targetResourceId ,changeTime, changeType, changedBy

Upvotes: 0

Views: 360

Answers (1)

Jahnavi
Jahnavi

Reputation: 7898

Query to retrieve resources is set to look for events in the last 1 hour upto 90 days in history:

In these scenarios, you can use between (left timerange..right timerange) operator available in KQL for events in the last 1 hour up to 90 days in the history.

Modify below line in the code:

where changeType in ('Create','Delete') and changeTime between (ago(90d) .. ago(1h))

Complete KQL query:

resourcechanges
| extend 
     changeTime = todatetime(properties.changeAttributes.timestamp)
    ,changeType = tostring(properties.changeType)    
    ,changedBy = tostring(properties.changeAttributes.changedBy)
    ,targetResourceType = tostring(properties.targetResourceType)
    ,targetResourceId = tostring(properties.targetResourceId)
| where changeType in ('Create','Delete') and changeTime between (ago(90d) .. ago(1h))
| join kind=inner (resources | project resources_Name = name, resources_Type = type, resources_Subscription= subscriptionId, resources_ResourceGroup= resourceGroup, id) on $left.targetResourceId == $right.id 
| join kind=inner (resourcecontainers | where type=='microsoft.resources/subscriptions' | project SubName=name, subscriptionId, subproperties=properties) on $left.resources_Subscription == $right.subscriptionId
| project resources_Name, resources_Type, resources_Subscription,SubName, resources_ResourceGroup, targetResourceId ,changeTime, changeType, changedBy

Output:

enter image description here

Upvotes: 0

Related Questions