AWS Notes - SS67/project-docs GitHub Wiki

Create these in both datasets, in this order.

1. is_windows (flag)

ifelse(locate(toLower({os}), "windows") > 0, 1, 0)

2. is_linux (flag)

ifelse(
  locate(toLower({os}), "linux") > 0 OR
  locate(toLower({os}), "red hat") > 0 OR
  locate(toLower({os}), "ubuntu") > 0 OR
  locate(toLower({os}), "centos") > 0 OR
  locate(toLower({os}), "debian") > 0 OR
  locate(toLower({os}), "suse") > 0 OR
  locate(toLower({os}), "rocky") > 0 OR
  locate(toLower({os}), "alma") > 0, 1, 0)

3. windows_vms (measure)

distinct_countIf({vm_uuid}, {is_windows} = 1 AND {has_uuid} = 1)

4. linux_vms (measure)

distinct_countIf({vm_uuid}, {is_linux} = 1 AND {has_uuid} = 1)

Optional: compliance per OS

Windows patch %:

distinct_countIf({vm_uuid}, {is_windows} = 1 AND {patch_ok} = 1 AND {agent_eligible} = 1)
/ distinct_countIf({vm_uuid}, {is_windows} = 1 AND {agent_eligible} = 1)

Linux patch %:

distinct_countIf({vm_uuid}, {is_linux} = 1 AND {patch_ok} = 1 AND {agent_eligible} = 1)
/ distinct_countIf({vm_uuid}, {is_linux} = 1 AND {agent_eligible} = 1)

Swap patch_ok for defender_ok, rapid7_ok, or ansible_ok to get the other checks per OS.

Quick check: windows_vms + linux_vms should be close to total_vms. If the gap is large, add a table grouped by os filtered to is_windows = 0 AND is_linux = 0 to see which OS names aren't matching, then add them to the is_linux list.

Keep the is_windows / is_linux flags OS-only, and add the power-on condition to the measures.

windows_vms

distinct_countIf({vm_uuid}, {is_windows} = 1 AND {is_powered_on} = 1 AND {has_uuid} = 1)

linux_vms

distinct_countIf({vm_uuid}, {is_linux} = 1 AND {is_powered_on} = 1 AND {has_uuid} = 1)

Windows patch %

distinct_countIf({vm_uuid}, {is_windows} = 1 AND {is_powered_on} = 1 AND {patch_ok} = 1 AND {agent_eligible} = 1)
/ distinct_countIf({vm_uuid}, {is_windows} = 1 AND {is_powered_on} = 1 AND {agent_eligible} = 1)

Linux patch %

distinct_countIf({vm_uuid}, {is_linux} = 1 AND {is_powered_on} = 1 AND {patch_ok} = 1 AND {agent_eligible} = 1)
/ distinct_countIf({vm_uuid}, {is_linux} = 1 AND {is_powered_on} = 1 AND {agent_eligible} = 1)

If powered-off VMs are irrelevant across all compliance metrics, not just OS ones, the simplest fix is to fold power state into the eligibility flag. Update agent_eligible once:

ifelse({appliance} = "No" AND {vm_state} = "poweredOn", 1, 0)

Every *_pct field that uses agent_eligible (Defender, Rapid7, Ansible, Patch) then automatically excludes powered-off VMs, and the patch % formulas above can drop is_powered_on.

Keep total_vms, powered_off_vms, and zombie_vms as they are. Those metrics exist specifically to count powered-off VMs.

The domain check goes on dns_name: a VM counts as domain joined if its name ends with one of your corporate suffixes. Create these in order.

1. is_domain_joined

ifelse(
  endsWith(toLower(trim({dns_name})), ".dev.example.com") OR
  endsWith(toLower(trim({dns_name})), ".prod.example.com"), 1, 0)

Add more OR endsWith(...) lines if you have other corporate domains, such as .lab.example.com.

2. wsus_scope (Windows, powered on, not domain joined)

