19.5.20. Querying trail content with the lucene-search function

Warning

This function works only if you have enabled indexing for the audit trails.

The lucene_search function allows you to search the content of indexed audit trails for a specific keyword and return the IDs of the channels that contain the search keyword. The lucene_search function requires four parameters:

  • search phrase: The keyword or keyphrase you are looking for, for example, a command issued in an SSH session (exit). The keyphrase can contain the following special operators to be used & (AND), | (OR), ! (NOT). Brackets can be used to group parts of the keyphrase.

  • beginning_timestamp: The date in UNIX-timestamp format. Only audit trails created after this date will be queried.

  • ending_timestamp: The date in UNIX-timestamp format. Only audit trails created before this date will be queried.

For example:

select lucene_search
from lucene_search('root', 1287402232, 1318938150);

# Sample output:
(1,2,3,1)
(1 row)

The output of this query is a formatted as the following:

(<channel_id>,<trail_id>,<hits_count>,<rank>)

Alternatively, you can use the following query format that returns a header of the displayed columns, and uses the pipe (|) character for separator:

For example:

select * from lucene_search('tmp',1496885628,1496954028);
channel_id | trail_id | hits_count | rank
-----------------------------+-----
1 | 2 | 3 | 1
(1 row)

The output contains the following columns:

  • channel_id: The ID of the channel within the audit trail (an audit trail file can contain audit trails of multiple channels).

  • trail_id: Identifies the audit trail using the unique identifier of the session (the _connection_channel_id of the channel for which the audit trail was created).

  • hits_count: The number of hits in the audit trail.

  • rank: Shows the relevance of the search result on a 0-1 scale, where 1 is the most relevant. Note that on the PSM search page, this information is scaled to 0-5 (and shown graphically with stars).

For details on how to use more complex keyphrases, see the Apache Lucene documentation.

For details of content indexing, see Procedure 15.1, Configuring the internal indexer.