How to Evaluate Policies on Multiple Instances

Question

How to Evaluate Policies on Multiple Instances

Jayakumar Krishnan

SSCarpal Tunnel

Points: 4710
More actions
March 28, 2010 at 3:00 am

#386588

Comments posted to this topic are about the item How to Evaluate Policies on Multiple Instances
Thanks
Jay
http://www.sqldbops.com

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

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.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

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

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

Cheers!

Jayakumar Krishnan SSCarpal Tunnel Points: 4710 More actions · Answer 4

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

Thanks
Jay
http://www.sqldbops.com