Manage Lookups
Using Oracle Logging Analytics, you can enrich event data by adding field-value combinations from lookups. Oracle Logging Analytics uses lookups to match field-value combinations from events to an external lookup table, and if matched, Oracle Logging Analytics appends the field-value combinations to the events.
- 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
, andEQUAL
. For an exhaustive list of operators, examples, and steps to create this type of lookup, see Create a Dictionary Lookup.
- BEA Error Messages
- CRS Error Messages
- OGC Error Messages
- ORA Error Messages
- TNS Error Messages
The size of a single lookup file must not exceed 10 MB. However, there is no limit to the number of rows in the lookup.
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 formORA%
Log Source
: The log source of the logsEntity
: The entity of the logs
- Information from the Oracle-defined lookup table
ORA Error Messages
:errid
: The error ID specified in the formORA%
errmsg
: The error message for the error ID
The other details available in the
ORA Error Messages
lookup table areerrcause
(Error Cause) anderraction
(Error Action) for each error ID. To view the contents of the lookup table, use thesearchlookup
command as follows:* | searchlookup table = 'ORA Error Messages'
View the Lookup Detail
-
Open the navigation menu and click Observability & Management. Under Logging Analytics, click Administration. The Administration Overview page opens.
-
The administration resources are listed in the left hand navigation pane under Resources. Click Lookups.
-
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.
To perform other actions on the lookup, in the Lookups listing page, select the Type and Creation Type of your lookup, and narrow down your search for your lookup in the Filters section. After identifying your lookup, click Actions icon in the row of your lookup:
-
Reupload File: In case of user-created lookups, if you have a new version of the lookup table, or if a previous upload has failed, then you can reupload it.
-
Delete: You may have to delete some of your old or unused lookups. To delete the lookup, confirm in the Delete Lookup dialog box. You can delete a lookup only if it is user-created.
Update an Existing Lookup
-
Update your CSV file with the field-value combinations.
-
Open the navigation menu and click Observability & Management. Under Logging Analytics, click Administration. The Administration Overview page opens.
-
The administration resources are listed in the left hand navigation pane under Resources. Click Lookups.
-
Under Lookups, click the Actions icon in the row that corresponds to the lookup that you want to update, click Reupload File.
The Reupload File dialog box opens.
-
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.
-
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
anddescription
titles for the values in the subsequent rows. -
Open the navigation menu and click Observability & Management. Under Logging Analytics, click Administration. The Administration Overview page opens.
-
The administration resources are listed in the left hand navigation pane under Resources. Click Lookups.
-
Under Lookups, click Create Lookup.
-
In the Lookup page, enter the name of the lookup, such as
server error code lookups
and an optional description. -
Under Type, select Simple.
-
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.
More Topics:
For examples of using dictionary lookup in Cluster and Link, see Use Dictionary Lookup in Cluster and Use Dictionary Lookup in Link.
For an example of using dictionary lookup to provide the geo-location, see Specify Geo-location Using a Lookup.
-
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
andCondition
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.
-
-
Open the navigation menu and click Observability & Management. Under Logging Analytics, click Administration. The Administration Overview page opens.
-
The administration resources are listed in the left hand navigation pane under Resources. Click Lookups.
-
Under Lookups, click the Create Lookup link.
-
In the Create Lookup page, enter the name of the lookup, such as
cluster dictionary lookups
and an optional description. -
Under Type, select Dictionary.
-
Select the lookup CSV file that you had created earlier, and click Create.
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 |
---|---|---|
|
True if the value contains the string specified in the Condition field. Case-sensitive |
|
|
Same as above, except the case is ignored |
|
|
True if the value matches the specified regular expression |
|
|
Same as above, but ignores the case |
|
|
Use this to match against a multi-line string |
|
|
Same as above, except the case is ignored |
|
|
Specify more than one regular expression. True if at least one matches. List the regular expressions inside
If you need to use double quotes inside the regex, escape each double quote using another double quote. |
|
|
Does not contain the specified string |
|
|
Content equals the specified value |
|
|
Same as above, except the case is ignored |
|
|
True if the content is not equal to the value specified |
|
|
Compares to the beginning of the content |
|
|
Compares to the end of the content |
|
|
True if at least one of the value is equal |
|
|
Same as above, except the case is ignored |
|
|
True if the content is not equal to any value in the list |
|
|
True if the content in field is null |
|
|
True if the content in field is not null |
|
List of Numeric and Logical Operators and Examples
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 |
|
|
Numerical Not Equal To |
|
|
Above the given value |
|
|
Below the given value |
|
|
Above or equal to the given value |
|
|
Below or equal to the given value |
|
|
Between the given two values, both inclusive |
|
|
Above N1 and Below N2 |
|
|
Same as Between. Above or equal to N1 and Below or equal to N2 |
|
|
Above or equal to N1 and Below N2 |
|
|
Above N1 and Below or equal to N2 |
|
|
Above N1 or Below N2 |
Above 1 or below 10 |
|
Above or equal to N1 or Below or equal to N2 |
|
|
Above or equal to N1 or Below N2 |
|
|
Above N1 or Below or equal to N2 |
|
|
Above or equal to N1 or not equal to N2 |
|
|
Below or equal to N1 or not equal to N2 |
|
|
Above or equal to N1 or equal to N2 |
|
|
Below or equal to N1 or equal to N2 |
|
|
Above N1 and not equal to N2 |
|
|
Below N1 and not equal to N2 |
|
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