The Enhanced Event Logging System

Basic SQL tips

Some of the EELS commands use SQL statements to retrieve records from the EELS database. This section provides a quick summary of some simple SQL statements that you may find useful if you have no previous SQL experience.

Structured Query Language (SQL) is a query language that can be used to formulate requests for data from a Relational DataBase Management System (RDBMS). An SQL query is usually made up of two or three parts:

select: specifies which columns (or fields) of the retrieved database record(s) to display
from: specifies which database table to search
where: specifies the search criteria to use (optional)

For example, to retrieve everything from a database table called events, use the following query:

select * from events where EventSpecificInformation <> ""

The where part of the query is optional, and if it is not used, all records are selected. This means that the query above could also be specified as:

select * from events

The Operators you will find the most useful include:

=: equals
<: less than
>: greater than
<>: not equals
like: contains

For example, to retrieve all the EventSpecificInformation fields from database records that have a UniqEventID less than 10, use the following query:

select EventSpecificInformation from events where UniqEventID < 10

You could use this query in conjunction with eels_db_query(1Meels), to retrieve records from your own EELS database, for example:

eels_db_query -d defaultdb -q "select EventSpecificInformation from \
events where UniqEventID < 10"

To extract more than one field from a database record, separate the field names using a comma. For example:

select UniqEventID, EventSpecificInformation from events \
where UniqEventID < 10

If you want to search for a string when using the where clause, you must enclose the string in quotes. Since you must also use quotes with eels_db_query, eels_log_archive and so on, enclose any strings in single quotes, for example:

eels_db_query -d defaultdb -q "select EventSpecificInformation from \
events where LogSystemsSource = 'syslog'"

To search all the records in the EELS database for the word ``telnet'' you must use the "like" operator. For example, to search for ``telnet'' in all the EventSpecificInformation fields, enter the following command:

eels_db_query -d defaultdb -q "select * from events \
where EventSpecificInformation like '%telnet%'"

NOTE: The "like" operator is a powerful way of searching for sub strings within a field. However, performing sub string searches does consume more resources than straight text searches using ``=''. Use the "like" operator only when you really need it.

You can also use the boolean operators:

For example, to search for all occurrences of ``telnet'' in EventSpecificInformation where the UniqEventID is greater than 100, enter the following command:

eels_db_query -d defaultdb -q "select * from events \
where EventSpecificInformation like '%telnet%' and UniqEventID > 100"