How to Evaluate Policies on Multiple Instances

  • Jayakumar Krishnan

    SSCarpal Tunnel

    Points: 4710

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

  • SQLRNNR

    SSC Guru

    Points: 281210

    Nice article. Thanks

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

  • Jagdev

    Mr or Mrs. 500

    Points: 552

    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"

  • Richard BEALE

    SSC Rookie

    Points: 37

    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,

    coalesce(

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

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

    ) expected_value,

    coalesce(

    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

    calulations:

    System.DateTime the 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

    as

    begin

    return case

    when @value='0' then @value

    when @value_type='System.DateTime' then

    convert(varchar,

    convert(datetime,

    (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

    end

    end

    -----------------------------------------------------------------

    Cheers!

  • Jayakumar Krishnan

    SSCarpal Tunnel

    Points: 4710

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

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

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