How to Evaluate Policies on Multiple Instances

  • Comments posted to this topic are about the item How to Evaluate Policies on Multiple Instances

  • Nice article. Thanks

    Jason...AKA CirqueDeSQLeil
    I have given a name to my pain...MCM SQL Server, MVP
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • I am getting below error. i am trying to generate report of policies with your code, but unable to debug below error.

    Exception calling ".ctor" with "1" argument(s): "failed to connect to server"

  • Thanks Jayakumar, your article helped us automate policy evaluation across 400 instances based on our central inventory table.

    We went a bit further with result detail parsing, so here's a couple of snippets in return.


    1. Expected/actual result values can be any combination of attributes, functions or constants. Here's how we parse the values for any combination:

    Res.Expr.value('*[6]/Name[1]', 'sysname') parameter,

    Res.Expr.value('*[6]/ResultObjType[1]', 'sysname') value_type,


    Res.Expr.value('*[7]/ResultValue[1]', 'sysname')-- for attributes / functions

    ,Res.Expr.value('*[7]/Value[1]', 'sysname')-- for constants

    ) expected_value,


    Res.Expr.value('*[6]/ResultValue[1]', 'sysname')-- for attributes / functions

    ,Res.Expr.value('*[6]/Value[1]', 'sysname')-- for constants

    ) actual_value


    2. Date values come back in ticks. Here's a function to convert them:

    create function fn_convert_xml_data_type


    used to convert the values in the xml returned by the policies


    System.DateTimethe value represents the number of ticks since the year 0 (that, the number of seconds multiplied by 10000000)

    sql datetime is represented by the number of days since 1-1-1900

    to convert, substract the number of ticks between 0 and 1-1-1900 (=599266080000000000)

    then divide by the number of ticks in a day (=864000000000)

    second format from the xml file counts down in ticks from the year 29246 approx (no idea why...)

    this second type is converted to the first type by subtracting year 0 expressed in an equivalent way: 9223372108849516425

    the operation is then the same as for the first type



    @value sysname,

    @value_type sysname


    returns sysname



    return case

    when @value='0' then @value

    when @value_type='System.DateTime' then



    (case when convert(bigint,@value)<-8000000000000000000 then convert(bigint,@value)+9223372108849516425 -- year 0-- inital constant: 9223371841699516425

    else convert(bigint,@value) end

    -599266080000000000) -- 1900-01-01

    /864000000000 -- number of ticks in a day


    ,120) -- 864000000000=nbr of ticks in a day, 599266080000000

    else @value





  • Cool... Thanks for sharing your work...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply