Get-SqlDscCumulativeUpdate: New command proposal

[johlju]

Command proposal

Suggest adding a command that can return SQL Server Cumulative Update executables as System.IO.FileInfo objects.

Proposed parameters

Parameter	Mandatory	Data type	Description	Default value	Allowed values
Path	Yes	String	Specifies the path to the cumulative update executables.	None	None
Recursive	No	Switch	Specifies the if a recursive search are made for executables in the specified path.	None	None
Latest	No	Switch	Specifies that the executable with the highest version are returned (based on file information).	None	None
Version	No	Version	Specifies that the specific version are returned (based on file information).	None	None
Language	No	String	Specifies the language of the cumulative updates to return (based on file information).	English	Chinese (Traditional), Chinese (Simplified), Japanese, Russian, French, Spanish, Korean, Portuguese (Brazil), German, English, Italian
ProductName	No	String	Specifies the product name of the cumulative updates to return (based on file information).	*SQL Server*	None
Description	No	String	Specifies the description of the cumulative updates to return (based on file information).	*Hotfix*	None
SkipSignatureCheck	No	Switch	Specifies to opt-out from the digital signature check.	None	None

Special considerations or limitations

By default the command should return all the available cumulative update executables. The parameters Latest and Version are mutually exclusive (by using parameter sets).

Parameter Version should be able to handle Microsoft’s NuGet package versioning format - version ranges

Language parameter might not work, because there is no way to know the language based on file information, it is possible to choose language on the Microsoft download site but all languages seems to download the same executable.

Parameters ProductName and Description should support wildcards (-like).

Download URL for KB5074901 - Cumulative Update 1 for SQL Server 2025:

https://download.microsoft.com/download/313ddc44-e6e1-47bc-b9b5-f9bf4d5f38f4/SQLServer2025-KB5074901-x64.exe

Get tile file information using:

(Get-Item -Path '<path>\SQLServer2025-KB5074901-x64.exe').VersionInfo | fl *

FileVersionRaw     : 17.0.4005.7
ProductVersionRaw  : 17.0.4005.7
Comments           : SQL
CompanyName        : Microsoft Corporation
FileBuildPart      : 4005
FileDescription    : Hotfix Pack
FileMajorPart      : 17
FileMinorPart      : 0
FileName           : <path>\SQLServer2025-KB5074901-x64.exe
FilePrivatePart    : 7
FileVersion        : 17.0.4005.7
InternalName       : boxstub_sql
IsDebug            : False
IsPatched          : False
IsPrivateBuild     : False
IsPreRelease       : False
IsSpecialBuild     : False
Language           : English (United States)
LegalCopyright     : Microsoft. All rights reserved.
LegalTrademarks    : Microsoft SQL Server is a registered trademark of Microsoft Corporation.
OriginalFilename   : boxstub_sql.exe
PrivateBuild       :
ProductBuildPart   : 4005
ProductMajorPart   : 17
ProductMinorPart   : 0
ProductName        : Microsoft SQL Server 2025
ProductPrivatePart : 7
ProductVersion     : 17.0.4005.7
SpecialBuild       :

We should also verify the digital signature by default (always after the command has filtered the result), if an invalid signature is detected a warning should be output and that executable should not be returned in the result.

$signature = Get-AuthenticodeSignature -FilePath '<path>\SQLServer2025-KB5074901-x64.exe'
# This must be true to return a result unless overridden by parameter
$signature.Status -eq 'Valid' -and $signature.SignerCertificate.Subject -like 'CN=Microsoft Corporation,*'

Optionally (and additionally) we can also look in the Enhanced Key Usage List

$signature.SignerCertificate.EnhancedKeyUsageList.ObjectId -contains '1.3.6.1.4.1.311.76.8.1' # Microsoft Publisher

Signature look like this (today):

EnhancedKeyUsageList : {Microsoft Publisher (1.3.6.1.4.1.311.76.8.1), Code Signing (1.3.6.1.5.5.7.3.3)}
DnsNameList          : {}
SendAsTrustedIssuer  : False
Archived             : False
Extensions           : {System.Security.Cryptography.Oid, System.Security.Cryptography.Oid,
                       System.Security.Cryptography.Oid, System.Security.Cryptography.Oid…}
FriendlyName         :
HasPrivateKey        : False
PrivateKey           :
IssuerName           : System.Security.Cryptography.X509Certificates.X500DistinguishedName
NotAfter             : 2026-06-17 20:21:35
NotBefore            : 2025-06-19 20:21:35
PublicKey            : System.Security.Cryptography.X509Certificates.PublicKey
RawData              : {48, 130, 6, 3…}
RawDataMemory        : System.ReadOnlyMemory<Byte>[1543]
SerialNumber         : 330000048498E212E078A3315D000000000484
SignatureAlgorithm   : System.Security.Cryptography.Oid
SubjectName          : System.Security.Cryptography.X509Certificates.X500DistinguishedName
Thumbprint           : F5877012FBD62FABCBDC8D8CEE9C9585BA30DF79
Version              : 3
Handle               : 2179748729392
Issuer               : CN=Microsoft Code Signing PCA 2011, O=Microsoft Corporation, L=Redmond, S=Washington, C=US
Subject              : CN=Microsoft Corporation, O=Microsoft Corporation, L=Redmond, S=Washington, C=US
SerialNumberBytes    : System.ReadOnlyMemory<Byte>[19]

