{"id":5518,"date":"2025-08-13T18:42:38","date_gmt":"2025-08-13T17:42:38","guid":{"rendered":"https:\/\/mortenknudsen.net\/?p=5518"},"modified":"2025-08-20T16:22:27","modified_gmt":"2025-08-20T15:22:27","slug":"script-sentinel-data-lake-table-management","status":"publish","type":"post","link":"https:\/\/mortenknudsen.net\/?p=5518","title":{"rendered":"Script: Sentinel Data Lake Table Management"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Microsoft Sentinel\u2019s data lake story is quietly powerful: you get fast, 90-day <strong>Analytics <\/strong>(Shortterm) for hunting and detections, plus scalable, lower-cost <strong>DataLake <\/strong>(Longterm)<strong> <\/strong>retention for compliance, threat intel enrichment, and deep forensics. That unlocks richer investigations, more complete timelines, and simpler evidence handling\u2014without forcing everything to live in the hot tier.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The catch? <strong>Tables never stop multiplying.<\/strong> New connectors, product teams, and solutions are constantly adding tables (and renaming them), each with its own defaults and quirks. Keeping every table aligned to your retention standards\u2014today and three months from now\u2014becomes a game of whack-a-mole: some stick at 30 days, others creep beyond policy, and new tables quietly arrive with mismatched settings.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"281\" src=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-20-1024x281.png\" alt=\"\" class=\"wp-image-5532\" srcset=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-20-1024x281.png 1024w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-20-300x82.png 300w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-20-768x211.png 768w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-20-1536x421.png 1536w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-20.png 1714w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">The problem we\u2019re actually solving<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Sentinel\u2019s model is great: keep recent data hot for fast hunting (<strong>Analytics<\/strong> Shortterm) and push older data into lower-cost storage (<strong>DataLake<\/strong> Longterm). In practice, though, <strong>retention is set per table<\/strong>, not just per workspace\u2014and new tables appear constantly.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">That creates three recurring pain points:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Default drift<\/strong><br>Many tables land with short analytics windows (often 30 days) even though 90 days is available\/desired. Others arrive with different defaults. Over time, your estate becomes a patchwork of mismatched retentions.<\/li>\n\n\n\n<li><strong>Table sprawl<\/strong><br>New connectors, new solutions, renamed tables\u2014there\u2019s a steady stream. Yesterday\u2019s configuration is incomplete by next week. Manually chasing these is error-prone and doesn\u2019t scale.<\/li>\n\n\n\n<li><strong>Inconsistent policy enforcement<\/strong><br>Compliance might want 180\/365\/3650 days for some logs; IR wants 90 days hot for investigations. Without automation, some tables never get the long-term setting, while others accidentally exceed policy and cost.<\/li>\n<\/ol>\n\n\n\n<h3 class=\"wp-block-heading\">Why it\u2019s hard (the important challenges)<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Per-table reality vs. workspace intent<\/strong><br>You can have a tidy policy on paper, but every individual table has its own <code>Analytics<\/code> and <code>DataLake<\/code> values. One-off fixes don\u2019t hold as new tables arrive.<\/li>\n\n\n\n<li><strong>Constant change<\/strong><br>Microsoft and ISV connectors add and evolve tables frequently. What you aligned last month isn\u2019t the full set this month.<\/li>\n\n\n\n<li><strong>Signal diversity<\/strong><br>Not all data deserves the same treatment. SecurityEvent vs. SigninLogs vs. high-volume custom logs have different value\/cost profiles. You need patterns (e.g., <code>Device*Events<\/code>, <code>*_CL<\/code>) to set sane defaults by family.<\/li>\n\n\n\n<li><strong>Cost &amp; performance trade-offs<\/strong><br>Setting every table to long retention can be expensive; keeping everything hot is unnecessary. The trick is a <strong>consistent, selective<\/strong> long-term policy while standardizing analytics to a meaningful window.<\/li>\n\n\n\n<li><strong>Risk of accidental data loss<\/strong><br>Lowering analytics days can trim older hot data. Changes must be explicit, reviewable, and previewable.<\/li>\n\n\n\n<li><strong>Multi-subscription scale<\/strong><br>Large orgs span many subscriptions\/tenants. Manual portal clicks won\u2019t cut it; you need idempotent, cross-subscription automation.<\/li>\n\n\n\n<li><strong>Ops safety<\/strong><br>You want \u201cmake it so\u201d\u2014but only where current \u2260 desired. Anything else creates noise and risk.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">What \u201cgood\u201d looks like<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Standardize Analytics to 90 days<\/strong> for Sentinel tables (where appropriate) so investigators have a meaningful free window.<\/li>\n\n\n\n<li><strong>Apply DataLake (longterm) selectively<\/strong> by table family or pattern (e.g., 180 for auth, 365\/730+ for high-value sources, lighter for noisy custom logs).<\/li>\n\n\n\n<li><strong>Express policy as code<\/strong> (CSV patterns + script) so new tables are automatically captured on the next run.<\/li>\n\n\n\n<li><strong>Idempotent enforcement<\/strong>: compare expected vs. actual per table; change only what\u2019s different.<\/li>\n\n\n\n<li><strong>WhatIf by default<\/strong>: preview the blast radius before applying.<\/li>\n\n\n\n<li><strong>Auditable output<\/strong>: clear \u201cin-scope \/ out-of-scope\u201d lists, change summaries, and a CSV artifact for evidence.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">How the solution addresses the challenges<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Pattern-based coverage<\/strong><br>You can target families (<code>SecurityEvent<\/code>, <code>Device*Events<\/code>, <code>*_CL<\/code>) and <strong>TYPE tokens<\/strong> (<code>TYPE:SENTINEL<\/code>, <code>TYPE:CUSTOM<\/code>) so new tables match your policy without new lines every time.<\/li>\n\n\n\n<li><strong>Built-in guardrails<\/strong><br>The script validates ranges, honors existing plan unless you specify, and supports a safe <strong>auto-upgrade<\/strong> (Analytics 30 \u2192 90) for Sentinel tables to avoid under-retention.<\/li>\n\n\n\n<li><strong>Scale &amp; safety<\/strong><br>It works across subscriptions\/tenants, supports interactive or service-principal login, and is <strong>idempotent<\/strong>\u2014only deltas are applied. <code>WhatIf<\/code> makes it easy to review before committing.<\/li>\n\n\n\n<li><strong>Operational clarity<\/strong><br>Output starts with out-of-scope tables (so you see what you\u2019re <em>not<\/em> touching), then shows \u201cRetention Setting after Change\u201d and a detailed \u201cWhatIf Changes\u201d section, plus totals.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">The stakes (why this matters)<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Future-proofing<\/strong><br>As tables proliferate, your policy still applies\u2014without manual rework.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Investigation quality<\/strong><br>A consistent 90-day analytics window vastly reduces \u201cwe no longer have the data\u201d dead-ends.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Compliance confidence<\/strong><br>You can prove which tables are kept longer (and which aren\u2019t), with evidence.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Cost control<\/strong><br>Long-term retention is targeted, not accidental; analytics stays hot where it counts, not everywhere.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">What I have build<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">To turn the data-lake promise into day-to-day reality, I built a lightweight, repeatable script (powershell) approach &#8211; with lots of scenarios supported.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"571\" src=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-12-1024x571.png\" alt=\"\" class=\"wp-image-5520\" srcset=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-12-1024x571.png 1024w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-12-300x167.png 300w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-12-768x428.png 768w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-12-1536x856.png 1536w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-12.png 1773w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>CSV-driven intent \u2192 enforced configuration<\/strong><br>Describe target settings once (per table, wildcard, or TYPE token) and the script compares <strong>expected vs. actual<\/strong>, changing only what\u2019s different (idempotent).<\/li>\n\n\n\n<li><img decoding=\"async\" src=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-16-1024x650.png\" alt=\"\"><\/li>\n\n\n\n<li><strong>Positive defaulting for analysts<\/strong><br>Automatically upgrades Sentinel tables from <strong>30 \u2192 90 days<\/strong> in <strong>Analytics_Shortterm<\/strong> (cost-neutral for Sentinel) while letting you choose <strong>DataLake (Longterm)<\/strong> e.g., 180, 365, 3650 days where it truly adds value.<\/li>\n\n\n\n<li><img decoding=\"async\" src=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-12-1024x571.png\" alt=\"\"><\/li>\n\n\n\n<li><img decoding=\"async\" src=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-14-1024x190.png\" alt=\"\"><\/li>\n\n\n\n<li><strong>Covers the firehose of new tables<\/strong><br>Use patterns (<code>Device*Events<\/code>, <code>*_CL<\/code>) and <strong>TYPE:SENTINEL \/ TYPE:CUSTOM<\/strong> so that <em>newly introduced tables<\/em> are captured by policy on the next run\u2014no manual chase.<\/li>\n\n\n\n<li><img decoding=\"async\" src=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-15-1024x104.png\" alt=\"\"><\/li>\n\n\n\n<li><strong>Safe, transparent operations<\/strong><br><code>WhatIf<\/code> previews, grouped workspace headers, clear \u201cwould change\u201d vs \u201cno change,\u201d and a CSV export for audit.<\/li>\n\n\n\n<li><strong>Enterprise-ready<\/strong><br>Works across subscriptions and tenants, supports <strong>interactive<\/strong> or <strong>service principal (secret)<\/strong> login, and respects per-table <strong>Plan<\/strong> (Analytics\/Basic) when you set it.<\/li>\n\n\n\n<li><img decoding=\"async\" src=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-16-1024x650.png\" alt=\"\"><\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Bottom line:<\/strong> Sentinel\u2019s data lake gives us the breadth and depth we want &#8211; but hopefully by automation we can keep it <strong>consistent, compliant, and future-proof<\/strong> as the table landscape keeps growing.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<div class=\"wp-block-buttons is-layout-flex wp-block-buttons-is-layout-flex\">\n<div class=\"wp-block-button is-style-fill\"><a class=\"wp-block-button__link has-base-3-color has-contrast-2-background-color has-text-color has-background has-link-color wp-element-button\" href=\"https:\/\/github.com\/KnudsenMorten\/DefenderRepo\/tree\/main\/Sentinel-Data-Lake-Table-Management\">Download scripts (Github)<\/a><\/div>\n<\/div>\n\n\n\n<div class=\"wp-block-buttons is-layout-flex wp-block-buttons-is-layout-flex\">\n<div class=\"wp-block-button is-style-outline is-style-outline--1\"><a class=\"wp-block-button__link has-base-3-color has-contrast-2-background-color has-text-color has-background has-link-color wp-element-button\" href=\"https:\/\/raw.githubusercontent.com\/KnudsenMorten\/DefenderRepo\/refs\/heads\/main\/Sentinel-Data-Lake-Table-Management\/Sentinel-Data-Lake-Retentions.ps1\" target=\"_blank\" rel=\"noreferrer noopener\">GitHub &#8211; Sentinel-Data-Lake-Retentions.ps1<\/a><\/div>\n<\/div>\n\n\n\n<div class=\"wp-block-buttons is-layout-flex wp-block-buttons-is-layout-flex\">\n<div class=\"wp-block-button is-style-outline is-style-outline--2\"><a class=\"wp-block-button__link has-base-3-color has-contrast-2-background-color has-text-color has-background has-link-color wp-element-button\" href=\"https:\/\/raw.githubusercontent.com\/KnudsenMorten\/DefenderRepo\/refs\/heads\/main\/Sentinel-Data-Lake-Table-Management\/Sentinel-Data-Lake-Retentions.csv\" target=\"_blank\" rel=\"noreferrer noopener\">GitHub &#8211; Sentinel-Data-Lake-Retentions.csv<\/a><\/div>\n<\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Blog Content (quick links)<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"#background\" data-type=\"internal\" data-id=\"#background\">Background: How Sentinel stores &amp; prices data<\/a><\/li>\n\n\n\n<li><a href=\"#analytics\" data-type=\"internal\" data-id=\"#analytics\">Analytics tier (short-term, \u201chot\u201d)<\/a><\/li>\n\n\n\n<li><a href=\"#longterm\" data-type=\"internal\" data-id=\"#longterm\">Long-term retention (\u201cdata lake\u201d \/ total retention)<\/a><\/li>\n\n\n\n<li><a href=\"#twostates\">Two states per table<\/a><\/li>\n\n\n\n<li><a href=\"#maximums\">Maximums<\/a><\/li>\n\n\n\n<li><a href=\"#script-what\" data-type=\"internal\" data-id=\"#script-what\">What the script does?<\/a><\/li>\n\n\n\n<li><a href=\"#script-synopsis\" data-type=\"internal\" data-id=\"#script-synopsis\">Script synopsis (detailed)<\/a><\/li>\n\n\n\n<li><a href=\"#benefits\" data-type=\"internal\" data-id=\"#benefits\">Why this matters (benefits)<\/a><\/li>\n\n\n\n<li><a href=\"#Common-rollout-scenarios-with-CSV-file\">Common rollout scenarios with CSV-file<\/a><\/li>\n\n\n\n<li><a href=\"#Configuration\/scoping-of-tables-via-CSV-file\">Configuration\/scoping of tables via CSV-file<\/a><\/li>\n\n\n\n<li><a href=\"#How-to-implement-(quick-guide)\" data-type=\"internal\" data-id=\"#How-to-implement-(quick-guide)\">How to implement (quick guide)<\/a><\/li>\n\n\n\n<li><a href=\"#whatif\" data-type=\"internal\" data-id=\"#whatif\">Implementation in WhatIf Mode<\/a><\/li>\n\n\n\n<li><a href=\"#governance\" data-type=\"internal\" data-id=\"#governance\">Governance<\/a><\/li>\n\n\n\n<li><a href=\"#tips\" data-type=\"internal\" data-id=\"#tips\">Practical tips &amp; gotchas<\/a><\/li>\n\n\n\n<li><a href=\"#references\" data-type=\"internal\" data-id=\"#references\">Sources (key references)<\/a><\/li>\n\n\n\n<li><a href=\"#script-variables\" data-type=\"internal\" data-id=\"#script-variables\">Detailed: Script Variables<\/a><\/li>\n\n\n\n<li><a href=\"#script\" data-type=\"internal\" data-id=\"#script\">Script &#8211; Set-Sentinel-Data-Lake-Retentions.ps1<\/a><\/li>\n\n\n\n<li><a href=\"#datafile\" data-type=\"internal\" data-id=\"#datafile\">Scoping\/Configuration file (sample)<\/a><\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"background\">Background: How Sentinel stores &amp; prices data<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"analytics\">Analytics tier (short-term, \u201chot\u201d)<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Used for day-to-day hunting, analytics rules, and fast queries.<\/li>\n\n\n\n<li><strong>Analytics tier (short-term) retention can\u2014and should\u2014be set to 90 days.<\/strong> Sentinel includes <strong>90 days of analytics-tier retention at no extra cost<\/strong>; many workspaces are still left at the 30-day default. Moving from 30 \u2192 90 days gives analysts more history without increasing Sentinel costs. <a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/sentinel\/billing?utm_source=chatgpt.com\" target=\"_blank\" rel=\"noreferrer noopener\">Microsoft Learn+2Microsoft Learn+2<\/a><\/li>\n\n\n\n<li><strong>Default<\/strong> in many workspaces is <strong>30 days<\/strong>, but <strong>Sentinel includes 90 days free<\/strong>. If you\u2019re still at 30, raising to 90 <strong>does not increase Sentinel cost<\/strong>. <a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/sentinel\/manage-data-overview?utm_source=chatgpt.com\" target=\"_blank\" rel=\"noreferrer noopener\">Microsoft Learn+1<\/a><\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"longterm\">Long-term retention (\u201cdata lake\u201d \/ total retention)<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>A lower-cost tier meant for regulatory\/audit look-backs and rare investigations.<\/li>\n\n\n\n<li><strong>Long-term retention<\/strong> is the portion of a table\u2019s <strong>total retention<\/strong> that extends <strong>beyond<\/strong> its analytics (interactive) retention. By default, most tables keep <strong>30 days<\/strong> in analytics, with <strong>no long-term<\/strong> unless you set <strong>total retention higher than analytics<\/strong>. You can configure <strong>total retention up to 12 years<\/strong> (Portal\/API), and analytics up to <strong>730 days<\/strong>. In Microsoft Sentinel, <strong>the first 90 days of analytics retention are included<\/strong> at no extra charge; raising analytics from 30 \u2192 90 <strong>doesn\u2019t increase Sentinel cost<\/strong>, while any retention beyond 90 days is billed per standard Log Analytics retention pricing.<\/li>\n<\/ul>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\">References: Microsoft\u2019s Sentinel billing guidance confirms <strong>90 days are included at no charge<\/strong>; Log Analytics table defaults are often 30 days unless changed. <a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/sentinel\/billing?utm_source=chatgpt.com\" target=\"_blank\" rel=\"noreferrer noopener\">Microsoft Learn+1<\/a><\/p>\n<\/blockquote>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"twostates\">Two states per table<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Analytics retention<\/strong> (interactive\/hot) and <strong>long-term retention<\/strong> (low-cost). Long-term is the period <strong>between<\/strong> <em>total retention<\/em> and <em>analytics retention<\/em>. <a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/azure-monitor\/logs\/data-retention-configure\" target=\"_blank\" rel=\"noreferrer noopener\">Microsoft Learn<\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Defaults:<\/strong> Most tables default to <strong>30 days<\/strong> analytics; a few have <strong>90-day<\/strong> defaults (for example <code>Usage<\/code>, <code>AzureActivity<\/code>, and some App Insights tables). <a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/azure-monitor\/logs\/data-retention-configure\" target=\"_blank\" rel=\"noreferrer noopener\">Microsoft Learn<\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>When long-term actually happens:<\/strong> You only get a long-term copy when <strong><code>totalRetentionInDays<\/code> &gt; <code>retentionInDays<\/code><\/strong>. If they\u2019re equal, there\u2019s effectively <strong>no long-term<\/strong> period. Microsoft\u2019s example: set analytics to <strong>90<\/strong> while total remains <strong>180<\/strong> \u2192 data from <strong>day 90\u2013180<\/strong> is kept in long-term. <a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/azure-monitor\/logs\/data-retention-configure\" target=\"_blank\" rel=\"noreferrer noopener\">Microsoft Learn<\/a><\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"maximums\"><strong>Maximums<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Analytics<\/strong> (interactive) up to <strong>730 days (2 years)<\/strong>. <a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/azure-monitor\/logs\/data-retention-configure\" target=\"_blank\" rel=\"noreferrer noopener\">Microsoft Learn<\/a><\/li>\n\n\n\n<li><strong>Total retention<\/strong> up to <strong>12 years<\/strong> (Portal\/API today; CLI\/PowerShell currently limited to 7 years\u201412-year support \u201cwill follow\u201d). <a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/azure-monitor\/logs\/data-retention-configure\" target=\"_blank\" rel=\"noreferrer noopener\">Microsoft Learn<\/a><\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"script-what\"><br>What the script does?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The script I have created enforces a <strong>data hygiene policy<\/strong> across one or many Sentinel-connected Log Analytics workspaces:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Reads a CSV<\/strong> with: SubscriptionId, ResourceGroup, Workspace, TablePattern, AnalyticsRetentionDays, DataLakeTotalRetentionInDays, Plan (optional).<\/li>\n\n\n\n<li><strong>Resolves patterns<\/strong> (<code>SecurityEvent<\/code>, <code>Device*Events<\/code>, <code>*_CL<\/code>, or <code>TYPE:SENTINEL<\/code>, <code>TYPE:CUSTOM<\/code>) to the real tables in each workspace.<\/li>\n\n\n\n<li><strong>Compares current vs. desired<\/strong> settings and updates <strong>only when different<\/strong> (idempotent).<\/li>\n\n\n\n<li><strong>Honors plan<\/strong> if provided (Analytics\/Basic), but won\u2019t change plan if you leave it blank.<\/li>\n\n\n\n<li><strong>Optional auto-upgrade<\/strong>: if a table is treated as \u201cSentinel\u201d and CSV asks for <code>30<\/code>, script automatically targets <strong><code>90<\/code><\/strong> (since it\u2019s included) to avoid accidental under-retention.<\/li>\n\n\n\n<li><strong>Multi-subscription aware<\/strong>: switches Azure context safely for each CSV row\u2019s subscription, with either <strong>interactive<\/strong> or <strong>service principal (secret)<\/strong> login.<\/li>\n\n\n\n<li><strong>Safety first<\/strong>: run with <code>-WhatIf<\/code> to preview; produces clear \u201cin-scope summary,\u201d \u201cwhat would change,\u201d and totals.<\/li>\n<\/ul>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\">Technical guardrails: Analytics retention per-table must be <strong>4\u2013730 days<\/strong>; \u201cTotal retention\u201d (long-term) must be <strong>\u2265 0<\/strong> and <strong>greater than Analytics<\/strong> to actually enable long-term storage. These limits follow the Log Analytics table API. <a href=\"https:\/\/learn.microsoft.com\/en-us\/powershell\/module\/az.operationalinsights\/update-azoperationalinsightstable?view=azps-14.2.0&amp;utm_source=chatgpt.com\" target=\"_blank\" rel=\"noreferrer noopener\">Microsoft Learn<\/a><\/p>\n<\/blockquote>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"script-synopsis\">Script synopsis (detailed)<\/h2>\n\n\n\n<pre class=\"wp-block-code has-small-font-size\"><code>NAME\n    Set-Sentinel-Data-Lake-Retentions.ps1\n\nSYNOPSIS\n    Idempotent per-table retention &amp; plan manager for Microsoft Sentinel \/ Log Analytics workspaces.\n    Reads desired settings from a CSV and applies them only when different, with a WhatIf-safe dry run.\n\nWHAT COUNTS AS \u201cSENTINEL\u201d HERE?\n    Per your requirement: **if a table exists in the Log Analytics workspace, and the workspace is connected\n    to Sentinel, it is treated as a Sentinel table**\u2014except **Custom** tables, which the UI shows as \u201cCustom\u201d.\n    We therefore classify:\n      \u2022 Sentinel  = every Log Analytics table that is NOT Custom\n      \u2022 Custom    = schema.tableType = CustomLog  (or name like *_CL)\n      \u2022 XDR       = ignored (does not exist in Log Analytics; only in Defender) \u2014 TYPE:XDR returns none\n\nDESCRIPTION\n    - Multi-subscription \/ multi-tenant via per-row SubscriptionId.\n    - Wildcard pattern matching (* and ?) for table names.\n    - Supports special TYPE filters in CSV:\n        TYPE:SENTINEL              \u2192 all LA tables that are NOT Custom\n        TYPE:SENTINEL:ANALYTICS    \u2192 same, but Plan = Analytics\n        TYPE:CUSTOM                \u2192 all CustomLog tables (e.g., *_CL)\n        TYPE:XDR                   \u2192 (empty by design; XDR isn\u2019t in LA)\n    - Idempotent: only updates when values differ.\n    - Optional plan changes per table (Analytics\/Basic) if provided.\n    - Optional auto-upgrade: Sentinel tables with CSV=30 \u2192 90 days Analytics.\n    - Coverage report (UNMATCHED printed first; full bulleted lists), compact matched summary,\n      and detailed \u201cWhatIf\/Changes\u201d section.\n    - Exports a CSV of the full run.\n\nCSV FORMAT\n    Required columns:\n      SubscriptionId,ResourceGroup,Workspace,TablePattern,AnalyticsRetentionDays,DataLakeTotalRetentionInDays\n    Optional:\n      Plan  (Analytics | Basic) \u2014 blank to keep current.\n\n    TablePattern can be a wildcard or a TYPE token:\n      Examples:\n        SecurityEvent\n        Device*Events\n        *_CL\n        *\n        TYPE:SENTINEL\n        TYPE:SENTINEL:ANALYTICS\n        TYPE:CUSTOM\n        TYPE:XDR  # returns none (by design)\n\nEXAMPLE CSV\n    SubscriptionId,ResourceGroup,Workspace,TablePattern,AnalyticsRetentionDays,DataLakeTotalRetentionInDays,Plan\n    00000000-0000-0000-0000-000000000000,rg,ws,TYPE:SENTINEL:ANALYTICS,90,365,Analytics\n    00000000-0000-0000-0000-000000000000,rg,ws,SecurityEvent,90,180,Analytics\n    00000000-0000-0000-0000-000000000000,rg,ws,*_CL,30,730,Analytics\n\nAUTO-UPGRADE (OPTIONAL)\n    $AutoUpgradeSentinelAnalyticsFrom30To90 = $true :\n      For **Sentinel** tables (i.e., not Custom), if CSV asks for 30 days, target becomes 90.\n    $AutoUpgradeSentinelAnalyticsFrom30To90 = $false :\n      CSV value is respected.\n\nOUTPUT ORDER\n    1) Out-of-Scope tables (no match) \u2014 grouped by workspace header\n    2) Retention Setting after Change (in-scope tables) \u2014 grouped by workspace header\n    3) Retention Setting WhatIf Changes (in-scope tables) \u2014 grouped by workspace header\n\nUSAGE\n    \u2022 Run with static defaults (no params):  .\\Set-Sentinel-Data-Lake-Retentions.ps1\n    \u2022 Actually apply changes:                .\\Set-Sentinel-Data-Lake-Retentions.ps1 -WhatIf:$false\n    \u2022 Verbose logging:                       .\\Set-Sentinel-Data-Lake-Retentions.ps1 -Verbose<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"benefits\">Why this matters (benefits)<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Operational effectiveness:<\/strong> 90-day analytics history improves investigations and reduces \u201cwe don\u2019t have the data anymore\u201d moments\u2014without adding SIEM cost. <a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/sentinel\/billing?utm_source=chatgpt.com\" target=\"_blank\" rel=\"noreferrer noopener\">Microsoft Learn+1<\/a><\/li>\n\n\n\n<li><strong>Compliance &amp; audit:<\/strong> Long-term retention (180 days, 1 year, 7 years, etc.) supports regulatory evidence needs. <a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/sentinel\/manage-table-tiers-retention?utm_source=chatgpt.com\" target=\"_blank\" rel=\"noreferrer noopener\">Microsoft Learn<\/a><\/li>\n\n\n\n<li><strong>Cost control:<\/strong> You decide which tables get long-term retention. Defaulting to 30 days everywhere leaves value on the table; raising analytics to 90 (included) costs the same, while long-term retention is applied <strong>selectively<\/strong> where it\u2019s worth it. <a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/sentinel\/manage-data-overview?utm_source=chatgpt.com\" target=\"_blank\" rel=\"noreferrer noopener\">Microsoft Learn<\/a><\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"Common-rollout-scenarios-with-CSV-file\">Common rollout scenarios with CSV-file<\/h2>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\">In all examples below, <strong>increasing Analytics from 30 \u2192 90 days is cost-neutral for Sentinel<\/strong> and recommended. <a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/sentinel\/billing?utm_source=chatgpt.com\" target=\"_blank\" rel=\"noreferrer noopener\">Microsoft Learn+1<\/a><\/p>\n<\/blockquote>\n\n\n\n<h3 class=\"wp-block-heading\">Scenario A \u2014 Baseline all Sentinel tables at 90\/180<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Goal:<\/strong> Give analysts 90 days hot, keep a 6-month total copy.<\/li>\n\n\n\n<li><strong>CSV pattern:<\/strong> <code>TYPE:SENTINEL<\/code> (covers non-custom tables)<\/li>\n\n\n\n<li><strong>Example row:<\/strong><br><code>\u2026 , TYPE:SENTINEL, 90, 180, Analytics<\/code><\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Scenario B \u2014 Keep Custom tables lightweight (30\/730)<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Goal:<\/strong> Control spend on ad-hoc custom ingestion while still keeping a year+ history.<\/li>\n\n\n\n<li><strong>CSV pattern:<\/strong> <code>*_CL<\/code> or <code>TYPE:CUSTOM<\/code><\/li>\n\n\n\n<li><strong>Example row:<\/strong><br><code>\u2026 , *_CL, 30, 730, Analytics<\/code><\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Scenario C \u2014 Only bump Analytics to 90; don\u2019t enable long-term<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Goal:<\/strong> Maximize \u201cfree\u201d analytics window, skip extra long-term storage for now.<\/li>\n\n\n\n<li><strong>CSV:<\/strong> set <strong>AnalyticsRetentionDays=90<\/strong> and <strong>DataLakeTotalRetentionInDays=90<\/strong> (so total equals analytics \u2192 no long-term layer). <a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/sentinel\/manage-table-tiers-retention?utm_source=chatgpt.com\" target=\"_blank\" rel=\"noreferrer noopener\">Microsoft Learn<\/a><\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"391\" src=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-13-1024x391.png\" alt=\"\" class=\"wp-image-5521\" srcset=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-13-1024x391.png 1024w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-13-300x115.png 300w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-13-768x294.png 768w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-13-1536x587.png 1536w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-13.png 1779w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Scenario D \u2014 Fine-grain per data family<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Goal:<\/strong> Differentiate retention by signal (e.g., <code>SecurityEvent<\/code> 90\/365; <code>SigninLogs<\/code> 90\/90; Defender <code>Device*Events<\/code> 90\/120).<\/li>\n\n\n\n<li><strong>CSV patterns:<\/strong> <code>SecurityEvent<\/code>, <code>SigninLogs<\/code>, <code>Device*Events<\/code> as separate lines.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Scenario E \u2014 Respect current plan; only change retention<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Goal:<\/strong> Avoid changing Analytics\/Basic plan state; just adjust days.<\/li>\n\n\n\n<li><strong>CSV:<\/strong> leave <strong>Plan<\/strong> blank to keep each table\u2019s current plan.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Scenario F \u2014 Multi-subscription enforcement<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Goal:<\/strong> Consistent retention across prod\/dev subscriptions.<\/li>\n\n\n\n<li><strong>CSV:<\/strong> one row per workspace with its <strong>SubscriptionId<\/strong>; script switches context per group.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"Configuration\/scoping-of-tables-via-CSV-file\">Configuration\/scoping of tables via CSV-file<\/h2>\n\n\n\n<h5 class=\"wp-block-heading\">Scenario #1 &#8211; Use Table name filtering (*, ?)<\/h5>\n\n\n\n<pre class=\"wp-block-code has-small-font-size\"><code>SubscriptionId,ResourceGroup,Workspace,TablePattern,AnalyticsRetentionDays,DataLakeTotalRetentionInDays,Plan\n&lt;subid&gt;,&lt;rg&gt;,&lt;ws name&gt;,SecurityEvent,90,180,Analytics\n&lt;subid&gt;,&lt;rg&gt;,&lt;ws name&gt;,Device*Events,90,120,Analytics\n&lt;subid&gt;,&lt;rg&gt;,&lt;ws name&gt;,SigninLogs,90,120,Analytics\n&lt;subid&gt;,&lt;rg&gt;,&lt;ws name&gt;,*_CL,30,730,Analytics\nScenario #2 - Use Default Settings for all Sentinel tables<\/code><\/pre>\n\n\n\n<h5 class=\"wp-block-heading\">Scenario #2 &#8211; Use Default Settings for all Sentinel tables<\/h5>\n\n\n\n<pre class=\"wp-block-code has-small-font-size\"><code>SubscriptionId,ResourceGroup,Workspace,TablePattern,AnalyticsRetentionDays,DataLakeTotalRetentionInDays,Plan\n&lt;subid&gt;,&lt;rg&gt;,&lt;ws name&gt;,TYPE:SENTINEL:ANALYTICS,90,90,Analytics<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"whatif\">Implementation in WhatIf Mode<\/h2>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>.\\Set-Sentinel-Data-Lake-Retentions.ps1 -WhatIf:$true<br><\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Shows \u201cOut-of-scope,\u201d \u201cRetention Setting after Change,\u201d and \u201cWhatIf Changes.\u201d<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code has-small-font-size\"><code>What if: Performing the operation \"&#91;54468121-98ba-48ba-ba59-ba10a9711ed3\/rg-log-platform-management-security-p\/log-platform-management-security-p]&#91;SecurityRegulatoryCompliance] Analytics_Shortterm: 30 -&gt; 90; DataLake_Lon\ngterm: 30 -&gt; 90\" on target \"SecurityRegulatoryCompliance\".\n&#91;WHATIF]Would update 'SecurityRegulatoryCompliance' \u2192 Analytics_Shortterm: 30 -&gt; 90; DataLake_Longterm: 30 -&gt; 90\n&#91;INFO]  Table 'SecurityRecommendation' current: Plan=Analytics Analytics_Shortterm=30 DataLake_Longterm=30; target: Plan=Analytics Analytics_Shortterm=90 DataLake_Longterm=90\nWhat if: Performing the operation \"&#91;54468121-98ba-48ba-ba59-ba10a9711ed3\/rg-log-platform-management-security-p\/log-platform-management-security-p]&#91;SecurityRecommendation] Analytics_Shortterm: 30 -&gt; 90; DataLake_Longterm:\n 30 -&gt; 90\" on target \"SecurityRecommendation\".\n&#91;WHATIF]Would update 'SecurityRecommendation' \u2192 Analytics_Shortterm: 30 -&gt; 90; DataLake_Longterm: 30 -&gt; 90\n&#91;INFO]  Table 'SecurityBaselineSummary' current: Plan=Analytics Analytics_Shortterm=30 DataLake_Longterm=30; target: Plan=Analytics Analytics_Shortterm=90 DataLake_Longterm=90<\/code><\/pre>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"650\" src=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-16-1024x650.png\" alt=\"\" class=\"wp-image-5524\" srcset=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-16-1024x650.png 1024w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-16-300x191.png 300w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-16-768x488.png 768w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-16-1536x976.png 1536w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-16.png 1606w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><figcaption class=\"wp-element-caption\">See summary of changes (WhatIf)<\/figcaption><\/figure>\n<\/div>\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"281\" src=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-17-1024x281.png\" alt=\"\" class=\"wp-image-5525\" srcset=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-17-1024x281.png 1024w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-17-300x82.png 300w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-17-768x211.png 768w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-17-1536x421.png 1536w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-17.png 1714w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><figcaption class=\"wp-element-caption\">See summary of changes (WhatIf)<\/figcaption><\/figure>\n<\/div>\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"243\" src=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-19-1024x243.png\" alt=\"\" class=\"wp-image-5527\" srcset=\"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-19-1024x243.png 1024w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-19-300x71.png 300w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-19-768x182.png 768w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-19-1536x365.png 1536w, https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/image-19.png 1835w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><figcaption class=\"wp-element-caption\">See summary of changes (WhatIf)<\/figcaption><\/figure>\n<\/div>\n\n\n<h2 class=\"wp-block-heading\" id=\"governance\"><strong>Governance<\/strong><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Re-run monthly\/quarterly to catch drift or new tables.<\/li>\n\n\n\n<li>Keep the CSV in version control; PR review doubles as a cost\/compliance review.<\/li>\n\n\n\n<li>Consider a pipeline (e.g., GitHub Actions\/DevOps) with a <code>-WhatIf<\/code> stage before approval.<\/li>\n<\/ul>\n\n\n\n<ol start=\"6\" class=\"wp-block-list\">\n<li><\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"tips\">Practical tips &amp; gotchas<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Lowering retention can purge older data.<\/strong> Increasing (e.g., 30 \u2192 90) is safe; decreasing may remove data older than the new window. Plan changes carefully.<\/li>\n\n\n\n<li><strong>\u201cTotal retention\u201d must be &gt; Analytics<\/strong> to actually create a long-term copy; 90\/90 means <strong>no<\/strong> separate data-lake layer. <a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/sentinel\/manage-table-tiers-retention?utm_source=chatgpt.com\" target=\"_blank\" rel=\"noreferrer noopener\">Microsoft Learn<\/a><\/li>\n\n\n\n<li><strong>Per-table limits:<\/strong> Analytics retention range is <strong>4\u2013730 days<\/strong> via the API; enforceable by the script. <a href=\"https:\/\/learn.microsoft.com\/en-us\/powershell\/module\/az.operationalinsights\/update-azoperationalinsightstable?view=azps-14.2.0&amp;utm_source=chatgpt.com\" target=\"_blank\" rel=\"noreferrer noopener\">Microsoft Learn<\/a><\/li>\n\n\n\n<li><strong>Defaults vary by table:<\/strong> While many tables default to 30 days, some Microsoft-solution tables may default to 90; the script compares actuals and will only change what\u2019s needed. <a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/azure-monitor\/logs\/data-retention-configure?utm_source=chatgpt.com\" target=\"_blank\" rel=\"noreferrer noopener\">Microsoft Learn<\/a><\/li>\n\n\n\n<li><strong>What\u2019s \u201cSentinel table\u201d?<\/strong> In practice: any table in a Sentinel-connected workspace that isn\u2019t Custom (<code>*_CL<\/code>). The script treats those as in-scope for the 30 \u2192 90 auto-upgrade.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"references\">Sources (key references)<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Sentinel data lake overview (concept). <a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/sentinel\/datalake\/sentinel-lake-overview?utm_source=chatgpt.com\" target=\"_blank\" rel=\"noreferrer noopener\">Microsoft Learn<\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Sentinel billing &amp; retention: 90-day analytics included. <a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/sentinel\/billing?utm_source=chatgpt.com\" target=\"_blank\" rel=\"noreferrer noopener\">Microsoft Learn+1<\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Default retention behavior in Log Analytics: many tables at 30 days by default. <a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/azure-monitor\/logs\/data-retention-configure?utm_source=chatgpt.com\" target=\"_blank\" rel=\"noreferrer noopener\">Microsoft Learn<\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Sentinel data tiers, defaults, and 90-day extension at no cost for Sentinel solution tables. <a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/sentinel\/manage-data-overview?utm_source=chatgpt.com\" target=\"_blank\" rel=\"noreferrer noopener\">Microsoft Learn<\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Table-level retention API limits and semantics. <a href=\"https:\/\/learn.microsoft.com\/en-us\/powershell\/module\/az.operationalinsights\/update-azoperationalinsightstable?view=azps-14.2.0&amp;utm_source=chatgpt.com\" target=\"_blank\" rel=\"noreferrer noopener\">Microsoft Learn<\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Total (long-term) retention behavior and example (90 \u2192 180). <a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/sentinel\/manage-table-tiers-retention?utm_source=chatgpt.com\" target=\"_blank\" rel=\"noreferrer noopener\">Microsoft Learn<\/a><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"script-variables\">Script Variables<\/h2>\n\n\n\n<pre class=\"wp-block-code has-small-font-size\"><code># ---------- STATIC DEFAULTS ----------\n$DefaultCsvPath  = \"C:\\xxxxxx\\Sentinel-Data-Lake-Retention\\Sentinel-Data-Lake-Retentions.csv\"\n$DefaultTenantId = \"&lt;tenant id&gt;\"\n$DefaultWhatIf   = $true\n\n# ---------- LOGIN SETTINGS ----------\n# Choose one:\n#   \"Interactive\"            \u2192 opens browser sign-in (no device code used)\n#   \"ServicePrincipalSecret\" \u2192 uses Client ID + Secret below\n$LoginMode           = \"Interactive\"\n$ForceLoginAtStart   = $true   # prompt at start even if a context exists\n\n# Service principal secret creds (used only when $LoginMode = \"ServicePrincipalSecret\")\n$SpnTenantId         = $DefaultTenantId      # override per your SPN tenant if different\n$SpnClientId         = \"&lt;APP\/CLIENT ID GUID&gt;\"\n$SpnClientSecret     = \"&lt;CLIENT SECRET VALUE&gt;\"  # load from secure store in production\n\n# Auto-upgrade (CSV 30 -&gt; force 90) for Sentinel tables (non-Custom)\n$AutoUpgradeSentinelAnalyticsFrom30To90 = $true\n\n# Include NoChange rows in final summary table\/CSV\n$IncludeNoChangeInSummary = $true\n\n# ---------- READABILITY\/OUTPUT TUNING ----------\n$DefaultSummaryCsvPath = \"C:\\xxxxxx\\Sentinel-Data-Lake-Retention\\retention-summary_{date}.csv\"\n$EnableOutGridView     = $true\n$ConsoleBufferWidth    = 220\n$MaxRG = 30; $MaxWS = 30; $MaxTable = 42<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"script\">Script &#8211; Set-Sentinel-Data-Lake-Retentions.ps1<\/h2>\n\n\n\n<pre class=\"wp-block-code has-small-font-size\"><code># ====================================================================================================\n# Developed by Microsoft MVP, Morten Knudsen (aka.ms\/morten - mok@mortenknudsen.net)\n#\n# NAME\n#     Set-Sentinel-Data-Lake-Retentions.ps1\n#\n# SYNOPSIS\n#     Idempotent per-table retention &amp; plan manager for Microsoft Sentinel \/ Log Analytics workspaces.\n#     Reads desired settings from a CSV and applies them only when different, with a WhatIf-safe dry run.\n#\n# WHAT COUNTS AS \u201cSENTINEL\u201d HERE?\n#     If a table exists in the Log Analytics workspace and the workspace is connected to Sentinel,\n#     it is treated as a Sentinel table\u2014except Custom tables (schema.tableType = CustomLog or *_CL).\n#\n# DESCRIPTION (abridged)\n#     - Multi-subscription\/tenant via per-row SubscriptionId.\n#     - Wildcards (* ?) for table names and TYPE filters: TYPE:SENTINEL, TYPE:SENTINEL:ANALYTICS, TYPE:CUSTOM, TYPE:XDR.\n#     - Auto-upgrade option: Sentinel tables with CSV=30 \u2192 90 days Analytics.\n#     - Coverage report, compact matched summary, and detailed WhatIf\/Changes.\n#     - Exports a CSV summary of the full run.\n#     - **Precedence-based application: exact name > wildcard > TYPE:*** (prevents broad rows from overriding specific ones).\n#\n# CSV FORMAT (required)\n#     SubscriptionId,ResourceGroup,Workspace,TablePattern,AnalyticsRetentionDays,DataLakeTotalRetentionInDays\n#     Optional: Plan (Analytics|Basic)\n#\n# USAGE\n#     \u2022 Dry run w\/ static defaults: .\\Set-Sentinel-Data-Lake-Retentions.ps1\n#     \u2022 Apply changes:              .\\Set-Sentinel-Data-Lake-Retentions.ps1 -WhatIf:$false\n#     \u2022 Verbose:                    .\\Set-Sentinel-Data-Lake-Retentions.ps1 -Verbose\n# ====================================================================================================\n\n# ---------- STATIC DEFAULTS ----------\n$DefaultCsvPath  = \"C:\\xxxxxx\\Sentinel-Data-Lake-Retention\\Sentinel-Data-Lake-Retentions.csv\"\n$DefaultTenantId = \"&lt;tenant id>\"\n$DefaultWhatIf   = $true\n\n# ---------- LOGIN SETTINGS ----------\n# Choose one:\n#   \"Interactive\"            \u2192 opens browser sign-in (no device code used)\n#   \"ServicePrincipalSecret\" \u2192 uses Client ID + Secret below\n$LoginMode           = \"Interactive\"\n$ForceLoginAtStart   = $true   # prompt at start even if a context exists\n\n# Service principal secret creds (used only when $LoginMode = \"ServicePrincipalSecret\")\n$SpnTenantId         = $DefaultTenantId      # override per your SPN tenant if different\n$SpnClientId         = \"&lt;APP\/CLIENT ID GUID>\"\n$SpnClientSecret     = \"&lt;CLIENT SECRET VALUE>\"  # load from secure store in production\n\n# Auto-upgrade (CSV 30 -> force 90) for Sentinel tables (non-Custom)\n$AutoUpgradeSentinelAnalyticsFrom30To90 = $true\n\n# Include NoChange rows in final summary table\/CSV\n$IncludeNoChangeInSummary = $true\n\n# ---------- READABILITY\/OUTPUT TUNING ----------\n$DefaultSummaryCsvPath = \"C:\\xxxxxx\\Sentinel-Data-Lake-Retention\\retention-summary_{date}.csv\"\n$EnableOutGridView     = $true\n$ConsoleBufferWidth    = 220\n$MaxRG = 30; $MaxWS = 30; $MaxTable = 42\n# ----------------------------------------------\n\n\n# ---------- Output helpers ----------\nfunction Write-Step(&#91;string]$msg) { Write-Host (\"&#91;STEP]  {0}\" -f $msg) }\nfunction Write-Info(&#91;string]$msg) { Write-Host (\"&#91;INFO]  {0}\" -f $msg) }\nfunction Write-Done(&#91;string]$msg) { Write-Host (\"&#91;DONE]  {0}\" -f $msg) }\nfunction Write-Act (&#91;string]$msg) { Write-Host (\"&#91;APPLY] {0}\" -f $msg) }\nfunction Write-Sim (&#91;string]$msg) { Write-Host (\"&#91;WHATIF]{0}\" -f $msg) }\nfunction Write-Skip(&#91;string]$msg) { Write-Host (\"&#91;SKIP]  {0}\" -f $msg) }\nfunction Write-Err (&#91;string]$msg) { Write-Host (\"&#91;ERROR] {0}\" -f $msg); Write-Warning $msg }\n\nfunction Ensure-Module {\n  param(&#91;string]$Name)\n  if (-not (Get-Module -ListAvailable -Name $Name)) {\n    Write-Step (\"Installing module $($Name)...\")\n    Install-Module $Name -Scope CurrentUser -Force -AllowClobber\n    Write-Done (\"Installed module $($Name).\")\n  }\n  Import-Module $Name -ErrorAction Stop\n  Write-Info (\"Imported module $($Name).\")\n}\n\nfunction Ensure-SubscriptionContext {\n  param(&#91;string]$SubscriptionId)\n\n  $ctx = Get-AzContext\n  if ($ctx -and $ctx.Subscription -and $ctx.Subscription.Id -eq $SubscriptionId) {\n    Write-Info (\"Already in subscription context $($SubscriptionId).\")\n    return $true\n  }\n\n  try {\n    Write-Step (\"Setting context to subscription $($SubscriptionId)\")\n    Set-AzContext -Subscription $SubscriptionId -ErrorAction Stop | Out-Null\n    Write-Done (\"Context set to $($SubscriptionId).\")\n    return $true\n  } catch {\n    Write-Info (\"Set-AzContext failed for $($SubscriptionId): $($_)\")\n    try {\n      $subInfo   = Get-AzSubscription -SubscriptionId $SubscriptionId -ErrorAction Stop\n      $subTenant = $subInfo.TenantId\n      if (-not $subTenant) { throw \"TenantId not found for subscription $($SubscriptionId)\" }\n\n      Write-Step (\"Cross-tenant login to Tenant=$($subTenant) for Subscription=$($SubscriptionId)\")\n      Invoke-AzLogin -TenantToUse $subTenant\n\n      Write-Step (\"Retry Set-AzContext to subscription $($SubscriptionId)\")\n      Set-AzContext -Subscription $SubscriptionId -ErrorAction Stop | Out-Null\n      Write-Done (\"Context set to $($SubscriptionId) after cross-tenant login.\")\n      return $true\n    } catch {\n      Write-Err (\"Cannot set context to subscription $($SubscriptionId): $($_)\")\n      return $false\n    }\n  }\n}\n\n\nfunction Get-WorkspaceTables {\n  param(&#91;string]$ResourceGroupName,&#91;string]$WorkspaceName)\n  Write-Info (\"Fetching tables for RG='$($ResourceGroupName)' WS='$($WorkspaceName)' ...\")\n  $result = Get-AzOperationalInsightsTable -ResourceGroupName $ResourceGroupName -WorkspaceName $WorkspaceName\n  Write-Done (\"Fetched $(@($result).Count) tables.\")\n  return $result\n}\n\nfunction Normalize-RequestedName {\n  param(&#91;string]$Name)\n  switch -Regex ($Name) { '^SecurityEvents$' { 'SecurityEvent'; break } default { $Name } }\n}\n\nfunction Convert-WildcardToRegex { param(&#91;string]$Pattern) $Pattern -replace '\\.', '\\.' -replace '\\*', '.*' -replace '\\?', '.' }\n\n# --- helpers to read nested schema props safely ---\nfunction Get-TableSchemaProp {\n  param(&#91;object]$Table,&#91;string]$PropName)\n  if ($Table.PSObject.Properties&#91;'Schema'] -and $Table.Schema.PSObject.Properties&#91;$PropName]) { return $Table.Schema.$PropName }\n  if ($Table.PSObject.Properties&#91;'Properties'] -and $Table.Properties.PSObject.Properties&#91;'Schema'] -and $Table.Properties.Schema.PSObject.Properties&#91;$PropName]) { return $Table.Properties.Schema.$PropName }\n  return $null\n}\n\n# CLASSIFICATION:\n# - Custom   = schema.tableType == 'CustomLog' OR name like *_CL (case-insensitive)\n# - Sentinel = NOT Custom  (everything that exists in LA and is not Custom)\nfunction Test-IsCustomTable {\n  param(&#91;object]$Table)\n  $tt = Get-TableSchemaProp -Table $Table -PropName 'TableType'\n  if ($tt -and $tt -eq 'CustomLog') { return $true }\n  if ($Table.Name -match '(?i)_CL$') { return $true }\n  return $false\n}\n\nfunction Test-IsSentinelTable {\n  param(&#91;object]$Table)\n  return -not (Test-IsCustomTable -Table $Table)\n}\n\n# --- Login helper (Interactive or Service Principal Secret; no device code) ---\nfunction Invoke-AzLogin {\n  param(&#91;string]$TenantToUse)\n  $t = if (&#91;string]::IsNullOrWhiteSpace($TenantToUse)) { $DefaultTenantId } else { $TenantToUse }\n\n  if ($LoginMode -eq \"ServicePrincipalSecret\") {\n    if (&#91;string]::IsNullOrWhiteSpace($SpnClientId) -or &#91;string]::IsNullOrWhiteSpace($SpnClientSecret) -or &#91;string]::IsNullOrWhiteSpace($SpnTenantId)) {\n      throw \"ServicePrincipalSecret login selected but SPN variables are missing.\"\n    }\n    Write-Step (\"Logging in as Service Principal (ClientId=$($SpnClientId)) to Tenant=$($t)\")\n    $sec = ConvertTo-SecureString $SpnClientSecret -AsPlainText -Force\n    $cred = New-Object System.Management.Automation.PSCredential($SpnClientId, $sec)\n    Connect-AzAccount -ServicePrincipal -Tenant $t -Credential $cred -ErrorAction Stop | Out-Null\n    Write-Done (\"Logged in (SPN).\")\n  }\n  else {\n    Write-Step (\"Interactive login to Tenant=$($t)\")\n    Connect-AzAccount -Tenant $t -ErrorAction Stop | Out-Null\n    Write-Done (\"Logged in (Interactive).\")\n  }\n}\n\n# ---------- NEW: Pattern specificity scoring (exact > wildcard > TYPE:*) ----------\nfunction Get-PatternSpecificity {\n  param(&#91;string]$Pattern, &#91;string]$ResolvedPattern)\n\n  if ($ResolvedPattern -match '^(?i)\\s*TYPE\\s*:\\s*') { return 100 }  # lowest class\n\n  $stars = ($ResolvedPattern.ToCharArray() | Where-Object { $_ -eq '*' }).Count\n  $qs    = ($ResolvedPattern.ToCharArray() | Where-Object { $_ -eq '?' }).Count\n  $hasWild = ($stars + $qs) -gt 0\n\n  if (-not $hasWild) { return 1000 } # exact table name\n\n  # wildcards: fewer wildcards is more specific\n  return 600 - ( ($stars * 10) + ($qs * 5) )\n}\n\n# ---------- Updated matcher: returns Matches + Resolved pattern ----------\nfunction Match-TablesByPattern {\n  param(&#91;System.Collections.Generic.List&#91;object]]$AllTables,&#91;string]$Pattern)\n\n  $norm = Normalize-RequestedName $Pattern\n\n  if ($norm -match '^(?i)\\s*TYPE\\s*:\\s*SENTINEL\\s*(?::\\s*ANALYTICS\\s*)?$') {\n    $onlyAnalytics = ($norm -match '(?i):\\s*ANALYTICS')\n    Write-Info (\"Pattern '$($Pattern)' resolved to TYPE:SENTINEL (onlyAnalytics=$($onlyAnalytics))\")\n    $ts = $AllTables | Where-Object { Test-IsSentinelTable -Table $_ }\n    if ($onlyAnalytics) { $ts = $ts | Where-Object { $_.Plan -eq 'Analytics' } }\n    Write-Done (\"TYPE:SENTINEL matched $(@($ts).Count) tables.\")\n    return &#91;pscustomobject]@{ Matches=$ts; Resolved=$norm }\n  }\n  if ($norm -match '^(?i)\\s*TYPE\\s*:\\s*CUST(OM)?\\s*$') {\n    Write-Info (\"Pattern '$($Pattern)' resolved to TYPE:CUSTOM\")\n    $ts = $AllTables | Where-Object { Test-IsCustomTable -Table $_ }\n    Write-Done (\"TYPE:CUSTOM matched $(@($ts).Count) tables.\")\n    return &#91;pscustomobject]@{ Matches=$ts; Resolved=$norm }\n  }\n  if ($norm -match '^(?i)\\s*TYPE\\s*:\\s*XDR\\s*$') {\n    Write-Info (\"Pattern '$($Pattern)' resolved to TYPE:XDR (not in LA) \u2192 0 matches by design\")\n    return &#91;pscustomobject]@{ Matches=@(); Resolved=$norm }\n  }\n\n  $regex = '^' + (Convert-WildcardToRegex $norm) + '$'\n  Write-Info (\"Pattern '$($Pattern)' -> regex '$($regex)'\")\n  $ts = $AllTables | Where-Object { $_.Name -match $regex }\n  Write-Done (\"Wildcard matched $(@($ts).Count) tables for pattern '$($Pattern)'.\")\n  return &#91;pscustomobject]@{ Matches=$ts; Resolved=$norm }\n}\n\n# ---- Readability helpers ----\nfunction Shorten(&#91;string]$s,&#91;int]$max){\n  if (&#91;string]::IsNullOrEmpty($s)) { return $s }\n  if ($s.Length -le $max) { return $s }\n  return ($s.Substring(0,&#91;Math]::Max(1,$max-1)) + '\u2026')\n}\n\n# ---------------- MAIN ADVANCED FUNCTION ----------------\nfunction Set-SentinelDataLakeRetentions {\n  &#91;CmdletBinding(SupportsShouldProcess = $true)]\n  param(&#91;string]$CsvPath,&#91;string]$TenantId)\n\n  Write-Step (\"Starting Set-SentinelDataLakeRetentions\")\n  Write-Info (\"Defaults: CsvPath='$($DefaultCsvPath)', TenantId='$($DefaultTenantId)', DefaultWhatIf=$($DefaultWhatIf)\")\n  Write-Info (\"LoginMode='$($LoginMode)', ForceLoginAtStart=$($ForceLoginAtStart)\")\n\n  if ($ConsoleBufferWidth -and $Host.UI -and $Host.UI.RawUI) {\n    try {\n      $raw = $Host.UI.RawUI\n      $cur = $raw.BufferSize\n      if ($cur.Width -lt $ConsoleBufferWidth) {\n        Write-Info (\"Increasing console buffer width to $($ConsoleBufferWidth)\")\n        $raw.BufferSize = New-Object Management.Automation.Host.Size ($ConsoleBufferWidth, &#91;Math]::Max($cur.Height,5000))\n      }\n    } catch {\n      Write-Info (\"Console resize skipped: $($_)\")\n    }\n  }\n\n  if (-not $CsvPath)  { $CsvPath  = $DefaultCsvPath }\n  if (-not $TenantId) { $TenantId = $DefaultTenantId }\n  if (-not $PSBoundParameters.ContainsKey('WhatIf') -and $DefaultWhatIf) { $WhatIfPreference = $true }\n\n  try {\n    Ensure-Module -Name Az.Accounts\n    Ensure-Module -Name Az.OperationalInsights\n  } catch {\n    Write-Err (\"Failed to load Az modules: $($_)\")\n    return\n  }\n\n  if (-not (Test-Path $CsvPath)) { Write-Err (\"CSV not found: $($CsvPath)\"); return }\n\n  try {\n    if ($ForceLoginAtStart -or -not (Get-AzContext)) {\n      Invoke-AzLogin -TenantToUse $TenantId\n    } else {\n      Write-Info (\"Using existing Az context.\")\n    }\n  } catch {\n    Write-Err (\"Azure login failed: $($_)\")\n    return\n  }\n\n  Write-Step (\"Loading CSV from '$($CsvPath)'\")\n  $rows = Import-Csv -Path $CsvPath\n  if (-not $rows) { Write-Err (\"CSV is empty.\"); return }\n  Write-Done (\"Loaded $(@($rows).Count) CSV rows.\")\n  foreach ($r in $rows) {\n    if (&#91;string]::IsNullOrWhiteSpace($r.SubscriptionId)) { Write-Err (\"Row with Workspace '$($r.Workspace)' is missing SubscriptionId.\"); return }\n  }\n\n  $groups   = $rows | Group-Object SubscriptionId, ResourceGroup, Workspace\n  Write-Info (\"CSV grouped into $(@($groups).Count) workspace scopes.\")\n  $summary  = @()\n  $coverage = @()\n\n  foreach ($g in $groups) {\n    $sub, $rg, $ws = $g.Name -split ',\\s*'\n    Write-Step (\"Scope: Sub='$($sub)' RG='$($rg)' WS='$($ws)'  (rows=$(@($g.Group).Count))\")\n\n    if (-not (Ensure-SubscriptionContext -SubscriptionId $sub)) {\n      foreach ($row in $g.Group) {\n        $summary += &#91;pscustomobject]@{ SubscriptionId=$sub; ResourceGroup=$rg; Workspace=$ws; Table=$row.TablePattern; Plan=$null; AnalyticsRetentionInDays=$null; DataLakeTotalRetentionInDays=$null; Action=\"Error(SetContext)\"; Change=$null }\n      }\n      continue\n    }\n\n    try { $tables = Get-WorkspaceTables -ResourceGroupName $rg -WorkspaceName $ws }\n    catch {\n      Write-Err (\"Could not list tables for $($rg)\/$($ws) in $($sub): $($_)\")\n      foreach ($row in $g.Group) {\n        $summary += &#91;pscustomobject]@{ SubscriptionId=$sub; ResourceGroup=$rg; Workspace=$ws; Table=$row.TablePattern; Plan=$null; AnalyticsRetentionInDays=$null; DataLakeTotalRetentionInDays=$null; Action=\"Error(GetTables)\"; Change=$null }\n      }\n      continue\n    }\n\n    $totalTables  = @($tables).Count\n    $matchedNames = New-Object System.Collections.Generic.HashSet&#91;string]\n    Write-Info (\"Scope has $($totalTables) tables in workspace.\")\n\n    # ---------- DECISION PASS (build winners per table) ----------\n    $desiredByTable = @{}   # tableName -> @{ TargetPlan=..; TargetHot=..; TargetDL=..; Score=..; FromPattern=.. }\n\n    foreach ($row in $g.Group) {\n      $pattern   = $row.TablePattern\n      $planIn    = $row.Plan\n      $retHotCsv = &#91;int]$row.AnalyticsRetentionDays\n\n      # Data Lake: preferred + fallbacks\n      $dlStr = $row.DataLakeTotalRetentionInDays\n      if (&#91;string]::IsNullOrWhiteSpace($dlStr)) { $dlStr = $row.DataLakeRetentionDays }\n      if (&#91;string]::IsNullOrWhiteSpace($dlStr)) { $dlStr = $row.TotalRetentionDays }\n      if (&#91;string]::IsNullOrWhiteSpace($dlStr)) {\n        Write-Skip (\"Missing DataLakeTotalRetentionInDays for pattern '$($pattern)' \u2192 Skipping row.\")\n        $summary += &#91;pscustomobject]@{ SubscriptionId=$sub; ResourceGroup=$rg; Workspace=$ws; Table=$pattern; Plan=$planIn; AnalyticsRetentionInDays=$retHotCsv; DataLakeTotalRetentionInDays=$null; Action=\"Skipped(DataLakeMissing)\"; Change=$null }\n        continue\n      }\n      $retDataLakeCsv = &#91;int]$dlStr\n\n      if ($retHotCsv -lt 4 -or $retHotCsv -gt 730) {\n        Write-Skip (\"AnalyticsRetentionDays '$($retHotCsv)' out-of-range (4..730) \u2192 Skipping row.\")\n        $summary += &#91;pscustomobject]@{ SubscriptionId=$sub; ResourceGroup=$rg; Workspace=$ws; Table=$pattern; Plan=$planIn; AnalyticsRetentionInDays=$retHotCsv; DataLakeTotalRetentionInDays=$retDataLakeCsv; Action=\"Skipped(HotOutOfRange)\"; Change=$null }\n        continue\n      }\n      if ($retDataLakeCsv -lt 0) {\n        Write-Skip (\"DataLakeTotalRetentionInDays '$($retDataLakeCsv)' invalid (&lt;0) \u2192 Skipping row.\")\n        $summary += &#91;pscustomobject]@{ SubscriptionId=$sub; ResourceGroup=$rg; Workspace=$ws; Table=$pattern; Plan=$planIn; AnalyticsRetentionInDays=$retHotCsv; DataLakeTotalRetentionInDays=$retDataLakeCsv; Action=\"Skipped(DataLakeInvalid)\"; Change=$null }\n        continue\n      }\n\n      $matchObj = Match-TablesByPattern -AllTables (&#91;System.Collections.Generic.List&#91;object]]$tables) -Pattern $pattern\n      $matches  = @($matchObj.Matches)\n      $resolved = $matchObj.Resolved\n\n      if (-not $matches -or $matches.Count -eq 0) {\n        Write-Skip (\"No tables matched pattern '$($pattern)'.\")\n        $summary += &#91;pscustomobject]@{ SubscriptionId=$sub; ResourceGroup=$rg; Workspace=$ws; Table=$pattern; Plan=$planIn; AnalyticsRetentionInDays=$retHotCsv; DataLakeTotalRetentionInDays=$retDataLakeCsv; Action=\"NoMatch\"; Change=$null }\n        continue\n      }\n\n      $score = Get-PatternSpecificity -Pattern $pattern -ResolvedPattern $resolved\n      foreach ($t in $matches) {\n        &#91;void]$matchedNames.Add($t.Name)\n\n        $isSentinel = Test-IsSentinelTable -Table $t\n        $targetHot  = $retHotCsv\n        if ($AutoUpgradeSentinelAnalyticsFrom30To90 -and $isSentinel -and $retHotCsv -eq 30) {\n          $targetHot = 90\n        }\n\n        # record candidate (Plan only if provided; otherwise keep null -> means \"keep current plan\")\n        $candidate = @{\n          TargetPlan = if (&#91;string]::IsNullOrWhiteSpace($planIn)) { $null } else { $planIn }\n          TargetHot  = $targetHot\n          TargetDL   = $retDataLakeCsv\n          Score      = $score\n          FromPattern= $resolved\n        }\n\n        if (-not $desiredByTable.ContainsKey($t.Name)) {\n          $desiredByTable&#91;$t.Name] = $candidate\n        } else {\n          # keep the more specific (higher score). On tie, keep existing.\n          if ($candidate.Score -gt $desiredByTable&#91;$t.Name].Score) {\n            $desiredByTable&#91;$t.Name] = $candidate\n          }\n        }\n      }\n    }\n\n    # ---------- APPLY PASS (compare\/apply once per table) ----------\n    foreach ($t in ($tables | Sort-Object Name)) {\n      $currentPlan     = $t.Plan\n      $currentHot      = &#91;int]$t.RetentionInDays\n      $currentDataLake = &#91;int]$t.TotalRetentionInDays\n\n      if (-not $desiredByTable.ContainsKey($t.Name)) { continue } # out-of-scope (covered later)\n\n      $desired = $desiredByTable&#91;$t.Name]\n      $targetPlan = if ($null -ne $desired.TargetPlan) { $desired.TargetPlan } else { $currentPlan }\n      $targetHot  = $desired.TargetHot\n      $targetDL   = $desired.TargetDL\n\n      $needPlanChange     = ($targetPlan -ne $currentPlan)\n      $needHotChange      = ($targetHot  -ne $currentHot)\n      $needDataLakeChange = ($targetDL   -ne $currentDataLake)\n\n      Write-Info (\"Table '$($t.Name)' current: Plan=$($currentPlan) Analytics_Shortterm=$($currentHot) DataLake_Longterm=$($currentDataLake); target (from '$($desired.FromPattern)'): Plan=$($targetPlan) Analytics_Shortterm=$($targetHot) DataLake_Longterm=$($targetDL)\")\n\n      if (-not ($needPlanChange -or $needHotChange -or $needDataLakeChange)) {\n        Write-Info (\"No change required for '$($t.Name)'.\")\n        if ($IncludeNoChangeInSummary) {\n          $summary += &#91;pscustomobject]@{\n            SubscriptionId=$sub; ResourceGroup=$rg; Workspace=$ws; Table=$t.Name; Plan=$currentPlan;\n            AnalyticsRetentionInDays=$currentHot; DataLakeTotalRetentionInDays=$currentDataLake;\n            Action=\"NoChange\"; Change=$null\n          }\n        }\n        continue\n      }\n\n      $changeDesc = @()\n      if ($needPlanChange)     { $changeDesc += \"Plan: $($currentPlan) -> $($targetPlan)\" }\n      if ($needHotChange)      { $changeDesc += \"Analytics_Shortterm: $($currentHot) -> $($targetHot)\" }\n      if ($needDataLakeChange) { $changeDesc += \"DataLake_Longterm: $($currentDataLake) -> $($targetDL)\" }\n      $changeText = $changeDesc -join '; '\n      $msg = \"&#91;$($sub)\/$($rg)\/$($ws)]&#91;$($t.Name)] $($changeText)\"\n\n      if ($PSCmdlet.ShouldProcess($t.Name, $msg)) {\n        try {\n          Write-Act (\"Updating '$($t.Name)' with: $($changeText)\")\n          $args = @{ ResourceGroupName=$rg; WorkspaceName=$ws; TableName=$t.Name }\n          if ($needPlanChange)     { $args&#91;'Plan'] = $targetPlan }\n          if ($needHotChange)      { $args&#91;'RetentionInDays'] = $targetHot }\n          if ($needDataLakeChange) { $args&#91;'TotalRetentionInDays'] = $targetDL }\n          Update-AzOperationalInsightsTable @args | Out-Null\n          Write-Done (\"Updated '$($t.Name)'.\")\n          $summary += &#91;pscustomobject]@{\n            SubscriptionId=$sub; ResourceGroup=$rg; Workspace=$ws; Table=$t.Name; Plan=$targetPlan;\n            AnalyticsRetentionInDays=$targetHot; DataLakeTotalRetentionInDays=$targetDL;\n            Action=\"Updated\"; Change=$changeText\n          }\n        } catch {\n          Write-Info (\"Single-call update failed for '$($t.Name)'. Trying two-step. Error: $($_)\")\n          $ok = $true\n          if ($needPlanChange) {\n            try {\n              Write-Act (\"Updating plan for '$($t.Name)' \u2192 $($targetPlan)\")\n              Update-AzOperationalInsightsTable -ResourceGroupName $rg -WorkspaceName $ws -TableName $t.Name -Plan $targetPlan | Out-Null\n            } catch { Write-Err (\"Plan update failed for '$($t.Name)': $($_)\"); $ok = $false }\n          }\n          if ($ok -and ($needHotChange -or $needDataLakeChange)) {\n            try {\n              Write-Act (\"Updating retention(s) for '$($t.Name)' (Analytics_Shortterm=$($targetHot) \/ DataLake_Longterm=$($targetDL))\")\n              $args2 = @{ ResourceGroupName=$rg; WorkspaceName=$ws; TableName=$t.Name }\n              if ($needHotChange)      { $args2&#91;'RetentionInDays'] = $targetHot }\n              if ($needDataLakeChange) { $args2&#91;'TotalRetentionInDays'] = $targetDL }\n              Update-AzOperationalInsightsTable @args2 | Out-Null\n            } catch { Write-Err (\"Retention update failed for '$($t.Name)': $($_)\"); $ok = $false }\n          }\n          $summary += &#91;pscustomobject]@{\n            SubscriptionId=$sub; ResourceGroup=$rg; Workspace=$ws; Table=$t.Name;\n            Plan = if ($needPlanChange) { $targetPlan } else { $currentPlan };\n            AnalyticsRetentionInDays = if ($needHotChange) { $targetHot } else { $currentHot };\n            DataLakeTotalRetentionInDays = if ($needDataLakeChange) { $targetDL } else { $currentDataLake };\n            Action = if ($ok) { \"Updated\" } else { \"Error\" };\n            Change = $changeText\n          }\n        }\n      } else {\n        Write-Sim (\"Would update '$($t.Name)' \u2192 $($changeText)\")\n        $summary += &#91;pscustomobject]@{\n          SubscriptionId=$sub; ResourceGroup=$rg; Workspace=$ws; Table=$t.Name; Plan=$targetPlan;\n          AnalyticsRetentionInDays=$targetHot; DataLakeTotalRetentionInDays=$targetDL;\n          Action=\"WouldUpdate\"; Change=$changeText\n        }\n      }\n    }\n\n    # ---- Save coverage info for UNMATCHED header ----\n    $matchedCount   = $matchedNames.Count\n    $unmatchedItems = $tables | Where-Object { -not $matchedNames.Contains($_.Name) } | Sort-Object Name\n    $unmatchedNames = $unmatchedItems | Select-Object -ExpandProperty Name\n\n    Write-Info (\"Scope coverage: total=$($totalTables), matched=$($matchedCount), unmatched=$(@($unmatchedNames).Count)\")\n    $coverage += &#91;pscustomobject]@{\n      SubscriptionId = $sub\n      ResourceGroup  = $rg\n      Workspace      = $ws\n      Total          = $totalTables\n      Matched        = $matchedCount\n      Unmatched      = @($unmatchedNames).Count\n      UnmatchedNames = $unmatchedNames\n    }\n  }\n\n  # ===================== 1) OUT-OF-SCOPE (UNMATCHED) =====================\n  Write-Host \"\"\n  Write-Host \"Out-of-Scope tables (no match)\"\n  if ($coverage.Count -eq 0) {\n    Write-Host \"(no coverage data)\"\n  } else {\n    foreach ($c in ($coverage | Sort-Object SubscriptionId, ResourceGroup, Workspace)) {\n      Write-Host \"\"\n      Write-Host (\"Workspace: {0}\/{1}\/{2}  (total={3}, matched={4}, unmatched={5})\" -f $c.SubscriptionId, $c.ResourceGroup, $c.Workspace, $c.Total, $c.Matched, $c.Unmatched)\n      if ($c.Unmatched -gt 0) {\n        Write-Host (\"Out-of-Scope tables (no match) ({0}):\" -f $c.Unmatched)\n        foreach ($n in $c.UnmatchedNames) { Write-Host (\"  - {0}\" -f $n) }\n      } else {\n        Write-Host \"Out-of-Scope tables (no match): (none)\"\n      }\n    }\n  }\n\n  # ===================== 2) IN-SCOPE \u2014 SUMMARY (grouped headers) =====================\n  $matchedSummary = $summary | Where-Object { $_.Action -in @('Updated','WouldUpdate','NoChange','Error') }\n  Write-Host \"\"\n  Write-Host \"Retention Setting after Change (in-scope tables)\"\n  if ($matchedSummary.Count -gt 0) {\n    $byWs = $matchedSummary | Group-Object SubscriptionId, ResourceGroup, Workspace\n    foreach ($grp in ($byWs | Sort-Object Name)) {\n      $sub, $rg, $ws = $grp.Name -split ',\\s*'\n      Write-Host \"\"\n      Write-Host (\"Workspace: {0}\/{1}\/{2}\" -f $sub, $rg, $ws)\n      $display = $grp.Group | Select-Object `\n        @{n='Table';                e={ Shorten $_.'Table' $MaxTable }}, `\n        @{n='Plan';                 e={ $_.'Plan' }}, `\n        @{n='Analytics_Shortterm';  e={ $_.'AnalyticsRetentionInDays' }}, `\n        @{n='DataLake_Longterm';    e={ $_.'DataLakeTotalRetentionInDays' }}, `\n        @{n='Action';               e={ $_.'Action' }}\n      $out = $display | Sort-Object Action, Table | Format-Table -AutoSize | Out-String -Width 4096\n      Write-Host $out\n    }\n  } else {\n    Write-Host \"(no matched table entries)\"\n  }\n\n  # ===================== 3) IN-SCOPE \u2014 WHATIF\/CHANGES (grouped headers) =====================\n  $changesOnly = $summary | Where-Object { $_.Action -in @('Updated','WouldUpdate') } | Sort-Object Workspace, Table\n  Write-Host \"\"\n  Write-Host \"Retention Setting WhatIf Changes (in-scope tables)\"\n  if ($changesOnly.Count -gt 0) {\n    $byWs2 = $changesOnly | Group-Object SubscriptionId, ResourceGroup, Workspace\n    foreach ($grp in ($byWs2 | Sort-Object Name)) {\n      $sub, $rg, $ws = $grp.Name -split ',\\s*'\n      Write-Host \"\"\n      Write-Host (\"Workspace: {0}\/{1}\/{2}\" -f $sub, $rg, $ws)\n      foreach ($row in ($grp.Group | Sort-Object Table)) {\n        Write-Host (\"  - &#91;{0}] {1}\" -f $row.Table, $row.Change)\n      }\n    }\n  } else {\n    Write-Host \"(no changes to report)\"\n  }\n\n  # ===================== FINAL COUNTS =====================\n  $applied  = ($summary | Where-Object { $_.Action -eq 'Updated' }).Count\n  $planned  = ($summary | Where-Object { $_.Action -eq 'WouldUpdate' }).Count\n  $nochange = ($summary | Where-Object { $_.Action -eq 'NoChange' }).Count\n  $skipped  = ($summary | Where-Object { $_.Action -like 'Skipped*' }).Count\n  $nomatch  = ($summary | Where-Object { $_.Action -eq 'NoMatch' }).Count\n  $errors   = ($summary | Where-Object { $_.Action -eq 'Error' }).Count\n\n  $notChangedStrict  = $nochange\n  $notChangedOverall = $nochange + $skipped + $nomatch\n\n  Write-Host \"\"\n  Write-Host \"===== TOTALS =====\"\n  Write-Host (\"  Changed (applied):            {0}\" -f $applied)\n  Write-Host (\"  Changes planned (WhatIf):     {0}\" -f $planned)\n  Write-Host (\"  Not changed:                  {0}\" -f $notChangedStrict)\n  Write-Host (\"  Not changed (overall):        {0}   &#91;Skipped={1}, NoMatch={2}]\" -f $notChangedOverall, $skipped, $nomatch)\n  Write-Host (\"  Errors:                       {0}\" -f $errors)\n\n  # -------- CSV export --------\n  if ($DefaultSummaryCsvPath) {\n    $ts = Get-Date -Format 'yyyyMMdd_HHmmss'\n    $csvOut = $DefaultSummaryCsvPath -replace '\\{date\\}', $ts\n    try {\n      Write-Step (\"Exporting summary CSV to '$($csvOut)'\")\n      $summary | Export-Csv -Path $csvOut -NoTypeInformation -Encoding UTF8\n      Write-Done (\"Summary CSV: $csvOut\")\n    } catch {\n      Write-Err (\"Failed to export summary CSV to $($csvOut): $($_)\")\n    }\n  }\n\n  if ($EnableOutGridView -and $env:OS -like '*Windows*') {\n    try { $matchedSummary | Out-GridView -Title \"Retention Setting after Change (in-scope tables)\" }\n    catch { Write-Err (\"Out-GridView failed (non-interactive session?): $($_)\") }\n  }\n\n  Write-Done (\"Set-SentinelDataLakeRetentions completed.\")\n}\n\n# --------- RUN WITH STATIC DEFAULTS (params are optional) ---------\ntry {\n  Ensure-Module -Name Az.Accounts\n  Ensure-Module -Name Az.OperationalInsights\n} catch {\n  Write-Err (\"Preloading Az modules failed: $($_)\")\n}\n\n# Example invocation (uses defaults\/WhatIf unless overridden)\nSet-SentinelDataLakeRetentions -CsvPath $DefaultCsvPath -TenantId $DefaultTenantId -WhatIf:$DefaultWhatIf\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"datafile\">Scoping\/Configuration file (sample)<\/h2>\n\n\n\n<pre class=\"wp-block-code has-small-font-size\"><code>SubscriptionId,ResourceGroup,Workspace,TablePattern,AnalyticsRetentionDays,DataLakeTotalRetentionInDays,Plan\nxxxxxxxx-98ba-48ba-ba59-ba10a9711ed3,rg-log-platform-management-security-p,log-platform-management-security-p,SecurityEvent,90,180,Analytics\nxxxxxxxx-98ba-48ba-ba59-ba10a9711ed3,rg-log-platform-management-security-p,log-platform-management-security-p,Device*Events,90,120,Analytics\nxxxxxxxx-98ba-48ba-ba59-ba10a9711ed3,rg-log-platform-management-security-p,log-platform-management-security-p,SigninLogs,90,120,Analytics\nxxxxxxxx-98ba-48ba-ba59-ba10a9711ed3,rg-log-platform-management-security-p,log-platform-management-security-p,*_CL,30,730,Analytics\nxxxxxxxx-98ba-48ba-ba59-ba10a9711ed3,rg-log-platform-management-security-p,log-platform-management-security-p,TYPE:SENTINEL:ANALYTICS,90,90,Analytics<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Microsoft Sentinel\u2019s data lake story is quietly powerful: you get fast, 90-day Analytics (Shortterm) for hunting and detections, plus scalable, &#8230; <\/p>\n<p class=\"read-more-container\"><a title=\"Script: Sentinel Data Lake Table Management\" class=\"read-more button\" href=\"https:\/\/mortenknudsen.net\/?p=5518#more-5518\" aria-label=\"Read more about Script: Sentinel Data Lake Table Management\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":5549,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"ngg_post_thumbnail":0,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":true,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[154,55,57,218,152,142,133,134,157,60,132,58,217],"tags":[220,104,219,112,17,24],"class_list":["post-5518","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-automation","category-azure","category-azure-security","category-data-lake","category-kusto","category-logging","category-microsoft-security","category-mvpbuzz","category-powershell","category-scripting","category-security","category-sentinel","category-sentinel-data-lake","tag-compliance","tag-cost","tag-data-lake","tag-retention","tag-security","tag-sentinel","infinite-scroll-item","resize-featured-image"],"featured_image_src":"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/ChatGPT-Image-Aug-13-2025-08_12_29-PM.png","author_info":{"display_name":"Morten Knudsen","author_link":"https:\/\/mortenknudsen.net\/?author=1"},"jetpack_sharing_enabled":true,"jetpack_likes_enabled":true,"jetpack_featured_media_url":"https:\/\/mortenknudsen.net\/wp-content\/uploads\/2025\/08\/ChatGPT-Image-Aug-13-2025-08_12_29-PM.png","_links":{"self":[{"href":"https:\/\/mortenknudsen.net\/index.php?rest_route=\/wp\/v2\/posts\/5518","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/mortenknudsen.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mortenknudsen.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mortenknudsen.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mortenknudsen.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5518"}],"version-history":[{"count":24,"href":"https:\/\/mortenknudsen.net\/index.php?rest_route=\/wp\/v2\/posts\/5518\/revisions"}],"predecessor-version":[{"id":5568,"href":"https:\/\/mortenknudsen.net\/index.php?rest_route=\/wp\/v2\/posts\/5518\/revisions\/5568"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/mortenknudsen.net\/index.php?rest_route=\/wp\/v2\/media\/5549"}],"wp:attachment":[{"href":"https:\/\/mortenknudsen.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5518"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mortenknudsen.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5518"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mortenknudsen.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5518"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}