Study guide

13.3 Custom Tables, Retention & Purview Audit

Microsoft Certified: Cloud and AI Security Engineer Associate SC-500

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

1.1 Current Exam Facts 1.2 Skills Measured & Weightings 1.3 Study Path, Prerequisites & Exam Strategy

2 Microsoft Entra ID Secure Access

2.1 Privileged Identity Management (PIM) 2.2 Conditional Access Policies 2.3 MFA and Passwordless Authentication 2.4 Application Identities, OAuth Consent & Managed Identities

3 Azure Key Vault Secrets and Keys

3.1 Deploy and Configure Key Vault 3.2 Access Control and Firewall for Key Vault 3.3 Keys, Secrets, Certificates & Defender for Key Vault

4 Governance, Policy & RBAC

4.1 Azure Policy for Security Controls 4.2 Defender for Cloud Compliance & Security Standards 4.3 Resource Locks and Azure RBAC 4.4 Custom Roles, Overprivileged Access, Backup & IaC Security

5 Secure Storage Accounts

5.1 Storage Account Security Fundamentals 5.2 Storage Firewalls and Access Policies 5.3 Defender for Storage

6 Secure Databases

6.1 Azure SQL Platform Security 6.2 Database Auditing 6.3 Defender for Databases

7 Network Security Groups & Virtual Network Manager

7.1 Network Security Groups and Application Security Groups 7.2 Azure Virtual Network Manager Security Admin Rules 7.3 Virtual WAN and VPN Security

8 Private Access, Private Link & Azure Firewall

8.1 Microsoft Entra Private Access 8.2 Private Endpoints and Private Link 8.3 Azure Firewall 8.4 Network Watcher Diagnostics and Effective Rules

9 Security for AI Workloads

9.1 Purview DSPM for AI and Data Overexposure 9.2 Copilot Studio Protection and Agent Management 9.3 Microsoft Entra Agent ID Security 9.4 Defender for AI, Foundry Guardrails & AI Gateway

10 Secure Servers and Virtual Machines

10.1 Disk Encryption 10.2 Azure Bastion and JIT VM Access 10.3 Azure Arc and Defender for Servers 10.4 Trusted Launch, VM Security Features & Machine Configuration

11 Application Platform Security

11.1 Containers: AKS, ACR, ACI, Container Apps & Defender for Containers 11.2 Azure Functions and Logic Apps Security 11.3 Azure App Service Security 11.4 Web Application Firewall and API Management Protection

12 Defender for Cloud Security Posture

12.1 Defender CSPM and Secure Score 12.2 Workload Protection Plans and Multicloud Connectors 12.3 Vulnerability Management and Defender EASM

13 Microsoft Sentinel Activity and Event Collection

13.1 Workspaces, Roles & Content Hub 13.2 Data Connectors, Syslog/CEF & Windows Event Collection 13.3 Custom Tables, Retention & Purview Audit 13.4 Automation Rules and Playbooks

14 Microsoft Security Copilot & Final Review

14.1 Microsoft Security Copilot Workspaces and Roles 14.2 Plugins and Security Agents 14.3 Final Review and Exam-Day Strategy

13.3 Custom Tables, Retention & Purview Audit

Key Takeaways Explain This Topic AI

Custom log tables (typically *_CL ) store nonstandard or partner-ingested schemas defined with DCRs/Logs Ingestion API and must include TimeGenerated.

Analytics (interactive) retention keeps data queryable for real-time KQL, alerts, and workbooks; long-term retention (archive) is lower cost and is accessed mainly via search jobs.

Default analytics retention is often 30 days (some tables 90); analytics retention can extend up to about 2 years and total retention up to 12 years depending on configuration and tools.

Cost-aware design filters at ingestion, chooses table plans wisely, and keeps hot retention only as long as SOC analytics truly need.

Query Microsoft Purview unified audit in Defender XDR / Purview for investigation of Microsoft 365 and directory activities—complementary to Sentinel workspace logs.

Last updated: July 2026

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

  • Rule Detail
  • Suffix Custom tables typically end with _CL (portal may append automatically)
  • Required column TimeGenerated (datetime of the record; platform may inject if missing)
  • Plans Analytics (default interactive SOC use), Basic , or Auxiliary/Lake (cost/query tradeoffs)
  • Column naming Letters/digits/underscores; custom columns on Azure tables often use _CF suffix patterns
  • Billing caution Table names appear in billing context — avoid sensitive strings in names
  • Creation pattern (portal mental model)

Log Analytics workspace -> Tables -> Create .

Name the table, choose table plan .

Associate or create a data collection rule and, when required, a data collection endpoint .

Provide sample JSON to infer schema.

Optionally add a KQL transformation ( transformKql ) to reshape/filter before storage.

Create the table; point the connector/API/ outputStream (for example Custom-MyTable_CL ) at it.

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.

  • Need Design choice
  • Daily SOC hunting last 30-90 days Interactive analytics retention
  • Rare forensics / regulatory 1-7+ years Long-term total retention + search jobs when needed
  • High-volume low-value debug logs Filter out, use cheaper table plan, or short total retention
  • 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:

Do not ingest what you will never query — XPath, facility filters, DCR transforms, diagnostic categories.

Right table plan — Analytics for detection/hunting; Basic/Auxiliary patterns for infrequently queried high-volume data when appropriate.

Right retention — short interactive where possible; long-term for cold compliance.

Avoid duplication — CEF+Syslog double paths, multiple destinations, dual agents.

Separate noisy non-security telemetry from the SOC workspace when architecture allows (dedicated workspaces or filtered streams).

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)

Interface What you do

Microsoft Purview portal -> Audit Start search jobs by time range, users, activities (friendly names or operation names), record types, workloads, files/sites

  • Exchange Online PowerShell Search-UnifiedAuditLog for scripted searches
  • Office 365 Management Activity API Programmatic pull for pipelines/SIEM enrichment

Defender XDR investigation context Use unified portal workflows and hunting tables populated by connected Microsoft 365 / Defender for Cloud Apps style activity while correlating with Sentinel incidents

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:

Built-in tables via Microsoft and AMA connectors (section 13.2).

Custom _CL tables for residual sources.

Interactive retention tuned for SOC; long-term retention for compliance.

Purview Audit queries during human investigations of Microsoft 365 and identity admin actions.

Automation (next section) that enriches incidents using these sources carefully and with least privilege.

Exam traps checklist

  • Trap Reality
  • Custom tables need no TimeGenerated Schema expects TimeGenerated
  • Archive data is fully interactive forever Long-term needs search jobs (not the same as hot analytics)

Cutting interactive retention to 7 days always saves cost Baseline analytics retention is largely included with ingestion

  • Purview Audit is only for legal hold, not SOC SOC uses it heavily for M365/identity investigations
  • _CL suffix is optional chaos Custom logs convention is _CL

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.