[johlju]

@coderabbitai plan

[coderabbitai]

Coding Plan

Summary

• Create a self-contained public command following established SqlServerDsc patterns with dual parameter sets for mutually exclusive Latest/Version filtering
• Implement NuGet version range parsing via a dedicated private helper function using regex-based parsing to avoid external dependencies
• Apply defense-in-depth signature verification checking status, certificate subject, and Enhanced Key Usage by default with opt-out capability
• Provide comprehensive unit test coverage using mock-based testing patterns consistent with the 160+ existing public command tests in the module

Design Choices

Design Choice 1: NuGet Version Range Implementation Approach

Options Considered:

1. Load and use the NuGet.Versioning .NET assembly for parsing
2. Implement a custom regex-based parser following NuGet specification
3. Support only a simplified subset of common patterns (exact match, minimum only)

Chosen Option: 2

Rationale: Implement a custom parser because it keeps the module self-contained without external assembly dependencies, aligns with existing version comparison patterns in the codebase, and provides full NuGet syntax support.

Design Choice 2: Signature Verification Scope

Options Considered:

1. Check only certificate subject (CN=Microsoft Corporation,*)
2. Check subject plus Enhanced Key Usage for Microsoft Publisher OID
3. Make EKU check configurable via additional parameter

Chosen Option: 2

Rationale: Include EKU check by default because it provides stronger verification with minimal additional complexity, and the ticket indicates this is preferred for security. The existing SkipSignatureCheck parameter handles opt-out scenarios.

💡 User Tips

Regenerate the plan with different choices with @coderabbitai <feedback>.

Implementation Steps

Phase 1: Core Command and Infrastructure

Establish the public command structure with all parameters, localized strings, and basic file discovery logic following established module patterns.

Task 1: Create Public Command Structure

Define the command shell with all parameters, parameter sets, and comment-based help documentation.

• Create source/Public/Get-SqlDscCumulativeUpdate.ps1 with the standard command template
• Define two parameter sets: Default (with optional Latest switch) and ByVersion (with Version parameter) to make them mutually exclusive
• Implement parameters: Path (mandatory), Recursive (switch), Latest (switch, Default set), Version (string, ByVersion set), Language (ValidateSet with allowed values, default 'English'), ProductName (string, default 'SQL Server'), Description (string, default 'Hotfix'), SkipSignatureCheck (switch)
• Add comprehensive comment-based help with .SYNOPSIS, .DESCRIPTION, .PARAMETER for each parameter, multiple .EXAMPLE sections showing different use cases, .INPUTS (None), .OUTPUTS (System.IO.FileInfo[]), and .NOTES documenting the Language parameter limitation
• Set [OutputType([System.IO.FileInfo[]])] attribute

Task 2: Add Localized Strings

Add all user-facing messages to the module's string resource file.

• Update source/en-US/SqlServerDsc.strings.psd1 with new string entries
• Add verbose messages: CumulativeUpdate_Get_SearchingPath, CumulativeUpdate_Get_FoundExecutables, CumulativeUpdate_Get_FilteringByVersion, CumulativeUpdate_Get_FilteringByLatest, CumulativeUpdate_Get_VerifyingSignature
• Add warning messages: CumulativeUpdate_Get_InvalidSignature (for signature verification failures), CumulativeUpdate_Get_InvalidVersionRange (for malformed version range syntax)
• Add error messages: CumulativeUpdate_Get_PathNotFound, CumulativeUpdate_Get_NoExecutablesFound
• Follow existing naming pattern: {Component}_{Action}_{Context} with unique error IDs in parentheses (e.g., GSCU0001)

Task 3: Implement File Discovery Logic

Implement the core logic to find and filter executable files based on path and file metadata.

• Use Get-ChildItem with -Path, -Filter '*.exe', and conditionally -Recurse based on Recursive parameter
• Extract VersionInfo from each file using the VersionInfo property of FileInfo
• Filter by ProductName using -like operator against VersionInfo.ProductName
• Filter by Description using -like operator against VersionInfo.FileDescription
• Filter by Language using -eq operator against VersionInfo.Language (document that this may be unreliable)
• Store filtered results for subsequent version and signature filtering stages
• Add verbose output at each filtering stage using localized strings

🤖 Prompt for AI agents

