Result Details: shredding the XML

Apr 27, 2010 at 7:43 PM

 Is there a documentation available on the XML format used for the policy evaluation Details (like the one msdb.dbo.syspolicy_policy_execution_history_details.result_detail or in EPM's policy.PolicyHistoryDetail.ResultDetail)?

It is good to know if a policy failed or passed on a certain object, but would also be useful to actually see what failed. I try to find my way around this XML but I still have problems. Documentation will be really useful, but I don't seem to find any... If stored in the system tables in msdb, the result_detail can be viewed in the Management Studio with red and green balls showing expected values and actual values. If stored in a custom table like policy.PolicyHistoryDetail.ResultDetail (as will be the case when evaluating policies as described in this EPM project) one needs a little bit of translation of the XML to be able to read where the problem was. So, I try to produce a table that will look similar with what Mangement Studion offers, but without some details on the XML is not easy...

For example, now I try to undertstand how the date/time information is represented in this XML.

This is a fragment from the XML that represents the evaluation details for a policy.

<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>DateTime</TypeClass>
<?char 13?>
<Name>LastBackupDate</Name>
<?char 13?>
<ResultObjType>System.DateTime</ResultObjType>
<?char 13?>
<ResultValue>634075356020000000</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>-8589302516236000408</ResultValue>
<?char 13?>
<Count>3</Count>
<?char 13?>
...

According to the GUI display provided by Management Studio, the value ResultValue in red above (-8589302516236000408) should be something like 04/16/2010 5:13:18 PM and the value in blue (634075356020000000) should be representing 01/01/0001 12:00 AM, which in fact correspond to a NULL value.
Any idea how these numbers can actually be converted to DATETIME in T-SQL?

Any help will be really appreciated.

Gabriela

Coordinator
Apr 29, 2010 at 2:57 AM

Hello Gabriela,

I wish I had better news for you, but I don't think this is documented.  I have asked the product team for help on this same question in the past, but was told that the logic to interpret the details was written in the gui. (SSMS)  If I remember correctly, dissecting the XML is even more challenging when the condition had multiple expressions (I am completely going off some very old memories, though - it has been a long time since I tried to attack this issue).  In the end, I had to put aside writing reports against this data due to the lack of documentation.  Perhaps I can try to look at it again in a few weeks. 

May 30, 2013 at 8:05 PM
I'm looking to do the exact same thing. Being able to easily see both the expected and existing values would be extremely helpful.
Oct 7, 2014 at 5:11 PM
lararubbelke wrote:
Hello Gabriela, I wish I had better news for you, but I don't think this is documented.  I have asked the product team for help on this same question in the past, but was told that the logic to interpret the details was written in the gui. (SSMS)  If I remember correctly, dissecting the XML is even more challenging when the condition had multiple expressions (I am completely going off some very old memories, though - it has been a long time since I tried to attack this issue).  In the end, I had to put aside writing reports against this data due to the lack of documentation.  Perhaps I can try to look at it again in a few weeks. 
Have you had any luck with this? My problem is that when something fails, I can't even go to my central management server to see a GUI like detailed problem, but I have to go the server in question and then run the policy to see the specific problem. This has two problems:
  1. It doesn't allow the report to be all that it could be.
  2. I have to go through the SSMS GUI in order to evaluate the policy to see the detail.
I understand and can appreciate your perspective, but we need a solution. Any luck?
Coordinator
Oct 9, 2014 at 2:25 PM
Sorry but I don't have an update on my end. There are two other from Microsoft who are building and managing this project, so perhaps they have a creative solution.
Oct 30, 2014 at 7:48 PM
Have you guys ever looked at populating the system tables that store policy information? That way we can use SSMS to show us the visual rather than re-run the policy to get the visual?