Inserting data in to the PolicyHistoryDetail table

May 10, 2012 at 11:49 AM

I have implemented EPM in our development environment and have found that no data is being written to the PolicyHistoryDetail table in my datawarehouse if i DO NOT specify a category for the policy. I appreciate it is best practise to categorize the policies but during testing I did not do this for some of the policies I was implementing and then spent some time establishing why this wasn't working for me. I believe this is due to the inner join in the stored proc between:-

msdb.dbo.syspolicy_policies

msdb.dbo.syspolicy_policy_categories

Just wanted to see if anyone else had run into this issue and to alert others to this potential pitfall.

My testing is still in it's infancy, but this certainly looks like it will be a useful tool in our arsenal.

Many Thanks for your efforts.

Brian

May 11, 2012 at 11:19 AM

Brian

Have been using EPM to monitor 12 servers in our production environment for the past 9-10 months.

You'll find that the reports make much more sense if you categorize the policies.

 

Cheers

Phil

Developer
May 17, 2012 at 8:12 PM

Brian and Phil,

This is an issue with one of the major views used by the dashboard.  Policies without a category will be assigned to <Default> and would not be included in the dashboard.  As Phil had indicated by categorizing the policies you can get the most out of the dashboard to maintain your system health.  If you still choose to assign policies to default then you can modify the view being used to render the policy dashboard.  I have included a modification I have been using but I suggest you backup your existing view and test the modification prior to implementing to make sure it will meet your requirements:

CREATE

 VIEW [policy].[v_PolicyHistory]

AS

--The policy.v_PolicyHistory view will return all results

--and identify the policy evaluation result AS PASS, FAIL, or

--ERROR. The ERROR result indicates that the policy was not able

--to evaluate against an object.

--Modification: 2/2/2010

--Changed join to syspolicy_policy_categories from INNER JOIN to LEFT OUTER JOIN

 

 

 SELECT PH.PolicyHistoryID

, PH.EvaluatedServer

, PH.EvaluationDateTime

, PH.EvaluatedPolicy

, PH.PolicyResult

, PH.ExceptionMessage

, PH.ResultDetail

, PH.EvaluatedObject

, PH.policy_id

, PH.CategoryName

, PH.MonthYear

FROM policy.PolicyHistoryDetail PH

INNER JOIN msdb.dbo.syspolicy_policies AS p ON p.name = PH.EvaluatedPolicy

LEFT OUTER JOIN msdb.dbo.syspolicy_policy_categories AS c ON

p

.policy_category_id = c.policy_category_id

GO

 

Enjoy,

Cory

Jan 19, 2014 at 7:39 PM
Edited Jan 19, 2014 at 7:40 PM
Hi,
 I assume that you might have successfully implemented EPM since the last time you faced the issue of running the Powershell without specifying the policy category. Right now I am at this stage and I am not able to move forward. I have only one policy enabled in the CMS as I am in the infant stage here . There are no results when I run the query.
SELECT * FROM policy.v_PolicyHistory
GO
SELECT * FROM policy.v_EvaluationErrorHistory
GO

Also when I tried to setup the Management Data Warehouse database when I ran the first script from the codeplex library.( I received an error:)

Warning! The maximum key length is 900 bytes. The index 'IX_PolicyHistoryView' has maximum length of 1330 bytes. For some combination of large values, the insert/update operation will fail.
Warning! The maximum key length is 900 bytes. The index 'IX_EvaluatedServer' has maximum length of 1032 bytes. For some combination of large values, the insert/update operation will fail.

I appreciate your time in getting back to me with a solution.

Thanks,
Joe
Oct 15, 2014 at 7:59 PM
I'm looking for an answer to the following just referenced by Joechelad:


Warning! The maximum key length is 900 bytes. The index 'IX_PolicyHistoryView' has maximum length of 1330 bytes. For some combination of large values, the insert/update operation will fail.
Warning! The maximum key length is 900 bytes. The index 'IX_EvaluatedServer' has maximum length of 1032 bytes. For some combination of large values, the insert/update operation will fail.


I'd like to avoid this problem and just have a clean fix.

Thanks!
Keith