Compliance thresholds

Help ID: compliance.tab.thresholds

Threshold rules tune pass/fail behavior for a metric on specific servers or databases. This is where "the CIS baseline says sysadmin count must be ≤ 5, but Prod-DB-01 legitimately has 8" gets expressed.

[Image omitted: Thresholds tab with groups expanded showing the scope pills]

The four-tier cascade

HealthCheck.ReportMaster applies threshold rules in cascading precedence. For every (instance, database, metric) combination the engine encounters at run time, it looks for the most specific matching rule and uses that. If it doesn't find one, it falls back to the next tier down.

flowchart TD
    Start["Run needs threshold for<br/>metric M on server S DB D"] --> T1
    T1{"Full override?<br/>MetricID = M<br/>InstanceID = S<br/>DBName = D"}
    T1 -- "yes" --> Use1["Use it — most specific wins"]
    T1 -- "no" --> T2
    T2{"DB override?<br/>MetricID = M<br/>InstanceID = 0<br/>DBName = D"}
    T2 -- "yes" --> Use2["Use it"]
    T2 -- "no" --> T3
    T3{"Instance override?<br/>MetricID = M<br/>InstanceID = S<br/>DBName = NULL"}
    T3 -- "yes" --> Use3["Use it"]
    T3 -- "no" --> T4
    T4{"Default?<br/>MetricID = M<br/>InstanceID = NULL<br/>DBName = NULL"}
    T4 -- "yes" --> Use4["Use it"]
    T4 -- "no" --> Bake["Fall back to the SP's<br/>hardcoded default"]

Tier visual mapping

What a rule contains

One row of HealthCheck.MetricThreshold:

Operators come from GUI.HCMetricOperators: =, <>, !=, <, <=, >, >=, IS, LIKE, /.

The Violation flag

Simple rule of thumb:

• Check it when your rule describes a bad state. Example: SysadminCount > 5 — matching the rule means "too many sysadmins", which should count as a fail.
• Leave it unchecked when the rule describes a good state. Example: SysadminCount <= 5 — matching the rule means the server is safe, which should count as a pass.

Most shipped metric SPs hardcode their own pass/fail logic and don't consult ReportViolation today. It's plumbing for future custom metrics that need "match = fail" semantics without inverting the operator. When in doubt, write the rule as a good-state rule and leave Violation off.

Grid features

The Thresholds grid is a shared MinionGrid, so it comes with:

• Drag-to-group panel at the top — drag any header there to regroup. Default is Metric.
• Per-column search row — one cell of typing narrows every column.
• Column chooser — hide the ones you don't need.
• Sort / filter / move every column.
• Red group separator — visual divider between groups.

Adding a rule

The form above the grid takes the form's values, inserts a MetricThreshold row, and appends it to the grid.

[Image omitted: Threshold add form with server picked and cascading DB combo]

• Metric — picks from metrics currently in this profile. If a metric you want isn't there, add it on the Metrics tab first.
• Instance — picks a server. Leave blank for a default or DB-scope rule. Selecting a server cascades the DB dropdown — the DB list refreshes to show only that server's collected databases.
• DB — pick from the selected server's DBs, type a name (wildcards allowed with LIKE), or leave blank for "all DBs on the instance".
• Operator — from GUI.HCMetricOperators.
• Value — string; the SP casts as needed.
• Measure / Note — optional measure column name. Some metrics have multiple measurable values; this picks which one the rule grades against.
• + Add — inserts.
• Remove — deletes the currently-selected row in the grid.

Editing inline

Click any editable cell (Op, Value, Measure, Violation, Active, Comment) and change it. Changes save automatically when you tab out of the row or click away. There's no per-row Save button.

Grouping and reading the grid

Default grouping is by Metric. Each metric group collapses to a header showing the metric name and rule count. Expand a group to see its rules stacked in cascading order (Default → Instance → Database → Server + DB) so specificity reads top-to-bottom.

Drag other columns into the group panel to slice differently:

• Group by Server to see every rule pinned to a specific host, across metrics.
• Group by Scope to see all Defaults first, then all Instance rules, etc.
• Group by DB to see every rule targeting a specific database name.

Worked examples

Example 1 — CIS Sysadmin Count tuned per server

You've cloned CIS Audit into Acme Prod CIS. The Sysadmin Count metric ships with a default rule; you need to raise the limit on two production hosts and tighten it on a Bronze dev server.

Result: every server uses <= 5 unless it's one of the three overridden hosts.

Example 2 — Database owner is not SA with a per-DB exception

The check "database owner should not be sa" is a hard rule everywhere except for one legacy DB you've been told not to touch.

Notes: - The exception rule uses = (matches when owner IS sa) and Violation stays OFF because a match here is "as expected". This is the practical way to say "we accept sa as owner on this specific DB". - Only the Legacy-01 / LegacyPayroll combo hits the exception. Every other (server, DB) falls back to the default.

Example 3 — Trustworthy disabled everywhere except MSDB

VA1102 mandates Trustworthy = OFF on every DB except MSDB. Ship one default rule and one DB-scoped exemption.

The DB rule matches every server that has an MSDB (all of them), so a single row covers the fleet.

Example 4 — Dev environment tempdb looseness

Dev servers need more TempDB files than the CIS baseline suggests. Rather than editing every dev server one by one, use an Instance-scoped rule against a specific dev subset. If dev is a whole SLA, consider running a different profile (Dev permissive) against it instead.

Common gotchas

• Empty Instance means "all servers". If you set a value expecting it to apply to just one server and leave Instance blank, you've written a Default rule that applies to your whole fleet. Look at the Scope pill on the row to sanity-check.
• The MetricThreshold grid uses InstanceID = 0 for DB-only rules, not NULL. The UI hides this — leaving Instance blank while typing a DB name is the correct move. ReportMaster's DB-only join looks for InstanceID = 0, so that's what the service writes.
• Deleting a metric from the profile doesn't delete its rules. The Metrics tab's ✕ button only affects ReportMetric. Threshold rules for that metric are left in place and reappear if you re-add.
• Violation isn't a magic toggle. Most shipped metrics ignore it. Prefer inverting the operator if you actually want to invert pass/fail.
• LIKE is SQL's LIKE, not regex. %dev% works; .*dev.* doesn't.

Related: Compliance overview, Compliance metrics, Compliance scope, How-to recipes, Compliance backend.