Enterprise Policy Management Framework 4.0 versus SQL Server 2014

Sep 20, 2014 at 8:27 AM
I have seen that Enterprise Policy Management Framework 4.0 is working with SQL Server 2012 but is it working with a SQL Server 2014 instance ?
Maybe it is a "basic" question but I think it's an interesting one since SQ Server 2014 has been released about 3 months ago.
I would understand that it is too soon to have a new version of this framework which seems really interesting
Have a nice day
Coordinator
Sep 20, 2014 at 11:59 AM
Yes, EPMF v4 has been tested and validated against 2014.

Sent from my Windows Phone

Nov 4, 2014 at 6:31 PM
It seems I have some issues with EPMv4 and SQL2014. Here's my configuration: SQLServer 2014 Developer Edition (enterprise engine), with CU4 (v12.0.2430). I also have a SQL2012 Express edition on the same laptop and a SQL2012 server at my office. My 2014 instance is the central management server. I created a simple condition and policy with the category 'test'. When I run the powershellscript (as described in the manual), no policies are evaluated. I removed the delete statement to examine the .xml outpufiles. These are in the format {servername}_{category].xml, but both are only 136 bytes in size, content: [<?xml version="1.0" encoding="utf-16"?>
<PolicyEvaluationResults] (yes, truncated)

I did some extensive testing earlier today on SQL2012/2008R2 with no problems ...
Any ideas?
Nov 6, 2014 at 7:22 PM
I added some output to the powershell script, this is the errormessage: 'System.ArgumentNullException, Value cannot be null. Parameter name: policy"
This is in the trap part of the while loop. Os is Windows 8.1
Coordinator
Nov 24, 2014 at 12:50 AM
FYI, our initial tests were with the CMS on a SQL 2102 and evaluating against all versions from 2000 onwards. Also tested having SQL 2014 as the CMS against all versions from 2000 onwards, but not having the CMS in SQL 2014 while there was also a SQL2012 in the same box, where this precedence hits and breaks the data collection.
Thanks for bringing this to our attention. I have been able to replicate the issue when more than one PSModulePath are listed, and SQL11 precedes SQL12. I'm in the process of testing a fix, and should come back shortly with it with a syntax that is more resilient to this scenario.
Coordinator
Nov 24, 2014 at 2:34 PM
Hello,
Just released v4.1, so please update the PS script and the data loading SP, and test again. Please let me know how it works.
Thanks
Nov 24, 2014 at 4:43 PM
v4.1 doesn't appear to be returning Evaluation Results. Error mesage is System.ArgumentException, parsing "\" - Illegal...Quantifier
v4.0 returns Evaluation results with the same policies.
Coordinator
Nov 24, 2014 at 5:32 PM
Hi Keith, could you please tell me which SQL version is your CMS, on which OS version and PS version are you running please.
Thanks
Nov 24, 2014 at 6:16 PM
SQL is 2012 version = 11.0.5532.0

OS is 2008r2 version = Microsoft Windows NT 6.1 (7601)

PowerShell version = 3.0



Also having the following issue with all of the subreports (ver 4 or 4.1):

The definition of the report '/PolicyDetails' is invalid.

The report definition may have been created with a later version of Reporting Services, or contain content that is not well-formed or not valid based on Reporting Services schemas. Details: '.', hexadecimal value 0x00, is an invalid character. Line 963, position 10.
Nov 24, 2014 at 7:28 PM
Edited Nov 24, 2014 at 7:30 PM
Almost the same issue ...
It seems the script is trying to extend the results directory or is a backslash missing???? . Here's the output of the powershell script:
<output>
Loading Assemblies...

The job script encountered the following errors. These errors did not stop the script:
A job step received an error at line 164 in a PowerShell script. The corresponding line is ' $PolicyResult = Get-Content $OutputFile -encoding UTF8
'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot find path 'C:\PBM\Results\GUT689S3U\SQLEXPRESS_AUTO-SHRINKstaatuit.xml' because it does not exist.
'
</output>
The outputdirectory is set to " C:\PBM\Results\"
OS is windows 8.1 (6.3.9600)
PBM is located om MSSQL2014 (12.0.2402)
Powershell version 4,0,-1,-1
querying 2012 SQLExpress edition


Output from v_evaluationerrorhistory (column EvaluationResults):
System.IO.DirectoryNotFoundException, Could not find a part of the path C:\PBM\Results\GUT689S3U\SQLEXPRESS_AlwaysOnAgSynchronousReplicasDataSynchronizationHealthPolicy.xml.
System.ArgumentException, parsing "\" - Illegal \ at end of pattern.
System.ArgumentException, parsing "?" - Quantifier {x,y} following nothing.
System.ArgumentException, parsing "*" - Quantifier {x,y} following nothing.
System.IO.DirectoryNotFoundException, Could not find a part of the path C:\PBM\Results\GUT689S3U\SQLEXPRESS_AlwaysOnAgWSFClusterHealthPolicy.xml.
System.ArgumentException, parsing "\" - Illegal \ at end of pattern.
System.ArgumentException, parsing "?" - Quantifier {x,y} following nothing.
Nov 24, 2014 at 7:51 PM
  • UPDATE *
Made some progress, I examined the Powershell output and saw I did not had the rights to load the SQLPS module. See http://technet.microsoft.com/nl-NL/library/hh847748.aspx
Nov 24, 2014 at 8:00 PM
Edited Nov 24, 2014 at 8:04 PM
** UPDATE 2 **

Here's the complete output of the powershell script:
.\EPM_EnterpriseEvaluation_41.ps1 -ConfigurationGroup "root" -PolicyCategoryFilter "tst" –EvalMode "Check"
Loading Assemblies...
C:\PBM\Results\GUT689S3U\SQLEXPRESS_AUTO-SHRINKstaatuit.xml
Get-Content : Cannot find path 'C:\PBM\Results\GUT689S3U\SQLEXPRESS_AUTO-SHRINKstaatuit.xml' because it does not exist.
At C:\PBM\EPM_EnterpriseEvaluation_41.ps1:166 char:20
  • $PolicyResult = Get-Content $OutputFile -encoding UTF8
  • ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    • CategoryInfo : ObjectNotFound: (C:\PBM\Results...INKstaatuit.xml:String) [Get-Content], ItemNotFoundEx
      ception
    • FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetContentCommand
VERBOSE: Nov 24 2014 9:58:45:420PM - Starting data integration for Category tst
VERBOSE: Nov 24 2014 9:58:45:420PM |- 0 rows inserted...
VERBOSE: Nov 24 2014 9:58:45:420PM - Starting no target data integration
VERBOSE: Nov 24 2014 9:58:45:423PM |- 0 rows inserted...
VERBOSE: Nov 24 2014 9:58:45:423PM - Starting errors data integration
VERBOSE: Nov 24 2014 9:58:45:423PM |- 0 rows inserted...
VERBOSE: Nov 24 2014 9:58:45:423PM - Finished data integration for Category tst
PS SQLSERVER:\SQLPolicy\GUT689S3U\DEFAULT\Policies>
Coordinator
Nov 24, 2014 at 11:50 PM
Edited Nov 24, 2014 at 11:57 PM
-KeithCorser
So you're having issues with the reports? These have not changed from v4, as the schema in the database remains the same.
About the PS issue, can you provide the full output please?
Thanks

