Manage Lookups

Using Oracle Cloud Logging Analytics, you can enrich event data by adding field-value combinations from lookups. Oracle Cloud Logging Analytics uses lookups to match field-value combinations from events to an external lookup table, and if matched, Oracle Cloud Logging Analytics appends the field-value combinations to the events.

Types of lookups:
  • Simple: This table would contain a simple listing of items where the rows correspond to a single relation. For example, if you create a lookup table of errors, then each row can have the information for a single error like error ID, error message, cause, corrective action. See Create a Simple Lookup.
  • Dictionary: This is a smarter option to enlist the logical relations between fields and their values using operators like CONTAINS, CONTAINS IGNORE CASE MULTILINE REGEX, and EQUAL. For an exhaustive list of operators, examples, and steps to create this type of lookup, see Create a Dictionary Lookup.
Following are the Oracle-defined simple lookup tables currently available in Oracle Cloud Logging Analytics:
  • BEA Error Messages
  • CRS Error Messages
  • OGC Error Messages
  • ORA Error Messages
  • TNS Error Messages

How Do Lookups Enrich Logs

Often, the logs would not contain all the required information to consolidate the analysis. In such cases, you can create a lookup table to pickup the information that you have already collated to make more sense of the data in the logs. For example, the Error ID field in log events doesn’t provide a description of the errors. You can create a lookup that maps Error ID to descriptions, and then use the Field Enrichment options to make the descriptions available to search in the log records. Some example scenarios where you could create lookups:

  • Product inventory: Product ID, technical specifications, price, available stock, rack number
  • Employee information: Employee ID, Personal information, organization, role, scale, salary, manager
  • Library reference: Tile, author, summary, edition, previous editions, price

How to Use Lookups

In the following lookup query, the error message is picked up from the lookup table ORA Error Messages by mapping the Error ID field, and displayed in a summary table along with other fields like log source and entity:

'Error ID' like 'ORA%' | lookup table = 'ORA Error Messages' select errmsg using 'Error ID' = errid | fields -*, 'Log Source', Entity, errmg
  • Information from the logs:
    • Error ID: Error ID as mentioned in the logs specified in the form ORA%
    • Log Source: The log source of the logs
    • Entity: The entity of the logs
  • Information from the Oracle-defined lookup table ORA Error Messages:
    • errid: The error ID specified in the form ORA%
    • errmsg: The error message for the error ID

    The other details available in the ORA Error Messages lookup table are errcause (Error Cause) and erraction (Error Action) for each error ID. To view the contents of the lookup table, use the searchlookup command as follows:

    * | searchlookup table = 'ORA Error Messages'

View the Lookup Detail

  1. Open the navigation menu and click Observability & Management. Under Logging Analytics, click Administration. The Administration Overview page opens.

  2. The administration resources are listed in the left hand navigation pane under Resources. Click Lookups.

  3. Under Lookups, click on the lookup whose details you want to view.

    The Lookup Detail page displays the details that you had provided while creating it, and also the content of the lookup table.

Update an Existing Lookup

  1. Update your CSV file with the field-value combinations.

  2. Open the navigation menu and click Observability & Management. Under Logging Analytics, click Administration. The Administration Overview page opens.

  3. The administration resources are listed in the left hand navigation pane under Resources. Click Lookups.

  4. Under Lookups, click the Actions Actions menu icon in the row that corresponds to the lookup that you want to update, click Reupload File.

    The Reupload File dialog box opens.

  5. Select the updated lookup CSV file that you had created earlier, and click Upload.

Create a Simple Lookup

After creating the simple lookup, run a query with the searchlookup command to list the values in the lookup, and use the lookup command in a query to map the output fields to those values.

  1. Create a lookup CSV file with the field-value combinations. For example, to create a lookup that maps Error ID to descriptions:

    errid,description
    02323,Network Not Reachable
    09912,User Activity
    12322,Out of Memory

    Note that the first row is the header with errid and description titles for the values in the subsequent rows.

  2. Open the navigation menu and click Observability & Management. Under Logging Analytics, click Administration. The Administration Overview page opens.

  3. The administration resources are listed in the left hand navigation pane under Resources. Click Lookups.

  4. Under Lookups, click Create Lookup.

  5. In the Lookup page, enter the name of the lookup, such as server error code lookups and an optional description.

  6. Under Type, select Simple.

  7. Select the lookup CSV file that you had created earlier, and click Create.

Create a Dictionary Lookup

After creating the Dictionary type lookup, use searchlookup command to list the lookups. Use the lookup command to map to the fields with any query only after using the link or cluster commands in the query.

  1. Create a lookup CSV file with the field-value combinations.

    For example:

    Operator,Condition,Issue,Area
    CONTAINS,message header or abbreviation processing failed,Processing Error,Messaging
    CONTAINS,Failed to associate the transaction context with the response while marshalling,Marshalling Error,Response
    CONTAINS,A RuntimeException was generated by the RMI server,Exception,RMI

    Note that the first row is the header where Operator and Condition are the mandatory fields specified in the same order. The subsequent parameters are listed in the header row after the mandatory parameters. The subsequent rows are the values of the parameters listed in the header row in the same order.

    See the table below for the list of valid operators and examples to use them.

    Note:

    • If a field contains a comma, enclose the entire field in double quotes.

    • If a field contains double quotes, escape the double quote by using two double quotes.

  2. Open the navigation menu and click Observability & Management. Under Logging Analytics, click Administration. The Administration Overview page opens.

  3. The administration resources are listed in the left hand navigation pane under Resources. Click Lookups.

  4. Under Lookups, click the Create Lookup link.

  5. In the Create Lookup page, enter the name of the lookup, such as cluster dictionary lookups and an optional description.

  6. Under Type, select Dictionary.

  7. Select the lookup CSV file that you had created earlier, and click Create.

For examples of using dictionary lookup in Cluster and Link, see Use Dictionary Lookup in Cluster and Use Dictionary Lookup in Link.

CIDRMATCH Operator

The CIDRMATCH operator supports CIDR (Classless Inter-Domain Routing) match operation rule inside a dictionary lookup. For example, the following dictionary returns Network Name as Database Network if the input IP Address falls in the range between 192.0.2.0 and 192.0.2.255:

Operator,Condition,Network Name
CIDRMATCH,192.0.2.10/24,Database Network

List of Valid Operators and Examples for Using Them

Operator Description Example

CONTAINS

True if the value contains the string specified in the Condition field. Case-sensitive

CONTAINS,Request 'GetResponse' Timed out,Timeout Error

CONTAINS IGNORE CASE

Same as above, except the case is ignored

CONTAINS IGNORE CASE,request 'getresponse' timed out,Timeout Error

CONTAINS REGEX

True if the value matches the specified regular expression

CONTAINS REGEX,Request '\S+' Timed out,Timeout Error

CONTAINS IGNORE CASE REGEX

Same as above, but ignores the case

CONTAINS IGNORE CASE REGEX,request '\S+' timed out,Timeout Error

CONTAINS MULTILINE REGEX

Use this to match against a multi-line string

CONTAINS MULTILINE REGEX,Request 'GetResponse' Timed out,Timeout Error

CONTAINS IGNORE CASE MULTILINE REGEX

Same as above, except the case is ignored

CONTAINS IGNORE CASE MULTILINE REGEX,Request 'GetResponse' Timed out,Timeout Error

CONTAINS ONE OF REGEXES

Specify more than one regular expression. True if at least one matches.

List the regular expressions inside [] and separate by comma. The regular expressions cannot contain a comma.

If you need to use double quotes inside the regex, escape each double quote using another double quote.

CONTAINS ONE OF REGEXES,"[Request '\S+' Timed out,Server\S+Timed out]",Timeout Error

NOT CONTAINS

Does not contain the specified string

NOT CONTAINS,Request 'GetResponse' Timed out,Success

EQUAL

Content equals the specified value

EQUAL,500,HTTP Server Error

EQUAL IGNORE CASE

Same as above, except the case is ignored

EQUAL,In-Progress,Request In Progress

NOT EQUAL

True if the content is not equal to the value specified

NOT EQUAL,200,HTTP Request Failed

STARTS WITH

Compares to the beginning of the content

STARTS WITH,Request failed with,Fail

ENDS WITH

Compares to the end of the content

ENDS WITH,timed out,Timeout

IN

True if at least one of the value is equal

IN,"[500,501,502,503]",HTTP Server Error

IN IGNORE CASE

Same as above, except the case is ignored

IN IGNORE CASE,[fail,timeout,error,fatal],Request Failed

NOT IN

True if the content is not equal to any value in the list

NOT IN,"[500,501,503,400,401,404]",HTTP Request Successful

NULL

True if the content in field is null

NULL,,No Value

NOT NULL

True if the content in field is not null

NOT NULL,,Value Present

List of Numeric and Logical Operators and Examples

Note

Ensure that the numeric operators are not mixed with the string operators in the same dictionary. If the operators are mixed in the same dictionary and a string value is passed in the query, then an error message is returned about the type while matching using the numerical operators.
Operator Description Example

=

Numerical Equal To

=,1,Value is 1

!=

Numerical Not Equal To

!=,1,Value is Not 1

>

Above the given value

>,1,Value is above 1

<

Below the given value

<,1,Value is below 1

>=

Above or equal to the given value

>=,1,Value is equal or above 1

<=

Below or equal to the given value

<=,1,Value is equal or below 1

BETWEEN

Between the given two values, both inclusive

BETWEEN,1-10,Value is equal or above 1 and equal or below 10

> AND <

Above N1 and Below N2

> AND <,1-10,Above 1 and below 10

>= AND <=

Same as Between. Above or equal to N1 and Below or equal to N2

>= AND <=,1-10,Above or equal to 1 and below or equal to 10

>= AND <

Above or equal to N1 and Below N2

>= AND <,1-10,Above or equal to 1 and below 10

> AND <=

Above N1 and Below or equal to N2

> AND <=,1-10,Above 1 and below or equal to 10

> OR <

Above N1 or Below N2

> OR <,1-10

Above 1 or below 10

>= OR <=

Above or equal to N1 or Below or equal to N2

>= OR <=,100-10,Above or equal to 100 or below or equal to 10

>= OR <

Above or equal to N1 or Below N2

>= OR <,10-1,Above or equal to 10 or below 1

> OR <=

Above N1 or Below or equal to N2

> OR <=,100-10,Above 100 or below or equal to 10

>= OR !=

Above or equal to N1 or not equal to N2

>= OR !=,10-1,Above or equal to 10 or not equal to 1

<= OR !=

Below or equal to N1 or not equal to N2

<= OR !=,10-100,Below or equal to 10 or not equal to 100

>= OR =

Above or equal to N1 or equal to N2

>= OR =,10-1,Above or equal to 1 or equal to 1

<= OR =

Below or equal to N1 or equal to N2

<= OR =,10-100,Below or equal to 10 or equal to 100

> AND !=

Above N1 and not equal to N2

> AND !=,10-100,Above 10 and not equal to 100

< AND !=

Below N1 and not equal to N2

< AND !=,10-1,Below 10 and not equal to 1

Use Comments While Defining Dictionary Lookups

Use # as the first field to add comments to a dictionary lookup. Following is an example of a sample lookup with comments:

Operator,Condition,Label,Module
# ----------------------------------
# Startup/Shutdown and Terminations
# ----------------------------------
CONTAINS,Server started in RUNNING mode,Server Started,WebLogic Server
CONTAINS,A critical service failed. The server will shut itself down,Server Shutdown,WebLogic Server
CONTAINS,state changed to FAILED,Server Failed,
CONTAINS,Removing .* from cluster view due to PeerGone,Cluster Removed,WebLogic Server
# ----------------------
# Connection Error / Timeouts and Slowness
# ----------------------
CONTAINS,Unable to connect to WSM policy manager,WSM Policy Manager Connection Error,
CONTAINS REGEX,java.sql.SQLTimeoutException: \S+: user requested cancel of current operation,SQL Timeout,Database
CONTAINS,This member is running extremely slowly and may endanger the rest of the cluster,WebLogic Cluster Slowness,WebLogic Server

Use Dictionary Lookup in a Query

You can use the same dictionary in multiple queries. For example, the following query applies the same dictionary to the Message field and shows the rolled up results using the Pie Chart visualization:

'Log Source' = 'Linux Syslog Logs'
| lookup table = 'Linux Error Categories' select Issue, Area using Message
| stats count by Area

Use Dictionary Lookup in a Query