How to Evaluate Policies on Multiple Instances

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jayakumar Krishnan » How to Evaluate Policies on Multiple...

How to Evaluate Policies on Multiple Instances

Rate Topic

Display Mode

Topic Options

Author

Message

Jayakumar Krishnan

Posted Sunday, March 28, 2010 3:00 AM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 09, 2013 11:58 AM
Points: 415, Visits: 737

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

Thanks
Jay
http://www.sqldbops.com

Post #891420

SQLRNNR

Posted Monday, March 29, 2010 11:38 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 18,733, Visits: 12,332

Nice article. Thanks

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

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

Jagdev

Posted Wednesday, November 24, 2010 5:42 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, March 28, 2013 7:00 AM
Points: 5, Visits: 99

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

Richard BEALE

Posted Tuesday, June 26, 2012 8:38 AM

Forum 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

Jayakumar Krishnan

Posted Tuesday, June 26, 2012 10:36 AM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 09, 2013 11:58 AM
Points: 415, Visits: 737

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

Thanks
Jay
http://www.sqldbops.com

Post #1321392

« Prev Topic | Next Topic »

Permissions