Implement the core public command `Get-SqlDscCumulativeUpdate` with complete
parameter definitions, localized strings, and file discovery logic following
SqlServerDsc module patterns.

**Command Structure:**
- Create `source/Public/Get-SqlDscCumulativeUpdate.ps1` following the standard
SqlServerDsc command template
- Define two parameter sets: `Default` (with optional `Latest` switch) and
`ByVersion` (with `Version` parameter) to make them mutually exclusive
- Add all required parameters:
  - `Path` (mandatory, string): Path to search for CU executables
  - `Recursive` (switch): Enable recursive directory search
  - `Latest` (switch, Default parameter set): Return only the latest version
- `Version` (string, ByVersion parameter set): NuGet-style version range filter
- `Language` (ValidateSet with allowed values, default 'English'): Filter by
language
- `ProductName` (string, default '*SQL Server*'): Filter by product name with
wildcard
- `Description` (string, default '*Hotfix*'): Filter by file description with
wildcard
  - `SkipSignatureCheck` (switch): Skip digital signature verification
- Set `[OutputType([System.IO.FileInfo[]])]` attribute
- Add comprehensive comment-based help including:
  - `.SYNOPSIS` and `.DESCRIPTION`
  - `.PARAMETER` for each parameter
  - Multiple `.EXAMPLE` sections demonstrating different usage scenarios
  - `.INPUTS` (None) and `.OUTPUTS` (`System.IO.FileInfo[]`)
  - `.NOTES` documenting the Language parameter limitation

**Localized Strings:**
- Update `source/en-US/SqlServerDsc.strings.psd1` with new entries:
- Verbose messages: `CumulativeUpdate_Get_SearchingPath`,
`CumulativeUpdate_Get_FoundExecutables`,
`CumulativeUpdate_Get_FilteringByVersion`,
`CumulativeUpdate_Get_FilteringByLatest`,
`CumulativeUpdate_Get_VerifyingSignature`
- Warning messages: `CumulativeUpdate_Get_InvalidSignature`,
`CumulativeUpdate_Get_InvalidVersionRange`
- Error messages with unique IDs: `CumulativeUpdate_Get_PathNotFound`
(GSCU0001), `CumulativeUpdate_Get_NoExecutablesFound` (GSCU0002)
- Follow naming pattern: `{Component}_{Action}_{Context}`

**File Discovery Logic:**
- Use `Get-ChildItem` to find executable files:
  - `-Path` parameter from user input
  - `-Filter '*.exe'` to target executables only
  - Conditionally add `-Recurse` based on `Recursive` switch parameter
- Extract `VersionInfo` property from each `FileInfo` object
- Apply metadata filters in sequence:
- Filter by `ProductName` using `-like` operator against
`VersionInfo.ProductName`
- Filter by `Description` using `-like` operator against
`VersionInfo.FileDescription`
  - Filter by `Language` using `-eq` operator against `VersionInfo.Language`
- Store filtered results for subsequent version and signature filtering (to be
implemented in later phases)
- Add verbose output using localized strings at each filtering stage
- Leave placeholders for version filtering and signature verification logic
(next phases)

Phase 2: Version Range Filtering

Implement NuGet-style version range parsing and filtering capabilities through a dedicated private helper function.

Task 1: Create Version Range Parser Helper

Create a private function to parse NuGet version range syntax and test version membership.

• Create source/Private/Test-SqlDscVersionInRange.ps1 as a private helper function
• Accept parameters: Version (the version to test, as [System.Version]) and Range (the range specification string)
• Parse range syntax using regex to identify: bare version (1.0 = minimum inclusive), exact match ([1.0]), interval notation with brackets/parentheses ([1.0,2.0), (1.0,), (,2.0], etc.)
• Extract minimum version, maximum version, and inclusivity flags (inclusive vs exclusive for each bound)
• Return [System.Boolean] indicating whether the version falls within the specified range
• Handle edge cases: empty/null range (return true), invalid syntax (write warning and return false), version comparison using [System.Version] type casting
• Add corresponding localized strings for any error/warning messages

Task 2: Integrate Version Filtering in Main Command

Wire up the version range filtering logic into the main command flow.

• When Version parameter is provided, iterate filtered files and call Test-SqlDscVersionInRange with each file's VersionInfo.FileVersionRaw and the specified range
• When Latest switch is specified, sort remaining files by VersionInfo.FileVersionRaw descending and select the first item
• Ensure version filtering occurs after metadata filtering (ProductName, Description, Language) but before signature verification
• Add verbose output indicating version filtering is being applied

🤖 Prompt for AI agents

Implement NuGet version range parsing and filtering using a custom regex-based
private helper function, then integrate it into the main command.

**Private Helper Function:**
- Create `source/Private/Test-SqlDscVersionInRange.ps1` with:
  - Parameter `Version` typed as `[System.Version]`: the version to test
  - Parameter `Range` typed as string: NuGet version range specification
- Return type `[System.Boolean]`: true if version is within range, false
otherwise
- Parse NuGet range syntax using regex to identify:
  - Bare version (e.g., `1.0`) = minimum inclusive (equivalent to `[1.0,)`)
  - Exact match with brackets (e.g., `[1.0]`)
  - Interval notation: `[1.0,2.0]`, `(1.0,2.0)`, `[1.0,2.0)`, `(1.0,2.0]`
  - Open-ended ranges: `[1.0,)`, `(1.0,)`, `(,2.0]`, `(,2.0)`
  - `[` = inclusive bound, `(` = exclusive bound
- Extract from parsed range:
  - Minimum version (if specified)
  - Maximum version (if specified)
  - Minimum inclusivity flag (inclusive vs exclusive)
  - Maximum inclusivity flag (inclusive vs exclusive)
- Version comparison logic:
  - Use `[System.Version]` type for comparisons
  - Apply minimum bound check: if exclusive use `-gt`, if inclusive use `-ge`
  - Apply maximum bound check: if exclusive use `-lt`, if inclusive use `-le`
  - Return true only if all applicable bounds are satisfied
- Handle edge cases:
  - Empty or null range: return true (no filtering)
  - Invalid syntax: write warning using localized string and return false
- Add localized strings for warnings/errors if needed

**Integration into Main Command:**
- In `Get-SqlDscCumulativeUpdate`, after metadata filtering (ProductName,
Description, Language):
  - If `Version` parameter is provided:
- Write verbose message using localized string (e.g.,
`CumulativeUpdate_Get_FilteringByVersion`)
    - Iterate through filtered files
- Call `Test-SqlDscVersionInRange` with `$file.VersionInfo.FileVersionRaw` and
the `Version` parameter
    - Keep only files where the function returns true
  - If `Latest` switch is specified:
- Write verbose message using localized string (e.g.,
`CumulativeUpdate_Get_FilteringByLatest`)
    - Sort remaining files by `VersionInfo.FileVersionRaw` in descending order
    - Select the first item (highest version)
- Ensure version filtering occurs after metadata filtering but before signature
verification
- Pass filtered results to the next phase (signature verification)

Phase 3: Signature Verification

Implement digital signature validation to ensure only properly signed Microsoft executables are returned by default.

Task 1: Implement Signature Verification Logic

Add signature checking that validates both signature status and signer identity.

• After all other filtering, iterate remaining files unless SkipSignatureCheck is specified
• Call Get-AuthenticodeSignature -FilePath for each file
• Verify $signature.Status -eq 'Valid'
• Verify $signature.SignerCertificate.Subject -like 'CN=Microsoft Corporation,*'
• Verify Enhanced Key Usage contains Microsoft Publisher OID: $signature.SignerCertificate.EnhancedKeyUsageList.ObjectId -contains '1.3.6.1.4.1.311.76.8.1'
• For files failing verification, output a warning with Write-Warning using localized string (include filename and reason for failure)
• Exclude failed files from the returned results
• Add verbose output when signature verification is being performed and when it's skipped

Task 2: Return Final Results

Finalize the command output with properly typed return values.

• Return the filtered FileInfo objects (after all filtering stages)
• Ensure empty result sets return $null or empty array consistently with other Get commands in the module
• Add verbose output indicating the count of files being returned

🤖 Prompt for AI agents

Implement digital signature verification with comprehensive validation checks
for Microsoft-signed executables, then finalize command output.

**Signature Verification Logic:**
- After all other filtering (metadata and version), check if
`SkipSignatureCheck` parameter is NOT specified
- If signature verification is enabled:
- Write verbose message using localized string (e.g.,
`CumulativeUpdate_Get_VerifyingSignature`)
  - Iterate through remaining files and for each file:
    - Call `Get-AuthenticodeSignature -FilePath $file.FullName`
    - Perform three verification checks:
      1. `$signature.Status -eq 'Valid'`: signature must be valid
2. `$signature.SignerCertificate.Subject -like 'CN=Microsoft Corporation,*'`:
must be signed by Microsoft
3. `$signature.SignerCertificate.EnhancedKeyUsageList.ObjectId -contains
'1.3.6.1.4.1.311.76.8.1'`: must have Microsoft Publisher OID in Enhanced Key
Usage
    - If any check fails:
- Write warning using localized string (e.g.,
`CumulativeUpdate_Get_InvalidSignature`)
      - Include filename and specific reason for failure in the warning message
      - Exclude this file from the results
    - If all checks pass, keep the file in the results
- If `SkipSignatureCheck` is specified:
  - Write verbose message indicating signature verification is being skipped
  - Skip all signature verification logic

**Return Final Results:**
- Return the filtered `FileInfo` objects after all filtering stages (metadata,
version, signature)
- Handle empty results consistently with other SqlServerDsc Get commands:
  - Return `$null` or empty array when no files match
  - Ensure consistency with module patterns
