23.6. Log Query Language (LQL)

Home Prev	InControl 3.19.01 Administration Guide	Next

Overview

Clavister Log Query Language (LQL) is the language used to perform searches in the ILA log database.

As described previously in Section 23.4, The Log Explorer and Section 23.5, The Query Filter, log queries can be constructed without the need to know LQL. However, what happens internally to InControl is that such queries are first converted by the InControl client into LQL statements before being passed to the ILA server for processing.

With the Query Filter, the LQL statements created in this way can be examined and changed before the query is processed. For example, the following parameter values might be specified using the following Query Filter function.

If the LQL tab in the Query Filter is now selected, the LQL statement that this translates to can be immediately viewed and this is shown below for the example.

Alternatively, a search can be made by creating the entire LQL statement from scratch. The LQL tab is first selected and then an LQL query is entered directly into the dialog's text box. To do this, it is necessary to understand how to construct LQL statements.

LQL Syntax

LQL is similar to the traditional SQL used as a query language in many database products. However, LQL has a large number of cOS Core specific keywords and statements. The syntax of an LQL statement is as follows:

SELECT <output-type> [, <output-type>] FROM <firewall_and_time_statement>
[WHERE <logical_statement>]

Each LQL query is expected to start with the SELECT keyword

Directly after the SELECT keyword, one or more output types (described later), separated by a comma, are specified.

After the mandatory FROM keyword, one or more firewall and time statements are specified.

Optionally, the WHERE keyword followed by a logical statement may be specified.

Logical Operators

Logical operators are used to combine different LQL statements to form more complex statements. The following logical operators are available in the LQL language:

Operator	Usage	Description
NOT	NOT expression	Negates a boolean expression.
AND	expression1 AND expression2	Combines two Boolean expressions and evaluates to TRUE when both expressions are TRUE.
OR	expression1 OR expression2	Combines two boolean expressions and evaluates TRUE when either of the expressions are TRUE.

The following should be noted about using logical operators:

The logical operator OR and the operator AND cannot be used in a single filter expression. For example, the following is valid:
```
 srcip='10.0.0.1' AND destip='192.168.123.1' AND destip='192.168.123.2'
```
However, the following is not valid:
```
 srcip='10.0.0.1' AND (destip='192.168.123.1' OR destip='192.168.123.2')
```
To achieve the equivalent of mixing AND with OR, create separate filters and enable or disable the option Require all filters to match. Disabling this option is equivalent to a logical OR between the individual filters. Enabling the option is equivalent to a logical AND between the filters.

For example, to implement the invalid expression above, the expression must be broken into two filters. First, create the following filter for the first part of the expression:
```
 srcip='10.0.0.1'
```
Then, create the following filter for the second part of the expression:
```
 destip='192.168.123.1' OR destip='192.168.123.2'
```
Now, enable the option Require all filters to match to logically AND the two filters.
The NOT operator always takes precedence over the other operators.
Parentheses can be used with the NOT operator. For example:
```
 NOT(srcip='10.0.0.1' AND destip='192.168.123.1')
```

	Note: Capitalizing the logical operators is optional

Comparison operators

Comparison operators are used to compare search variables with user specified values. The following operators are supported:

Operator	Description
=	Equal to
>=	Greater than or equal to
<=	Less than or equal to
>	Greater than
<	Less than
IN	Range comparison

