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

'not in' condition in Policy Based Expand / Collapse
Author
Message
Posted Wednesday, October 5, 2011 4:04 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, September 1, 2014 3:40 AM
Points: 68, Visits: 285
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


  Post Attachments 
PBM Condition Question - ScreenShot.pdf (13 views, 121.35 KB)
Post #1185749
Posted Wednesday, October 5, 2011 4:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 5:00 AM
Points: 150, Visits: 3,164
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...
Post #1185763
Posted Wednesday, October 5, 2011 6:35 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, September 1, 2014 3:40 AM
Points: 68, Visits: 285
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.
Post #1185829
Posted Thursday, January 10, 2013 5:25 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 5:27 AM
Points: 369, Visits: 1,214
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
Post #1405360
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse