[SOLVED]: Invoke-PolicyEvaluation fails with 'Value cannot be null. Parameter name: policy'

Oct 18, 2016 at 8:49 PM
Edited Oct 18, 2016 at 8:50 PM

SOLVED

I made a few changes in order to avoid the PowerShell's Invoke-PolicyEvaluation cmdlet fail. Hope it's as usefull as it was to me.
# Evaluate specific Policies against a Server List
# Uses the Invoke-PolicyEvaluation Cmdlet

[CmdletBinding()]
param([string]$ConfigurationGroup=$(Throw `
    "Paramater missing: -ConfigurationGroup ConfigGroup"),`
[string]$PolicyCategoryFilter=$(Throw "Parameter missing: `
    -PolicyCategoryFilter Category"), `
[string]$EvalMode=$(Throw "Parameter missing: -EvalMode EvalMode"))

$ErrorActionPreference = 'Stop'

# Declare variables to define the central warehouse
# in which to write the output, store the policies
$CentralManagementServer = "CONTOSO\SQL"
$HistoryDatabase = "ReportPolicyManagement"
$PolicyCategories = $PolicyCategoryFilter.Split(",");
# Define the location to write the results of the
# policy evaluation.  Delete any files in the directory.
$ResultDir = "D:\PolicyManagement\XML\"
$ResultDirDel = $ResultDir + "*.xml"
Remove-Item -Path $ResultDirDel
# End of variables

#Function to insert policy evaluation results
#into SQL Server - table policy.PolicyHistory
function PolicyHistoryInsert($sqlServerVariable, $sqlDatabaseVariable, $EvaluatedServer, $EvaluatedPolicy, $EvaluationResults) 
{
   &{
    $sqlQueryText = "INSERT INTO policy.PolicyHistory (EvaluatedServer, EvaluatedPolicy, EvaluationResults) VALUES(N'$EvaluatedServer', N'$EvaluatedPolicy', N'$EvaluationResults')"
    Invoke-Sqlcmd -ServerInstance $sqlServerVariable -Database $sqlDatabaseVariable -Query $sqlQueryText -ErrorAction Stop
    }
    trap
    {
      $ExceptionText = $_.Exception.Message -replace "'", "" 
    }
}

#Function to insert policy evaluation errors 
#into SQL Server - table policy.EvaluationErrorHistory
function PolicyErrorInsert($sqlServerVariable, $sqlDatabaseVariable, $EvaluatedServer, $EvaluatedPolicy, $EvaluationResultsEscape) 
{
    &{
    $sqlQueryText = "INSERT INTO policy.EvaluationErrorHistory (EvaluatedServer, EvaluatedPolicy, EvaluationResults) VALUES(N'$EvaluatedServer', N'$EvaluatedPolicy', N'$EvaluationResultsEscape')"
    Invoke-Sqlcmd -ServerInstance $sqlServerVariable -Database $sqlDatabaseVariable -Query $sqlQueryText -ErrorAction Stop
    }
    trap
    {
      $ExceptionText = $_.Exception.Message -replace "'", "" 
    }
}

#Connection to the policy store
#$conn = new-object Microsoft.SQlServer.Management.Sdk.Sfc.SqlStoreConnection("server=$CentralManagementServer;Trusted_Connection=true");
#$PolicyStore = new-object Microsoft.SqlServer.Management.DMF.PolicyStore($conn);
$conn = "SQLSERVER:\SQLPolicy\$CentralManagementServer\Policies"
$PolicyStore = Get-ChildItem $conn -Force | Where-Object {$_.PolicyCategory -in $PolicyCategories}

# Create recordset of servers to evaluate
$sconn = new-object System.Data.SqlClient.SqlConnection("server=$CentralManagementServer;Trusted_Connection=true");
$q = "SELECT DISTINCT server_name FROM $HistoryDatabase.[policy].[pfn_ServerGroupInstances]('$ConfigurationGroup');"

$sconn.Open()
$cmd = new-object System.Data.SqlClient.SqlCommand ($q, $sconn);
$cmd.CommandTimeout = 0;
$dr = $cmd.ExecuteReader();

# Loop through the servers and then loop through
# the policies.  For each server and policy,
# call cmdlet to evaluate policy on server

while ($dr.Read()) { 
    $ServerName = $dr.GetValue(0);
    foreach ($Policy in $PolicyStore)
   {
        &{
            $OutputFile = $ResultDir + ("{0}_{1}.xml" -f (Encode-SqlName $ServerName ), (Encode-SqlName $Policy.Name));
            #Invoke-PolicyEvaluation -Policy $Policy -TargetServerName $ServerName -AdHocPolicyEvaluationMode $EvalMode -OutputXML > $OutputFile;
            $Policy | Invoke-PolicyEvaluation -TargetServerName $ServerName -AdHocPolicyEvaluationMode $EvalMode -OutputXML > $OutputFile;
            $PolicyResult = Get-Content $OutputFile -encoding UTF8;
            $PolicyResult = $PolicyResult -replace "'", "" 
            PolicyHistoryInsert $CentralManagementServer $HistoryDatabase $ServerName $Policy.Name $PolicyResult;
        }
            trap [Exception]
            { 
                  $ExceptionText = $_.Exception.Message -replace "'", "" 
                  $ExceptionMessage = $_.Exception.GetType().FullName + ", " + $ExceptionText
                  PolicyErrorInsert $CentralManagementServer $HistoryDatabase $ServerName $Policy.Name $ExceptionMessage;
                  continue;   
            }
   } 
 }

$dr.Close()
$sconn.Close()

#Shred the XML results to PolicyHistoryDetails
Invoke-Sqlcmd `
    -ServerInstance $CentralManagementServer `
    -Database $HistoryDatabase `
    -Query "exec policy.epm_LoadPolicyHistoryDetail" `
    -ErrorAction Stop `
    -QueryTimeout 7200
Dec 16, 2016 at 8:24 PM
Perfect! Works for me with this code. I added back the code to delete the xml files as for some reason it wasn't cleaning up all of the time.
I was playing around with this and found that without any filter on the PolicyStore it would not pull anything back.
I commented out the where-object portion and it worked perfectly.