Oracle Cloud Infrastructure Documentation

Query Search

Oracle Logging Analytics enables you to filter through and analyze vast amounts of log data across your enterprise databases, through a single, unified, and customizable display that’s easy to read and navigate. Use the in-built Search feature to filter through all your available log data and return specific log entries.

Oracle Logging Analytics Search helps you drill down to specific log entries, enabling focused analysis and monitoring across your enterprise. Use the Oracle Logging Analytics query language to formulate your Search queries, which will retrieve log entries specific to the problem that you are troubleshooting.

The query language for analyzing the logs allows you to specify what action to perform on the search results. The commands can be either search commands or statistical commands. Search commands are those commands which further filter the available log entries. Statistical commands perform statistical operations on the search results. For the full list of commands, their syntax, and examples to use them, see Command Reference.

The Oracle Logging Analytics query language enables you to:

  • Filter and explore all available log data

  • Perform root cause analysis

  • Run statistical analysis on selected entities

  • Generate reports

  • Save search queries for later use

  • Retrieve saved searches to build dashboards

You can construct your Search query by either dragging elements from the Field panel and dropping them in the appropriate sections in the Visualize column, or by directly entering your query in the Search field. For more information about using the user interface to formulate your queries, see Formulate Queries Using the Logging Analytics UI.

For writing performant queries using regex command, refer to the RE2J syntax at Java Implementation of RE2.

Use Query Help Wizard

Logging Analytics now offers you active help to quickly learn to write queries and also provides a vast set of example queries for advanced analysis.

Click the help icon Query help icon next to the query bar in the Log Explorer to open the query help wizard. Within minutes, you can understand the format and the syntax of the queries that you can compose. Run the example queries starting from the very basic search to advanced analysis and familiarize yourself with the query reference. The wizard gives you some tips and shortcuts to make your search efficient. You can also view the result of running some of the common commands for their typical use cases.

Topics:

Also see Filter Logs by Hash Mask.

Search Logs Using Keywords and Phrases

You use commands to retrieve log data as well as to perform manipulation on that data. The first (and implicit) command in a query is the search command. A search is a series of commands delimited by a pipe (|) character. The first white-spaced string following the pipe character identifies the command to be used. The pipe character indicates that the results from the prior command should be used as input for the next command.

For example, to search for all database error messages, enter the following logical expression in the Search bar of Oracle Logging Analytics: 

Severity = 'error' AND 'Entity Type' = 'Database Instance'

By enclosing the words in quotation marks and including them in the query string as a phrase (‘Database Instance’, for example), only those logs containing the phrase ‘Database Instance’ are returned. In addition, keyword searches where the substring could be interpreted as a separate directive should be specific within quotation marks. For example, to search for the string and, you have to enter the string within single quotation marks (‘and’) to prevent the system from using its Boolean meaning.

For more examples and details on using query language to search the logs, see Write Search Queries.

List the Recent Searches

Oracle Logging Analytics lets you select and run a recently used search. When you click the Search field or enter text in the Search field, Oracle Logging Analytics displays a list of recently used searches. This lets you quickly access recently used search commands. You can select any of the listed commands and click Run to execute the selected search command.

Note

The recently used list is available on a per session basis. So if you sign out of Oracle Logging Analytics, and then sign in again, the list from the previous session isn’t displayed. A new list of recent searches is created for your session.

Use the Autosuggest Feature

When you enter a query in the Search field, the autosuggest feature of Oracle Logging Analytics automatically suggests terms that you can use in your query. Oracle Logging Analytics displays a list of suggestions, based on the text that you’ve entered in the Search field. For example, if you’ve entered the name of a field or a search action, the autosuggest feature displays the possible values only for that field or the list of available actions.

Write Search Queries

You can specify entities, keywords, phrases or wildcards, comparison operators, Boolean expressions, functions, and time to create your Oracle Logging Analytics search query.

To use the Search feature in Oracle Logging Analytics, you must formulate a search query and enter it in the Search field.

Topics:

Use Logging Analytics UI

The Oracle Logging Analytics user interface enables you to formulate your Search query.

You can use the following elements of the UI to formulate your Search query:

  • Search bar: Your search query is displayed here. You can directly edit the text in this field to further refine your search results.

    The search bar grows or shrinks based on the number of lines added to the query. It can have a maximum of 21 lines and a minimum of 1 line. Some of the custom shortcuts available are:

    • Ctrl + i: Indent every line of text present in the editor. Note that the uppercase I opens the debugger.
    • Ctrl + Enter: Execute the query displayed in the editor
    • Ctrl + Space: Display the auto-complete list of options based on the cursor position
    • Ctrl + Z: Undo the last edit
    • Ctrl + Y: Redo the last edit
    • Ctrl + D: Delete current line

    Note: Do not use the SHIFT key unless specified.

    Position the cursor in the open or closing brackets to view the matching element highlighted. The elements that can be highlighted are ( ) and [ ].

    The search bar supports two different themes, color and gray-scale. You can change the themes dynamically by changing the option in the help pop-up.

  • Field: The Field panel is divided into the following sections:

    • The Pinned attributes let you filter log data based on:

      • Log sources, such as database logs, Oracle WebLogic Server logs, and so on.

      • Log entities, which are the actual log file names.

      • Labels, which are tags added to log entries when log entries match specific defined conditions.

      • Upload names of log data uploaded on demand.

      By default, the entities and collection details are available in the Pinned bucket of the Fields panel for filtering. You can pin additional fields to the Pinned bucket depending on your usage. Once pinned, the fields are moved to the Pinned bucket. You can unpin any field and remove it from the Pinned bucket and move it back to the Interesting or Other bucket.

    • Based on your search and queries, Oracle Log Analytics automatically adds fields to the Interesting bucket for your quick reference. You can pin a field that’s available under Interesting bucket. The pinned field then gets moved to the Pinned bucket.

    • You can pin any field in the Other bucket and move it to the Pinned bucket. If you use a field from the Other bucket in your search or query, then it’s moved to the Interesting bucket.

      The selected options are automatically added to the query in the Search bar.

  • Visualize: In this pane, you can select how you would prefer to view the search results. In the Group by field, you can decide what metrics to group the results by.

  • Save: Use this button to save the search query that is currently in the Search field, to be run at a later time.

  • Open: Use this button to view previously saved search queries. You can run these queries and get current results, or you can use these queries to create dashboards.

  • New: Use this button to start a new search query.

  • Export: Use this button to export the result of the current search query in a file of the Comma-seperated Values(CSV) or JavaScript Object Notation(JSON) format.

  • Run: Use this button to run the query which is currently in the Search field.

  • Time Selector: Use the Time Selector to specify the time period.

  • Visualization Pane: The results of the search query are displayed in this pane. The filtered information in this pane loads when the query in the Search field is run. By clicking on any area in the chart in the visualization pane, you can drill down into the search query and update it.

Use Keywords, Phrases, and Wildcards

String queries can include keywords and phrases. A keyword is a single word (for example, database), while a phrase refers to multiple words, enclosed in single (‘ ‘) or double (“ “) quotes (for example, ‘database connection’). If you specify a keyword or a phrase in your query, then all log entries containing the specified keyword or phrase are returned after the query is run.

The Oracle Logging Analytics search language also supports special pattern mapping. In other words, you can use wildcard characters, such as asterisk (*), question mark (?), and percentage (%), to complete keywords.

The following table lists the supported wildcard characters and provides a brief description of each.

Wildcard Character Description

?

Use this character to match exactly one character of the possibilities to the keyword. For example, if you enter host?, then the keyword host1 is considered to be a match, while host.foo.bar is not.

* or %

Use either of these characters to match 0 or more characters of the possibilities, to the keyword. For example, if you enter host* or host%, then host1 and host.foo.bar are considered to match the keyword. Similarly, if you enter %host%, then ahostb and myhost are considered to match the specified keyword.

You can specify multiple keywords. For example, database and connection. Logs containing the words database and connection (but not necessarily together) are returned. However, these words need not necessarily occur consecutively. However, by enclosing the words in quotes and including them in the query string as a phrase (‘database connection’, for example), then only those logs containing the phrase ‘database connection’ are returned. To see how to use multiple keywords, see Use Boolean Expressions.

When specifying a keyword or phrase, remember the following:

  • Keywords and phrase strings are not case-sensitive.

  • Keywords which are not enclosed within quotes must contain only alphanumeric characters, underscore (_), and wildcard characters (*, %, and ?).

  • Keyword searches where the substring could be interpreted as a separate directive should be specific within quotes. For example, to search for the string and, you will have to enter it within single quotes (‘and’) to prevent the system from picking up its Boolean meaning.

Note

To use wildcards with the message field, you must also use LIKE or LIKE IN. Following are examples of using wildcards with message.
ORA-* AND message LIKE 'connection* error*'
ORA-* AND message LIKE IN ('tablesp*','connection* error*')

Use Comparison Operators

Comparison operators are conditions you specify to establish a relationship between a field and its value. Fields without values are considered to be null.

The following table lists the supported comparison operators and provides a brief description of each.

Comparison Operator Description
< If you use this operator in your query, then all log entries with a value, for the corresponding field, of less than the specified value are returned.
<= If you use this operator in your query, then all log entries with a value, for the corresponding field, of less than or equal to the specified value are returned.
> If you use this operator in your query, then all log entries with a value, for the corresponding field, of greater than the specified value are returned.
>= If you use this operator in your query, then all log entries with a value, for the corresponding field, of greater than or equal to the specified value are returned.
= If you specify this operator in your query, then all log entries with a value, for the corresponding field, of equal to the specified value are returned.
!= If you specify this operator in your query, then all log entries with a value, for the corresponding field, of not equal to the specified value are returned.

Use these operators to find logs with fields having specific values. For example, specify Severity=’ERROR’ to search through the available logs where the value of the field Severity is ERROR. Similarly, Severity!=NULL returns all logs where the value of the Severity field is not null (in other words, where severity has been specified).

Note

The value to the right of the comparison operator must be specified within quotes if the value is not numeric or NULL.

Use Boolean Expressions

The Oracle Logging Analytics Search feature has the capabilities of LIKE and REGEX, as per standard conventions. Boolean Expressions can have a value of either true or false.

The following table lists the supported Boolean Expressions, along with a brief description of each.

Boolean Expression Description
AND Use this expression to view only those logs which contain both specified parameters.
NOT IN or IN Use this expression to find data which is in a specified subset of available data. For example, ‘Entity Type’ IN (‘Database Instance’,‘Automatic Storage Management’,’Listener’,’Cluster’) will first consider only those logs which contain ‘Database Instance’, ‘Automatic Storage’, Listener, or Cluster, and then identify those logs containing ‘Entity Type’. However, when you use NOT IN, then log entries with the specified keyword or phrase are returned, excluding the specified entries. For example, ‘Entity Type’ NOT IN (‘Database Instance’,’Automatic Storage Management’,’Listener’,’Cluster’) first filters out log entries with ‘Database Instance’, ‘Automatic Storage Management’, Listener, and Cluster, and then returns those log entries where the value of ‘Target Type’ is not one of the specified values.

The reserved word NULL is supported with this Boolean operator.

NOT LIKE or LIKE Use this expression to find data which either matches or does not match the specified character pattern. The character pattern is a string that can contain one or more wildcard characters.
NOT LIKE IN or LIKE IN Similar to [NOT] IN, this expression allows you to use a shorthand for expressing multiple LIKE clauses together.
OR Use this to view those logs which contain either of the specified parameters.

The Oracle Logging Analytics Search language supports nesting Boolean expressions within other Boolean expressions. For example, consider the following query: 

fatal ('order' OR host LIKE '*.oracle.com')

Running this query returns all logs which contain fatal and either contain the keyword order or originated from a host whose name ends with .oracle.com.

Formulate Queries Using the Logging Analytics UI

You can use the Oracle Logging Analytics user interface to formulate your Search query.

By default, the query * | stats count by ‘log source’ is specified in the Search field.

To view data for specific Entities, complete the following steps:

  1. Open the navigation menu and click Observability & Management. Under Logging Analytics, click Log Explorer.

  2. Under the heading Entities in the Fields panel, select Entity or Entity Type, depending on how you wish to view the entities. This groups the registered databases on the basis of the selection you have made. For example, if you select Entity Type, then the selected entities are grouped according to their types.

  3. Select the Entity or Entity Type for which you wish to view the data.

  4. Click Submit.

    The button clear appears next to the entity you have selected, and the data displays in the visualization pane.

To view data for a specific Field, complete the following steps:

  1. Select the type of field from the Fields panel, and under Pinned attributes, Interesting bucket, or Other bucket.

  2. Select the Label, Log Entity, Log Source, Owner, or Upload Name for which you wish to view the data. You can select more than one Label, Log Entity, Log Source, Owner, or Upload Name.

  3. Click Submit.

    Data for the field is loaded in the visualization pane.

Specify the Time Range in Your Query

Typically, the time range that you select in the Log Explorer is not included in the query string. You can specify the time range in your query by using the absolute time or relative time modifier in the search command.

Topics:
Note

In case of Saved Search, the time range selected in the Log Explorer while creating the saved search is stored as one of the Saved Search components. When you use the saved search, you can edit the time using the time range selector in the Log Explorer.

On the other hand, when the time range is specified in a query, the time range selected in the Log Explorer is ignored. The time range included in the query is used for log analysis. When you save this query as Saved Search, the time range specified in the query is considered for Saved Search tasks and not the time specified in the Log Explorer.

Some examples where a time range can be specified in the query:

  • Return all ORA-600 error logs discovered in the last 24 hours:

    Message like 'ORA-600%' and time > dateRelative(24h)

  • Return the count of logs for host target myHost over the past 90 days: 

    'Host Name (Server)' = myHost and Time > dateRelative(90day) | stats count as 'Num Host Logs'

Time based functions that can be used with search command

