Skip to content

Policy Assignment Parameters from a CSV File

Assigning single or multiple security and compliance focused Policy Sets (Initiatives), such as Microsoft cloud security benchmark, NIST 800-53 R5, PCI, NIST 800-171, etc, with just JSON parameters becomes very complex fast. Add to this the complexity of overriding the effect if it is not surfaced as a parameter in the Policy Set. Finally, adding the optional nonComplianceMessages further increases the complexity.

To address the problem of reading and maintaining hundreds or thousands of JSON lines, EPAC can use the content of a spreadsheet (CSV) to create parameters, overrides and optionally nonComplianceMessages for a single Policy assignment definitionEntry or multiple Policy definitions (definitionEntryList).

Note

EPAC also accepts JSON and JSONC parameter sidecar files for assignment definitions. When a file is a JSON/JSONC parameter source, set "parameterFile" to the file name and omit parameterSelector; the file is expected to contain a top-level parameters object (or a plain parameter map) that EPAC merges into the assignment.

Tip

This approach is best for large Policy Sets such as Azure Security Benchmark, NIST 800-53, etc. Smaller Policy Sets should still be handled with JSON parameters, overrides and nonComplianceMessages.

Generate the CSV File

From a list of Policy Sets

Generating documentation for one or more Policy Sets, then modify the effect and parameter columns for each environment type you will use.

From a list of deployed Policy Assignments

If you want to switch from JSON to CSV or start EPAC from an existing deployment, generate this CSV file from your already deployed Assignment(s).

CSV File

In the example header below the infrastructure environments prod, test, dev, and sandbox are used as prefixes to the columns for Effect and Parameters respectively. Optionally you can add a column for nonComplianceMessages

The CSV file generated contains the following headers/columns:

  • name is the name of the policyDefinition referenced by the Policy Sets being assigned.
  • referencePath is only used if the Policy is used more than once in at least one of the Policy Sets to disambiguate them. The format is <policySetName>//<policyDefinitionReferenceId>.
  • policyType,category,displayName,description,groupNames,policySets,allowedEffects are optional and not used for deployment planning. They assist you in filling out the <env>Effect columns. The CSV file is sorted alphabetically by category and displayName.
  • <env>Effect columns must contain one of the allowedValues or allowedOverrides values. You define which scopes define each type of environment and what short name you give the environment type to use as a column prefix.
  • <env>Parameters can contain additional parameters. You can also specify such parameters in JSON. EPAC will use the union of all parameters.
  • nonComplianceMessages column is optional. The documentation script does not generate this column.

Note

Additional columns are allowed and ignored by EPAC.

EPAC will find the effect parameter name for each Policy in each Policy Set and use them. If no effect parameter is defined by the Policy Set, EPAC will use overrides to set the effect. EPAC will generate the policyDefinitionReferenceId for nonComplianceMessages.

After building the spreadsheet, you must reference the CSV file and the column prefix in each tree branch. parameterFile must occur once per tree branch. Define it adjacent to the 'definitionEntry or definitionEntryList to improve readability.

parameterFile accepts either a discovered CSV filename, a path relative to the assignment JSON file, or an absolute path. This allows parameter files to live outside the policyAssignments tree while keeping the existing filename-based behavior for in-tree CSVs.

Examples:

"parameterFile": "security-baseline-parameters.csv",
"parameterFile": "../../parameters/epac-prod/security-baseline-parameters.csv",
"parameterFile": "/mnt/shared/epac/parameters/security-baseline-parameters.csv"

Relative paths are resolved from the directory containing the assignment JSON file. Absolute paths are used as-is. The classic bare filename lookup is still supported when the CSV is discovered under the assignments root.

"parameterFile": "security-baseline-parameters.csv",
"definitionEntryList": [
    {
        "policySetName": "1f3afdf9-d0c9-4c3d-847f-89da613e70a8",
        "displayName": "Azure Security Benchmark",
        "assignment": {
            "append": true,
            "name": "asb",
            "displayName": "Azure Security Benchmark",
            "description": "Azure Security Benchmark Initiative. "
        }
    },
    {
        "policySetName": "179d1daa-458f-4e47-8086-2a68d0d6c38f",
        "displayName": "NIST SP 800-53 Rev. 5",
        "assignment": {
            "append": true,
            "name": "nist-800-53-r5",
            "displayName": "NIST SP 800-53 Rev. 5",
            "description": "NIST SP 800-53 Rev. 5 Initiative."
        }
    }
],

In the child nodes specifying the scope(s) specify which column prefix to use for selecting the CSV columns with parameterSelector. The actual prefix names have no meaning; they only need to match between the JSON below and the CSV file.

{
    "nodeName": "Prod/",
    "assignment": {
        "name": "pr-",
        "displayName": "Prod ",
        "description": "Prod Environment controls enforcement with initiative "
    },
    "parameterSelector": "prod",
    "scope": {
        "epac-dev": [
            "/providers/Microsoft.Management/managementGroups/Epac-Mg-Prod"
        ],
        "tenant": [
            "/providers/Microsoft.Management/managementGroups/Contoso-Prod"
        ]
    }
},

The element nonComplianceMessageColumn may appear anywhere in the tree. Definitions at a child override the previous setting. If no nonComplianceMessageColumn is specified, the spreadsheet is not used for the (optional) nonComplianceMessages.

{
    "nodeName": "Prod/",
    "assignment": {
        "name": "pr-",
        "displayName": "Prod ",
        "description": "Prod Environment controls enforcement with initiative "
    },
    "parameterSelector": "prod",
    "nonComplianceMessageColumn": "nonComplianceMessages"
    "scope": {
        "epac-dev": [
            "/providers/Microsoft.Management/managementGroups/Epac-Mg-Prod"
        ],
        "tenant": [
            "/providers/Microsoft.Management/managementGroups/Contoso-Prod"
        ]
    }
},

Effects for definitionEntryList Policy Sets with Overlapping Policies

Policy Set definitions often have a large overlap. In CSV files the Policy only shows up once. When EPAC processes the CSV file, it will use the effect from the first Policy Set definition in the definitionEntryList that contains the Policy.

For the next Policy Set in the definitionEntryList that contains the same Policy, EPAC will adjust the effect: - Append, Modify and Deny will be adjusted to Audit - DeployIfNotExists will be adjusted to AuditIfNotExists

Updating the CSV File

Policy Set definitions for built-in or custom Policy Sets are sometimes updated. When this happens, the CSV file must be updated to reflect the changes. EPAC displays a warning when this happens.

Use Build-PolicyDocumentation.ps1 followed by Update-PolicyAssignmentCsvParameterFile.ps1 to reconcile a generated CSV with the parameter CSV stored in your definitions:

./Scripts/Operations/Build-PolicyDocumentation.ps1 `
    -DefinitionsRootFolder ./Definitions `
    -OutputFolder ./Outputs `
    -Interactive:$false

./Scripts/Operations/Update-PolicyAssignmentCsvParameterFile.ps1 `
    -GeneratedCsvPath ./Outputs/policy-documentation/security-baseline.csv `
    -ParameterCsvPath ./Definitions/policyAssignments/security-baseline-parameters.csv

The update command matches rows by name and referencePath and applies these rules:

  • Generated metadata such as display name, description, policy sets, and allowed effects is refreshed.
  • Existing columns ending in Effect or Parameters retain their values.
  • Existing custom columns, including nonComplianceMessages, are retained.
  • Policies removed from every generated Policy Set are removed from the parameter CSV.
  • Newly generated policies are added with generated effect and parameter defaults. Existing custom columns are empty for these rows.

The target file is validated before it is replaced. Duplicate name and referencePath identities, missing required columns, or invalid input leave the parameter CSV unchanged. Use -WhatIf to preview which file would be updated without writing it.