Agent Foskett Academy • Microsoft Sentinel • Module 5 • Lesson 49

Lesson 49 — Creating Effective Workbook Visualisations

Charts are easy to make. Charts that help a SOC analyst reach the right conclusion take more thought.

Learn to turn KQL results into clear, honest and investigation-ready workbook visuals.

Module 5 — Workbooks and Dashboards • Evidence-first visualisation
Agent Foskett Microsoft Sentinel workbook charts and visualisations
What you will learn

Show the right evidence, the right way.

✓ Time-series charts
✓ Rankings and KPIs
✓ Evidence tables
✓ Honest scales and context

From working workbook to useful workbook

Lesson 47 built an operational workbook. Lesson 48 made it interactive. In Lesson 49, Agent Foskett tackles the next problem: can an analyst interpret its charts correctly? A visualisation can be technically valid yet misleading.

Learning objectives

  • Choose the right visualisation for an operational question.
  • Build time-series, comparison and detail views with KQL.
  • Understand counts, unique entities, rates and denominators.
  • Avoid misleading time bins, truncated axes and overaggregation.
  • Use labels, filters and drill-downs that preserve context.
  • Test visualisations for sparse data, delays and permission gaps.

The first rule of SOC visualisation

QUESTION → METRIC → QUERY → VISUAL → ACTION Not: PRETTY CHART → GUESS WHAT IT MEANS

A workbook is not a verdict

A rising chart may represent an attack, a configuration problem, a new connector or a busy Monday. A workbook helps the analyst ask better questions; it does not establish malicious intent by itself.

Match the chart to the question

Analyst questionPreferred displayWhy
When did activity change?Time chartShows movement over time
Which accounts contribute most?Sorted bar chartSupports category comparison
What exactly happened?Evidence tablePreserves event detail
How many events match?KPI / numberQuick summary with definition
How are statuses distributed?Stacked bar / tableShows composition when categories are limited

The case: failed sign-ins after midnight

A SOC analyst reports that sign-in failures are increasing. The workbook must show when failures rose, which accounts contributed, and which events deserve inspection.

We will reuse the SigninLogs data source from Lessons 47 and 48. Query examples use a fixed 24-hour window for reproducibility; replace that fixed range with your validated workbook time parameter when publishing.

Step 1 — Verify the underlying data

Data availability before visualisation
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
SigninLogs
| where TimeGenerated > ago(24h)
| summarize TotalRecords = count(),
            LatestRecord = max(TimeGenerated),
            UniqueAccounts = dcount(UserPrincipalName)

If data is absent or stale, do not interpret an empty chart as “no threat.” Check the connector, selected workspace, permissions and ingestion expectations.

Step 2 — Create a reliable time series

Hourly failed sign-in trend
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType != "0"
| summarize FailedSignIns = count()
    by bin(TimeGenerated, 1h)
| order by TimeGenerated asc

Add a query component and select a time chart (or the corresponding time-series visual). Use TimeGenerated on the horizontal axis and FailedSignIns for the numeric value. Name it Failed sign-ins by hour.

This measures matching log records, not unique attackers or compromised identities.

Step 3 — Choose the right time bins

Investigation windowIllustrative binWatch out for
1–3 hours5 minutesSparse or bursty results
24 hours1 hourShort spikes hidden inside an hour
7 days1 dayIntraday patterns disappear
30 days1 dayIngestion gaps or seasonality

These are starting points, not mandatory defaults. A very coarse bin can conceal an attack burst; a very fine bin can exaggerate random noise.

Step 4 — Show the gap between events and people

Failures and unique accounts by hour
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType != "0"
| summarize FailedEvents = count(),
            UniqueAccounts = dcount(UserPrincipalName)
    by bin(TimeGenerated, 1h)
| order by TimeGenerated asc

A hundred failures from one account is a different investigation from a hundred failures spread across eighty accounts. If you show both measures, label them distinctly and avoid implying they use identical units or scales.

Step 5 — Rank contributors

Top accounts by failed events
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType != "0"
| summarize FailedEvents = count(),
            SourceIPs = dcount(IPAddress),
            LastSeen = max(TimeGenerated)
    by UserPrincipalName
| top 10 by FailedEvents desc

Use a horizontal bar chart for the ranked FailedEvents measure, or a table when the extra columns are important. Explain that the chart shows only the top ten, not the whole population.

Step 6 — Keep comparisons honest

  • Use a consistent scale for comparable bars.
  • For bar charts, a zero baseline usually prevents exaggerated differences.
  • Avoid sorting alphabetically when the purpose is to compare highest contributors.
  • Show the measurement unit and selected period.
  • Do not confuse an event count with a percentage or unique-user count.

Step 7 — Use a detail table for evidence

Recent failed sign-in evidence
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType != "0"
| project TimeGenerated, UserPrincipalName,
          IPAddress, AppDisplayName, ResultType,
          ConditionalAccessStatus
| order by TimeGenerated desc
| take 200

Place this below the summary visuals. An analyst should be able to verify the pattern against real event rows. The 200-row cap is for workbook responsiveness, not a statement about the total event count.

Step 8 — Add useful context

A good chart title says what is counted and when. A note beneath the chart should explain the source, filter and any limitation.

BAD: "Suspicious Activity" BETTER: "Failed Entra sign-in records by hour — selected workspace" NOTE: "Source: SigninLogs. Counts reflect nonzero ResultType; ingestion may be delayed."

Step 9 — Avoid ambiguous percentages

If you display a failure rate, define the denominator. “30% failed” is meaningful only if the reader knows whether that means failed records divided by all sign-in records in the same population and period.

Hourly failure percentage — defined denominator
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
SigninLogs
| where TimeGenerated > ago(24h)
| summarize Total = count(),
            Failed = countif(ResultType != "0")
    by bin(TimeGenerated, 1h)
| extend FailurePercent =
    iff(Total == 0, real(null),
        round(100.0 * Failed / Total, 1))
| order by TimeGenerated asc

This is a percentage of recorded sign-in events in the selected data, not the percentage of accounts under attack. The denominator can change if data collection or filtering changes.

Step 10 — Treat empty time bins carefully

A chart with missing hourly points may show gaps, not zeros. A true zero means the data source was present and no matching events occurred in that bin. Do not fill gaps with zero without understanding ingestion and source availability.

Step 11 — Use colour as an aid, not evidence

Colour can help separate categories or draw attention to a defined threshold. It must not imply that an account is compromised merely because a bar is red. Provide readable labels and avoid relying on colour alone for meaning.

Step 12 — Beware the “top ten” illusion

A top-ten chart can look reassuring if activity shifts to many low-volume accounts. Pair rankings with a total count, unique-account measure or an appropriately scoped detail view so analysts understand what was omitted.

Step 13 — Support drill-downs

Keep the time-range, account and application parameters from Lesson 48. A practical investigation path is hourly spike → contributing account → application → event-level records.

Where the workbook editor supports selection or parameter-setting links, configure them to pass the intended value and validate the result.

Step 14 — A dashboard layout that tells a story

IDENTITY SIGN-IN OPERATIONS [Time range] [Account] [Application] 1. DATA STATUS: latest event + coverage 2. OVERVIEW: total failures / accounts 3. TREND: failed sign-ins over time 4. RANKING: contributing accounts 5. EVIDENCE: filtered event records 6. GUIDANCE: interpretation + next steps

Step 15 — Keep operational performance in mind

  • Limit large detail tables and keep filters selective.
  • Summarise high-volume data before charting.
  • Avoid repeated, nearly identical expensive queries.
  • Test broad time windows as well as narrow ones.
  • Check the workbook with a realistic analyst role and real-world data volume.

Step 16 — Validate the same story three ways

CheckWhat to compare
Trend totalDoes it match the equivalent count for the same period and filters?
Top accountsIs it explicitly labelled as a subset?
Detail rowsDo the event timestamps and filters support the displayed pattern?
Time parameterDo all visuals use the same intended range?
Data freshnessCould delayed ingestion explain the change?

Step 17 — Common visualisation mistakes

ProblemImprovement
A pie chart with 40 accountsUse a sorted bar chart or table
A line chart for unrelated categoriesUse bars for category comparison
Two metrics with different units on one axisSeparate visuals or clearly configured axes
No period shownDisplay selected time range
Missing values treated as zeroExplain source gaps and freshness
A giant unfiltered tableAdd a bounded, filterable evidence view

Step 18 — The Agent Foskett review

Agent Foskett notices a large midnight spike in the chart. One account contributes most failures, but the account is a service identity tied to a scheduled application change. The chart was accurate; the initial conclusion was not.

The next question is whether the failures are expected, whether the account has unusual successful sign-ins, and whether there is supporting evidence of misuse. Never promote a visual pattern to a security finding without investigation.

Hands-on workbook challenge

  1. Create an hourly sign-in failure trend.
  2. Create a ranked bar chart of the top ten accounts.
  3. Add an event-level evidence table beneath the charts.
  4. Display the source, measurement definition and selected period.
  5. Add a clearly defined failure percentage or unique-account measure.
  6. Test a shorter and longer time range.
  7. Explain one scenario in which the chart could mislead an analyst.
  8. Write the next investigation step for a genuine spike.

Visualisation release checklist

  • Titles state the metric and scope.
  • Axes, units and time bins are appropriate.
  • Comparisons use honest scales and denominators.
  • Missing or stale data is not presented as clean activity.
  • Top-N charts disclose omitted categories.
  • Filters update all intended components.
  • Underlying evidence is accessible to authorised analysts.
  • The workbook is tested for readability, permissions and performance.

Agent Foskett takeaway

Make the evidence easier to see — without making it easier to misunderstand.

The logs already knew. A good chart helps the analyst ask the next question.

Lesson 49 complete
You can now design workbook visualisations that reveal useful trends without hiding context, distorting comparisons or losing the underlying evidence.
Sentinel Academy Home