All user-specified values are expected to be quoted with the single quote (') character:

 srcip='10.0.0.1' AND destip='192.168.123.1'

 srcip IN (10.0.0.1-10.0.0.255)
		AND destip IN (192.168.123.1-192.168.123.255,1.2.3.4)

Search variables

There are a number of predefined variables that can be used in the logical statements and these are listed below:

Variable	Value Type	Description
srcip	IPv4 address	Source IP address on the format: a.b.c.d
destip	IPv4 address	Destination IP address on the format: a.b.c.d
hwsrc	Ethernet address	Source Ethernet address
hwdesc	Ethernet address	Destination Ethernet address
severity	String	Log message severity
category	String	Category of the logged event. Example: SYSTEM, NETCON, USAGE, CONN, DROP
conn	String	Connection event. Example: Open, Close, Closing
srcport	Integer	Source port (0-65535)
destport	Integer	Destination port (0-65535)
ipproto	Integer	IP protocol (0-255 or name). Example: TCP, UDP, ICMP, 99
recviface	String	Receiving interface name. Example: ext, int, dmz
destiface	String	Destination interface name
icmptype	String	ICMP Message Type (0-255. Example: ECHO_REQUEST
arp	String	ARP opcode. Example: Request, Reply, Other
icmpsrcip	IPv4 address	Source IP address in ICMP-encapsulated IP packet
icmpdesctip	IPv4 address	Destination IP address in ICMP-encapsulated IP packet
icmpsrcport	Integer	Source port (0-65535) in ICMP-encapsulated IP packet
icmpdestport	Integer	Destination port (0-65535) in ICMP-encapsulated IP packet
icmpipproto	String	IP protocol (0-255) in ICMP-encapsulated IP packet
description	String	Description of the event
fin	Boolean	TCP FIN flag (0 or 1)
syn	Boolean	TCP SYN flag (0 or 1)
rst	Boolean	TCP RST flag (0 or 1)
psh	Boolean	TCP PSH flag (0 or 1)
ack	Boolean	TCP ACK flag (0 or 1)
urg	Boolean	TCP URG flag (0 or 1)
xmas	Boolean	TCP XMAS flag (0 or 1)
ymas	Boolean	TCP YMAS flag (0 or 1)
enetproto	Integer	Ethernet protocol number (0-65535)
rule	String	Rule name
satsrcrule	String	SAT source rule name
satdestrule	String	SAT destination rule name
enet[index]	Integer	Value at [index] bytes offset from the Ethernet header
ip[index]	Integer	Value at [index] bytes offset from the IP header
tcp[index]	Integer	Value at [index] bytes offset from the TCP header
udp[index]	Integer	Value at [index] bytes offset from the UDP header
algmod	String	Name of the ALG module that this log message originated from.
algsesid	Integer	ID of the ALG session that this log message originated from.
authrule	String	Userauth rule name.
authagent	String	Userauth agent. Example: http, xauth
authevent	String	Userauth event. Example: Login, Logout, Timedout, Disallowed
username	String	Username, from login/logout, as well as src/destusername
srcusername	String	The user that originated this connection/packet
destusername	String	The destination user

Output Types

There are a number of Output Types defined that are used when specifying what data is to be returned by the query.

All output types return data in plain text, except the binary type, which will return the data in a binary form used in the query tool.

The following output types are defined:

Name	Description
binary	Binary form output, only used within the query tool.
srcip	Source IP address.
destip	Destination IP address
srcport	Source port
destport	Destination port
hwsrc	Source Ethernet address
hwdest	Destination Ethernet address
iphdrlen	IP header length
ipdatalen	IP data length
iptotlen	IP total length (data + header)
udpdatalen	UDP data length
udptotlen	UDP total data length
firewall	Name of the firewall that sent the data
time	The time when the event took place
recvif	Receiving interface
destiface	Destination interface
ttl	Time To Live field in the IP header
date	The date when the packet arrived at the logger
description	Description of the event
arp	ARP packet type
arphwdest	Destination hardware address in ARP events
arphwsrc	Source hardware address in ARP events
ipproto	IP protocol
icmptype	ICMP type
icmpsrcip	Source IP in an ICMP-encapsulated IP packet
icmpdestip	Destination IP in an ICMP-encapsulated IP packet
icmpsrcport	Source port of an ICMP-encapsulated UDP/TCP packet
icmpstd	ttl, icmptype, icmpipproto, icmpdestip, icmpsrcip and icmpdestport
tcpflags	All TCP flags
enetproto	Ethernet protocol
usage	Interface throughput
connusage	Connection statistics
rule	Name of the rule that this log entry matched
satsrcrule	Name of the SAT source rule that this entry matched
satdestrule	Name of the SAT destination rule that this entry matched
origsent	Amount of data sent by the originator (client end) of the connection
termsent	Amount of data sent by the terminator (server end) of the connection
conn	Conn event type
ack	TCP ACK flag (0 or 1)
fin	TCP FIN flag (0 or 1)
psh	TCP PSH flag (0 or 1)
rst	TCP RST flag (0 or 1)
syn	TCP SYN flag (0 or 1)
urg	TCP URG flag (0 or 1)
ece	TCP EXE flag (0 or 1)
cwr	TCP CWR flag (0 or 1)
category	Category of the logged event
tcphdrlen	TCP header length
tcpdatalen	TCP data length
tcptotlen	TCP total length (data + header)
standard	date, time, firewall, category, recvif, srcip, srcport, destip, destport, ipproto and description
tcpstd	tcpdatalen, tcphdrlen, fin, syn, rst, psh, ack, urg, ece and cwr
udpstd	udpdatalen
severity	Log message severity
algmod	Name of the ALG module that this log message originated from
algsesid	ID of the ALG session that this log message originated from
authrule	Name of the userauth rule applied
authagent	User authentication agent
authevent	User authentication event
username	Name of the user that logged in/out
usernames	username, srcusername, and destusername
srcusername	The user that originated this connection/packet
destusername	The destination user

Firewall Statements

The LQL firewall statement is used to specify the particular firewall(s) to search for log events.

The syntax of a firewall statement is as follows:

<firewall> [,<firewall>] [<time_statement>]
[AND <firewall> [,<firewall>] [<time_statement>]]

Time Statements

The time statement is used to specify a time interval for the data that is requested.

A time statement can be any of the following statement types:

 TIMES yyyy-mm-dd HH:MM:SS TO yyyy-mm-dd HH:MM:SS

 LAST DAYS n

 LAST HOURS n

 LAST MINUTES n

Where n is any numerical value in the range from 1 to 1000.

If the TIMES statement is used, the date and time have to be specified in ISO standard format (shown above) and may be terminated at any point. For example, the following is a valid time statement:

 TIMES 2000-01 TO 2000-02