Drill down into failed policies?

Jun 12, 2009 at 4:18 PM


do you have any plans to enhance this to drill down into why a policy failed?  That is, supposed you have  a Last Successful Backup > 24 hours old failure and you want to see which database failed.  Right now, I don't see a way to drill down into that depth.  In an attempt to come up with something myself, I've been looking everywhere for some insight about how to interpret the XML of a policy evaluation, but no one ever talks about it.  If I knew a way to take the results, that look like:

  <DMF:ResultDetail type="string"><Operator><?char 13?> <TypeClass>Bool</TypeClass><?char 13?> <OpType>GE</OpType><?char 13?> <ResultObjType>System.Boolean</ResultObjType><?char 13?> <ResultValue>True</ResultValue><?char 13?> <Count>2</Count><?char 13?>
<Attribute><?char 13?> <TypeClass>Unsupported</TypeClass><?char 13?> <Name>LastBackupDate</Name><?char 13?> <ResultObjType>System.DateTime
</ResultObjType><?char 13?> <ResultValue>633802554050000000</ResultValue><?char 13?> </Attribute><?char 13?> <Function><?char 13?> <TypeClass>
DateTime</TypeClass><?char 13?> <FunctionType>DateAdd</FunctionType><?char 13?> <ReturnType>DateTime</ReturnType><?char 13?> <ResultObjType>
System.DateTime</ResultObjType><?char 13?> <ResultValue>-8589569603042557603</ResultValue><?char 13?> <Count>3</Count><?char 13?> <Constant><?char 13?>
<TypeClass>String</TypeClass><?char 13?> <ObjType>System.String</ObjType><?char 13?> <Value>day</Value><?char 13?> </Constant><?char 13?> <Constant>
<?char 13?> <TypeClass>Numeric</TypeClass><?char 13?> <ObjType>System.Int32</ObjType><?char 13?> <Value>-1</Value><?char 13?> </Constant><?char 13?>
<Function><?char 13?> <TypeClass>DateTime</TypeClass><?char 13?> <FunctionType>GetDate</FunctionType><?char 13?> <ReturnType>DateTime</ReturnType>
<?char 13?> <ResultObjType>System.DateTime</ResultObjType><?char 13?> <ResultValue>-8589568739042557603</ResultValue><?char 13?> <Count>0</Count>
<?char 13?> </Function><?char 13?> </Function><?char 13?> </Operator></DMF:ResultDetail>

and create something useful to read, that would be great, but I can't find any reference about what to do with this XML evaluation result.




Jun 12, 2009 at 4:30 PM

Hello Jason.  I have given this a lot of thought and have tried to find a solution with no success.  The issue is that the results are stored in a format that is only parseable through the GUI (from what I have learned through conversations with the product group).  Until I can find a way to parse the results, I am not able to pull the details into scope for the solution.  I will continue to work with the necessary MS teams to see if these can be presented in a more universally consumable method.  Thanks for the feedback!

Jun 12, 2009 at 4:37 PM

BTW - in your example of Last Backup Date and determining which database failed - you will get the necessary level of detail in the framework.  The results are presented to the grain of a policy evaluated against an object on an instance on a date.  So you will see the granularity to the object level.  The granularity in your example is the database object - which shoudl present appropriately.

My response was directed toward each expression in a policy.  So, consider if your policy has multiple expressions against multiple properties, I cannot interrogate which property failed evaluation.  I can only display that the policy has failed.  For example, a policy for Surface Area Configuration may be checking for xp_cmdshell and clr integration settings.  The framework will not tell you which property failed evaluation, only that the policy failed due to one or both properties.  You would need to manually execute the policy through the GUI to determine which property had an issue. 

Jun 12, 2009 at 4:45 PM
Edited Jun 12, 2009 at 4:55 PM
Thanks for the reply. It does seem odd that the GUI allows for an export of the results, but once exported, you reach a dead end.
The results can't be parsed, can't be displayed in an HTML file, can't be emailed, can't be imported into a GUI viewer, and basically are useless.
I wonder why the "export results" button even exists on the GUI.
From what I can tell, in the scenario where you want to view the details, you can't even go to the policy.PolicyHistory (or Policy.PolicyHistoryDetail)
table and process the results. You actually have to manually, via the GUI, re-evaluate the policy if you want to dive the full depth into the details
(finding out if the last backup time was 2 days or 2 years ago, for example.).
To your other point- yes, I misspoke. The fact that database "Northwind" failed the policy is revealed, but you can't tell that the last backup was
6/11/2009 or 6/11/2004. There's no way to see the date the policy was actually evaluated against. A database backup may have been missed
yesterday or a database may have never been backed up; you can't tell. The GUI reveals this detail.
Thanks for looking into it!