-wfvdijk
So I'm with the same PS version and SQL as CMS, and I get the following (not running PS in elevated prompt, but in account that is local admin):
PS D:\PBMTest> .\EPM_EnterpriseEvaluation_41.ps1 -ConfigurationGroup "" -PolicyCategoryFilter "Microsoft Best Practices: Maintenance" -EvalMode "Check"
Loading Assemblies...
Loading SQLPS module...
VERBOSE: Nov 25 2014 12:32:41:900AM - Starting data integration for Category Microsoft Best Practices: Maintenance
VERBOSE: Nov 25 2014 12:32:42:660AM   |- 111 rows inserted...
VERBOSE: Nov 25 2014 12:32:42:660AM - Starting no target data integration
VERBOSE: Nov 25 2014 12:32:42:670AM   |- 1 rows inserted...
VERBOSE: Nov 25 2014 12:32:42:670AM - Starting errors data integration
VERBOSE: Nov 25 2014 12:32:42:680AM   |- 0 rows inserted...
VERBOSE: Nov 25 2014 12:32:42:680AM - Finished data integration for Category Microsoft Best Practices: Maintenance
So, although I cannot seem to repro your issue, may I ask you to change the following (minus start and end double quotes, and try again?
"$OutputFile = $ResultDir + ("{0}_{1}.xml" -f ($ServerName), ($PolicyName))"
to
"$OutputFile = $ResultDir + ("{0}_{1}.xml" -f (Encode-SqlName $ServerName), ($PolicyName))"
Also change the + above to plus sign.

Also, let's escape the characters in the replace, like so:
$PolicyName = $PolicyName -replace "\\", "" 
$PolicyName = $PolicyName -replace "\/", "" 
$PolicyName = $PolicyName -replace "\?", "" 
$PolicyName = $PolicyName -replace "\:", "" 
$PolicyName = $PolicyName -replace "\*", "" 
$PolicyName = $PolicyName -replace "\<", "" 
$PolicyName = $PolicyName -replace "\>", "" 
$PolicyName = $PolicyName -replace " ", ""
Please let me know how it works.
Thanks
Coordinator
Nov 25, 2014 at 7:19 PM
About the reports, upon examining my source and the uploaded, seems several non-printing characters were in the .rdl code, which were in turn messing up the report validation.
Please download again, as I've replaced the EPM_Framework_41.zip file with the fixed reports inside.
Thanks again for reporting this.
Nov 29, 2014 at 9:24 AM
The powershell script in the release from Oct 24 is working fine on MSSQL 2014. I dropped my old database and did a complete re-install without any modifications. No problems on the SQL side. Will check the reports now ....
Feb 24, 2015 at 7:51 PM
I've installed the latest on 2012 but am not getting the policy history table loaded. When i open the report the category list is empty. Any ideas?
Coordinator
Feb 25, 2015 at 11:11 AM
Have you run the script\job that evaluated the policies? The PowerShell script is what calls your policies via the central management server and load results into mdw, which the reports key off of.

Sent from my Windows Phone

Feb 25, 2015 at 2:07 PM
Yes I have. The only table I see with data is the EvaluationErrorHistory table. I changed the database name to something else because we are using MDW for a different app. I'll try restarting from the beginning, however, I'm quite positive I ran all scripts.
Coordinator
Feb 25, 2015 at 2:29 PM
When you changed the database, did you update the report definitions to point to new database name and redeploy those reports?

Feb 25, 2015 at 3:15 PM
Edited Feb 25, 2015 at 3:17 PM
Yes I did. When I looked at where the report is pulling from, there is no data in that table/view. The category drop down is empty. I can select server group and date month though.
Coordinator
Feb 25, 2015 at 4:55 PM
And you verified that the deployed PowerShell script has the updated database information? What happens if you try to manually run the PS script?

Feb 26, 2015 at 6:17 PM
This is what I receive. Looks like it's not reading the registered servers in the group? I only have to for testing.
PS SQLSERVER:\SQL\CMS_ServerName\DEFAULT> # Function to load module
PS SQLSERVER:\SQL\CMS_ServerName\DEFAULT> function LoadModule($modname)
{
$Error.Clear()
if (-not(${env:programfiles(x86)}))
{
    $PrgFilePath = ${env:programfiles}.ToString()
}
else
{
    $PrgFilePath = ${env:programfiles(x86)}.ToString()
}

$env:PSModulePath = "$PrgFilePath\Microsoft SQL Server\120\Tools\PowerShe
ll\Modules\;" + $env:PSModulePath
if (-not(Get-Module -Name $modname))
{
    if (Get-Module -ListAvailable | Where-Object { $_.name -eq $modname }
)
    {
        if ($host.Name -eq "ConsoleHost")
        {
            Write-Host -ForegroundColor green "  Loading SQLPS module"
        }
        try
        {
            Import-Module -Name $modname -DisableNameChecking -Force -War
ningAction SilentlyContinue -ErrorAction Stop
        }
        catch
        {
            if ($Error -notlike "A drive with the name *SQLSERVER* alread
y exists.")
            {
                Write-Host $Error
            }
        }
    }
}
else
{
    if ($host.Name -eq "ConsoleHost")
    {
        Write-Host -ForegroundColor green "  SQLPS module already loaded"

    }
}
}

PS SQLSERVER:\SQL\CMS_ServerName\DEFAULT> #Function to insert policy evaluation result
s into SQL Server - table policy.PolicyHistory
PS SQLSERVER:\SQL\CMS_ServerName\DEFAULT> function PolicyHistoryInsert($sqlServerVaria
ble, $sqlDatabaseVariable, $EvaluatedServer, $EvaluatedPolicy, $EvaluationResult
s)
{
$sqlQueryText = "INSERT INTO policy.PolicyHistory (EvaluatedServer, Evalu
atedPolicy, EvaluationResults) VALUES (N'$EvaluatedServer', N'$EvaluatedPolicy',
N'$EvaluationResults')"
try
{
    Invoke-Sqlcmd -ServerInstance $sqlServerVariable -Database $sqlDataba
seVariable -Query $sqlQueryText -QueryTimeout 65535 -ErrorAction Stop
}
catch
{
    $ExceptionText = $_.Exception.Message -replace "'", ""
    return $ExceptionText
}
}

PS SQLSERVER:\SQL\CMS_ServerName\DEFAULT> #Function to insert policy evaluation errors
into SQL Server - table policy.EvaluationErrorHistory
PS SQLSERVER:\SQL\CMS_ServerName\DEFAULT> function PolicyErrorInsert($sqlServerVariabl
e, $sqlDatabaseVariable, $EvaluatedServer, $EvaluatedPolicy, $EvaluationResultsE
scape)
{
$sqlQueryText = "INSERT INTO policy.EvaluationErrorHistory (EvaluatedServ
er, EvaluatedPolicy, EvaluationResults) VALUES(N'$EvaluatedServer', N'$Evaluated
Policy', N'$EvaluationResultsEscape')"
try
{
    Invoke-Sqlcmd -ServerInstance $sqlServerVariable -Database $sqlDataba
seVariable -Query $sqlQueryText -QueryTimeout 65535 -ErrorAction Stop
}
catch
{
    $ExceptionText = $_.Exception.Message -replace "'", ""
    return $ExceptionText
}
}

PS SQLSERVER:\SQL\CMS_ServerName\DEFAULT> #Function to delete files from this policy o
nly
PS SQLSERVER:\SQL\CMS_ServerName\DEFAULT> function PolicyFileDelete($File)
{
# Delete evaluation files in the directory.
try
{
    Remove-Item -Path $File -Force
}
catch
{
    $ExceptionText = $_.Exception.Message -replace "'", ""
    return $ExceptionText
    continue
}
}

PS SQLSERVER:\SQL\CMS_ServerName\DEFAULT> if ($host.Name -eq "ConsoleHost")
{
Write-Host -ForegroundColor green "EPM Framework v4.1.2"
Write-Host -ForegroundColor green "Starting policy category evaluation -
$(Get-Date -Format G)"
}

EPM Framework v4.1.2
Starting policy category evaluation - 2/26/2015 1:11:02 PM
PS SQLSERVER:\SQL\CMS_ServerName\DEFAULT> # Load Assemblies
PS SQLSERVER:\SQL\CMS_ServerName\DEFAULT> LoadAssemblies
Loading Assemblies
WARNING: Using provider context. Server = CMS_ServerName.
PS SQLSERVER:\SQL\CMS_ServerName\DEFAULT> LoadModule -modname "SQLPS"
SQLPS module already loaded
PS SQLSERVER:\SQL\CMS_ServerName\DEFAULT>
PS SQLSERVER:\SQL\CMS_ServerName\DEFAULT> if ($host.Name -eq "ConsoleHost")
{
Write-Host -ForegroundColor green "  Connecting to the policy store"
}

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($con
n)

Connecting to the policy store
PS SQLSERVER:\SQL\CMS_ServerName\DEFAULT> if ($host.Name -eq "ConsoleHost")
{
Write-Host -ForegroundColor green "  Get list of servers to evaluate"
}

Create recordset of servers to evaluate

$sconn = new-object System.Data.SqlClient.SqlConnection("server=$CentralManag
ementServer;Trusted_Connection=true")
$q = "SELECT DISTINCT server_name FROM $HistoryDatabase.[policy].pfn_ServerG roupInstances;"

Get list of servers to evaluate
PS SQLSERVER:\SQL\CMS_ServerName\DEFAULT> $sconn.Open()
PS SQLSERVER:\SQL\CMS_ServerName\DEFAULT> $cmd = new-object System.Data.SqlClient.SqlC
ommand ($q, $sconn)
PS SQLSERVER:\SQL\CMS_ServerName\DEFAULT> $cmd.CommandTimeout = 0
PS SQLSERVER:\SQL\CMS_ServerName\DEFAULT> $dr = $cmd.ExecuteReader()
PS SQLSERVER:\SQL\CMS_ServerName\DEFAULT>
PS SQLSERVER:\SQL\CMS_ServerName\DEFAULT> # Handle PS4 or above
PS SQLSERVER:\SQL\CMS_ServerName\DEFAULT> if ($PSVersionTable.PSVersion.Major -ge 4)
{
if ($CentralManagementServer -like "*\*")
{
    sl "SQLSERVER:\SQLPolicy\$CentralManagementServer\Policies"
}
else
{
    sl "SQLSERVER:\SQLPolicy\$CentralManagementServer\DEFAULT\Policies"
}
}

PS SQLSERVER:\SQL\CMS_ServerName\DEFAULT> if ($host.Name -eq "ConsoleHost")
{
Write-Host -ForegroundColor green "  Starting server loop"
}

Loop through the servers and then loop through

the policies. For each server and policy,

call cmdlet to evaluate policy on server and delete xml file afterwards

while ($dr.Read()) {
$ServerName = $dr.GetValue(0);
foreach ($Policy in $PolicyStore.Policies)
{
    if (($Policy.PolicyCategory -eq $PolicyCategoryFilter) -or ($PolicyCa
tegoryFilter -eq ""))
    {
        # Remove illegal characters in file names
        $PolicyName = $Policy.Name
        # Done in several lines for v2 compatibility
        $PolicyName = $PolicyName -replace "\\", ""
        $PolicyName = $PolicyName -replace "\/", ""
        $PolicyName = $PolicyName -replace "\?", ""
        $PolicyName = $PolicyName -replace "\:", ""
        $PolicyName = $PolicyName -replace "\*", ""
        $PolicyName = $PolicyName -replace "\<", ""
        $PolicyName = $PolicyName -replace "\>", ""
        $PolicyName = $PolicyName -replace " ", ""

        $OutputFile = $ResultDir + ("{0}_{1}.xml" -f (Encode-SqlName $Ser
verName), ($PolicyName))
        if (-not ($OutputFile))
        {
            $ServerName = $ServerName -replace "\\", "_"
            $OutputFile = $ResultDir + ("{0}_{1}.xml" -f ($ServerName), (
$PolicyName))
        }

        try
        {
            if ($PSVersionTable.PSVersion.Major -ge 4)
            {
                Get-ChildItem | Where-Object {$_.Name -eq $Policy.Name} |
Invoke-PolicyEvaluation -TargetServerName $ServerName -AdHocPolicyEvaluationMod
e $EvalMode -OutputXML > $OutputFile
            }
            else
            {
                Invoke-PolicyEvaluation -Policy $Policy -TargetServerName
$ServerName -AdHocPolicyEvaluationMode $EvalMode -OutputXML > $OutputFile
            }
            $PolicyResult = Get-Content $OutputFile -encoding UTF8
            $PolicyResult = $PolicyResult -replace "'", ""
            PolicyHistoryInsert $CentralManagementServer $HistoryDatabase
$ServerName $Policy.Name $PolicyResult
            $File = $ResultDir + ("*_{0}.xml" -f ($PolicyName))
            PolicyFileDelete $File
        }
        catch
        {
            $File = $ResultDir + ("*_{0}.xml" -f ($PolicyName))
            PolicyFileDelete $File
            $ExceptionText = $_.Exception.Message -replace "'", ""
            $ExceptionMessage = $_.Exception.GetType().FullName + ", " +
$ExceptionText
            PolicyErrorInsert $CentralManagementServer $HistoryDatabase $
ServerName $Policy.Name $ExceptionMessage
            continue
        }
    }
}
}

Starting server loop
WARNING: Using provider context. Server = CMS_ServerName.
WARNING: Using provider context. Server = CMS_ServerName.
WARNING: Using provider context. Server = CMS_ServerName.
WARNING: Using provider context. Server = CMS_ServerName.
WARNING: Using provider context. Server = CMS_ServerName.
WARNING: Using provider context. Server = CMS_ServerName.
WARNING: Using provider context. Server = CMS_ServerName.
WARNING: Using provider context. Server = CMS_ServerName.
WARNING: Using provider context. Server = CMS_ServerName.
WARNING: Using provider context. Server = CMS_ServerName.
WARNING: Using provider context. Server = CMS_ServerName.
WARNING: Using provider context. Server = CMS_ServerName.
WARNING: Using provider context. Server = CMS_ServerName.
WARNING: Using provider context. Server = CMS_ServerName.
WARNING: Using provider context. Server = CMS_ServerName.
WARNING: Using provider context. Server = CMS_ServerName.
WARNING: Using pro
Feb 26, 2015 at 6:18 PM
S SQLSERVER:\SQL\CMS_ServerName\DEFAULT> if ($host.Name -eq "ConsoleHost")
{
Write-Host -ForegroundColor green "  Finished server loop"
}

Finished server loop
PS SQLSERVER:\SQL\CMS_ServerName\DEFAULT> $dr.Close()
PS SQLSERVER:\SQL\CMS_ServerName\DEFAULT> $sconn.Close()
PS SQLSERVER:\SQL\CMS_ServerName\DEFAULT>
PS SQLSERVER:\SQL\CMS_ServerName\DEFAULT> #Shred the XML results to PolicyHistoryDetai
ls
PS SQLSERVER:\SQL\CMS_ServerName\DEFAULT> Invoke-Sqlcmd -ServerInstance $CentralManage
mentServer -Database $HistoryDatabase -Query "EXEC policy.epm_LoadPolicyHistoryD
etail `$(PolicyCategory)" -Variable "PolicyCategory='${PolicyCategoryFilter}'" -
QueryTimeout 65535 -Verbose -ErrorAction Stop
WARNING: Using provider context. Server = CMS_ServerName.
VERBOSE: Feb 26 2015 1:11:09:570PM - Starting data integration for Category
Server Configuration
VERBOSE: Feb 26 2015 1:11:09:570PM |- 0 rows inserted...
VERBOSE: Feb 26 2015 1:11:09:570PM - Starting no target data integration
VERBOSE: Feb 26 2015 1:11:09:570PM |- 0 rows inserted...
VERBOSE: Feb 26 2015 1:11:09:570PM - Starting errors data integration
VERBOSE: Feb 26 2015 1:11:09:570PM |- 0 rows inserted...
VERBOSE: Feb 26 2015 1:11:09:570PM - Finished data integration for Category
Server Configuration
PS SQLSERVER:\SQL\CMS_ServerName\DEFAULT>
PS SQLSERVER:\SQL\CMS_ServerName\DEFAULT> if ($host.Name -eq "ConsoleHost")
{
Write-Host -ForegroundColor green "Finished policy category evaluation -
$(Get-Date -Format G)"
}
Feb 26, 2015 at 6:23 PM
Here are my variables:

Evaluate specific Policies against a Server List

Uses the Invoke-PolicyEvaluation Cmdlet

v4.12

Parameter -ConfigurationGroup specifies the

Central Management Server group to evaluate

Parameter -PolicyCategoryFilter specifies the

category of policies to evaluate

Parameter -EvalMode accepts "Check" to report policy

results, "Configure" to reconfigure any violations

Declare variables to define the central warehouse

in which to write the output, store the policies

$CentralManagementServer = "CMS_ServerName"
$HistoryDatabase = "EPM"
$configurationGroup = "DEV"
$PolicyCategoryFilter = "Server Configuration"
$EvalMode= "cHECK"

Define the location to write the results of the policy evaluation

$ResultDir = "D:\MSSQL\vDBA_EPM\Results\"

End of variables

Feb 26, 2015 at 6:47 PM
I haven't been able to get the 4.1 version to pull data.
I went back to 4.0 which works perfectly.



Coordinator
Feb 26, 2015 at 6:49 PM
Have you tried the latest versions? There have been several patches since 4.1.

Sent from my Windows Phone

Feb 26, 2015 at 6:52 PM
I'll try the latest verion on test system tonight.


Coordinator
Feb 26, 2015 at 6:56 PM
Out of sheer curiosity, if you changed the eval mode value to "Check" instead of "cHECK", does it work?

Sent from my Windows Phone

Feb 26, 2015 at 7:39 PM
Yes. The .ps1 is from the latest version. How can I download the 4.0 version that works? It's not a case sensitive server but I'll try it.
Feb 26, 2015 at 7:43 PM
Just tried it. Still getting the server loop and no rows inserted. I even upgraded to the latest sp and added the feature pack. Nothing yet.
Coordinator
Feb 26, 2015 at 7:57 PM
Edited Feb 26, 2015 at 8:13 PM
Hi, Have you upgraded from a previous version of EPM? If so, have you ran the upgrade steps as outlined by the documentation? You don't seem to be running the script in a way similar to the command line further below are you?

I have a test environment with one CMS and one server to test on, and these are the configs in the PS script:

# Declare variables to define the central warehouse
# in which to write the output, store the policies
$CentralManagementServer = "MyMachine\SQL2014"
$HistoryDatabase = "MDW"
# Define the location to write the results of the policy evaluation
$ResultDir = "D:\PBMTest\Results\"
# End of variables

My CMS is "MyMachine\SQL2014" and there's a server group with a single instance below it "MyMachine2" which is a default instance in this case.

I created a job that has a Powershell step, and here’s the command line:

SL "D:\PBMTest"
.\EPM_EnterpriseEvaluation_412.ps1 -ConfigurationGroup "" -PolicyCategoryFilter "Microsoft Best Practices: Server Configuration" -EvalMode "Check"

and I didn’t get any error from the job - no output messages are expected if no errors.

Also, running the same from the PS cmdline yields the following:

PS D:\PBMTest> .\EPM_EnterpriseEvaluation_412.ps1 -ConfigurationGroup "" -Policy CategoryFilter "Microsoft Best Practices: Database Configurations" -EvalMode "Check"

EPM Framework v4.1.2
Starting policy category evaluation - 26/02/2015 20:47:50
Loading Assemblies
SQLPS module already loaded
Connecting to the policy store
Get list of servers to evaluate
Starting server loop
Finished server loop
VERBOSE: Feb 26 2015 8:47:54:000PM - Starting data integration for Category
Microsoft Best Practices: Database Configurations
VERBOSE: Feb 26 2015 8:47:54:070PM |- 109 rows inserted...
VERBOSE: Feb 26 2015 8:47:54:070PM - Starting no target data integration
VERBOSE: Feb 26 2015 8:47:54:073PM |- 0 rows inserted...
VERBOSE: Feb 26 2015 8:47:54:073PM - Starting errors data integration
VERBOSE: Feb 26 2015 8:47:54:087PM |- 0 rows inserted...
VERBOSE: Feb 26 2015 8:47:54:087PM - Finished data integration for Category
Microsoft Best Practices: Database Configurations
Finished policy category evaluation - 26/02/2015 20:47:54

Which version of PS and OS are you running? So you’re not getting errors, just no rows loaded? Can you redeploy in your test environment for a clean test? Thanks PL Sent from Windows Mail
Feb 26, 2015 at 8:05 PM
Edited Feb 26, 2015 at 8:11 PM
I can t see these pictures in either chrome or ie. Im running Windows 2008 R2, SQL 2012 sp2 and PS $PSVersionTable.PSVersion says Major 2, Minor 0, Build -1 and Revision -1. This is the first time using EPM as well, no prior version.
Feb 26, 2015 at 8:14 PM
Here is what I scripted:
SL "D:\MSSQL\DBA_EPM"
.\EPM_EnterpriseEvaluation_412.ps1 -ConfigurationGroup "" -PolicyCategoryFilter "Server Configuration" -EvalMode "Check"

and Result:
PS SQLSERVER:\SQL\CMS_ServerName\DEFAULT> SL "D:\MSSQL\DBA_EPM"
PS D:\MSSQL\DBA_EPM> .\EPM_EnterpriseEvaluation_412.ps1 -ConfigurationGroup ""
-PolicyCategoryFilter "Server Configuration" -EvalMode "Check"
EPM Framework v4.1.2
Starting policy category evaluation - 2/26/2015 3:08:16 PM
Loading Assemblies
SQLPS module already loaded
Connecting to the policy store
Get list of servers to evaluate
Starting server loop
Finished server loop
VERBOSE: Feb 26 2015 3:08:17:627PM - Starting data integration for Category
Server Configuration
VERBOSE: Feb 26 2015 3:08:17:627PM |- 0 rows inserted...
VERBOSE: Feb 26 2015 3:08:17:627PM - Starting no target data integration
VERBOSE: Feb 26 2015 3:08:17:627PM |- 0 rows inserted...
VERBOSE: Feb 26 2015 3:08:17:627PM - Starting errors data integration
VERBOSE: Feb 26 2015 3:08:17:627PM |- 0 rows inserted...
VERBOSE: Feb 26 2015 3:08:17:627PM - Finished data integration for Category
Server Configuration
Finished policy category evaluation - 2/26/2015 3:08:17 PM
PS D:\MSSQL\DBA_EPM>
Coordinator
Feb 26, 2015 at 9:46 PM
Edited Feb 26, 2015 at 9:47 PM
Ok, so if you run the query "SELECT DISTINCT server_name FROM $HistoryDatabase.[policy].[pfn_ServerGroupInstances]('$ConfigurationGroup');" in MDW, what do you get back?
Please change $ConfigurationGroup with the actual string that represents your ConfigGroup, and $HistoryDatabase with the MDW DB name.
Also mind that the servers to evaluate need to be in a ConfigGroup(s) under CMS.
Feb 27, 2015 at 12:54 PM
I receive 2 named instances back.
Thank you so much for you help.
Coordinator
Feb 27, 2015 at 3:08 PM
So after you got the 2 servers in config groups, the data was loaded?
Feb 27, 2015 at 3:42 PM
No, they've been in the config group since inception. I was just thanking you for your assistance in this issue.
Coordinator
Feb 27, 2015 at 4:28 PM
Ah ok, sorry. Is there an email I can send you a special version with some more output on each step?
I also asked some questions earlier that you might have missed:
Which version of PS and OS are you running? Can you redeploy in your test environment for a clean test?
Thanks
Feb 27, 2015 at 4:58 PM
I did answer above. It's Windows 2008 R2, SQL 2012 sp2 and running PS $PSVersionTable.PSVersion returned Major 2, Minor 0, Build -1 and Revision -1.
Unfortunately I do not have a test environment for this only DEV and Prod. The CMS is on SQL 2012 sp2 however, the other 2 instances on that box are SQL 2008 R2 and SQL 2014. I don't think that should matter since it's running on the PS for 2012, but anything is possible. I can remove the database completely and redeploy. But I've done that twice already. Yes. It is sunshineconamor@gmail.com.