Administrator HandbookTOC

Database Query tool

Introduction

Once the ODBC driver installed and the software correctly set up, you could access the database directly from LoriotPro. This tool is able to accomplish simple queries to the database. More sophisticated tool are commonly available that could performs advanced queries and presents the results in various way.

If you are using a MySQL database:

You can for example use the SQLyog Windows program which is a full graphical interface to MYSQL server. It can help you to manage your MYSQL database easily. You can download it from http://www.webyog.com/

You can also use phpMyAdmin that is able to perfom mutiple tasks on your Database.


Using Query Tool

To start the Database Query tool from the main menu select:

Supervise>Database  Query                                                   


The main menu option Database Query

A window is displayed with all the options needed to perform the Database queries.


 Result of a simple query

Table of the Database Query tool

Options

Definitions

The ‘From table’ option allows you to select the table that you want to query.

The selected table name should appear in the combobox.

The ‘Limits’ option allows you to define from which table line you want to start and how many lines you want to process

In this example, we start from the first record with a maximum of 100 records.

Warning

If you specify a lager amount of records, you could fill the software memory and hang the process.

This option allows you to select the table fields that you want to display.

Examples

Results displayed

*

All fields

Timestamp, level, string

Only the specified fields.

Warning

This field should exist in the table.

This option allows you to select the table fields that you want to display according to specified filter.

Examples

Displayed results

Level=1

All the records where the filed level equal1.

Ip_agent_add like «%192%» and level=1

All the records where the ip_agent_add field contains the string 192 and having the level =1.

Perform a query on the database with the current options

Display the next or previous records according to the limit (0 100 by default).

This field allows you to perform direct SQL queries.

Warning: Only the SELECT command is supported.

Using SQL Select statement

In SQL, queries begin with the select clause. A typical query statement needs only two parts, we select what from where. The what will represent columns of your table you wish to select and the where represents the table name of your data table.

SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr, ...
[INTO OUTFILE 'file_name' export_options | INTO DUMPFILE 'file_name']
FROM table_references
[WHERE where_definition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_definition]
[ORDER BY {col_name | expr | position}
[ASC | DESC] , ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[FOR UPDATE | LOCK IN SHARE MODE]]

Examples:

Use of the SQLQuery command

Select * from loriot_events where level=4


Example of directe SQLQuery

An example of use of fields and filter options in a Query command.

Field : timestamp, level, string, ip_source_add

Filter : ip_agent_add like « %192% » and level=1


sample of a complex Query

The same operation with the SQLQuery command.

select timestamp, level, string, ip_source_add from loriot_events where ip_agent_add like "%192%" and  level=4

For more information on the available syntax, refer to the Database documentation.

 Warning : If the command does not provide results, the information box will not be refreshed and no warning message will be displayed.

Nonetheless, if you do a mistake in the syntax the ODBC driver will display a warning message.

Example 1 :

select timestamp, level, string, ip_source_add from loriot_events where ip_agent_add like "%192%" and  level4

The equal sign has been forgotten.

Example 2 :

select timestamp, level, stringip_source_add from loriot_events where ip_agent_add like "%192%" and  level=4

The coma has been forgotten.

 

www.loriotpro.com
Copyright © 2004 LUTEUS SARL. All rights reserved. This documentation is copyrighted by LUTEUS SARL. No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying or otherwise, without the prior express written permission of LUTEUS SARL