KQL Search

Query Details

EV Sync Drift Detection

Query

id: b2c3d4e5-f6a7-4b8c-9d0e-1f2a3b4c5d6e
name: EasyVista - Ticket Status Sync Drift Detected
description: |
  Detects when a Sentinel incident status and its linked EasyVista ticket status are out of sync for more than 30 minutes.
  This indicates the bidirectional sync playbooks may have failed or the status mapping needs attention.
  Ensures SOC and IT teams stay aligned in real time.
severity: Medium
requiredDataConnectors:
  - connectorId: EasyVistaITSM
    dataTypes:
      - EasyVista_Tickets_CL
queryFrequency: 30m
queryPeriod: 1h
triggerOperator: gt
triggerThreshold: 0
tactics:
  - Impact
relevantTechniques:
  - T1489
query: |
  SecurityIncident
  | where TimeGenerated > ago(1h)
  | where Labels has "EV:"
  | extend EVTicket = extract(@"EV:([^,\]]+)", 1, tostring(Labels))
  | where isnotempty(EVTicket)
  | join kind=inner (
      EasyVista_Tickets_CL
      | where TimeGenerated > ago(1d)
      | summarize arg_max(TimeGenerated, *) by RFC_NUMBER
      | project RFC_NUMBER, EV_Status = STATUS_EN, EV_LastUpdate = LAST_UPDATE
  ) on $left.EVTicket == $right.RFC_NUMBER
  | extend ExpectedEVStatus = case(
      Status == "New", "Open",
      Status == "Active", "Investigating",
      Status == "Closed", "Resolved",
      "Unknown")
  | where EV_Status != ExpectedEVStatus
  | where EV_Status != "Contained" or Status != "Active"
  | where datetime_diff('minute', now(), LastModifiedTime) > 30
  | project
      TimeGenerated,
      IncidentNumber,
      IncidentTitle = Title,
      SentinelStatus = Status,
      EVTicket,
      EV_Status,
      ExpectedEVStatus,
      DriftMinutes = datetime_diff('minute', now(), LastModifiedTime)
entityMappings:
  - entityType: CloudApplication
    fieldMappings:
      - identifier: Name
        columnName: EVTicket
incidentConfiguration:
  createIncident: true
  groupingConfiguration:
    enabled: true
    reopenClosedIncident: false
    lookbackDuration: 2h
    matchingMethod: Selected
    groupByCustomDetails:
      - EVTicket
customDetails:
  SentinelIncident: IncidentNumber
  SentinelStatus: SentinelStatus
  EasyVistaTicket: EVTicket
  EasyVistaStatus: EV_Status
  ExpectedStatus: ExpectedEVStatus
  DriftMinutes: DriftMinutes
alertDetailsOverride:
  alertDisplayNameFormat: "Sync Drift: Sentinel #{{IncidentNumber}} vs EV {{EVTicket}} ({{EV_Status}})"
  alertDescriptionFormat: "Status sync drift detected. Sentinel #{{IncidentNumber}} and EasyVista {{EVTicket}} are out of sync for {{DriftMinutes}} minutes."

Explanation

This query is designed to detect when there is a mismatch between the status of a security incident in Microsoft Sentinel and its corresponding ticket in EasyVista, lasting more than 30 minutes. This mismatch could indicate a failure in the synchronization process or an issue with status mapping, which is crucial for keeping the Security Operations Center (SOC) and IT teams aligned.

Here's a simplified breakdown of the query:

  1. Data Source: It uses data from Sentinel incidents and EasyVista tickets.
  2. Time Frame: It looks at incidents generated in the last hour and EasyVista tickets updated in the last day.
  3. Matching: It matches Sentinel incidents with EasyVista tickets using a unique identifier found in the labels.
  4. Status Comparison: It checks if the current status of the EasyVista ticket differs from the expected status based on the Sentinel incident status.
  5. Drift Detection: It identifies cases where the status mismatch has persisted for more than 30 minutes.
  6. Alert Creation: If such a drift is detected, it creates an alert with details about the incident and ticket statuses.
  7. Incident Management: It configures the system to create incidents for these alerts and groups them by the EasyVista ticket number.

The query helps ensure that any discrepancies between the systems are promptly identified and addressed, maintaining effective communication and coordination between teams.

Details

David Alonso profile picture

David Alonso

Released: April 16, 2026

Tables

SecurityIncidentEasyVista_Tickets_CL

Keywords

SecurityIncidentEasyVistaTicketsLabelsStatusTimeGeneratedRFCNumberLastUpdateIncidentNumberIncidentTitleSentinelStatusDriftMinutesCloudApplication

Operators

agohasextendextracttostringisnotemptyjoinsummarizearg_maxprojectcasedatetime_diffnowwhere

Actions