- Add verbose output indicating the count of files being returned (e.g.,
"Returning X file(s)")
- Ensure output type matches `[System.IO.FileInfo[]]` as declared in
`OutputType` attribute

Phase 4: Unit Tests

Create comprehensive unit tests following the established module testing patterns.

Task 1: Create Test File Structure

Set up the test file with standard module test scaffolding.

• Create tests/Unit/Public/Get-SqlDscCumulativeUpdate.Tests.ps1
• Include standard BeforeDiscovery, BeforeAll (with module import and $env:SqlServerDscCI), and AfterAll blocks
• Set up $PSDefaultParameterValues for InModuleScope, Mock, and Should module scoping
• Create test file fixtures in $TestDrive representing mock CU executables

Task 2: Implement Parameter Validation Tests

Test all parameter sets and parameter properties.

• Add parameter set validation test using -ForEach pattern to verify both Default and ByVersion parameter sets have correct parameter signatures
• Test that Latest and Version parameters are mutually exclusive (different parameter sets)
• Test that Path is mandatory
• Test Language parameter ValidateSet enforcement
• Test default values for ProductName and Description

Task 3: Implement Functional Tests

Test core command functionality with mocked dependencies.

• Mock Get-ChildItem to return controlled file objects with mock VersionInfo properties
• Mock Get-AuthenticodeSignature to return valid/invalid signature scenarios
• Test filtering by ProductName wildcard matching
• Test filtering by Description wildcard matching
• Test Recursive switch passes through to Get-ChildItem
• Test Latest returns only highest version file
• Test Version parameter with various range formats (exact, minimum, range)
• Test SkipSignatureCheck bypasses signature verification
• Test warning output for invalid signatures
• Test empty result scenarios
• Verify all mocks are invoked with expected parameters using Should -Invoke

Task 4: Create Private Helper Tests

Test the version range parser function.

• Create tests/Unit/Private/Test-SqlDscVersionInRange.Tests.ps1
• Test exact version match: [1.0.0.0]
• Test minimum inclusive: 1.0.0.0 and [1.0.0.0,)
• Test minimum exclusive: (1.0.0.0,)
• Test maximum inclusive: (,2.0.0.0]
• Test maximum exclusive: (,2.0.0.0)
• Test range combinations: [1.0.0.0,2.0.0.0], (1.0.0.0,2.0.0.0), [1.0.0.0,2.0.0.0)
• Test invalid syntax handling returns false with warning
• Test boundary conditions (version equals min/max with inclusive/exclusive)

🤖 Prompt for AI agents

Create comprehensive unit tests for `Get-SqlDscCumulativeUpdate` and
`Test-SqlDscVersionInRange` following SqlServerDsc testing patterns and
template.

**Main Command Test File Structure:**
- Create `tests/Unit/Public/Get-SqlDscCumulativeUpdate.Tests.ps1`
- Include standard test scaffolding:
  - `BeforeDiscovery` block for parameter discovery
  - `BeforeAll` block: import module, set `$env:SqlServerDscCI = $true`
  - `AfterAll` block: cleanup
- Set `$PSDefaultParameterValues` for `InModuleScope`, `Mock`, and `Should` with
module name scoping
- Create mock file fixtures in `$TestDrive` representing CU executables (use
`New-Item` to create dummy .exe files)

**Parameter Validation Tests:**
- Use `-ForEach` pattern to test both `Default` and `ByVersion` parameter sets
- Verify parameter set membership:
- `Default` set contains: `Path`, `Recursive`, `Latest`, `Language`,
`ProductName`, `Description`, `SkipSignatureCheck`
- `ByVersion` set contains: `Path`, `Recursive`, `Version`, `Language`,
`ProductName`, `Description`, `SkipSignatureCheck`
- Test mutual exclusivity: `Latest` and `Version` cannot be used together
- Verify `Path` parameter is mandatory
- Test `Language` ValidateSet enforcement (should reject invalid values)
- Verify default values: `Language` = 'English', `ProductName` = '*SQL Server*',
`Description` = '*Hotfix*'

**Functional Tests:**
- Mock `Get-ChildItem`:
  - Return mock `FileInfo` objects with custom `VersionInfo` properties
- Include properties: `ProductName`, `FileDescription`, `Language`,
`FileVersionRaw`
- Mock `Get-AuthenticodeSignature`:
- Return mock signature objects with `Status`, `SignerCertificate.Subject`, and
`SignerCertificate.EnhancedKeyUsageList.ObjectId`
  - Create scenarios for valid and invalid signatures
- Test cases:
  - Filter by `ProductName`: verify `-like` wildcard matching works
  - Filter by `Description`: verify `-like` wildcard matching works
