Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
For information on using these queries in the Azure portal, see Log Analytics tutorial. For the REST API, see Query.
Recent changes on a case
Shows the last 10 field-level changes on a specific case, including who made the change and what was modified.
// Last 10 changes on a specific case (including child entities like tasks, comments, attachments).
// Each row represents one event. Note: a single user action may produce multiple rows
// (one per property changed).
//
// Replace the placeholder below with a case ID
let targetCaseId = '<replace-with-case-id>';
SecurityCaseEvent
| where TimeGenerated >= ago(90d)
| where EntityId == targetCaseId or ParentEntityId == targetCaseId
//| where EntityType == 'Case' // Uncomment to show only case-level changes (no tasks/comments)
//| where OperationName == 'Update' // Uncomment to show only updates (no creates/deletes)
| project EventTime, ModifiedBy, OperationName, EntityType, EntityId, PropertyNames, NewValues
| order by EventTime desc
| take 10
Cases by status
Finds all cases where the current status matches a specific value by replaying Create and Update events.
// Find all cases where the current status matches a specific value.
// Replays Create/Update events to determine each case's latest status.
//
// Replace the placeholder below with the status value to match (e.g. 'Active', 'InProgress')
let targetStatus = '<replace-with-status-value>';
// --- Exclude deleted cases ---
let deletedCases = SecurityCaseEvent
| where TimeGenerated >= ago(30d)
| where EntityType == 'Case'
| where IsDeleted == true
| distinct EntityId;
// --- Determine each case's latest status and type ---
SecurityCaseEvent
| where TimeGenerated >= ago(30d)
| where EntityType == 'Case'
| where OperationName in ('Create', 'Update')
| where EntityId !in (deletedCases)
// mv-expand: expands array properties into individual rows per property change
| mv-expand PropertyNames, NewValues
| extend PropName = tostring(PropertyNames), PropValue = tostring(NewValues)
| where PropName in ('caseStatus.statusName', 'caseType')
// arg_max: keeps only the most recent value per case per property
| summarize arg_max(EventTime, PropValue) by EntityId, PropName
| summarize
Status = take_anyif(PropValue, PropName == 'caseStatus.statusName'),
CaseType = take_anyif(PropValue, PropName == 'caseType')
by EntityId
| where isnotempty(Status)
| where tolower(Status) == tolower(targetStatus)
//| where tolower(Status) != tolower(targetStatus) // Uncomment to exclude this status instead
//| where CaseType == '<case-type>' // Uncomment to filter by case type
| project EntityId, Status, CaseType
Cases with most open tasks
Ranks cases by number of open tasks, excluding deleted tasks and cases.
// Ranks cases by number of open tasks, excluding deleted tasks and cases.
//
// --- Exclude deleted cases and tasks ---
let deletedCases = SecurityCaseEvent
| where TimeGenerated >= ago(90d)
| where EntityType == 'Case'
| where IsDeleted == true
| distinct EntityId;
let deletedTasks = SecurityCaseEvent
| where TimeGenerated >= ago(90d)
| where EntityType == 'CaseTask'
| where IsDeleted == true
| distinct EntityId;
// --- Find the latest status of each task and count open ones per case ---
SecurityCaseEvent
| where TimeGenerated >= ago(90d)
| where EntityType == 'CaseTask'
| where OperationName in ('Create', 'Update')
| where EntityId !in (deletedTasks)
| where ParentEntityId !in (deletedCases)
// mv-expand: expands array properties into individual rows
| mv-expand PropertyNames, NewValues
| extend Status = tostring(NewValues)
| where PropertyNames == 'status'
// arg_max: keeps the latest status value per task
| summarize arg_max(EventTime, Status) by EntityId, ParentEntityId
// Keep only open tasks
| where tolower(Status) !in ('completed', 'skipped', 'failed')
//| where tolower(Status) == 'inprogress' // Uncomment to count only in-progress tasks
| summarize OpenTaskCount = count() by CaseId = ParentEntityId
| order by OpenTaskCount desc
Inactive cases
Finds cases with no activity (including child entity activity) in the last 7 days, excluding recently created and deleted cases.
// Cases with no activity in the last 7 days (excluding deleted and closed cases).
// "Activity" includes any event on the case itself or its child entities (tasks, comments, etc.).
//
// Parameters you can adjust:
let creationWindow = 21d; // How far back to look for case creation
let inactiveDays = 7d; // How many days of silence counts as "inactive"
//
// --- Exclude deleted cases ---
let deletedCases = SecurityCaseEvent
| where TimeGenerated >= ago(creationWindow)
| where EntityType == 'Case'
| where IsDeleted == true
| distinct EntityId;
// --- Exclude closed cases (based on latest topLevelStatusName) ---
let closedCases = SecurityCaseEvent
| where TimeGenerated >= ago(creationWindow)
| where EntityType == 'Case'
| where OperationName in ('Create', 'Update')
| mv-expand PropertyNames, NewValues
| extend PropName = tostring(PropertyNames), PropValue = tostring(NewValues)
| where PropName == 'caseStatus.topLevelStatusName'
// arg_max: get the most recent status value per case
| summarize arg_max(EventTime, PropValue) by EntityId
| where tolower(PropValue) == 'closed'
| distinct EntityId;
// --- All candidate cases: created within the window but old enough to be "inactive" ---
let allCases = SecurityCaseEvent
| where TimeGenerated >= ago(creationWindow)
| where EntityType == 'Case'
| where OperationName == 'Create'
| where EventTime between (ago(creationWindow) .. ago(inactiveDays))
| where EntityId !in (deletedCases)
| where EntityId !in (closedCases)
| distinct CaseId = EntityId;
// --- Find last activity time per case and determine if active (including child entity activity) ---
let caseActivity = SecurityCaseEvent
| where TimeGenerated >= ago(creationWindow)
// For child entities, map back to the parent case
| extend CaseId = iff(EntityType == 'Case', EntityId, ParentEntityId)
| where isnotempty(CaseId)
| summarize LastActivityTime = max(EventTime) by CaseId;
// --- Look up CaseType for each case ---
let caseTypes = SecurityCaseEvent
| where TimeGenerated >= ago(creationWindow)
| where EntityType == 'Case'
| where OperationName in ('Create', 'Update')
| mv-expand PropertyNames, NewValues
| extend PropName = tostring(PropertyNames), PropValue = tostring(NewValues)
| where PropName == 'caseType'
| summarize arg_max(EventTime, PropValue) by EntityId
| project EntityId, CaseType = PropValue;
// --- Result: cases with zero recent activity ---
allCases
| join kind=leftouter caseActivity on CaseId
| where LastActivityTime < ago(inactiveDays)
| join kind=leftouter caseTypes on $left.CaseId == $right.EntityId
//| where CaseType == '<case-type>' // Uncomment to filter by case type
| project EntityId = CaseId, CaseType, LastActivityTime
| order by LastActivityTime asc
Urgent open cases
Lists cases with Critical or High priority that are not in a closed status.
// Cases with Critical or High priority that are not closed.
// Reconstructs the latest property values per case, then filters for
// urgent (High/Critical) cases that remain open.
//
// --- Exclude deleted cases ---
let deletedCases = SecurityCaseEvent
| where TimeGenerated >= ago(90d)
| where EntityType == 'Case'
| where IsDeleted == true
| distinct EntityId;
// --- Reconstruct latest case properties via pivot ---
let CaseProperties =
SecurityCaseEvent
| where TimeGenerated >= ago(90d)
| where EntityType == 'Case'
| where OperationName in ('Create', 'Update')
| where EntityId !in (deletedCases)
// mv-expand: expands array properties into individual rows per property change
| mv-expand PropertyNames, NewValues
| extend PropName = tostring(PropertyNames), PropValue = tostring(NewValues)
| extend NormProp = case(
PropName in ('priority', 'severity'), 'Priority',
PropName == 'caseStatus.statusName', 'Status',
PropName == 'caseStatus.topLevelStatusName', 'TopLevelStatus',
PropName == 'displayName', 'DisplayName',
PropName == 'caseType', 'CaseType',
'')
| where NormProp != ''
// arg_max: keeps the most recent value per case per property
| summarize arg_max(EventTime, PropValue) by EntityId, NormProp
// project before pivot to avoid unintended grouping on EventTime
| project EntityId, NormProp, PropValue
// evaluate pivot: turns rows into columns (one column per property)
| evaluate pivot(NormProp, take_any(PropValue));
// --- Filter and output ---
// column_ifexists: safely accesses pivot columns that may not exist if no case has that property
CaseProperties
| extend
Priority = tostring(column_ifexists('Priority', '')),
Status = tostring(column_ifexists('Status', '')),
TopLevelStatus = tostring(column_ifexists('TopLevelStatus', '')),
DisplayName = tostring(column_ifexists('DisplayName', '')),
CaseType = tostring(column_ifexists('CaseType', ''))
| where tolower(Priority) in ('high', 'critical')
| where tolower(Status) !in ('closed') and tolower(TopLevelStatus) !in ('closed')
//| where tolower(Priority) == 'critical' // Uncomment to show only Critical
//| where CaseType == '<case-type>' // Uncomment to filter by case type
//| where AssignedTo has '<user>' // Uncomment to filter by assignee
| project EntityId, DisplayName, Priority, Status, TopLevelStatus, CaseType
| order by Priority asc
Cases with overdue tasks
Identifies cases that have open tasks past their due date, ranked by overdue task count.
// Cases with open tasks past their due date, ranked by overdue count.
// Replays task property events to find each task's latest status and due date,
// then filters for tasks that are open and past due.
//
// --- Exclude deleted cases and tasks ---
let deletedCases = SecurityCaseEvent
| where TimeGenerated >= ago(90d)
| where EntityType == 'Case'
| where IsDeleted == true
| distinct EntityId;
let deletedTasks = SecurityCaseEvent
| where TimeGenerated >= ago(90d)
| where EntityType == 'CaseTask'
| where IsDeleted == true
| distinct EntityId;
// --- Reconstruct latest task properties via pivot ---
SecurityCaseEvent
| where TimeGenerated >= ago(90d)
| where EntityType == 'CaseTask'
| where OperationName in ('Create', 'Update')
| where EntityId !in (deletedTasks)
| where ParentEntityId !in (deletedCases)
// mv-expand: expands array properties into individual rows per property change
| mv-expand PropertyNames, NewValues
| extend PropertyValue = tostring(NewValues)
// Map only the properties we need for this query
| extend PivotProperty = case(
PropertyNames == 'status', 'status',
PropertyNames == 'dueDateTime', 'dueDateTime',
''
)
| where PivotProperty != ''
// arg_max: keeps the most recent value of each property per task
| summarize arg_max(EventTime, PropertyValue) by TaskId = EntityId, CaseId = ParentEntityId, PivotProperty
// project before pivot to avoid unintended grouping on EventTime
| project TaskId, CaseId, PivotProperty, PropertyValue
// evaluate pivot: transforms rows into columns (one column for 'status', one for 'dueDateTime')
| evaluate pivot(PivotProperty, take_any(PropertyValue))
// column_ifexists: safely accesses a column that may not exist if no task ever had that property set
| extend status = tostring(column_ifexists('status', '')),
dueDateTime = tostring(column_ifexists('dueDateTime', ''))
| where isnotempty(CaseId)
// Filter for open tasks only (exclude completed/skipped/failed)
| where tolower(status) !in ('completed', 'skipped', 'failed')
// Filter for tasks past their due date
| where todatetime(dueDateTime) < now()
//| where todatetime(dueDateTime) < ago(7d) // Uncomment to find tasks overdue by more than 7 days
| summarize OverdueTaskCount = count(), EarliestDueDate = min(todatetime(dueDateTime)) by CaseId
| order by OverdueTaskCount desc
Case tasks created daily
Daily count of new tasks created over the last 30 days.
// Daily task creation volume over the last 30 days.
// Simple trend query — useful for spotting spikes in workload.
SecurityCaseEvent
| where TimeGenerated >= ago(30d)
| where EntityType == "CaseTask"
| where OperationName == "Create"
//| where ParentEntityId == '<case-id>' // Uncomment to filter by a specific case
| summarize TasksCreated = count() by Day = startofday(EventTime)
| order by Day
All case management activity by a specific user
Shows all case management activity (cases, tasks, relations) by a specific user in the last 30 days, grouped by case.
// All case management activity (cases, tasks, relations) by a specific user in the last 30 days, grouped by case.
// Shows the time range and types of operations each user performed on each case.
//
// Replace the placeholder below with a user alias or UPN
let targetUser = '<replace-with-user-alias>';
let timeRange = 30d; // Adjust to widen/narrow the search window
SecurityCaseEvent
| where TimeGenerated >= ago(timeRange)
| where ModifiedBy has targetUser
// Map child entities (tasks, comments, etc.) back to their parent case
| extend CaseId = iff(EntityType == 'Case', EntityId, ParentEntityId)
| where isnotempty(CaseId)
| summarize
FirstEvent = min(EventTime),
LastEvent = max(EventTime),
OperationsSet = make_set(OperationName),
EntityTypes = make_set(EntityType)
by CaseId
//| where OperationsSet has 'Delete' // Uncomment to show only cases where user performed deletions
//| where EntityTypes has 'CaseTask' // Uncomment to show only cases where user touched tasks
| order by LastEvent desc
Cases and tasks assigned to a specific user
Finds all cases and tasks currently assigned to a specific user, excluding deleted entities.
// All cases and tasks currently assigned to a specific user.
// Replays 'assignedTo' property changes to find the latest assignment per entity.
//
// Replace the placeholder below with a user alias or UPN
let targetUser = '<replace-with-user-alias>';
// --- Exclude deleted cases and tasks ---
let deletedCases = SecurityCaseEvent
| where TimeGenerated >= ago(90d)
| where EntityType == 'Case'
| where IsDeleted == true
| distinct EntityId;
let deletedTasks = SecurityCaseEvent
| where TimeGenerated >= ago(90d)
| where EntityType == 'CaseTask'
| where IsDeleted == true
| distinct EntityId;
// --- Find the latest 'assignedTo' value for each case and task ---
SecurityCaseEvent
| where TimeGenerated >= ago(90d)
| where EntityType in ('Case', 'CaseTask')
| where OperationName in ('Create', 'Update')
| extend CaseId = iff(EntityType == 'Case', EntityId, ParentEntityId)
| where CaseId !in (deletedCases)
| where EntityId !in (deletedTasks)
// mv-expand: expands array properties into individual rows per property change
| mv-expand PropertyNames, NewValues
| extend
PropertyName = tostring(PropertyNames),
PropertyValue = tostring(NewValues)
| where PropertyName == 'assignedTo'
// arg_max: keeps the most recent assignment per entity
| summarize arg_max(EventTime, PropertyValue, CaseId) by EntityType, EntityId
| where PropertyValue has targetUser
//| where EntityType == 'CaseTask' // Uncomment to show only tasks
| project
EntityType,
EntityId,
CaseId,
AssignedTo = PropertyValue,
LastAssignmentTime = EventTime
| order by LastAssignmentTime desc
Users with most open case tasks
Counts open tasks assigned to each user, excluding deleted tasks and cases.
// Users ranked by number of open tasks assigned to them.
// Reconstructs each task's latest status and assignee, then counts open tasks per user.
//
// --- Exclude deleted cases and tasks ---
let deletedCases = SecurityCaseEvent
| where TimeGenerated >= ago(90d)
| where EntityType == 'Case'
| where IsDeleted == true
| distinct EntityId;
let deletedTasks = SecurityCaseEvent
| where TimeGenerated >= ago(90d)
| where EntityType == 'CaseTask'
| where IsDeleted == true
| distinct EntityId;
// --- Reconstruct latest task properties via pivot ---
SecurityCaseEvent
| where TimeGenerated >= ago(90d)
| where EntityType == 'CaseTask'
| where OperationName in ('Create', 'Update')
| where EntityId !in (deletedTasks)
| where ParentEntityId !in (deletedCases)
// mv-expand: expands array properties into individual rows per property change
| mv-expand PropertyNames, NewValues
| extend PropertyValue = tostring(NewValues)
// Map only the properties we care about
| extend PivotProperty = case(
PropertyNames == 'status', 'status',
PropertyNames == 'assignedTo', 'assignedTo',
''
)
| where PivotProperty != ''
// arg_max: keeps the most recent value of each property per task
| summarize arg_max(EventTime, PropertyValue) by TaskId = EntityId, PivotProperty
// project before pivot to avoid unintended grouping on EventTime
| project TaskId, PivotProperty, PropertyValue
// evaluate pivot: transforms rows into columns (one for 'status', one for 'assignedTo')
| evaluate pivot(PivotProperty, take_any(PropertyValue))
// column_ifexists: safely accesses columns that may not exist if no task has that property
| extend status = column_ifexists('status', dynamic(null)),
assignedTo = tostring(column_ifexists('assignedTo', 'Unassigned'))
// Keep only open tasks (exclude completed/skipped/failed)
| where tolower(tostring(status)) !in ('completed', 'skipped', 'failed') or isnull(status)
| extend assignedTo = iff(isempty(assignedTo), 'Unassigned', assignedTo)
| where assignedTo != 'Unassigned'
//| where assignedTo has '<user>' // Uncomment to filter for a specific user
| summarize OpenTaskCount = count() by assignedTo
| order by OpenTaskCount desc
Full cases snapshot (all cases)
Reconstructs the current state of all cases changed within a time window, including tasks, comments, attachments, tags, and relations as JSON arrays.
// Reconstruct the current state of ALL cases changed within a time window, including child entities.
// Same approach as SingleCaseSnapshot but without filtering to a specific case ID.
// Output: one row per case with Tasks, Comments, Attachments, Tags, and Relations as JSON arrays.
//
// How it works:
// 1. Find all non-deleted cases with events in the lookback window
// 2. For each case property, find the latest value via arg_max
// 3. Repeat for child entities (tasks, comments, attachments, relations)
// 4. Join into a single row per case
//
let lookback = 90d;
//
// ============ STEP 1: Identify non-deleted cases ============
let deletedCases = SecurityCaseEvent
| where TimeGenerated >= ago(lookback)
| where EntityType == 'Case'
| where IsDeleted == true
| distinct EntityId;
let ChangedCaseIds = SecurityCaseEvent
| where TimeGenerated >= ago(lookback)
| where EntityType == 'Case'
| where EntityId !in (deletedCases)
| distinct CaseId = EntityId;
//
// ============ STEP 2: Reconstruct case-level properties ============
let CaseSnapshot = SecurityCaseEvent
| where TimeGenerated >= ago(lookback)
| where EntityType == 'Case'
| where IsDeleted == false
| where EntityId in (ChangedCaseIds)
| where OperationName in ('Create', 'Update')
| mv-expand PropertyNames, NewValues
| extend PropName = tostring(PropertyNames), PropValue = tostring(NewValues)
// arg_max: for each property, keep the value from the most recent event
| summarize arg_max(EventTime, PropValue, EntityCreatedTime) by EntityId, PropName
| summarize
DisplayName = take_anyif(PropValue, PropName == 'displayName'),
StatusName = take_anyif(PropValue, PropName == 'caseStatus.statusName'),
TopLevelStatus = take_anyif(PropValue, PropName == 'caseStatus.topLevelStatusName'),
Severity = take_anyif(PropValue, PropName == 'severity'), // Incident cases
Priority = take_anyif(PropValue, PropName == 'priority'), // Default cases
AssignedTo = take_anyif(PropValue, PropName == 'assignedTo'),
Classification = take_anyif(PropValue, PropName == 'classification'),
Determination = take_anyif(PropValue, PropName == 'determination'),
Description = take_anyif(PropValue, PropName == 'description'),
CaseType = take_anyif(PropValue, PropName == 'caseType'),
CreatedTime = take_anyif(PropValue, PropName == 'createdTime'),
EntityCreatedTime = take_any(EntityCreatedTime),
CustomFields = take_anyif(PropValue, PropName == 'customFields'),
ClosingNotes = take_anyif(PropValue, PropName == 'closingNotes'),
SlaAssignmentStatus = take_anyif(PropValue, PropName == 'sla.assignmentStatus'),
SlaResolutionStatus = take_anyif(PropValue, PropName == 'sla.resolutionStatus'),
SlaPolicyName = take_anyif(PropValue, PropName == 'sla.policyName'),
SlaPolicyId = take_anyif(PropValue, PropName == 'sla.policyId'),
SlaPolicyAppliedDateTime = take_anyif(PropValue, PropName == 'sla.policyAppliedDateTime')
by EntityId;
//
// ============ STEP 3: Reconstruct child entities ============
// --- Tasks ---
let ActiveTasks = SecurityCaseEvent
| where TimeGenerated >= ago(lookback)
| where EntityType == 'CaseTask'
| where ParentEntityId in (ChangedCaseIds)
| summarize arg_max(EventTime, IsDeleted, OperationName) by EntityId
| where IsDeleted == false and OperationName != 'Delete'
| project TaskId = EntityId;
let TasksJson = SecurityCaseEvent
| where TimeGenerated >= ago(lookback)
| where EntityType == 'CaseTask'
| where EntityId in (ActiveTasks)
| where OperationName in ('Create', 'Update')
| mv-expand PropertyNames, NewValues
| extend PropName = tostring(PropertyNames), PropValue = tostring(NewValues)
| summarize arg_max(EventTime, PropValue, EntityCreatedTime) by EntityId, ParentEntityId, PropName
| summarize TaskProps = make_bag(pack(PropName, PropValue)), EntityCreatedTime = take_any(EntityCreatedTime) by TaskId = EntityId, CaseId = ParentEntityId
| extend Task = bag_merge(TaskProps, bag_pack('taskId', TaskId, 'entityCreatedTime', tostring(EntityCreatedTime)))
| summarize Tasks = make_list(Task) by EntityId = CaseId;
// --- Comments ---
let ActiveComments = SecurityCaseEvent
| where TimeGenerated >= ago(lookback)
| where EntityType == 'CaseComment'
| where ParentEntityId in (ChangedCaseIds)
| summarize arg_max(EventTime, IsDeleted, OperationName) by EntityId
| where IsDeleted == false and OperationName != 'Delete'
| project CommentId = EntityId;
let CommentsJson = SecurityCaseEvent
| where TimeGenerated >= ago(lookback)
| where EntityType == 'CaseComment'
| where EntityId in (ActiveComments)
| where OperationName in ('Create', 'Update')
| mv-expand PropertyNames, NewValues
| extend PropName = tostring(PropertyNames), PropValue = tostring(NewValues)
| summarize arg_max(EventTime, PropValue, EntityCreatedTime) by EntityId, ParentEntityId, PropName
| summarize CommentProps = make_bag(pack(PropName, PropValue)), EntityCreatedTime = take_any(EntityCreatedTime) by CommentId = EntityId, CaseId = ParentEntityId
| extend Comment = bag_merge(CommentProps, bag_pack('commentId', CommentId, 'entityCreatedTime', tostring(EntityCreatedTime)))
| summarize Comments = make_list(Comment) by EntityId = CaseId;
// --- Attachments ---
let ActiveAttachments = SecurityCaseEvent
| where TimeGenerated >= ago(lookback)
| where EntityType == 'CaseAttachment'
| where ParentEntityId in (ChangedCaseIds)
| summarize arg_max(EventTime, IsDeleted, OperationName) by EntityId
| where IsDeleted == false and OperationName != 'Delete'
| project AttachmentId = EntityId;
let AttachmentsJson = SecurityCaseEvent
| where TimeGenerated >= ago(lookback)
| where EntityType == 'CaseAttachment'
| where EntityId in (ActiveAttachments)
| where OperationName in ('Create', 'Update')
| mv-expand PropertyNames, NewValues
| extend PropName = tostring(PropertyNames), PropValue = tostring(NewValues)
| summarize arg_max(EventTime, PropValue, EntityCreatedTime) by EntityId, ParentEntityId, PropName
| summarize AttachmentProps = make_bag(pack(PropName, PropValue)), EntityCreatedTime = take_any(EntityCreatedTime) by AttachmentId = EntityId, CaseId = ParentEntityId
| extend Attachment = bag_merge(AttachmentProps, bag_pack('attachmentId', AttachmentId, 'entityCreatedTime', tostring(EntityCreatedTime)))
| summarize Attachments = make_list(Attachment) by EntityId = CaseId;
// --- Tags ---
let CaseTags = SecurityCaseEvent
| where TimeGenerated >= ago(lookback)
| where EntityType == 'Case'
| where EntityId in (ChangedCaseIds)
| where OperationName in ('Create', 'Update')
| where PropertyNames has 'tags'
| extend TagIdx = array_index_of(PropertyNames, 'tags')
| extend Tags = iff(TagIdx >= 0, NewValues[TagIdx], NewValues)
| summarize arg_max(EventTime, Tags) by EntityId
| project EntityId, Tags;
// --- Relations ---
let ActiveRelations = SecurityCaseEvent
| where TimeGenerated >= ago(lookback)
| where EntityType == 'CaseRelation'
| where ParentEntityId in (ChangedCaseIds)
| summarize arg_max(EventTime, IsDeleted, OperationName) by EntityId
| where IsDeleted == false and OperationName !in ('Delete', 'Unlink')
| project RelationId = EntityId;
let RelationsJson = SecurityCaseEvent
| where TimeGenerated >= ago(lookback)
| where EntityType == 'CaseRelation'
| where EntityId in (ActiveRelations)
| where OperationName in ('Create', 'Update', 'Link')
| mv-expand PropertyNames, NewValues
| extend PropName = tostring(PropertyNames), PropValue = tostring(NewValues)
| summarize arg_max(EventTime, PropValue, EntityCreatedTime) by EntityId, ParentEntityId, PropName
| summarize RelationProps = make_bag(pack(PropName, PropValue)), EntityCreatedTime = take_any(EntityCreatedTime) by RelationId = EntityId, CaseId = ParentEntityId
| extend Relation = bag_merge(RelationProps, bag_pack('relationId', RelationId, 'entityCreatedTime', tostring(EntityCreatedTime)))
| summarize Relations = make_list(Relation) by EntityId = CaseId;
//
// ============ STEP 4: Join all pieces into final output ============
CaseSnapshot
| join kind=leftouter TasksJson on EntityId
| join kind=leftouter CommentsJson on EntityId
| join kind=leftouter AttachmentsJson on EntityId
| join kind=leftouter RelationsJson on EntityId
| join kind=leftouter CaseTags on EntityId
| project
EntityId, DisplayName, StatusName, TopLevelStatus, Severity, Priority,
AssignedTo, Classification, Determination, Description,
Tags = coalesce(Tags, dynamic([])), CaseType, CreatedTime, EntityCreatedTime, CustomFields, ClosingNotes,
SlaAssignmentStatus, SlaResolutionStatus, SlaPolicyName, SlaPolicyId, SlaPolicyAppliedDateTime,
Tasks = coalesce(Tasks, dynamic([])),
Comments = coalesce(Comments, dynamic([])),
Attachments = coalesce(Attachments, dynamic([])),
Relations = coalesce(Relations, dynamic([]))
Single case snapshot
Reconstructs the complete current state of a single case including all child entities.
// Reconstruct the complete current state of a single case including all child entities.
// This query replays all events to build a full "snapshot" of a case as it exists now,
// including tasks, comments, attachments, tags, and relations as JSON arrays.
//
// How it works:
// 1. Identify the target case (exclude if deleted)
// 2. For each property, find the latest value via arg_max (most recent event wins)
// 3. Repeat for each child entity type (tasks, comments, attachments, relations)
// 4. Join everything together into a single output row per case
//
// Replace with the case ID you want to inspect
let targetCaseId = '<replace-with-case-id>';
let lookback = 90d;
//
// ============ STEP 1: Exclude deleted cases ============
let deletedCases = SecurityCaseEvent
| where TimeGenerated >= ago(lookback)
| where EntityType == 'Case'
| where IsDeleted == true
| distinct EntityId;
let ChangedCaseIds = SecurityCaseEvent
| where TimeGenerated >= ago(lookback)
| where EntityType == 'Case'
| where EntityId == targetCaseId
| where EntityId !in (deletedCases)
| distinct CaseId = EntityId;
//
// ============ STEP 2: Reconstruct case-level properties ============
let CaseSnapshot = SecurityCaseEvent
| where TimeGenerated >= ago(lookback)
| where EntityType == 'Case'
| where IsDeleted == false
| where EntityId in (ChangedCaseIds)
| where OperationName in ('Create', 'Update')
// mv-expand: expand array properties into one row per property
| mv-expand PropertyNames, NewValues
| extend PropName = tostring(PropertyNames), PropValue = tostring(NewValues)
// arg_max: for each property, keep the value from the most recent event
| summarize arg_max(EventTime, PropValue, EntityCreatedTime) by EntityId, PropName
// take_anyif: pivot properties into named columns
| summarize
DisplayName = take_anyif(PropValue, PropName == 'displayName'),
StatusName = take_anyif(PropValue, PropName == 'caseStatus.statusName'),
TopLevelStatus = take_anyif(PropValue, PropName == 'caseStatus.topLevelStatusName'),
Severity = take_anyif(PropValue, PropName == 'severity'), // Incident cases
Priority = take_anyif(PropValue, PropName == 'priority'), // Default cases
AssignedTo = take_anyif(PropValue, PropName == 'assignedTo'),
Classification = take_anyif(PropValue, PropName == 'classification'),
Determination = take_anyif(PropValue, PropName == 'determination'),
Description = take_anyif(PropValue, PropName == 'description'),
CaseType = take_anyif(PropValue, PropName == 'caseType'),
CreatedTime = take_anyif(PropValue, PropName == 'createdTime'),
EntityCreatedTime = take_any(EntityCreatedTime),
CustomFields = take_anyif(PropValue, PropName == 'customFields'),
ClosingNotes = take_anyif(PropValue, PropName == 'closingNotes'),
SlaAssignmentStatus = take_anyif(PropValue, PropName == 'sla.assignmentStatus'),
SlaResolutionStatus = take_anyif(PropValue, PropName == 'sla.resolutionStatus'),
SlaPolicyName = take_anyif(PropValue, PropName == 'sla.policyName'),
SlaPolicyId = take_anyif(PropValue, PropName == 'sla.policyId'),
SlaPolicyAppliedDateTime = take_anyif(PropValue, PropName == 'sla.policyAppliedDateTime')
by EntityId;
//
// ============ STEP 3: Reconstruct child entities ============
// --- Tasks ---
let ActiveTasks = SecurityCaseEvent
| where TimeGenerated >= ago(lookback)
| where EntityType == 'CaseTask'
| where ParentEntityId in (ChangedCaseIds)
| summarize arg_max(EventTime, IsDeleted, OperationName) by EntityId
| where IsDeleted == false and OperationName != 'Delete'
| project TaskId = EntityId;
let TasksJson = SecurityCaseEvent
| where TimeGenerated >= ago(lookback)
| where EntityType == 'CaseTask'
| where EntityId in (ActiveTasks)
| where OperationName in ('Create', 'Update')
| mv-expand PropertyNames, NewValues
| extend PropName = tostring(PropertyNames), PropValue = tostring(NewValues)
| summarize arg_max(EventTime, PropValue, EntityCreatedTime) by EntityId, ParentEntityId, PropName
// make_bag + bag_merge: pack all properties into a single JSON object per task
| summarize TaskProps = make_bag(pack(PropName, PropValue)), EntityCreatedTime = take_any(EntityCreatedTime) by TaskId = EntityId, CaseId = ParentEntityId
| extend Task = bag_merge(TaskProps, bag_pack('taskId', TaskId, 'entityCreatedTime', tostring(EntityCreatedTime)))
| summarize Tasks = make_list(Task) by EntityId = CaseId;
// --- Comments ---
let ActiveComments = SecurityCaseEvent
| where TimeGenerated >= ago(lookback)
| where EntityType == 'CaseComment'
| where ParentEntityId in (ChangedCaseIds)
| summarize arg_max(EventTime, IsDeleted, OperationName) by EntityId
| where IsDeleted == false and OperationName != 'Delete'
| project CommentId = EntityId;
let CommentsJson = SecurityCaseEvent
| where TimeGenerated >= ago(lookback)
| where EntityType == 'CaseComment'
| where EntityId in (ActiveComments)
| where OperationName in ('Create', 'Update')
| mv-expand PropertyNames, NewValues
| extend PropName = tostring(PropertyNames), PropValue = tostring(NewValues)
| summarize arg_max(EventTime, PropValue, EntityCreatedTime) by EntityId, ParentEntityId, PropName
| summarize CommentProps = make_bag(pack(PropName, PropValue)), EntityCreatedTime = take_any(EntityCreatedTime) by CommentId = EntityId, CaseId = ParentEntityId
| extend Comment = bag_merge(CommentProps, bag_pack('commentId', CommentId, 'entityCreatedTime', tostring(EntityCreatedTime)))
| summarize Comments = make_list(Comment) by EntityId = CaseId;
// --- Attachments ---
let ActiveAttachments = SecurityCaseEvent
| where TimeGenerated >= ago(lookback)
| where EntityType == 'CaseAttachment'
| where ParentEntityId in (ChangedCaseIds)
| summarize arg_max(EventTime, IsDeleted, OperationName) by EntityId
| where IsDeleted == false and OperationName != 'Delete'
| project AttachmentId = EntityId;
let AttachmentsJson = SecurityCaseEvent
| where TimeGenerated >= ago(lookback)
| where EntityType == 'CaseAttachment'
| where EntityId in (ActiveAttachments)
| where OperationName in ('Create', 'Update')
| mv-expand PropertyNames, NewValues
| extend PropName = tostring(PropertyNames), PropValue = tostring(NewValues)
| summarize arg_max(EventTime, PropValue, EntityCreatedTime) by EntityId, ParentEntityId, PropName
| summarize AttachmentProps = make_bag(pack(PropName, PropValue)), EntityCreatedTime = take_any(EntityCreatedTime) by AttachmentId = EntityId, CaseId = ParentEntityId
| extend Attachment = bag_merge(AttachmentProps, bag_pack('attachmentId', AttachmentId, 'entityCreatedTime', tostring(EntityCreatedTime)))
| summarize Attachments = make_list(Attachment) by EntityId = CaseId;
// --- Tags ---
let CaseTags = SecurityCaseEvent
| where TimeGenerated >= ago(lookback)
| where EntityType == 'Case'
| where EntityId in (ChangedCaseIds)
| where OperationName in ('Create', 'Update')
| where PropertyNames has 'tags'
| extend TagIdx = array_index_of(PropertyNames, 'tags')
| extend Tags = iff(TagIdx >= 0, NewValues[TagIdx], NewValues)
| summarize arg_max(EventTime, Tags) by EntityId
| project EntityId, Tags;
// --- Relations ---
let ActiveRelations = SecurityCaseEvent
| where TimeGenerated >= ago(lookback)
| where EntityType == 'CaseRelation'
| where ParentEntityId in (ChangedCaseIds)
| summarize arg_max(EventTime, IsDeleted, OperationName) by EntityId
| where IsDeleted == false and OperationName !in ('Delete', 'Unlink')
| project RelationId = EntityId;
let RelationsJson = SecurityCaseEvent
| where TimeGenerated >= ago(lookback)
| where EntityType == 'CaseRelation'
| where EntityId in (ActiveRelations)
| where OperationName in ('Create', 'Update', 'Link')
| mv-expand PropertyNames, NewValues
| extend PropName = tostring(PropertyNames), PropValue = tostring(NewValues)
| summarize arg_max(EventTime, PropValue, EntityCreatedTime) by EntityId, ParentEntityId, PropName
| summarize RelationProps = make_bag(pack(PropName, PropValue)), EntityCreatedTime = take_any(EntityCreatedTime) by RelationId = EntityId, CaseId = ParentEntityId
| extend Relation = bag_merge(RelationProps, bag_pack('relationId', RelationId, 'entityCreatedTime', tostring(EntityCreatedTime)))
| summarize Relations = make_list(Relation) by EntityId = CaseId;
//
// ============ STEP 4: Join all pieces into final output ============
CaseSnapshot
| join kind=leftouter TasksJson on EntityId
| join kind=leftouter CommentsJson on EntityId
| join kind=leftouter AttachmentsJson on EntityId
| join kind=leftouter RelationsJson on EntityId
| join kind=leftouter CaseTags on EntityId
| project
EntityId, DisplayName, StatusName, TopLevelStatus, Severity, Priority,
AssignedTo, Classification, Determination, Description,
Tags = coalesce(Tags, dynamic([])), CaseType, CreatedTime, EntityCreatedTime, CustomFields, ClosingNotes,
SlaAssignmentStatus, SlaResolutionStatus, SlaPolicyName, SlaPolicyId, SlaPolicyAppliedDateTime,
Tasks = coalesce(Tasks, dynamic([])),
Comments = coalesce(Comments, dynamic([])),
Attachments = coalesce(Attachments, dynamic([])),
Relations = coalesce(Relations, dynamic([]))
Point-in-time single case snapshot
Reconstructs a case's state as of a specific historical timestamp (time-travel), enabling forensic investigation of past case states.
// Reconstruct a case's state as of a specific historical timestamp (time-travel).
// Useful for forensic investigation: "What did case X look like on date Y?"
//
// Unlike the other snapshots, this query intentionally has NO TimeGenerated filter
// because it needs to scan all history back to the case's creation.
//
// Replace these parameters with your target case and desired point in time:
let targetCaseId = '<replace-with-case-id>';
// Example: datetime(2026-05-13T14:30:00Z)
let snapshotTime = datetime('<replace-with-datetime>');
//
// ============ STEP 1: Exclude cases deleted before snapshot time ============
let deletedCases = SecurityCaseEvent
| where EntityType == 'Case'
| where EventTime <= snapshotTime
| where IsDeleted == true
| distinct EntityId;
let ChangedCaseIds = SecurityCaseEvent
| where EntityType == 'Case'
| where EntityId == targetCaseId
| where EntityId !in (deletedCases)
| distinct CaseId = EntityId;
//
// ============ STEP 2: Reconstruct case properties as of snapshotTime ============
let CaseSnapshot = SecurityCaseEvent
| where EntityType == 'Case'
| where IsDeleted == false
| where EntityId in (ChangedCaseIds)
| where OperationName in ('Create', 'Update')
| where EventTime <= snapshotTime
| mv-expand PropertyNames, NewValues
| extend PropName = tostring(PropertyNames), PropValue = tostring(NewValues)
// arg_max: for each property, keep the latest value that occurred BEFORE snapshotTime
| summarize arg_max(EventTime, PropValue, EntityCreatedTime) by EntityId, PropName
| summarize
DisplayName = take_anyif(PropValue, PropName == 'displayName'),
StatusName = take_anyif(PropValue, PropName == 'caseStatus.statusName'),
TopLevelStatus = take_anyif(PropValue, PropName == 'caseStatus.topLevelStatusName'),
Severity = take_anyif(PropValue, PropName == 'severity'), // Incident cases
Priority = take_anyif(PropValue, PropName == 'priority'), // Default cases
AssignedTo = take_anyif(PropValue, PropName == 'assignedTo'),
Classification = take_anyif(PropValue, PropName == 'classification'),
Determination = take_anyif(PropValue, PropName == 'determination'),
Description = take_anyif(PropValue, PropName == 'description'),
CaseType = take_anyif(PropValue, PropName == 'caseType'),
CreatedTime = take_anyif(PropValue, PropName == 'createdTime'),
EntityCreatedTime = take_any(EntityCreatedTime),
CustomFields = take_anyif(PropValue, PropName == 'customFields'),
ClosingNotes = take_anyif(PropValue, PropName == 'closingNotes'),
SlaAssignmentStatus = take_anyif(PropValue, PropName == 'sla.assignmentStatus'),
SlaResolutionStatus = take_anyif(PropValue, PropName == 'sla.resolutionStatus'),
SlaPolicyName = take_anyif(PropValue, PropName == 'sla.policyName'),
SlaPolicyId = take_anyif(PropValue, PropName == 'sla.policyId'),
SlaPolicyAppliedDateTime = take_anyif(PropValue, PropName == 'sla.policyAppliedDateTime')
by EntityId;
//
// ============ STEP 3: Reconstruct child entities as of snapshotTime ============
// --- Tasks ---
let ActiveTasks = SecurityCaseEvent
| where EntityType == 'CaseTask'
| where ParentEntityId in (ChangedCaseIds)
| where EventTime <= snapshotTime
| summarize arg_max(EventTime, IsDeleted, OperationName) by EntityId
| where IsDeleted == false and OperationName != 'Delete'
| project TaskId = EntityId;
let TasksJson = SecurityCaseEvent
| where EntityType == 'CaseTask'
| where EntityId in (ActiveTasks)
| where OperationName in ('Create', 'Update')
| where EventTime <= snapshotTime
| mv-expand PropertyNames, NewValues
| extend PropName = tostring(PropertyNames), PropValue = tostring(NewValues)
| summarize arg_max(EventTime, PropValue, EntityCreatedTime) by EntityId, ParentEntityId, PropName
| summarize TaskProps = make_bag(pack(PropName, PropValue)), EntityCreatedTime = take_any(EntityCreatedTime) by TaskId = EntityId, CaseId = ParentEntityId
| extend Task = bag_merge(TaskProps, bag_pack('taskId', TaskId, 'entityCreatedTime', tostring(EntityCreatedTime)))
| summarize Tasks = make_list(Task) by EntityId = CaseId;
// --- Comments ---
let ActiveComments = SecurityCaseEvent
| where EntityType == 'CaseComment'
| where ParentEntityId in (ChangedCaseIds)
| where EventTime <= snapshotTime
| summarize arg_max(EventTime, IsDeleted, OperationName) by EntityId
| where IsDeleted == false and OperationName != 'Delete'
| project CommentId = EntityId;
let CommentsJson = SecurityCaseEvent
| where EntityType == 'CaseComment'
| where EntityId in (ActiveComments)
| where OperationName in ('Create', 'Update')
| where EventTime <= snapshotTime
| mv-expand PropertyNames, NewValues
| extend PropName = tostring(PropertyNames), PropValue = tostring(NewValues)
| summarize arg_max(EventTime, PropValue, EntityCreatedTime) by EntityId, ParentEntityId, PropName
| summarize CommentProps = make_bag(pack(PropName, PropValue)), EntityCreatedTime = take_any(EntityCreatedTime) by CommentId = EntityId, CaseId = ParentEntityId
| extend Comment = bag_merge(CommentProps, bag_pack('commentId', CommentId, 'entityCreatedTime', tostring(EntityCreatedTime)))
| summarize Comments = make_list(Comment) by EntityId = CaseId;
// --- Attachments ---
let ActiveAttachments = SecurityCaseEvent
| where EntityType == 'CaseAttachment'
| where ParentEntityId in (ChangedCaseIds)
| where EventTime <= snapshotTime
| summarize arg_max(EventTime, IsDeleted, OperationName) by EntityId
| where IsDeleted == false and OperationName != 'Delete'
| project AttachmentId = EntityId;
let AttachmentsJson = SecurityCaseEvent
| where EntityType == 'CaseAttachment'
| where EntityId in (ActiveAttachments)
| where OperationName in ('Create', 'Update')
| where EventTime <= snapshotTime
| mv-expand PropertyNames, NewValues
| extend PropName = tostring(PropertyNames), PropValue = tostring(NewValues)
| summarize arg_max(EventTime, PropValue, EntityCreatedTime) by EntityId, ParentEntityId, PropName
| summarize AttachmentProps = make_bag(pack(PropName, PropValue)), EntityCreatedTime = take_any(EntityCreatedTime) by AttachmentId = EntityId, CaseId = ParentEntityId
| extend Attachment = bag_merge(AttachmentProps, bag_pack('attachmentId', AttachmentId, 'entityCreatedTime', tostring(EntityCreatedTime)))
| summarize Attachments = make_list(Attachment) by EntityId = CaseId;
// --- Tags ---
let CaseTags = SecurityCaseEvent
| where EntityType == 'Case'
| where EntityId in (ChangedCaseIds)
| where OperationName in ('Create', 'Update')
| where EventTime <= snapshotTime
| where PropertyNames has 'tags'
| extend TagIdx = array_index_of(PropertyNames, 'tags')
| extend Tags = iff(TagIdx >= 0, NewValues[TagIdx], NewValues)
| summarize arg_max(EventTime, Tags) by EntityId
| project EntityId, Tags;
// --- Relations ---
let ActiveRelations = SecurityCaseEvent
| where EntityType == 'CaseRelation'
| where ParentEntityId in (ChangedCaseIds)
| where EventTime <= snapshotTime
| summarize arg_max(EventTime, IsDeleted, OperationName) by EntityId
| where IsDeleted == false and OperationName !in ('Delete', 'Unlink')
| project RelationId = EntityId;
let RelationsJson = SecurityCaseEvent
| where EntityType == 'CaseRelation'
| where EntityId in (ActiveRelations)
| where OperationName in ('Create', 'Update', 'Link')
| where EventTime <= snapshotTime
| mv-expand PropertyNames, NewValues
| extend PropName = tostring(PropertyNames), PropValue = tostring(NewValues)
| summarize arg_max(EventTime, PropValue, EntityCreatedTime) by EntityId, ParentEntityId, PropName
| summarize RelationProps = make_bag(pack(PropName, PropValue)), EntityCreatedTime = take_any(EntityCreatedTime) by RelationId = EntityId, CaseId = ParentEntityId
| extend Relation = bag_merge(RelationProps, bag_pack('relationId', RelationId, 'entityCreatedTime', tostring(EntityCreatedTime)))
| summarize Relations = make_list(Relation) by EntityId = CaseId;
//
// ============ STEP 4: Join all pieces into final output ============
CaseSnapshot
| join kind=leftouter TasksJson on EntityId
| join kind=leftouter CommentsJson on EntityId
| join kind=leftouter AttachmentsJson on EntityId
| join kind=leftouter RelationsJson on EntityId
| join kind=leftouter CaseTags on EntityId
| project
EntityId, DisplayName, StatusName, TopLevelStatus, Severity, Priority,
AssignedTo, Classification, Determination, Description,
Tags = coalesce(Tags, dynamic([])), CaseType, CreatedTime, EntityCreatedTime, CustomFields, ClosingNotes,
SlaAssignmentStatus, SlaResolutionStatus, SlaPolicyName, SlaPolicyId, SlaPolicyAppliedDateTime,
Tasks = coalesce(Tasks, dynamic([])),
Comments = coalesce(Comments, dynamic([])),
Attachments = coalesce(Attachments, dynamic([])),
Relations = coalesce(Relations, dynamic([]))
Cases by SLA status
Finds all open cases where the SLA resolution status matches a specific value (e.g. Exceeded, At Risk).
// Find all open cases where the SLA resolution status matches a specific value.
// SLA status values: Pending, At Risk, Exceeded, Met
//
// Replace the placeholder below with the desired SLA status (e.g. 'Exceeded', 'At Risk')
let slaStatus = '<replace-with-sla-status>';
// --- Exclude deleted cases ---
let deletedCases = SecurityCaseEvent
| where TimeGenerated >= ago(90d)
| where EntityType == 'Case'
| where IsDeleted == true
| distinct EntityId;
// --- Exclude closed cases ---
let closedCases = SecurityCaseEvent
| where TimeGenerated >= ago(90d)
| where EntityType == 'Case'
| where OperationName in ('Create', 'Update')
| mv-expand PropertyNames, NewValues
| extend PropName = tostring(PropertyNames), PropValue = tostring(NewValues)
| where PropName == 'caseStatus.topLevelStatusName'
| summarize arg_max(EventTime, PropValue) by EntityId
| where tolower(PropValue) == 'closed'
| distinct EntityId;
// --- Reconstruct latest SLA and case properties via pivot ---
let CaseProperties =
SecurityCaseEvent
| where TimeGenerated >= ago(90d)
| where EntityType == 'Case'
| where OperationName in ('Create', 'Update')
| where EntityId !in (deletedCases)
| where EntityId !in (closedCases)
// mv-expand: expands array properties into individual rows per property change
| mv-expand PropertyNames, NewValues
| extend PropName = tostring(PropertyNames), PropValue = tostring(NewValues)
| extend NormProp = case(
PropName == 'displayName', 'DisplayName',
PropName == 'assignedTo', 'AssignedTo',
PropName == 'caseType', 'CaseType',
PropName == 'sla.resolutionStatus', 'SlaResolutionStatus',
PropName == 'sla.assignmentStatus', 'SlaAssignmentStatus',
PropName == 'sla.policyName', 'SlaPolicyName',
'')
| where NormProp != ''
// arg_max: keeps the most recent value per case per property
| summarize arg_max(EventTime, PropValue) by EntityId, NormProp
// project before pivot to avoid unintended grouping on EventTime
| project EntityId, NormProp, PropValue
// evaluate pivot: turns rows into columns (one column per property)
| evaluate pivot(NormProp, take_any(PropValue));
// --- Filter by SLA status ---
// column_ifexists: safely accesses pivot columns that may not exist if no case has SLA data
CaseProperties
| extend
SlaResolutionStatus = tostring(column_ifexists('SlaResolutionStatus', '')),
SlaAssignmentStatus = tostring(column_ifexists('SlaAssignmentStatus', '')),
SlaPolicyName = tostring(column_ifexists('SlaPolicyName', '')),
CaseType = tostring(column_ifexists('CaseType', ''))
| where tolower(SlaResolutionStatus) == tolower(slaStatus)
//| where tolower(SlaAssignmentStatus) == tolower(slaStatus) // Uncomment to filter by assignment SLA instead
//| where CaseType == '<case-type>' // Uncomment to filter by case type
//| where AssignedTo has '<user>' // Uncomment to filter by assignee
| project EntityId, DisplayName, AssignedTo, SlaResolutionStatus, SlaAssignmentStatus, SlaPolicyName, CaseType
| order by EntityId asc