Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to Evaluate Policies on Multiple Instances Expand / Collapse
Author
Message
Posted Sunday, March 28, 2010 3:00 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, September 13, 2013 6:31 PM
Points: 415, Visits: 749
Comments posted to this topic are about the item How to Evaluate Policies on Multiple Instances

Thanks
Jay
http://www.sqldbops.com
Post #891420
Posted Monday, March 29, 2010 11:38 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:30 PM
Points: 20,467, Visits: 14,104
Nice article. Thanks



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #892066
Posted Wednesday, November 24, 2010 5:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 02, 2014 12:30 AM
Points: 6, Visits: 106
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"
Post #1025812
Posted Tuesday, June 26, 2012 8:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 04, 2013 9:46 AM
Points: 1, Visits: 68
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!

Post #1321280
Posted Tuesday, June 26, 2012 10:36 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, September 13, 2013 6:31 PM
Points: 415, Visits: 749
Cool... Thanks for sharing your work...

Thanks
Jay
http://www.sqldbops.com
Post #1321392
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse