Description of the illustration cluster_sql.png

The large volume of log records are reduced to 89 clusters, thus offering you fewer groups of log data to analyze.

You can drill down the clusters by selecting the variables. For example, from the above set of clusters, select the cluster that has the sample message SELECT version FROM V$INSTANCE:

Description of the illustration cluster_sql_variable.png

This displays the histogram visualization of the log records containing the specified sample message. You can now analyze the original log content. Click Back to Cluster to return to the cluster visualization.

The Trends panel shows the SQLs that have similar execution pattern.

The Outliers panel displays the SQLs that are rare and different.

The nlp command supports semantic clustering. Semantic Clustering is done by extracting the relevant keywords from a message and clustering based on these keywords. Two sets of messages that have similar words are grouped together. Each such group is given a deterministic Cluster ID.

The following example shows the usage of NLP clustering and keywords on Linux Syslog Logs:

'Log Source' = 'Linux Syslogs Logs'
| link Time, Entity, cluster()
| nlp cluster('Cluster Sample') as 'Cluster ID', 
                    keywords('Cluster Sample') as Keywords
| classify 'Start Time', Keywords, Count, Entity as 'Cluster Keywords'

For more example use cases of semantic clustering, see Examples of Semantic Clustering.

nlp Command

The nlp command can be used only after the link command and supports two functions. cluster() can be used to cluster the specified field, and keywords() can be used to extract keywords from the specified field.

nlp command can be used only after the link command. See nlp.

• nlp cluster():

  cluster() takes the name of a field generated in Link, and returns a Cluster ID for each clustered value. The returned Cluster ID is a number, represented as a string. The Cluster ID can be used in queries to filter the clusters.

  For example:

  nlp cluster('Description') as 'Description ID' - This would extract relevant keywords from the Description field. The Description ID field would contain a unique ID for each generated cluster.

• nlp keywords():

  Extracts keywords from the specified field values. The keywords are extracted based on a dictionary. The dictionary name can be supplied using the table option. If no dictionary is provided, the out-of-the-box default dictionary NLP General Dictionary is used.

  For example:

  nlp keywords('Description') as Summary - This would extract relevant keywords from the Description field. The keywords are accessible using the Summary field.

  nlp table='My Issues' cluster('Description') as 'Description ID' - Instead of the default dictionary, use the custom dictionary My Issues.

NLP Dictionary

Semantic Clustering works by splitting a message into words, extracting the relevant words and then grouping the messages that have similar words. The quality of clustering thus depends on the relevance of the keywords extracted.

• A dictionary is used to decide what words in a message should be extracted.
• The order of items in the dictionary is important. An item in the first row has higher ranking than the item in the second row.
• A dictionary is created as a .csv file, and imported using the Lookup user interface with Dictionary Type option.
• It is not necessary to create a dictionary, unless you want to change the ranking of words. The default out-of-the-box NLP General Dictionary is used if no dictionary is specified. It contains pre-trained English words.

See Create a Dictionary Lookup.

Following is an example dictionary iSCSI Errors:

The first field is reserved for future use. Second field is a word. The third word specifies the type for that word. The type can be any string and can be referred to from the query using the category parameter.

In the above example, the word error has higher ranking than the words reported or iSCSI. Similarly, connection has higher ranking than closed.

Using a Dictionary

Suppose that the following text is seen in the Message field:

Kernel reported iSCSI connection 1:0 error (1020 - ISCSI_ERR_TCP_CONN_CLOSE: TCP connection closed) state (2) 
Please verify the storage and network connection for additional faults

The above message is parsed and split into words. Non-alphabets are removed. Following are some of the unique words generated from the split:

Kernel
reported
iSCSI
connection
error
ERR
TCP
CONN
CLOSE
closed
state
...
...

There are a total of 24 words in the message. By default, semantic clustering would attempt to extract 20 words and use these words to perform clustering. In a case like the above, the system needs to know which words are important. This is achieved by using the dictionary.

The dictionary is an ordered list. If iSCSI Errors is used, then NLP would not extract ERR, TCP, or CONN because these words are not included in the dictionary. Similarly, the words error, reported, iSCSI, connection, and closed are given higher priority due to their ranking in the dictionary.