SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Evaluate Policies on Multiple Instances


How to Evaluate Policies on Multiple Instances

Author
Message
Jayakumar Krishnan
Jayakumar Krishnan
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1398 Visits: 809
Comments posted to this topic are about the item How to Evaluate Policies on Multiple Instances

Thanks
Jay
http://www.sqldbops.com
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65761 Visits: 18570
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

Jagdev
Jagdev
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 129
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
Richard BEALE
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 97
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
Jayakumar Krishnan
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1398 Visits: 809
Cool... Thanks for sharing your work...

Thanks
Jay
http://www.sqldbops.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search