ifelse({is_windows} = 1 AND {is_powered_on} = 1 AND {is_domain_joined} = 0, 1, 0)

3. wsus_scope_vms (the count you asked for)

distinct_countIf({vm_uuid}, {wsus_scope} = 1 AND {has_uuid} = 1)

4. Useful follow-ups

WSUS coverage within scope:

distinct_countIf({vm_uuid}, {wsus_scope} = 1 AND {wsus} = "Yes")
/ distinct_countIf({vm_uuid}, {wsus_scope} = 1)

In scope but missing WSUS (your action list):

distinct_countIf({vm_uuid}, {wsus_scope} = 1 AND {wsus} <> "Yes")

One caveat: dns_name falls back to the VM name when the guest hostname isn't reported. Those VMs will look non-domain-joined even if they're actually joined. To separate them, add:

dns_unverified
ifelse({wsus_scope} = 1 AND toLower(trim({dns_name})) = toLower(trim({vm_name})), 1, 0)

Show it as a separate "unverified hostname" count, or exclude it from wsus_scope_vms if you want only confirmed non-domain VMs.

The same pattern, flipped to domain joined, using the sccm column.

1. sccm_scope (Windows, powered on, domain joined)

ifelse({is_windows} = 1 AND {is_powered_on} = 1 AND {is_domain_joined} = 1, 1, 0)

2. sccm_scope_vms (count)

distinct_countIf({vm_uuid}, {sccm_scope} = 1 AND {has_uuid} = 1)

3. sccm_coverage_pct

distinct_countIf({vm_uuid}, {sccm_scope} = 1 AND {sccm} = "Yes")
/ distinct_countIf({vm_uuid}, {sccm_scope} = 1)

4. sccm_missing_vms (your action list)

distinct_countIf({vm_uuid}, {sccm_scope} = 1 AND {sccm} <> "Yes")

5. sccm_unknown_vms (data gap, kept separate from missing)

distinct_countIf({vm_uuid}, {sccm_scope} = 1 AND {sccm} = "Unknown")

Optionally, a single dimension that shows both scopes side by side in one visual:

windows_patch_channel
ifelse({is_windows} = 1 AND {is_powered_on} = 1 AND {is_domain_joined} = 1, "SCCM (domain)",
       {is_windows} = 1 AND {is_powered_on} = 1 AND {is_domain_joined} = 0, "WSUS (non-domain)",
       "N/A")

Use it as the group in a stacked bar, with sccm or wsus as the color, filtered to exclude N/A. That shows the coverage of both patch channels in one chart.

Build one eligibility flag and one OS-aware compliance flag, then derive the measures. Appliances and powered-off VMs are excluded from both the numerator and the denominator.

1. compliance_eligible (who is measured)

ifelse({appliance} = "No" AND {vm_state} = "poweredOn" AND {has_uuid} = 1, 1, 0)

2. is_compliant (OS-aware rules)

ifelse(
  {compliance_eligible} = 0, 0,

  {is_windows} = 1
    AND {defender} = "Active"
    AND {rapid7_norm} = "Yes"
    AND {ansible} = "Yes"
    AND (({is_domain_joined} = 1 AND {sccm} = "Yes")
      OR ({is_domain_joined} = 0 AND {wsus} = "Yes")), 1,

  {is_linux} = 1
    AND {defender} = "Active"
    AND {rapid7_norm} = "Yes"
    AND {ansible} = "Yes", 1,

  0)

Windows needs the correct patch channel: SCCM if domain joined, WSUS if not. If Defender isn't deployed on Linux, remove that line from the Linux block.

3. Measures

eligible_vms        distinct_countIf({vm_uuid}, {compliance_eligible} = 1)
compliant_vms       distinct_countIf({vm_uuid}, {is_compliant} = 1)
non_compliant_vms   {eligible_vms} - {compliant_vms}
compliance_pct      {compliant_vms} / {eligible_vms}
excluded_appliances distinct_countIf({vm_uuid}, {appliance} = "Yes" AND {vm_state} = "poweredOn")

Suggested KPI row

[ Compliance % ] [ Compliant ] [ Non-Compliant ] [ Eligible VMs ] [ Appliances (excluded) ]

Showing the excluded appliance count makes it clear to leaders why the eligible total is lower than the total VM count.

Other OS: VMs that are neither Windows nor Linux (e.g. Unknown OS) fall through as non-compliant. If you'd rather exclude them, add AND ({is_windows} = 1 OR {is_linux} = 1) to compliance_eligible.

Create all of these in both datasets. On vm_snapshot, compliance_pct also feeds compliance_wow.

Log scale won't help here. It compresses differences, so 7,332 vs 7,389 would look even flatter. And bars should always start at zero, so a zoomed bar axis is misleading. The trend is real but small (under 1%), so show the change instead of the total.

A clustered bar chart showing +/- per day, by environment.

vms_daily_change:

periodOverPeriodDifference({total_vms}, {snapshot_date}, DAY, 1)

powered_on_daily_change:

periodOverPeriodDifference({powered_on_vms}, {snapshot_date}, DAY, 1)

• X axis: snapshot_date
• Value: vms_daily_change
• Color: environment

Days like −57 and +32 now stand out clearly. Add conditional formatting so negative values are red and positive values are green.

If you want absolute numbers, use a line chart, since lines don't need a zero baseline.

• X: snapshot_date
• Values: total_vms, powered_on_vms
• Format visual → Y-axis → Range → Custom, with min about 7,000 and max about 7,500, or Auto (exclude zero)

Use a line chart of powered_on_vms / total_vms. It's useful because shutdown spikes show up as dips even when the total VM count is flat.

• KPI cards: Total VMs and Powered On, each with a WoW change
• Line (zoomed): total vs powered-on trend
• Bars: daily net change by environment

Also confirm is_first_snapshot = 1 is applied as a filter on this sheet, so no day is double-counted if a second snapshot ever sneaks in.

The problem is likely that one eligibility rule is being used for every control, but each control applies to a different set of VMs (SCCM only to domain-joined Windows, WSUS only to non-domain Windows, and so on). Give each control its own eligible and compliant flags. Then each gauge is simply compliant ÷ eligible, and it responds to every filter.

base_eligible
ifelse(toLower(trim({appliance})) = "no" AND {vm_state} = "poweredOn" AND {has_uuid} = 1, 1, 0)
is_supported_os
ifelse({is_windows} = 1 OR {is_linux} = 1, 1, 0)

Name them defender_elig, defender_ok, rapid7_elig, rapid7_ok, and so on. VM Tools and VO Tag include appliances, because those checks still apply to them. If Defender is Windows-only in your estate, change its eligibility to {is_windows}=1.

The current report has one row per VM, so sum of the flags is exact, and simpler than distinct_countIf:

defender_eligible_vms   sum({defender_elig})
defender_compliant_vms  sum({defender_ok})

Repeat for each control.

• Value: defender_compliant_vms
• Target value: defender_eligible_vms
• Format visual → Value displayed as percentage of target
• Title: "Defender Coverage"

Any filter on environment, owner, cluster, OS, and so on recalculates both values, so the gauge stays correct.

Put them on vm_current. On vm_snapshot, a gauge would sum across all days unless you also filter to one snapshot_date.

Build a quick table on vm_current: rows = environment, values = each _eligible_vms and _compliant_vms. SCCM eligible + WSUS eligible should equal powered-on, non-appliance Windows VMs. If they don't, check the distinct values of appliance and dns_name patterns. That's usually where it goes wrong.

Here are trends worth adding. All of them go on vm_snapshot, with snapshot_date on the X axis and the is_first_snapshot = 1 filter applied. They reuse the per-control _elig / _ok flags, so create those in the snapshot dataset too.

1. Coverage % per control. Line chart, multi-value: Defender %, Rapid7 %, Ansible %, SCCM %, WSUS %, Patch %. Each % is sum({x_ok}) / sum({x_elig}). This is your headline compliance trend.
2. Overall compliance %. Line chart of compliance_pct, colored by environment. Shows which environment is improving or slipping.
3. Non-compliant VM count by control. Stacked bar where each bar is sum({x_elig}) - sum({x_ok}). Easier for teams to act on than a percentage.
4. Unknown / data gap trend. Line chart of patch_unknown_pct, rapid7_unknown_pct, and Defender Unknown. A falling line means telemetry is improving, not just compliance.
5. Windows patch channel. Two lines: SCCM coverage (domain) and WSUS coverage (non-domain).

6. Powered-on share %. Line chart of powered_on_vms / total_vms. Dips reveal environment shutdowns.
7. Zombie VMs. Combo chart with bars for zombie_vms and a line for reclaimable_disk_gb. This tells the cost-savings story.
8. Power state mix. 100% stacked area by power_status.
9. New vs removed VMs. Bars of vms_daily_change by environment, showing provisioning and decommission activity.

10. Owner tag coverage. Line chart of the VO Tag %, showing ownership hygiene over time.
11. Risk exceptions. Line chart of sum({is_risk_exception}). Exceptions should shrink, not grow.
12. Hold Power exemptions. Line chart of the hold_power = "Yes" count, flagging exemption creep.

13. Top 10 owners: non-compliant VMs over time. Line chart colored by vm_owner, with a Top 10 filter.
14. Compliance % by resource pool. Heat map with resource_pool × snapshot_date, colored by compliance_pct. Spots tenants that regress.

KPI cards with compliance_wow, defender_wow, patch_wow, and zombie_wow, each colored green or red by sign.

Start with 1, 2, 6 and 7. They answer the questions leaders ask most: "Are we getting more compliant?", "Which environment is lagging?", "Are shutdowns skewing the numbers?", and "What can we reclaim?"

We'll build the Coverage % per control line chart on vm_snapshot.

If you haven't already, create the per-control _elig / _ok flags here, the same as on vm_current. Then add these measures:

defender_pct_trend   sum({defender_ok}) / sum({defender_elig})
rapid7_pct_trend     sum({rapid7_ok})   / sum({rapid7_elig})
ansible_pct_trend    sum({ansible_ok})  / sum({ansible_elig})
patch_pct_trend      sum({patch_ok})    / sum({patch_elig})
sccm_pct_trend       sum({sccm_ok})     / sum({sccm_elig})
wsus_pct_trend       sum({wsus_ok})     / sum({wsus_elig})

• Type: Line chart
• X axis: snapshot_date (aggregate: Day)
• Value: all six _pct_trend fields
• Leave Color empty, since each measure becomes its own line.

• is_first_snapshot = 1
• snapshot_date: relative date filter, Last 8 weeks

• Each value field → Format → Percent with 1 decimal.
• Y-axis → Range → Auto (exclude zero), so small movements are visible.
• Turn on data labels for the latest point only, or leave them off and rely on tooltips.
• Title: "Compliance Coverage Trend".
• Subtitle: "Powered-on, non-appliance VMs. SCCM: domain-joined Windows. WSUS: non-domain Windows."

Duplicate the visual and keep one measure, e.g. defender_pct_trend, with Color = environment. That shows which environment is driving the overall line. Make one per key control, or add a parameter control to switch measures.

Hover over the latest date. Each % should match the corresponding gauge on the Compliance Overview tab when the same filters are applied. If they differ, the snapshot is from the first run of the day and the workbook is from the latest run. Small gaps are normal; large gaps mean a flag differs between the datasets.

Break the elapsed time into minutes, then format it as days, hours and minutes. Create these in order on vm_current.

You're right, my formula was wrong. power_state_since is a String in your dataset, and dateDiff only accepts Date values. It needs converting with parseDate first.

power_since_dt (snapshot, where the value is ISO, e.g. 2026-10-12T14:03:22+00:00)

ifelse(
  isNull({power_state_since}) OR trim({power_state_since}) = "", NULL,
  parseDate(substring({power_state_since}, 1, 19), "yyyy-MM-dd'T'HH:mm:ss")
)

Taking the first 19 characters drops the +00:00 offset. Since the value is already UTC and QuickSight's now() is UTC, nothing shifts.

power_since_dt (current/workbook, where the value is raw text)

The format depends on how vSphere wrote it. Use the version above if it looks like ISO, or match the pattern. For example:

dateDiff({power_since_dt}, now(), "MI")

Everything else (ps_days, ps_hours, ps_minutes, power_state_duration) stays the same.

If it still errors, paste one sample power_state_since value from the workbook and I'll give you the exact pattern. Also update days_in_state to use power_since_dt, since it has the same issue.

1. power_mins: total minutes since the power state changed

dateDiff({power_state_since}, now(), "MI")

2. ps_days

decimalToInt({power_mins} / 1440)

3. ps_hours

decimalToInt(mod({power_mins}, 1440) / 60)

4. ps_minutes

mod({power_mins}, 60)

5. power_state_duration: the display field

ifelse(
  isNull({power_mins}) OR {power_mins} < 0, "Unknown",
  {ps_days} > 0, concat(toString({ps_days}), "d ", toString({ps_hours}), "h ", toString({ps_minutes}), "m"),
  {ps_hours} > 0, concat(toString({ps_hours}), "h ", toString({ps_minutes}), "m"),
  concat(toString({ps_minutes}), "m")
)

This gives 1d 2h 34m, 10h 13m, or 45m.

Optional: shorter for long durations. Minutes don't matter after a week. Swap the ps_days > 0 line for:

{ps_days} >= 7, concat(toString({ps_days}), "d"),
{ps_days} > 0,  concat(toString({ps_days}), "d ", toString({ps_hours}), "h ", toString({ps_minutes}), "m"),

This gives 45d instead of 45d 3h 12m.

• Create these in the analysis, not the dataset. With SPICE, dataset-level fields that use now() are calculated at refresh time, so the duration would freeze until the next refresh. Analysis-level fields recalculate when the dashboard is viewed.
• Sort by power_mins, not by the text field. Text sorts alphabetically, so 10h would come before 2d. In a table, add power_mins as a hidden column and sort on it, or set the column's sort to use power_mins.

If the field errors, power_state_since is probably still raw text in vm_current. Set it to type Date in the dataset editor first, or wrap it with parseDate({power_state_since}, "<format>"), matching the pattern to how the raw value looks.

A donut chart fits best. The buckets are parts of a whole, and QuickSight can show each slice's % as a label and let you pick a colour per slice.

Make the buckets non-overlapping so the percentages add up to 100%:

poweroff_age_bucket
ifelse(
  {vm_state} <> "poweredOff", NULL,
  dateDiff({power_since_dt}, now(), "DD") > 365, "Over 1 year",
  dateDiff({power_since_dt}, now(), "DD") > 60,  "61 days to 1 year",
  dateDiff({power_since_dt}, now(), "DD") > 30,  "31 to 60 days",
  "30 days or less"
)

Create it in the analysis, not the dataset, so now() stays current.

• Group/Color: poweroff_age_bucket
• Value: vm_uuid (Count distinct)
• Filter: poweroff_age_bucket excludes NULL. This keeps only powered-off VMs.

• Click each slice or its legend item → Choose color:
  • Over 1 year → red
  • 61 days to 1 year → pink
  • 31 to 60 days → yellow
  • 30 days or less → grey (or exclude it if you only want the aging ones)
• Format visual → Data labels → on, showing Percent, or Value and percent for "312 (18%)".
• Title: "Powered-off VMs by age". Subtitle: "% of powered-off VMs".

• Horizontal 100% stacked bar (single bar): same colours and percentages, compact for a top-row strip.
• Trend over time (on vm_snapshot): a 100% stacked bar with X = snapshot_date, Color = the same bucket field, and is_first_snapshot = 1. Shows whether old powered-off VMs are being cleaned up or piling up.