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


'not in' condition in Policy Based


'not in' condition in Policy Based

Author
Message
PAH-440118
PAH-440118
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 367
Using Policy Based Management, I am creating a policy to fine databases which have not had a FULL or Incremental Backup in the last 24hrs. This works OK but I need to ad a condition to the targets so that some databases (temporary copies etc) can be excluded.
I can do this for a single database setting a condition with the expression: @name != '<DatabaseName>', however I want to add a series of databases to this. I thought the way to do this would be to use the 'NOT IN' operator, However I can not format the Value field to accept multiple values
(Screenshots attached)
Please can anyone provide an example of how to format this
Attachments
SQLArcher
SQLArcher
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 3166
Hi,

You can utilize the following steps:

1. Create your conditions which you want to evaluate.
2. Create a second condition that makes use of the "Database" facet. In this condition, you should use @Name and the databases you want to exclude the databases.
3. Create your policy, you will see a drop down for databases which says "every". Drop this down and select your condition to do the exclusion of the names.


Execute the below script to see an example.

Declare @object_set_id int
EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N'test_ObjectSet_1', @facet=N'IDatabaseMaintenanceFacet', @object_set_id=@object_set_id OUTPUT
Select @object_set_id

Declare @target_set_id int
EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'test_ObjectSet_1', @type_skeleton=N'Server/Database', @type=N'DATABASE', @enabled=True, @target_set_id=@target_set_id OUTPUT
Select @target_set_id

EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'db_name', @target_set_level_id=0

GO

Declare @policy_id int
EXEC msdb.dbo.sp_syspolicy_add_policy @name=N'test', @condition_name=N'backup date', @policy_category=N'', @description=N'', @help_text=N'', @help_link=N'', @schedule_uid=N'00000000-0000-0000-0000-000000000000', @execution_mode=0, @is_enabled=False, @policy_id=@policy_id OUTPUT, @root_condition_name=N'', @object_set=N'test_ObjectSet_1'
Select @policy_id

GO




Unfortunately you have to specify multiple databases, as the check target does not seem to support the Array function which the NOT IN operator supports.

Don't hesitate to ask any other questions.

If you are interested in Policy Based Management, you can get a book called Pro: SQL Server 2008 Policy Based Management written by Ken Simmons.

Regards...
PAH-440118
PAH-440118
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 367
Thanks for your response, unfortunately I have only worked through the GUI so don't understand much of what is in your scripts as they don't appear to match what is created by the 'script' button within the GUI.
I do have two 'conditions', one for the test I wish to make and one for the databases I wish to exclude. These are referenced in a Policy as you describe. The problem I have is the 'Condition' for the databases I wish to exclude. Currently I have this set to use a 'not like' clause for a single string. When I script this it shows
Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'Exclude databases', @description=N'', @facet=N'Database', @expression=N'<Operator>
<TypeClass>Bool</TypeClass>
<OpType>NOT_LIKE</OpType>
<Count>2</Count>
<Attribute>
<TypeClass>String</TypeClass>
<Name>Name</Name>
</Attribute>
<Constant>
<TypeClass>String</TypeClass>
<ObjType>System.String</ObjType>
<Value>%AuditLog2%</Value>
</Constant>
</Operator>', @is_name_condition=4, @obj_name=N'%AuditLog2%', @condition_id=@condition_id OUTPUT
Select @condition_id

GO



I can not make out how to change this for a 'NOT IN' condition with an array of database names, which is what I am trying to achieve.
Vedran Kesegic
Vedran Kesegic
Say Hey Kid
Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)

Group: General Forum Members
Points: 712 Visits: 1266
Use Array function in the Value filed of the condition.
Eg:

Array("master", "model", "msdb")

That works for me.

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

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