Automation Studio Query Activity Patterns

Automation audit hierarchy

_AutomationInstance tracks each automation run. _AutomationActivityInstance drills into individual activities (Query, Script, Email). _AutomationTaskInstance covers task-level detail within an activity.

Use these views to diagnose failed query activities, long-running automations, and SLA breaches.

Recent automation failures

SELECT TOP 100
  ai.AutomationName,
  ai.StartTime,
  ai.Status,
  aai.ActivityName,
  aai.Status AS ActivityStatus
FROM _AutomationInstance ai
INNER JOIN _AutomationActivityInstance aai
  ON ai.MemberID = aai.MemberID
WHERE ai.StartTime >= DATEADD(day, -7, GETDATE())
  AND (ai.Status <> 'Complete' OR aai.Status <> 'Complete')
ORDER BY ai.StartTime DESC

Practitioner tips

• Filter StartTime early — automation instance tables grow with every scheduled run.
• Pair with _Sent or DE row counts to validate query activity output volume.
• MemberID links instances within the same automation execution.