- `Recursive` switch: verify it's passed to `Get-ChildItem` (use `Should -Invoke
-ParameterFilter`)
- `Latest` switch: verify only the file with highest `FileVersionRaw` is
returned
- `Version` parameter: test exact match (`[1.0.0.0]`), minimum (`1.0.0.0`), and
range (`[1.0.0.0,2.0.0.0]`)
  - `SkipSignatureCheck`: verify `Get-AuthenticodeSignature` is NOT called
  - Invalid signature: verify warning is written and file is excluded
  - Empty results: verify `$null` or empty array is returned when no files match
- Use `Should -Invoke` to verify mocks are called with correct parameters

**Private Helper Function Tests:**
- Create `tests/Unit/Private/Test-SqlDscVersionInRange.Tests.ps1`
- Test version range parsing scenarios:
  - Exact match: `[1.0.0.0]` should match only 1.0.0.0
- Minimum inclusive: `1.0.0.0` and `[1.0.0.0,)` should match 1.0.0.0 and higher
- Minimum exclusive: `(1.0.0.0,)` should match versions higher than 1.0.0.0 (not
equal)
  - Maximum inclusive: `(,2.0.0.0]` should match 2.0.0.0 and lower
- Maximum exclusive: `(,2.0.0.0)` should match versions lower than 2.0.0.0 (not
equal)
- Range combinations: `[1.0.0.0,2.0.0.0]`, `(1.0.0.0,2.0.0.0)`,
`[1.0.0.0,2.0.0.0)`
- Test boundary conditions:
  - Version equals minimum with inclusive bound: should match
  - Version equals minimum with exclusive bound: should not match
  - Version equals maximum with inclusive bound: should match
  - Version equals maximum with exclusive bound: should not match
- Test edge cases:
  - Invalid syntax: verify function returns false and writes warning
  - Empty/null range: verify function returns true (no filtering)
- Use Pester assertions (`Should -Be $true`, `Should -Be $false`) to verify
return values

Research

SqlServerDsc is a PowerShell DSC module for SQL Server configuration with 160+ public commands following strict naming conventions ({Verb}-SqlDsc{Noun}). Public commands are organized as one function per file in source/Public/, use localized strings from source/en-US/SqlServerDsc.strings.psd1, and follow consistent patterns for comment-based help, parameter validation, error handling with unique error IDs, and ShouldProcess support. Unit tests in tests/Unit/Public/ follow a standardized template with parameter set validation, mock-based testing, and comprehensive coverage. The codebase includes existing version comparison patterns (notably in Get-SqlDscCompatibilityLevel.ps1) that use Where-Object -FilterScript with -ge/-le operators and regex extraction.

---

🚀 Next Steps

🤖 All AI agent prompts combined

Task: 1

Implement the core public command `Get-SqlDscCumulativeUpdate` with complete
parameter definitions, localized strings, and file discovery logic following
SqlServerDsc module patterns.

**Command Structure:**
- Create `source/Public/Get-SqlDscCumulativeUpdate.ps1` following the standard
SqlServerDsc command template
- Define two parameter sets: `Default` (with optional `Latest` switch) and
`ByVersion` (with `Version` parameter) to make them mutually exclusive
- Add all required parameters:
  - `Path` (mandatory, string): Path to search for CU executables
  - `Recursive` (switch): Enable recursive directory search
  - `Latest` (switch, Default parameter set): Return only the latest version
- `Version` (string, ByVersion parameter set): NuGet-style version range filter
- `Language` (ValidateSet with allowed values, default 'English'): Filter by
language
- `ProductName` (string, default '*SQL Server*'): Filter by product name with
wildcard
- `Description` (string, default '*Hotfix*'): Filter by file description with
wildcard
  - `SkipSignatureCheck` (switch): Skip digital signature verification
- Set `[OutputType([System.IO.FileInfo[]])]` attribute
- Add comprehensive comment-based help including:
  - `.SYNOPSIS` and `.DESCRIPTION`
  - `.PARAMETER` for each parameter
  - Multiple `.EXAMPLE` sections demonstrating different usage scenarios
  - `.INPUTS` (None) and `.OUTPUTS` (`System.IO.FileInfo[]`)
  - `.NOTES` documenting the Language parameter limitation

**Localized Strings:**
- Update `source/en-US/SqlServerDsc.strings.psd1` with new entries:
- Verbose messages: `CumulativeUpdate_Get_SearchingPath`,
`CumulativeUpdate_Get_FoundExecutables`,
`CumulativeUpdate_Get_FilteringByVersion`,
`CumulativeUpdate_Get_FilteringByLatest`,
`CumulativeUpdate_Get_VerifyingSignature`
- Warning messages: `CumulativeUpdate_Get_InvalidSignature`,
`CumulativeUpdate_Get_InvalidVersionRange`
- Error messages with unique IDs: `CumulativeUpdate_Get_PathNotFound`
(GSCU0001), `CumulativeUpdate_Get_NoExecutablesFound` (GSCU0002)
- Follow naming pattern: `{Component}_{Action}_{Context}`

**File Discovery Logic:**
- Use `Get-ChildItem` to find executable files:
  - `-Path` parameter from user input
  - `-Filter '*.exe'` to target executables only
  - Conditionally add `-Recurse` based on `Recursive` switch parameter
- Extract `VersionInfo` property from each `FileInfo` object
- Apply metadata filters in sequence:
- Filter by `ProductName` using `-like` operator against
`VersionInfo.ProductName`
- Filter by `Description` using `-like` operator against
`VersionInfo.FileDescription`
  - Filter by `Language` using `-eq` operator against `VersionInfo.Language`
- Store filtered results for subsequent version and signature filtering (to be
implemented in later phases)
- Add verbose output using localized strings at each filtering stage
- Leave placeholders for version filtering and signature verification logic
(next phases)
===============================================================================

Task: 2

Implement NuGet version range parsing and filtering using a custom regex-based
private helper function, then integrate it into the main command.

**Private Helper Function:**
- Create `source/Private/Test-SqlDscVersionInRange.ps1` with:
  - Parameter `Version` typed as `[System.Version]`: the version to test
  - Parameter `Range` typed as string: NuGet version range specification
- Return type `[System.Boolean]`: true if version is within range, false
otherwise
- Parse NuGet range syntax using regex to identify:
  - Bare version (e.g., `1.0`) = minimum inclusive (equivalent to `[1.0,)`)
  - Exact match with brackets (e.g., `[1.0]`)
  - Interval notation: `[1.0,2.0]`, `(1.0,2.0)`, `[1.0,2.0)`, `(1.0,2.0]`
  - Open-ended ranges: `[1.0,)`, `(1.0,)`, `(,2.0]`, `(,2.0)`
  - `[` = inclusive bound, `(` = exclusive bound
- Extract from parsed range:
  - Minimum version (if specified)
  - Maximum version (if specified)
  - Minimum inclusivity flag (inclusive vs exclusive)
  - Maximum inclusivity flag (inclusive vs exclusive)
- Version comparison logic:
  - Use `[System.Version]` type for comparisons
  - Apply minimum bound check: if exclusive use `-gt`, if inclusive use `-ge`
  - Apply maximum bound check: if exclusive use `-lt`, if inclusive use `-le`
  - Return true only if all applicable bounds are satisfied
- Handle edge cases:
  - Empty or null range: return true (no filtering)
  - Invalid syntax: write warning using localized string and return false
- Add localized strings for warnings/errors if needed

**Integration into Main Command:**
- In `Get-SqlDscCumulativeUpdate`, after metadata filtering (ProductName,
Description, Language):
  - If `Version` parameter is provided:
- Write verbose message using localized string (e.g.,
`CumulativeUpdate_Get_FilteringByVersion`)
    - Iterate through filtered files
- Call `Test-SqlDscVersionInRange` with `$file.VersionInfo.FileVersionRaw` and
the `Version` parameter
    - Keep only files where the function returns true
  - If `Latest` switch is specified:
- Write verbose message using localized string (e.g.,
`CumulativeUpdate_Get_FilteringByLatest`)
    - Sort remaining files by `VersionInfo.FileVersionRaw` in descending order
    - Select the first item (highest version)
- Ensure version filtering occurs after metadata filtering but before signature
verification
- Pass filtered results to the next phase (signature verification)
===============================================================================

Task: 3

Implement digital signature verification with comprehensive validation checks
for Microsoft-signed executables, then finalize command output.

**Signature Verification Logic:**
- After all other filtering (metadata and version), check if
`SkipSignatureCheck` parameter is NOT specified
- If signature verification is enabled:
- Write verbose message using localized string (e.g.,
`CumulativeUpdate_Get_VerifyingSignature`)
  - Iterate through remaining files and for each file:
    - Call `Get-AuthenticodeSignature -FilePath $file.FullName`
    - Perform three verification checks:
      1. `$signature.Status -eq 'Valid'`: signature must be valid
2. `$signature.SignerCertificate.Subject -like 'CN=Microsoft Corporation,*'`:
must be signed by Microsoft
3. `$signature.SignerCertificate.EnhancedKeyUsageList.ObjectId -contains
'1.3.6.1.4.1.311.76.8.1'`: must have Microsoft Publisher OID in Enhanced Key
Usage
    - If any check fails:
- Write warning using localized string (e.g.,
`CumulativeUpdate_Get_InvalidSignature`)
      - Include filename and specific reason for failure in the warning message
      - Exclude this file from the results
    - If all checks pass, keep the file in the results
- If `SkipSignatureCheck` is specified:
  - Write verbose message indicating signature verification is being skipped
  - Skip all signature verification logic

**Return Final Results:**
- Return the filtered `FileInfo` objects after all filtering stages (metadata,
version, signature)
- Handle empty results consistently with other SqlServerDsc Get commands:
  - Return `$null` or empty array when no files match
  - Ensure consistency with module patterns
- Add verbose output indicating the count of files being returned (e.g.,
"Returning X file(s)")
- Ensure output type matches `[System.IO.FileInfo[]]` as declared in
`OutputType` attribute
===============================================================================

Task: 4

Create comprehensive unit tests for `Get-SqlDscCumulativeUpdate` and
`Test-SqlDscVersionInRange` following SqlServerDsc testing patterns and
template.

**Main Command Test File Structure:**
- Create `tests/Unit/Public/Get-SqlDscCumulativeUpdate.Tests.ps1`
- Include standard test scaffolding:
  - `BeforeDiscovery` block for parameter discovery
  - `BeforeAll` block: import module, set `$env:SqlServerDscCI = $true`
  - `AfterAll` block: cleanup
- Set `$PSDefaultParameterValues` for `InModuleScope`, `Mock`, and `Should` with
module name scoping
- Create mock file fixtures in `$TestDrive` representing CU executables (use
`New-Item` to create dummy .exe files)

**Parameter Validation Tests:**
- Use `-ForEach` pattern to test both `Default` and `ByVersion` parameter sets
- Verify parameter set membership:
- `Default` set contains: `Path`, `Recursive`, `Latest`, `Language`,
`ProductName`, `Description`, `SkipSignatureCheck`
- `ByVersion` set contains: `Path`, `Recursive`, `Version`, `Language`,
`ProductName`, `Description`, `SkipSignatureCheck`
- Test mutual exclusivity: `Latest` and `Version` cannot be used together
- Verify `Path` parameter is mandatory
- Test `Language` ValidateSet enforcement (should reject invalid values)
- Verify default values: `Language` = 'English', `ProductName` = '*SQL Server*',
`Description` = '*Hotfix*'

**Functional Tests:**
- Mock `Get-ChildItem`:
  - Return mock `FileInfo` objects with custom `VersionInfo` properties
- Include properties: `ProductName`, `FileDescription`, `Language`,
`FileVersionRaw`
- Mock `Get-AuthenticodeSignature`:
- Return mock signature objects with `Status`, `SignerCertificate.Subject`, and
`SignerCertificate.EnhancedKeyUsageList.ObjectId`
  - Create scenarios for valid and invalid signatures
- Test cases:
  - Filter by `ProductName`: verify `-like` wildcard matching works
  - Filter by `Description`: verify `-like` wildcard matching works
- `Recursive` switch: verify it's passed to `Get-ChildItem` (use `Should -Invoke
-ParameterFilter`)
- `Latest` switch: verify only the file with highest `FileVersionRaw` is
returned
- `Version` parameter: test exact match (`[1.0.0.0]`), minimum (`1.0.0.0`), and
range (`[1.0.0.0,2.0.0.0]`)
  - `SkipSignatureCheck`: verify `Get-AuthenticodeSignature` is NOT called
  - Invalid signature: verify warning is written and file is excluded
  - Empty results: verify `$null` or empty array is returned when no files match
- Use `Should -Invoke` to verify mocks are called with correct parameters

**Private Helper Function Tests:**
- Create `tests/Unit/Private/Test-SqlDscVersionInRange.Tests.ps1`
- Test version range parsing scenarios:
  - Exact match: `[1.0.0.0]` should match only 1.0.0.0
- Minimum inclusive: `1.0.0.0` and `[1.0.0.0,)` should match 1.0.0.0 and higher
- Minimum exclusive: `(1.0.0.0,)` should match versions higher than 1.0.0.0 (not
equal)
  - Maximum inclusive: `(,2.0.0.0]` should match 2.0.0.0 and lower
- Maximum exclusive: `(,2.0.0.0)` should match versions lower than 2.0.0.0 (not
equal)
- Range combinations: `[1.0.0.0,2.0.0.0]`, `(1.0.0.0,2.0.0.0)`,
`[1.0.0.0,2.0.0.0)`
- Test boundary conditions:
  - Version equals minimum with inclusive bound: should match
  - Version equals minimum with exclusive bound: should not match
  - Version equals maximum with inclusive bound: should match
  - Version equals maximum with exclusive bound: should not match
- Test edge cases:
  - Invalid syntax: verify function returns false and writes warning
  - Empty/null range: verify function returns true (no filtering)
- Use Pester assertions (`Should -Be $true`, `Should -Be $false`) to verify
return values

💡 Iterate on the plan with: @coderabbitai <feedback>

Example Feedback
- `@coderabbitai` You can skip phase 3. Add a simple unit test case for phase 2.
- `@coderabbitai` For design choice 1 go ahead with option 3 and replan.

---

💬 Have feedback or questions? Drop into our discord!