Workbooks and reporting

On the morning of Friday, November 13, the day after the sprint ended, Nadia had a meeting with Sunil's manager and the chief financial officer about the Sentinel budget. She wanted to show three things: that the data was arriving, that the rules were finding real problems, and that the SOC was getting faster at handling them. None of that is visible in an incident queue. It needs reports, and in Sentinel reports are mostly built with workbooks. Ama spent the last hour of the sprint on November 12 building the workbook Nadia took to the meeting.

What workbooks are

Sentinel workbooks are Azure Monitor workbooks connected to the security workspace. A workbook is an interactive report made of steps: text, parameters such as a time range or a store, KQL queries, and visualizations of their results as tables, charts, tiles, maps or grids. Because every step is a query against the workspace, a workbook is always current, and an analyst can change the time range or filter and see the effect immediately. Workbooks are Azure resources saved in a resource group, so Sunil kept the SOC's in rg-secops-prod-cc with everything else.

Most content hub solutions include workbook templates, and installing a solution makes them available in the workbooks gallery. A template becomes a saved workbook when you save a copy, which you can then edit; saved copies are not updated when the template changes. Creating or editing a workbook needs the Workbook Contributor role, or Sentinel Contributor with it, as lesson 2 described; viewing one needs only Sentinel Reader. In the Defender portal, workbooks are found under Microsoft Sentinel in the navigation.

Measuring the SOC

The workbook Nadia needed most was the one nobody else provides: how the SOC itself is doing. The SecurityIncident table holds a record for every incident and every change to it, including when it was created, when it was first assigned or modified, when it was closed, its severity, its classification and the analytics rules that produced its alerts. From it you can calculate the two measures SOCs most often report. Mean time to acknowledge (MTTA) is the average time from an incident being created to someone starting work on it. Mean time to resolve (MTTR) is the average time from creation to closure. Both are better shown with medians as well, because one long incident can distort an average.

SecurityIncident
| where TimeGenerated > ago(14d)
| summarize arg_max(TimeGenerated, *) by IncidentNumber
| where Status == "Closed"
| extend MinutesToAck = datetime_diff("minute", FirstModifiedTime, CreatedTime),
         HoursToClose = datetime_diff("minute", ClosedTime, CreatedTime) / 60.0
| summarize Incidents = count(), MedianAckMinutes = percentile(MinutesToAck, 50),
            MedianCloseHours = percentile(HoursToClose, 50) by Severity, Classification

The arg_max step is the detail that catches people. SecurityIncident is written every time an incident changes, so a simple count counts changes, not incidents. Keeping only the latest record per incident number fixes that. Microsoft also notes that after a workspace is connected to the Defender portal, SecurityIncident no longer contains the Description field, so reports that relied on it need another source for that text.

from statistics import mean, median
# minutes to first action and hours to close for the incidents closed in the sprint
incidents = [
    ("High", 6, 1.5), ("High", 9, 2.2), ("Medium", 14, 3.0), ("Medium", 18, 4.1),
    ("Medium", 25, 3.8), ("Medium", 31, 5.6), ("Low", 40, 6.0), ("Low", 55, 26.0),
    ("Medium", 22, 3.9), ("High", 11, 2.0),
]
ack = [a for _, a, _ in incidents]
close = [c for _, _, c in incidents]
print(f"Incidents: {len(incidents)}")
print(f"Time to first action: mean {mean(ack):.1f} min, median {median(ack):.1f} min")
print(f"Time to close:        mean {mean(close):.2f} h,  median {median(close):.2f} h")
for sev in ("High", "Medium", "Low"):
    c = [h for s, _, h in incidents if s == sev]
    print(f"  {sev:<6} {len(c)} incidents, median close {median(c):.1f} h")

Incidents: 10
Time to first action: mean 23.1 min, median 20.0 min
Time to close:        mean 5.81 h,  median 3.85 h
  High   3 incidents, median close 2.0 h
  Medium 5 incidents, median close 3.9 h
  Low    2 incidents, median close 16.0 h

High-severity incidents had the shortest times, which was what Nadia wanted to show: the queue was being worked in the right order. The one slow incident, the reporting vendor's new location from lesson 9, had waited a day for the vendor to reply, which Ama labelled in the workbook so nobody would read it as a slow analyst.

Showing that rules earn their keep

Nadia's second point for the meeting was that the rules were finding real problems. The workbook showed, for each analytics rule, how many incidents it had produced and how they were classified when closed: true positive, benign positive (real activity that was allowed, such as the penetration test) or false positive. A rule with many incidents and nearly all false positives is a tuning task; a rule with no incidents at all may be fine, or may be watching data that never arrives. Over the two weeks the till PC rule had produced the most incidents, almost all from the expired service account password, which the automation rule in lesson 10 now closed automatically, while the new-country-then-MFA-change rule had found one true positive, a contractor's account signing in from a phishing kit's hosting address, contained the same day.

The Defender portal also offers SOC optimization, a set of recommendations Microsoft generates from your own workspace: tables you ingest but never query, threats for which you have the data but no detections, and similar suggestions about coverage and value. Ama added its recommendations as a link on the workbook's last page, because they answer the CFO's likely question about whether the company was paying for data it did not use.

Reporting outside the SOC

Not everyone who needs security reports should open the Defender portal. For the executive team, Nadia planned a one-page monthly summary: incidents by severity and outcome, median response times, the largest risks found and fixed, and cost against budget. The figures come from the workbook's queries, exported each month, rather than from a separate data pipeline. A Power BI report connected to the workspace was considered and postponed: it would be useful once the Finch Data Hub described in other courses existed, but for one page a month it was more machinery than the SOC needed.

For auditors, the evidence is different. They want to see that the controls work: that data has been collected continuously, that rules ran, that incidents were handled according to the procedure. The data collection health workbook, the SentinelHealth table and the SecurityIncident history together show that, and the two-year retention for incidents and alerts set in lesson 5 means the evidence will still exist when the auditors ask.

The sprint in review

At the end of the session Ama wrote a one-page review of the sprint for the design record. It listed what was built and what was planned, using the same distinction the whole course has used: what had been done by November 13, and what would happen later.

Designing a workbook people will read

A workbook that tries to show everything shows nothing. Ama followed a few rules Nadia gave her. Put the answer at the top: the four tiles say in a glance whether the week was normal. Use parameters so one workbook serves several audiences, such as a store filter for the regional managers and a severity filter for the SOC. Label anything that needs explaining in the workbook itself, as she did with the slow vendor incident. And show the trend, not just the total: a line of incidents per day over 14 days says more than a single count.

Workbook queries run every time someone opens or refreshes the workbook, and against the data lake they would be billed per data scanned. The SOC weekly workbook queries only the analytics tier and only 14 days, so it is quick and costs nothing extra; Sunil noted that any future workbook reading lake data should use a scheduled KQL job to summarize the data first and query the summary instead.

The CFO took the menu. The budget stayed as planned, with the two low-risk savings to be applied in December and reported in the January cost review. Ama noted that the most useful thing in the meeting had not been any single chart, but being able to say, for every gigabyte, what it was for.