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.

What you will learn
Show the right evidence, the right way.
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
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 question | Preferred display | Why |
|---|---|---|
| When did activity change? | Time chart | Shows movement over time |
| Which accounts contribute most? | Sorted bar chart | Supports category comparison |
| What exactly happened? | Evidence table | Preserves event detail |
| How many events match? | KPI / number | Quick summary with definition |
| How are statuses distributed? | Stacked bar / table | Shows 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
- 1
- 2
- 3
- 4
- 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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType != "0"
| summarize FailedSignIns = count()
by bin(TimeGenerated, 1h)
| order by TimeGenerated ascAdd 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 window | Illustrative bin | Watch out for |
|---|---|---|
| 1–3 hours | 5 minutes | Sparse or bursty results |
| 24 hours | 1 hour | Short spikes hidden inside an hour |
| 7 days | 1 day | Intraday patterns disappear |
| 30 days | 1 day | Ingestion 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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType != "0"
| summarize FailedEvents = count(),
UniqueAccounts = dcount(UserPrincipalName)
by bin(TimeGenerated, 1h)
| order by TimeGenerated ascA 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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType != "0"
| summarize FailedEvents = count(),
SourceIPs = dcount(IPAddress),
LastSeen = max(TimeGenerated)
by UserPrincipalName
| top 10 by FailedEvents descUse 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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType != "0"
| project TimeGenerated, UserPrincipalName,
IPAddress, AppDisplayName, ResultType,
ConditionalAccessStatus
| order by TimeGenerated desc
| take 200Place 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.
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.
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 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 ascThis 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
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
| Check | What to compare |
|---|---|
| Trend total | Does it match the equivalent count for the same period and filters? |
| Top accounts | Is it explicitly labelled as a subset? |
| Detail rows | Do the event timestamps and filters support the displayed pattern? |
| Time parameter | Do all visuals use the same intended range? |
| Data freshness | Could delayed ingestion explain the change? |
Step 17 — Common visualisation mistakes
| Problem | Improvement |
|---|---|
| A pie chart with 40 accounts | Use a sorted bar chart or table |
| A line chart for unrelated categories | Use bars for category comparison |
| Two metrics with different units on one axis | Separate visuals or clearly configured axes |
| No period shown | Display selected time range |
| Missing values treated as zero | Explain source gaps and freshness |
| A giant unfiltered table | Add 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
- Create an hourly sign-in failure trend.
- Create a ranked bar chart of the top ten accounts.
- Add an event-level evidence table beneath the charts.
- Display the source, measurement definition and selected period.
- Add a clearly defined failure percentage or unique-account measure.
- Test a shorter and longer time range.
- Explain one scenario in which the chart could mislead an analyst.
- 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.
