Advanced Edit (Condition) Dialog Box

  • Article

Use the Advanced Edit dialog box to create complex expressions for Policy-Based Management conditions.

Options

  • Cell value
    Displays the function or expression that will be used for the cell value as you create it. When you click OK, the cell value will appear in the Field or Value cell in the condition expression box of the Create New Condition or the Open Condition dialog box on the General page.

  • Functions and properties
    Displays the available functions and properties.

  • Details
    Displays the information about the functions and properties, in the format: function signature, function description, return value, and example.

Syntax

Valid expressions must be in the following format:

{property | function | constant}

{operator}

{property | function | constant}

Examples

Some examples of valid expressions are as follows:

  • Property1 > 5

  • Property1 = Property2

  • Add(5, Multiply(.2, Property1)) < Property2

  • Sometext IN Property1

  • Property1 < Fn(Property2)

  • BitwiseAnd(Property1, Property2) = 0

Additional Function Information

The following sections provide additional information about the functions you can use to create complex expressions for Policy-Based Management conditions.

Important

The functions that you can use to create Policy-Based Management conditions do not always use Transact-SQL syntax. Make sure that you follow the example syntax. For example, when you use the DateAdd or DatePart functions, you must enclose the datepart argument in single quotes.

Function

Description

Add()

Function signature: Numeric Add (Numeric expression1, Numeric expression2)

Function description: Adds two numbers.

Arguments:

  • expression1 and expression2 - Is any valid expression of any one of the data types in the numeric category, except the bit data type. Can be a constant, property, or function that returns a numeric type.

Return value: Returns the data type of the argument that has the greater precedence.

Example: Add(Property1, 5)

Array()

Function signature: Array Array (VarArgs expression)

Function description: Creates an array from a list of values. Can be used with aggregate functions such as Sum() and Count().

Arguments:

  • expression - Is an expression that will be converted to an array.

Return value: The array.

Example: Array(2,3,4,5,6)

Avg()

Function signature: Numeric Avg (VarArgs)

Function description: Returns the average of the values in the argument list.

Arguments:

  • VarArgs - Is list of Variant expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Return value: The return type is determined by the type of the evaluated result of expression.

If the expression result is integer, decimal, money and smallmoney, float and real category, the return types are int, decimal, money, and float; respectively.

Example: Avg(1.0, 2.0, 3.0, 4.0, 5.0) returns 3.0 in this example.

BitwiseAnd()

Function signature: Numeric BitwiseAnd (Numeric expression 1, Numeric expression2)

Function description: Performs a bitwise logical AND operation between two integer values.

Arguments:

  • expression1 and expression2 - Is any valid expression of any one of the data types of the integer data type category.

Return value: Returns a value of integer data type category.

Example: BitwiseAnd(Property1, Property2)

BitwiseOr()

Function signature: Numeric BitwiseOr (Numeric expression1, Numeric expression2)

Function description: Performs a bitwise logical OR operation between two specified integer values.

Arguments:

  • expression1 and expression2 - Is any valid expression of any one of the data types of the integer data type category.

Return value: Returns a value of integer data type category.

Example: BitwiseOr(Property1, Property2)

Concatenate()

Function signature: String Concatenate (String string1, String string2)

Function description: Concatenates two strings.

Arguments:

  • string1 and string2 - Are the two strings that you want to concatenate. Can be any valid non-null string.

Return value: The concatenated string, with string1 followed by string2.

Example: Concatenate("Hello", " World") returns "Hello World".

Count()

Function signature: Numeric Count (VarArgs)

Function description: Returns the number of items in the argument list.

Arguments:

  • VarArgs - Is an expression of any type except text, image, and ntext.

Return value: Returns a value of integer data type category.

Example: Count(1.0, 2.0, 3.0, 4.0, 5.0) returns 5 in this example.

DateAdd()

Function signature: DateTime DateAdd (String datepart, Numeric number, DateTime date)

Function description: Returns a new datetime value that is based on adding an interval to the specified date.

Arguments:

  • datepart - Is the parameter that specifies on which part of the date to return a new value. Some of the supported types are year(yy, yyyy), month(mm, m)and dayofyear(dy, y). For more information, see DATEADD (Transact-SQL).

  • number - Is the value that is used to increment datepart.

  • date - Is an expression that returns a datetime value, or a character string in a date format.

Return value: Is the new datetime value that is based on adding an interval to the specified date.

Example: DateAdd('day', 21, DateTime('2007-08-06 14:21:50')) returns '2007-08-27 14:21:50' in this example.

The following table lists the dateparts and abbreviations that are supported by this function.

Datepart

Abbreviations

year

yy, yyyy

month

mm, m

dayofyear

dy, y

day

dd, d

week

wk, ww

weekday

dw, w

