13.3 Custom Tables, Retention & Purview Audit

13.3 Custom Tables, Retention & Purview Audit

Quick Answer: Store nonstandard ingested data in custom tables (usually name_CL) driven by DCRs and modern ingestion APIs. Keep analytics (interactive) retention long enough for SOC KQL/alerts, and use long-term retention/archive for cheaper compliance storage. For Microsoft 365/Entra admin and user activity, query Microsoft Purview Audit (and related Defender XDR investigation views) as part of end-to-end investigations.

SC-500 skills include create custom log tables, implement data retention in Microsoft Sentinel data stores, and query Microsoft Purview Audit in Defender XDR. This section connects storage design, cost, and investigation telemetry outside pure network/endpoint tables.

---

Custom log tables for ingested data

Built-in tables (SecurityEvent, AzureActivity, CommonSecurityLog, and similar) have fixed schemas. When a source does not map cleanly — or a partner connector defines its own stream — you create a custom table.

Naming and schema rules

Creation pattern (portal mental model)

1. Log Analytics workspace -> Tables -> Create.
2. Name the table, choose table plan.
3. Associate or create a data collection rule and, when required, a data collection endpoint.
4. Provide sample JSON to infer schema.
5. Optionally add a KQL transformation (transformKql) to reshape/filter before storage.
6. Create the table; point the connector/API/outputStream (for example Custom-MyTable_CL) at it.
7. Ingest test events; query MyTable_CL | take 10.

Custom tables are also created implicitly by many Content hub connectors that deploy Something_CL destinations. Engineers still need to understand schema ownership: when you change columns, update DCRs that write to the table.

Scenario: A SaaS firewall API pushes JSON that is not CEF. You create VendorFirewall_CL with Analytics plan, DCR transform extracting SrcIp and Action, and build analytics rules against that table.

Exam trap: Treating custom tables as free-form Blob folders is wrong. They are Log Analytics tables with plans, retention, and KQL access rules.

---

Interactive retention vs long-term retention (archive)

Log Analytics (and therefore Sentinel) retains table data in two conceptual states:

Analytics (interactive) retention

• Data is available for interactive KQL, analytics rules, workbooks, and near-real-time SOC workflows.
• Default for many tables: 30 days (some tables such as Usage / AzureActivity historically include longer free defaults such as 90 days — know the idea of special defaults).
• Can be extended (commonly up to 730 days / about 2 years for Analytics plan tables) at additional retention cost.
• Lowering below about 31 days generally does not save money because a baseline analytics retention window is included with ingestion pricing.

Long-term retention (archive)

• Configured by raising total retention beyond interactive retention (total retention can reach up to 12 years via portal/API capabilities).
• After interactive period ends, data remains in the table as low-cost long-term storage.
• Not used like hot interactive data for all table-plan features; access is typically via search jobs that pull matching data into a temporary search results table for investigation.

Where you configure it

• Workspace default analytics retention (applies to Analytics tables still on defaults).
• Table-level retentionInDays (interactive) and totalRetentionInDays (interactive + long-term).
• Sentinel engineers often set longer interactive retention on high-value security tables (SecurityEvent, identity logs) and shorter interactive windows on bulky verbose streams while keeping long-term if compliance requires.

Scenario: Finance requires one year of firewall CEF availability but analysts only interactively query 60 days. Set interactive retention 60 days and total retention 365 days so days 61-365 sit in long-term retention until a search job restores a window for an investigation.

---

Cost-aware design (high level)

SC-500 will not require memorizing price sheets. It will test whether you design for cost without destroying detections:

1. Do not ingest what you will never query — XPath, facility filters, DCR transforms, diagnostic categories.
2. Right table plan — Analytics for detection/hunting; Basic/Auxiliary patterns for infrequently queried high-volume data when appropriate.
3. Right retention — short interactive where possible; long-term for cold compliance.
4. Avoid duplication — CEF+Syslog double paths, multiple destinations, dual agents.
5. Separate noisy non-security telemetry from the SOC workspace when architecture allows (dedicated workspaces or filtered streams).
6. Watch free/non-billable quirks — some tables/flags (_IsBillable) behave differently; still design intentionally.

Exam trap: Setting everything to maximum interactive retention forever maximizes cost. Prefer interactive for operational windows and archive for long tail.

---

Query Microsoft Purview Audit in Defender XDR

Microsoft Purview Audit (unified audit log) records user and admin activities across Microsoft 365 services (Exchange, SharePoint, OneDrive, Teams, Entra directory activities, Power BI when enabled, and many more). For SC-500 security engineers, Purview Audit is a critical investigation source when cloud identity/data actions matter — not only Azure resource logs in Sentinel.

Why it appears next to Sentinel on the blueprint

Modern Microsoft SOCs investigate across:

• Sentinel workspace logs (infra, network, custom, many security products)
• Defender XDR advanced hunting / incidents
• Purview Audit for rich Microsoft 365 and directory operational truth

Blueprint language: Query Microsoft Purview Audit in Defender XDR emphasizes using the unified security operations experience — searching audit activity while working investigations in the Defender portal, not only in a siloed compliance UI.

How operators query audit (exam-level)

Practical search tips:

• Confirm unified audit ingestion is enabled (UnifiedAuditLogIngestionEnabled).
• Retention depends on licensing (Standard vs Premium/E5-style longer retention and policies).
• Core workload events often appear within roughly an hour but are not hard real-time SLAs.
• Use precise operation names when friendly filters are insufficient.
• Export results for case notes; scope by admin units when using restricted admin models.

Scenario: Sentinel incidents show impossible travel for a user, but the decisive evidence is a Purview Audit record of FileDownloaded from SharePoint at a rare IP plus an Entra admin role activation. The engineer queries Purview Audit in the unified Defender/Purview investigation flow, attaches findings to the incident, and only then runs containment playbooks.

Exam trap: Believing Purview Audit replaces Windows Security events or Azure Activity is wrong. It is complementary — M365/SaaS and directory activity versus OS/control-plane/infra streams.

Exam trap: Assuming every audit record lives in SecurityEvent is wrong. Purview unified audit is its own system (searchable in Purview/Defender experiences and optionally exported); do not conflate it with AMA Security channel collection.

---

Putting storage + audit together

A complete activity-collection design for SC-500 looks like:

1. Built-in tables via Microsoft and AMA connectors (section 13.2).
2. Custom _CL tables for residual sources.
3. Interactive retention tuned for SOC; long-term retention for compliance.
4. Purview Audit queries during human investigations of Microsoft 365 and identity admin actions.
5. Automation (next section) that enriches incidents using these sources carefully and with least privilege.

---

Exam traps checklist

Design custom tables correctly, separate interactive versus long-term retention for cost and compliance, and query Purview Audit during Defender XDR investigations — and you cover the storage and unified-audit slice of SC-500 Sentinel skills.