March 28, 2010 at 3:00 am
Comments posted to this topic are about the item How to Evaluate Policies on Multiple Instances
Thanks
Jay
http://www.sqldbops.com
March 29, 2010 at 11:38 am
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
November 24, 2010 at 5:42 am
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"
June 26, 2012 at 8:38 am
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
end
-----------------------------------------------------------------
Cheers!
June 26, 2012 at 10:36 am
Cool... Thanks for sharing your work...
Thanks
Jay
http://www.sqldbops.com
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy