Chat now with support
Chat with Support

Identity Manager 9.2.1 - Configuration Guide

About this guide One Identity Manager software architecture Customizing the One Identity Manager default configuration Customizing the One Identity Manager base configuration One Identity Manager schema basics
Overview of the One Identity Manager schema Table types and default columns in the One Identity Manager data model Notes on editing table definitions and column definitions Table definitions Column definitions Table relations Dynamic foreign key Supporting file groups
Editing the user interface
Object definitions for the user interface User interface navigation Forms for the user interface Statistics in One Identity Manager Extending the Launchpad Task definitions for the user interface Applications for configuring the user interface Icons and images for configuring the user interface Using predefined database queries
Localization in One Identity Manager Process orchestration in One Identity Manager
Mapping processes in One Identity Manager Setting up Job servers
The One Identity Manager Service functionality Tracking changes with process monitoring Conditional compilation using preprocessor conditions Scripts in One Identity Manager
Visual Basic .NET scripts usage Notes on message output Notes on using date values Tips for using PowerShell scripts Using dollar ($) notation Using base objects Calling functions Pre-scripts for use in processes and process steps Using session services Using #LD notation Script library Support for processing scripts in the Script Editor Creating and editing scripts in the Script Editor Copying scripts in the Script Editor Testing scripts in the Script Editor Testing script compilation in the Script Editor Overriding scripts Permissions for running scripts Editing and testing script code with the System Debugger Extended debugging in the Object Browser
One Identity Manager query language Reports in One Identity Manager Adding custom tables or columns to the One Identity Manager schema Web service integration One Identity Manager as SCIM 2.0 service provider Processing DBQueue tasks One Identity Manager Service configuration files

Comparing columns

Columns can be compared against another set of targets. In the One Identity Manager query language, all these comparisons start with the column name.

WHERE <Column> <Operator> <Operand>

The following operators are supported when comparing columns.

Operator Program Type of operand
= Equal Value that matches the column type, the parameter, or another column
<> Not equal Value that matches the column type, the parameter, or another column
< Less than Value that matches the column type, the parameter, or another column
> Greater than Value that matches the column type, the parameter, or another column
<= Less than or equal to Value that matches the column type, the parameter, or another column
>= Greater than or equal to Value that matches the column type, the parameter, or another column
LIKE Match with a specified pattern (as in SQL operator like) String or multivalued property
NOT LIKE No match with a given pattern String or multivalued property
STARTSWITH String starts with String or multivalued property
ENDSWITH String ends with String or multivalued property
CONTAINS String contains String or multivalued property
BITSSET The given bit positions are set Integer value or parameter
BITSNOTSET The given bit positions are not set Integer value or parameter
Examples of column comparisons

WHERE Lastname = 'Einstein'

WHERE XDateInserted > 2020-02-01

WHERE Lastname STARTSWITH 'Ein'

WHERE XMarkedForDeletion BITSSET 2

Comparison by means of IN and NOT IN clauses

In the One Identity Manager query language, comparisons with the keywords IN and NOT IN enable the comparison of a column with a set of values.

The values can be given

  • In list form

  • As a multivalued string, delimited by an ASCII 7 character

  • As a parameter containing a multivalued string delimited by an ASCII-7 character

Value lists

WHERE <Column> IN ( <Value>, <Value>, ...)

WHERE <Column> NOT IN ( <Value>, <Value>, ...)

All values must be of the same type and must be convertible to the column type.

Examples of value lists

WHERE StringColumn IN ('Value 1', 'Value 2', 'Value 3')

WHERE StringColumn NOT IN ('Value 1', 'Value 2', 'Value 3')

Multivalued string

WHERE <Column> IN 'Separated string value'

WHERE <Column> NOT IN 'Separated string value'

Example for multivalued strings

WHERE Lastname IN 'User1 User2 User3'

Parameters

Multivalued strings can be passed to queries by parameters.

WHERE <Column> IN @Parameter

Parameter examples

WHERE Lastname IN @Lastnames

You can also use MVP syntax. The normal IN clause syntax should be preferred in this case. For more information, see String values.

Compare date differences

A special case of column comparisons in the One Identity Manager query language are date difference comparisons. These compare the value of a column with a time range based on the current time.

WHERE <Column> <Operator> DATE <Integer> <Unit> AGO

Time unit Keyword
Years YEARS YEAR Y
Months MONTHS MONTH M
Weeks WEEKS WEEK
Days DAYS DAY D
Hours HOURS HOUR H
Minutes MINUTES MINUTE MIN
Seconds SECONDS SECOND S
Milliseconds MILLISECONDS MILLISECOND MS
Examples

WHERE XDateInserted < DATE 3 MONTHS AGO

WHERE XDateInserted > DATE 5 MIN AGO

Compare date ranges

Comparisons of date ranges in the One Identity Manager query language check whether a date falls within a given date range.

Syntax for named ranges

WHERE <Column> IN RANGE <Range name> [ TIMEZONE 'Timezone ID'] [CULTURE 'Language code ID']

Syntax for a specific time range

WHERE <Column> IN RANGE <Start time> TO <End time> [TIMEZONE <Timezone ID>]

Area start time is included, end time is excluded.

Permitted identifiers are:

TODAY

YESTERDAY

THIS WEEK

THIS MONTH

THIS YEAR

LAST WEEK

LAST MONTH

LAST YEAR

LAST <integer> DAYS

LAST <integer> DAY

The names of the time zones must match the identifiers on the system running the query. If no time zone is given, UTC is used.

The language code indicates what day starts the week. It is only useful if one of the week ranges is specified.

Examples

WHERE XDateInserted IN RANGE YESTERDAY

WHERE XDateInserted IN RANGE YESTERDAY

TIMEZONE 'W. Europe Standard Time'

WHERE XDateInserted IN RANGE LAST YEAR

WHERE XDateInserted IN RANGE THIS WEEK

TIMEZONE 'W. Europe Standard Time'

CULTURE 'de-DE'

WHERE XDateInserted IN RANGE 2020-01-01 TO 2020-02-01

TIMEZONE 'W. Europe Standard Time'

Related topics
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating