[FEATURE] Add a stable, machine‑readable dedup fingerprint (and involved‑object list) to alert payloads - esp. deadlocks & blocking

[gotqn]

Which component(s) does this affect?

• Full Dashboard
• Lite
• SQL collection scripts
• Installer
• Documentation

Problem Statement

Downstream automation (Logic Apps → Azure DevOps tickets, SIEMs) needs to tell distinct incidents apart and collapse recurrences of the same one. Today the webhook/alert payload exposes server + metric + summarized facts, but no stable per‑incident key - so consumers must reconstruct one from truncated query text / volatile wait‑resource IDs, which is fragile (and Logic Apps can't even hash).

Proposed Solution

Please emit a precomputed fingerprint.

1. A dedupKey/fingerprint field: a stable hash the app computes server‑side, where it has the full data:

   • Deadlocks / Blocking: hash of the sorted set of fully‑qualified objects involved (database.schema.object), across all participating sessions/databases. (Don't use raw query text — it varies by literals; don't use bare hobt/page IDs — they change on index rebuild.)
   • Long‑Running Query: the query_hash.
   • Long‑Running Job: the job name (+ instance).
   • Volume Free Space: the drive/volume.

2. A human‑readable involvedObjects list (e.g. SalesDB.dbo.Orders, SalesDB.dbo.LineItems) — for the ticket body. For multi‑database deadlocks, list all databases/objects (one incident, not split).

Use Case

I have build a Logic app in order to create tickets in Azure DevOps Board. If a deadlock in database X and specific objects occurs, I want to create a ticket. If the deadlock occurs again, I want to increase a custom field counter, not to create separate ticket. If a developer opens the Board, it will be easier to focus on issues with high occurrences.

Alternatives Considered

No response

Additional Context

Why a precomputed hash and not just names: consumers can dedup on one stable field without parsing/normalizing/sorting; names alone are ambiguous and order‑sensitive, and several automation platforms have no hashing primitive.

Scope: deadlocks + blocking are the priority; query/job/disk keys are a nice follow‑on. Keep the existing LowDiskAlertGate/rolling‑count gating - this is about identifying which incident, not whether to fire.

[gotqn]

Additional evidence (blocking) + a fingerprint refinement

A blocking alert from the same instance highlights a second facet to fold in. The card showed Current Value = 8, but listed the same blocking relationship three times, differing only by Wait Time (90.8 / 85.8 / 80.8 s) — identical Database, identical Blocked Query (UPDATE SurveyInstances …), identical Blocking Query (SELECT … CROSS JOIN …). That's one blocking chain (a runaway cartesian‑product SELECT holding a lock while 8 UPDATEs queue behind it), not 8 (or 3) distinct incidents.

Two requests on top of the per‑event delivery + stable‑fingerprint asks above:

1. Group identical relationships within a notification. BuildBlockingContextAsync (Lite MainWindow.xaml.cs:2245) lists raw blocked‑process rows via events.Take(3) with no grouping, so the same chain repeats. Please group by (database + blocked query + blocking query / blocker) and show occurrence count + wait range (min/max/latest) instead of near‑duplicate blocks — e.g. "SELECT … CROSS JOIN … is blocking 8 sessions running UPDATE SurveyInstances … in smModel_…; waits 80.8–90.8 s." Also, the Take(3) cap silently drops events (count says 8, only 3 shown) — surface the true count or make the cap configurable.

2. Exclude per‑sample/volatile fields from the dedup fingerprint. Wait Time (and similar measured values) changes every sample, so it must not be part of the incident identity. For blocking, identity = database + blocked/blocking query pair (or involved objects); for deadlocks, the involved objects. Otherwise every sample of the same chain looks "new."

[erikdarlingdata]

Done — implemented and merged to dev, so this will be in tonight's nightly build.

Both fields are now emitted on every alert surface (Teams facts, Slack fields, email, the in-app dialog, and persisted alert history):

• Dedup Key — a stable SHA-256 fingerprint computed server-side, scoped by server + incident type, with volatile per-sample values (wait time, etc.) deliberately excluded so recurrences of the same incident hash identically. Identity per type:
  • Deadlocks: the sorted set of fully-qualified objects across all participants (parsed from the deadlock graph). Multi-database deadlocks are one incident listing all DBs/objects.
  • Blocking: the resolved contentious object, falling back to database + literal-stripped blocked/blocking query pair when the object can't be resolved. It's resolved from the blocked-process report's object_id, so it's stable across index rebuilds — per your note about not using hobt/page IDs.
  • Long-running query: query_hash. Long-running job: job name. Volume: the drive.
• Involved Objects — the human-readable list for the ticket body.

This covers both the Full Dashboard and Lite, and both the live "Detected" alerts and the anomaly/spike alerts. Existing gating (LowDiskAlertGate / rolling-count) is untouched — this only identifies which incident.

Point your Logic App at a nightly build and dedup on the Dedup Key field. Would appreciate your feedback once you've run it through.

[gotqn]

Tested on the dev build (Lite, v3.0.0), Per-event delivery mode. The dedup fingerprint works great 👍 - I forced repeated deadlocks over the same two tables and got a single stable Dedup Key (37486afbc3…07bd3a15) across both the first alert and the recurrence, with Occurrences correctly climbing to 4. The grouping/fingerprint side of #1140 is doing exactly what it should.

While testing it in Per-event mode I noticed two things in the resulting Teams cards. These are really about the per-event delivery (#1141) that consumes the fingerprint, but flagging here since it's the same test:

1. Per-event drops the per-incident detail (looks like info loss). In Summary mode the deadlock card includes Victim SQL and Processes (Lite — added in Lite/MainWindow.xaml.cs:2382-2385). The per-event card for the same deadlock shows neither -only Dedup Key, Involved Objects, and Occurrences. Root cause looks like PerEventNotification.Split building a fresh context:

   // PerEventNotification.cs
   var ctx = new AlertContext { SeverityOverride = source.SeverityOverride };
   AlertIncidentRenderer.Apply(ctx, new[] { incident });
   

   It copies only SeverityOverride and re-derives Details purely from AlertIncidentRenderer.Apply (Dedup Key + Involved Objects, plus Occurrences when >1), so the builder's detail items are gone. Same pattern would hit Dashboard (it'd drop its Database / Query / Wait Resource / Lock Mode / Client App facts) and the email channel loses the deadlock_graph.xml attachment too, since AttachmentXml/AttachmentFileName aren't carried onto the per-incident context. Net effect: per-event notifications -which have one incident's worth of room -actually carry less forensic detail than Summary mode.

2. Per-event "Current Value" shows the object list instead of a number. In the per-event card, Current Value = the involved-objects string (smModel_…dbo.T1, smModel_…dbo.T2), from DescribeIncident (PerEventNotification.cs:74-79). That duplicates the Involved Objects fact right below it, and replaces the numeric reading the field carries in Summary mode (the deadlock count, e.g. 1). When objects don't resolve it falls back to the Dedup Key. Might read cleaner if Current Value kept the occurrence count (or a short incident label) so the field's meaning stays consistent.

   

Neither blocks the dedup feature — the fingerprint/count is exactly what I needed for downstream ticket dedup. Happy to open a separate issue against #1141 if you'd prefer to track the per-event detail loss there.

[erikdarlingdata]

Thanks for the thorough test run — great to hear the dedup fingerprint behaved (stable key + Occurrences climbing to 4 is exactly the intent). And good eyes on the two per-event issues; both are real and now fixed on dev (kept here under #1141, no separate issue needed):

1. Per-event cards no longer carry less detail than Summary. PerEventNotification.Split was building a bare context. Now the incident carries its forensic detail through to the per-event card — deadlock keeps Victim SQL / Processes (Dashboard: Query / Wait Resource / Lock Mode), blocking keeps Database / Contentious Object / Blocked Query / Blocking Query / Lock Mode — and the per-event context now also carries the deadlock_graph.xml / blocked_process_report.xml attachment so the email channel keeps it. Summary mode is unchanged.
2. Per-event "Current Value" is now the occurrence count (a number, matching Summary) instead of the involved-objects string that duplicated the Involved Objects fact.

Both ship in tonight's nightly — appreciate the report.