|
Administrator Handbook | Table of contents | |
|
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.
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.
|
||||||
| |
This option allows you to select the table fields that you want to display according to specified filter.
|
||||||
| |
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
An example of use of fields and filter options in a Query command.
Field : timestamp, level, string, ip_source_addFilter : 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 |
|