hour

hh

minute

mi, n

second

ss, s

millisecond

ms

DatePart()

Function signature: Numeric DatePart (String datepart, DateTime date)

Function description: Returns an integer that represents the specified datepart of the specified date.

Arguments:

  • datepart - Is the parameter that specifies the part of the date to return. Some of the supported types are year(yy, yyyy), month (mm, m)and dayofyear(dy, y). For more information, see DATEPART (Transact-SQL).

  • date - Is an expression that returns a datetime value, or a character string in a date format.

Return value: Returns value of integer data type category that represents the specified datepart of the specified date.

Example: DatePart('month', DateTime('2007-08-06 14:21:50.620')) returns 8 in this example.

DateTime()

Function signature: DateTime DateTime (String dateString)

Function description: Creates a datetime value from a string.

Arguments:

  • dateString - Is the datetime value as a string.

Return value: Returns a datatime value created from the input string.

Example: DateTime('3/12/2006')

Divide()

Function signature: Numeric Divide (Numeric expression_dividend, Numeric expression_divisor)

Function description: Divides one number by another.

Arguments:

  • expression_dividend - Is the numeric expression to divide. The dividend can be any valid expression of any one of the data types of the numeric data type category, except the datetime data type.

  • expression_divisor - Is the numeric expression by which to divide the dividend. The divisor can be any valid expression of any one of the data types of the numeric data type category, except the datetime data type.

Return value: Returns the data type of the argument that has the greater precedence.

Example: Divide(Property1, 2)

Note

This will be a double operation. To do an integer compare, you must combine the results with Round(). For example: Round(Divide(10, 3), 0) = 3.

Enum()

Function signature: Numeric Enum (String enumTypeName, String enumValueName)

Function description: Creates an enum value from a string.

Arguments:

  • enumTypeName - Is the name of the enum type.

  • enumValueName - Is the value of the enum.

Return value: Returns the enum value as a numeric value.

Example: Enum('CompatibilityLevel','Version100')

Escape()

Function signature: String Escape (String replaceString, String stringToEscape, String escapeString)

Function description: Escapes a substring of the input string with a given escape string.

Arguments:

  • replaceString – Is the input string.

  • stringToEscape – Is a substring of replaceString. This is the string that you want to add an escape string in front of.

  • escapeString – Is the escape string that you want to add in front of each instance of stringToEscape.

Return value: Returns a modified replaceString in which each instance of stringToEscape is preceded by escapeString.

Example: Escape("Hello", "l", "[") returns "He[l[lo".

ExecuteSQL()

Function signature: Variant ExecuteSQL (String returnType, String sqlQuery)

Function description: Executes the Transact-SQL query against the target server.

Arguments:

  • returnType - Specifies the return type of data returned by the Transact-SQL statement. The valid literals for returnType are as follows: Numeric, String, Bool, DateTime, Array, and Guid.

  • sqlQuery - Is the string that contains query to be executed.

Example: ExecuteSQL ('Numeric', 'SELECT COUNT(*) FROM msdb.dbo.sysjobs') <> 0

Runs a scalar-valued Transact-SQL query against a target instance of SQL Server. Only one column can be specified in a SELECT statement; additional columns beyond the first are ignored. The resulting query should return only one row; additional rows beyond the first are ignored. If the query returns an empty set, then the condition expression built around ExecuteSQL will evaluate to false. ExecuteSql supports the On demand and On schedule evaluation modes.

  • @@ObjectName - Corresponds to the name field in sys.objects. The variable will be replaced with the name of the current object.

  • @@ObjectSchema - Corresponds to the name field in sys.schemas. The variable will be replaced with the name of the schema for the current object, if applicable.

Note

To include a single quotation mark in an ExecuteSQL statement, escape the single quotation mark with a second single quotation mark. For example, to include a reference to a user named O'Brian, type O''Brian.

ExecuteWQL()

Function signature: Variant ExecuteWQL (string returnType , string namespace, string wql)

Function description: Executes the WQL script against the namespace that is provided. Select statement can contain only a single return column. If more than one column is provided, error will be thrown.

Arguments and return type definition

  • returnType - Specifies the return type of data that is returned by the WQL. The valid literals are Numeric, String, Bool, DateTime, Array, and Guid.

  • namespace - Is the WMI Namespace to execute against.

  • wql - Is the string that contains the WQL to be executed.

Example: ExecuteWQL('Numeric', 'root\CIMV2', 'select NumberOfProcessors from win32_ComputerSystem') <> 0

False()

Function signature: Bool False()

Function description: Returns the Boolean value FALSE.

Return value: Returns the Boolean value FALSE.

Example: IsDatabaseMailEnabled = False()

GetDate()

Function signature: DateTime GetDate()

Function description: Returns the system date.

Return value: Returns the system date as DateTime.

Example: @DateLastModified = GetDate()

Guid()

Function signature: Guid Guid(String guidString)

Function description: Returns a GUID from a string.

Arguments:

  • guidString - Is the string representation of the GUID to be created.

Return value: Returns the GUID created from the string.

Example: Guid('12340000-0000-3455-0000-000000000454')

IsNull()

Function signature: Variant IsNull (Variant check_expression, Variant replacement_value)

Function description: The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned. If the types are different, replacement_value is implicitly converted to the type of check_expression.

Arguments:

  • check_expression - Is the expression to be checked for NULL. check_expression can be of any Policy-Based Management supported types: Numeric, String, Bool, DateTime, Array, and Guid.

  • replacement_value - Is the expression to be returned if check_expression is NULL. replacement_value must be of a type that is implicitly converted to the type of check_expression.

Return value: The return type is type of check_expression if check_expression is not NULL; otherwise, the type of replacement_value is returned.

Len()

Function signature: Numeric Len (string_expression)

Function description: Returns the number of characters, of the given string expression, excluding trailing blanks.

Arguments:

  • string_expression - Is the string expression to be evaluated.

Return value: Returns a value of integer data type category.

Example: Len('Hello') returns 5 in this example.

Lower()

Function signature: String Lower (String_expression)

Function description: Returns the string after converting all uppercase characters to lower case.

Arguments:

  • expression - Is the source string expression.

Return value: Returns a string that represents the source string expression after all uppercase characters are converted to lowercase.

Example: Len('HeLlO') returns 'hello' in this example.

Mod()

Function signature: Numeric Mod (Numeric expression_dividend, Numeric expression_divisor)

Function description: Provides the integer remainder after dividing the first numeric expression by the second numeric expression.

Arguments:

  • expression_dividend - Is the numeric expression to divide. expression_dividend must be a valid expression of any one of the data types in the integer or the numeric data type categories.

  • expression_divisor - Is the numeric expression to divide the dividend by. expression_divisor must be any valid expression of any one of the data types in the integer or the numeric data type categories.

Return value: Returns a value of integer data type category.

Example: Mod(Property1, 3)

Multiply()

Function signature: Numeric Multiply (Numeric expression1, Numeric expression2)

Function description: Multiplies two expressions.

Arguments:

  • expression1 and expression2 - Is any valid expression of any one of the data types in the numeric category, except the datetime data type.

Return value: Returns the data type of the argument that has the greater precedence.

Example: Multiply(Property1, .20)

Power()

Function signature: Numeric Power (Numeric numeric_expression, Numeric expression_power)

Function description: Returns the value of the specified expression to the specified power.

Arguments:

  • numeric_expression - Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

  • expression_power - Is the power to which to raise numeric_expression. expression_power can be an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Return value: Return type is same as numeric_expression.

Example: Power(Property1, 3)

Round()

Function signature: Numeric Round (Numeric expression, Numeric expression_precision)

Function description: Returns a numeric expression that is rounded to the specified length or precision.

Arguments:

  • expression - Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

  • expression_precision - Is the precision to which expression is to be rounded. When expression_precision is a positive number, numeric_expression is rounded to the number of decimal positions that is specified by length. When expression_precision is a negative number, numeric_expression is rounded on the left side of the decimal point, as specified by expression_precision.

Return value: Returns the same type as numeric_expression.

Example: Round(5.333, 0)

String()

Function signature: String String (Variant_expression)

Function description: Converts a variant to a string.

Arguments:

  • expression - Is the variant expression to be converted to a string.

Return value: Returns the string value of the variant expression.

Example: String(4)

Sum()

Function signature: Numeric Sum (VarArgs)

Function description: Returns the sum of all the values in the argument list. Sum can be used with numeric values.

Arguments:

  • VarArgs- Is a list of Variant expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Return value: Returns the summation of all expression values in the most precise expression data type.

If the expression result is integer, numeric, money and small money, float and real category, the return types are int, numeric, money, and float; respectively.

Example: Sum(1.0, 2.0, 3.0, 4.0, 5.0) returns 15 in this example.

True()

Function signature: Bool TRUE()

Function description: Returns the Boolean value TRUE.

Return value: Returns the Boolean value TRUE.

Example: IsDatabaseMailEnabled = True()

Upper()

Function signature: String Upper (String_expression)

Function description: Returns the string after converting all lowercase characters to uppercase.

Arguments:

  • expression - Is the source string expression.

Return value: Returns a string that represents the source string expression after all lowercase characters are converted to uppercase.

Example: Len('HeLlO') returns 'HELLO' in this example.

See Also

Reference

Create New Condition or Open Condition Dialog Box, General Page

Concepts

Administer Servers by Using Policy-Based Management