The following time based functions can be used only with the search command:

  • toDate: This is the absolute time, for example, 2014-07-15T16:24:51.000Z or '2014-07-12', 'yyyy-MM-dd'.

    Syntax for toDate: toDate(<time>).

  • dateRelative: Creates a date relative to the current date, for example, 12h or 2d, day.

    Syntax for dateRelative: dateRelative(<timespan>, <rounding interval>) where rounding is based on UTC time.

  • dateadd: Adds units of time to the specified date, for example, add Day, 2 to toDate('2024-05-12', 'yyyy-MM-dd')

    Syntax for dateadd: dateadd(<date>, <unit>, <amount>).

  • dateset: Changes a date by the units of time. For example, toDate('2015-08-12', 'yyyy-MM-dd') changed with year, 2014, month, 7

    Syntax for dateset: dateset(<date>, <unit>, <value> [, <unit>,<value>]).

The time criteria can be expressed using the comparison operators =, !=, <, <=, >, >= and the logical operator and.

Supported time units

  • All Time

    Equivalent JSON example: "timeFilter": { "type" : "relative", "timeUnit" : "allTime" }

  • Second: s, sec, secs, second, seconds

  • Minute: m, min, mins, minute, minutes

    Example: Last 60 minutes

    Equivalent JSON example: "timeFilter": { "type" : "relative", "duration" : "60", "timeUnit" : "minutes" }

  • Hour: h, hr, hrs, hour, hours

  • Day: d, day, days

  • Week: w, week, weeks

  • Month: mon, month, months

  • Year: y, yr, yrs, year, years

An example for the absolute date in JSON format is "timeFilter": { "type" : "absolute", "startTime" : "2015-04-26T08:00:00.000Z", "endTime" : "2015-04-27T08:00:00.000Z" }.

Examples for time range in queries

The following query searches for everything between 2 absolute dates specified in ISO standard format: 

time between '2014-07-15T16:24:51.000Z' and '2014-07-17T18:14:16.000Z'

See ISO 8601: DATE AND TIME FORMAT.

The following query searches for everything based on 2 absolute dates that are not the ISO standard format: 

time between toDate('2014-07-12', 'yyyy-MM-dd') and toDate('2014-07-15', 'yyyy-MM-dd')

The following query searches for everything except from the last 12 hrs (* on left side of the between expression represents new Date(0), * on right is now): 

time between * AND dateRelative(12h)

The following query searches for everything in the last 12 hrs: 

time > dateRelative(12h)

The following query searches for everything in the last 30 min rounded to start from top of the hour: 

time > dateRelative(30min, hour)

The following query searches for records dated before 12th July, 2014:

time < dateAdd(toDate('2014-07-22', 'yyyy-MM-dd'), day, -10)

The following query searches for records dated after 10th June, 2010:

time > dateSet(toDate('2015-08-10', 'yyyy-MM-dd'), year, 2010, month, 6)

Write Sub-Queries

Sub-queries allow the child query to provide a dynamic filter to its parent queries. Sub-queries are evaluated first, and the result is then used in the parent query.

  • You can nest sub-queries inside one another as well as a particular query having multiple sub-queries at the same level.
  • Sub-queries by default inherit the global time range, but you can override it using the time between T1 and T2 syntax, if required.
  • Sub-queries are restricted to return only the first 2000 matches as input to its parent. Other results are truncated.
  • They have a maximum timeout of 30 seconds to complete.
  • All of the fields returned by a sub-query must match the fields in the parent query by name. Otherwise, it will result in an error.
  • You can use sub-queries only inside a search command.
  • You can use all the commands inside a sub-query except cluster, clustersplit, clustercompare, fieldsummary, delete, classify, highlight, and highlightrows.

Examples:

  • Chart the traffic from the IP blacklist over time:
    [searchlookup table=ip_blacklist | distinct ip | rename ip as 'host address'] | timestats count
  • List the most purchased products for the top users of an e-commerce site:
    'Log Source'='WLS Access Logs' status=200 action=purchase ['Log Source'='WLS Access Logs' status=200 action=purchase | stats count by 'Host (Client Address)' | top limit=1 'Host(Client Address)' | fields -*, 'Host (Client Address)'] | lookup table=products select 'product name' using productid | stats count, distinctcount(productId), unique('product name') by 'Host (Client Address)'
  • Find Top 4 OS Process IDs with highest sum: 
    [ *|stats sum('OS Process ID') as OSprocessidSum by 'OS Process ID' | top 4 OSprocessidSum | fields -OSprocessidSum ] | stats count by 'OS Process ID', 'Log Source', 'Host Name(Server)'
  • Show all the logs from the target with the most fatal severity logs:
    * and [ Severity = fatal | stats count by Target | top limit = 1 Count | fields -Count]