fulltext

Searches for data stored in a table using a full-text index.

Syntax

fulltext [OPTIONS] EXPR [from TABLE[.INDEX], ...]
Required Parameters
EXPR [from TABLE_1[.INDEX], TABLE_2[.INDEX], ...]

String literal or expression to match records in the table and/or index. If you specify only TABLE, all indexes in the table are retrieved. If you specify the same table or index multiple times, the same search results are duplicated and returned as many time as specified.

EXPR is an expression that expresses the data to be searched and must satisfy the following rules.

  • You can use comparison operators. The comparison operators you can use are: ==, !=, >=, >, <, <=
  • The string to be searched must be enclosed in a pair of double quotes (" ") and is case insensitive.
  • You can provide a combination of the logical operators and, or, not, and a pair of parentheses (( )).
  • This searches all tables unless you specify a table.
  • If there is a table or index specified more than once, it is returned as many times as it is duplicated.

EXPR recognizes the subquery enclosed in a pair of square brackets([ ]) in the expression. This runs the subquery first before running an index search and then searches all terms to be returned in the results of the subquery. The more search targets returned by the subquery, the slower the index search speed. We recommend that you use the 'fields' command in a subquery to retrieve only the fields that you really need.

Optional Parameters
duration=INT{mon|w|d|h|m|s}

Time range to search the previous data based on the current time. You can specify time in units of mon (month), w (week), d (day), h (hour), m (minute), and s (second). For example, 10s refers to "the last 10 seconds" based on the current time. This option cannot be used with from and to.

from=yyyyMMddHHmmss

Start date and time of the period to search in the form of yyyyMMddHHmmss. The time period for the search includes the specified time point. If you provide only the first part, the remaining digits are recognized as 0. For example, if you provide 20130605, it is recognized as 20130605000000 (June 5, 2013, 00:00:00). This option cannot be used with duration.

to=yyyyMMddHHmmss

Specify the date and time to end the search in the form of yyyyMMddHHmmss. The time period for the search does NOT include the specified time point. The input format is the same as from. This option cannot be used with duration.

limit=INT

Maximum number of search results (default: unlimited).

offset=INT

Number of search results to skip (default: 0).

order={desc|asc}

Search order of the index (default: desc)

  • desc: Searches from the most recent data to the oldest ones.
  • asc: Searches from the oldest data to the most recent ones.
tt=BOOL

Boolean option to use the search term tokenizer (default: f).

  • t: Searches the string by splitting it into tokenizers for each index.
  • f: NOT use the search tokenizer

When you use the tt option, the string wildcard (*) in EXPR can only be placed at the beginning or end of the string. For example, you can enter "*asp", "asp*", and "*asp*" in the EXPR, but not "a*sp". Tokens are separated by the and logical operator to reconstruct the query statement. For example, the query statement fulltext tt=t dst == "10.10.130.235" is reconstructed as fulltext dst == "10" and dst == "10" and dst == "130" and dst == "235".

Note
If you do not use 'duration', 'from' or 'to', all logs are searched.

Usage

  1. Search for logs dated June 5, 2013 including "1.2.3.4" from the table.

    fulltext from=20130605 to=20130606 "1.2.3.4"
    
  2. Search for all web logs including "cmdshell" from the iis table.

    fulltext "cmdshell" from iis
    
  3. Search for all web logs including "MSIE" or "Firefox" strings while including the word "game" from the "iis" table.

    fulltext "game" and ("MSIE" or "Firefox") from iis
    
  4. Search for web logs including numbers in the range of 400 to 500 as strings from the iis table.

    fulltext range(400, 500) from iis
    
  5. Search for the IP set of the blacklist DB from the iis table.

    fulltext [ dbquery black select ip from ip_blacklist ] from iis
    
  6. Search for full text for a set of tables where the parser of the table is openssh.

    fulltext "term" from meta("parser==openssh")
    
  7. Search for full text for the next 20 fidx index data after skipping the first 5 index data.

     fulltext offset=5 limit=20 "*" from iis.fidx
    
  8. Split the "1.2.3.4" string using the tokenizer for the fidx index and match them against the fidx index from the iis table

    fulltext tt=t "1.2.3.4" from iis.fidx