SQL queries

The Structured Query Language allows you to access and manipulate the source of log files.

You can write your queries in the query pane which is displayed in the Log Monitoring dashboard as follows.

Some of the major operations such as Select, Where, From, Like and so on are supported. Following is a list of commands with examples.

Select

An event query begins with SELECT along with multiple arguments.

Syntax:

SELECT count FROM log;

Example:

SELECT count(*) from log

Result:

Returns the number of records for the log.

Where

WHERE is used to filter records that fulfill a certain condition.

Syntax:

SELECT count FROM log WHERE condition;

Example:

Select count (*) from log where service = 'abcd'

Result:

Returns the number of records for the specified log where service is 'abcd'.

Note:

You can use the following operators along with the WHERE clause.

• Comparison operators such as LIKE, IN, in condition expressions.
• Logical operators such as AND, OR, or NOT.
• REGEXP operators such as . ?, +, *, |, {, }, [, ], (, ), and/or ".

From

Syntax:

SELECT count From log;

Example:

select count (*) from log;

Result:

Returns number of records from the specified source "log".

Like

Like is used along with WHERE to search for a specified pattern in a column.

Syntax:

Select count From log Where condition Like pattern;

Example:

select count(*) from log where service='devtest' and message_message like '%Extranet%'

Result:

Returns number of records from the specified log "devtest" where message_message column contains "Extranet".

Order

Order enables you to sort the query results by one or more fields.

Syntax:

Select field_name From log Order BY field_name desc

Example:

Select message from log order BY timestamp desc

Result:

Returns column "message" from log demo with timestamp column in descending order.

Complex queries

Sorting by aggregation

Using Limit for queries that use sorting by aggregation displays the top N desired results.

Example:

Select log_name, count(*) from log group by log_name order by count(*) limit 100;

Results in grouping the logs by log name from log, sorts (ascending by default) according to the count of the grouped records, and displays 100 records.

Using a sub-select

Using sub-selects (SELECT X FROM (SELECT Y)) is supported.

Example:

select * from (select message_first_name, message_last_name from log where message_last_name not like '%a%') where message_first_name like 'A%' order by 1;

Results in first name and last name from "log" where the last name does not contain character "a". The query will result all the records from the above sub set with the first name starting with character "A" and records order by the first column.

Using TIME data type in GROUP BY

Using time data type as a grouping key is supported if it is wrapped with a scalar function that returns another data type, as in the following example.

Example:

SELECT count(*) FROM log GROUP BY MINUTE(timestamp);

Results in the total count of records from the log which is grouped by timestamp in minutes.

Using distinct keyword

Example:

select distinct service from (select * from log where message_message ='info');

This command selects the records which contains info in the message and shows the distinct service name.

Example:

select message_level, count(*) from log where message_level in (select message_level from log where latnecy > 2000) group by message_level;

This query will return records which are greater than 2000 latency and a count of message level.

Supported keywords

Following is a list of supported keywords that you can use to build your queries. Usage of both capital and small case letters is allowed.

Keywords	Keywords	Keywords	Keywords
alter	and	as	between
by	case	cast	constraint
create	cross	cube	current_date
current_time	current_timestamp	current_user	deallocate
delete	describe	distinct	drop
else	end	escape	except
execute	exists	extract	false
for	from	full	group
grouping	having	in	inner
insert	intersect	into	is
join	left	like	localtime
localtimestamp	natural	normalize	not
null	on	or	order
outer	prepare	recursive	right
rollup	select	table	then
true	uescape	union	unnest
using	values	when	where
with

For more information about creating and running queries, see Creating and running queries.