Proteggiamo il tuo ambiente digitale da qualsiasi attacco informatico. Sfrutta tutte le potenzialità della piattaforma SGBox!

Gallery

Contatti

Via Melchiorre Gioia, 168 - 20125 Milano

info@sgbox.it

+39 02 60830172

Search another article?

The Events Queries

You are here:
< Back

The Event Query is a feature introduced from the version 5 that permit to perform SQL style queries from events or logs

Query Purpose

The utilization of queries offers numerous benefits, primarily due to their exceptional speed as a result of the direct access they have to the database where the events are stored.

The primary reasons for utilizing these queries include:

  • To replace a LCE Rule
  • Generate a statistic (temporary or permanent)
  • Generate an event based on a specific event threshold
  • Generate an e-mail alert or setup other action triggered by the query

Query Basics

You can follow this KB in order to achieve the basic first-step syntax and compose the very first query: SGBox – Events Queries Basics

Also more details about query directly from logs: SGBox – Events Queries from Raw Logs

Query Options

  • 🕑 Interval: monitor window where the query looking for (5 minutes in the example). You can find this option in the scheduling options
    The Events Queries
  • 📅 Active Scheduling: Query scheduled will run every minute
    The Events Queries

A timeline summary of the two options combined:
The Events Queries

Join

If you need to correlate two different events, and use different WHERE condition in the two query, you can later combine it using the “Join Query” panel.
Here you can define the two query

More details and example you can find it in this KB article:

Advanced Syntax

In a query you can use various functions, as decribed in the list below

Column alias

Every parameter, represented as a column, can be aliased with a name.

The general syntax is:
column AS aliasname
where column can be $TIMESTAMP, $HOST, $EVENT, or any $PARAM:*

The use of the alias is a best practice that permit also to pre-calculate the value in a column, to use it later in the WHERE or FINALLY statement.

Note: some column alias are forbidden to use: ts, Pattern, pid, hid

Functions and Statements

FunctionPositionDefinition
<column> as columnnamealiasSELECTdefine a column name (the alias can be referenced later also in WHERE and FINALLY part)
count()SELECT, WHERE, FINALLYCount the rows by type (must be used in addition with GROUP BY)
min(column), max(column)SELECT, WHERE, FINALLYWhen other columns grouped, select the first or last value of the column set
extract(column, ‘regex (valuetoextract) secondpart’)SELECT, WHERE, FINALLYExtract part of the value from a column (only first occurrence)
exractAll(column, ‘regex(value1)other(value2)’)SELECT, WHERE, FINALLYextract multiple value by regex, and output them as Array (not string)
extract(column, ‘(?i)regex (valuetoextract) secondpart’)SELECT, WHERE, FINALLYExtract value with case insentive modificator (?i) at beginning of the regex
toString(column or expression)SELECT, WHERE, FINALLYconvert a column value to a String
uniqExact(column)SELECT, WHERE, FINALLYcount specific column
arrayStringConcat(extractAll(column, ‘regex (val1) continue (val2)’), ‘separator_char’)SELECTCombination of multiextract of values and join to a single string, separated by specific char or string
toStartOfHour(timestamp)SELECT, WHERE, FINALLYRound Timestamp to Hour. Similar to toStartOfDay, toStartOfMinute, toStartOfMonth, toStartOfYear
runningDifference(timestamp) as DifferenceSELECTCalculate difference between current and preceeding row (first row always 0)
COLUMNS(‘string or regex’)WHERE, FINALLYSelect any column match the tring or regex
GROUP BY column1, column2, columnalias1FINALLYGroup by similar value in the columns specified. If used all the columns must be referenced in the GROUP BY clause or use in the SELECT an aggregte function (like min(), max(), sum(), avg(), etc.)
HAVING column …FINALLYSimilar to the filters in the where statement, can be used to filter certain values after the use of the GROUP BY keyword
column IN (‘val1’, ‘val2’)WHEREFilter by one or more value (specific) inside a column
column LIKE ‘%Value%’WHEREFilter by value contained in column
column LIKE ‘%Value’WHEREFilter by value at the end of the column
column LIKE ‘Value%’WHEREFilter by value at the start of the column
NOTWHEREReverse a Filter
match(mycolumn, arrayStringConcat( (SELECT groupArray(value) FROM $LIST:[regexlist]) , '\|' ) )WHEREFilter mycolumn against custom pre-built regex list
column IN (SELECT value FROM $LIST:listname)WHERESyntax to use to filter values by a list
match(column, ‘myfilterregex’)SELECT, WHERE, FINALLYVerify if a regex expression match a column (often used in WHERE)
multiMatchAny(column, [‘regex1’, ‘regex2’])WHERESimilar to match, but can verify multiple different regex. Similar but simpler syntax can be obtained with: col1 LIKE ” OR col1 LIKE ”
LIMIT numberFINALLYLimit set of result for number specified
LIMIT number BY columnFINALLYLimit set of result for each value in column

Complex Example
SELECT
count() as cnt
, max($TIMESTAMP) as lastlog
, sum($PARAM:[packetsize]) as totalsize
, extract($PARAM:[longmessage], 'beforetext: (\w+ mystringtocatch) ') as extractedMsg
FROM ()
WHERE
(
extractedMsg LIKE '%anytext%'
OR match($PARAM:[longmessage], 'anyotherstring')
OR extractedMsg IN ('fixedvalue1', 'fixedstring2')
)
AND NOT totalsize > 500
FINALLY
GROUP BY totalsize, extractedMsg
HAVING count() > 10
ORDER BY cnt DESC, lastlog

Troubleshooting

Convert Event Queries as Report

There are two main way to generate (or schedule) a Report starting by an Event Queries

Using dashboard

The steps to follow:

  1. Generate query (pay attention to order the result correctly with the ORDER BY directive)
  2. Associate the event query to a new specific dashboard
  3. Schedule the newly created dashboard as Report

Note: the report will generate only a pdf type attachment. To generate a tabular (csv) report you must use the second solution

By creating a new event

  1. Generate query and output event as action (define event name and class)
  2. Schedule the report starting by the newly class created

Event Queries to generate Widget in Dashboard

Some Event Queries can be used as base to fill up widget on dashboard

On dashboard, in the Add Widget Menu
The Events Queries
Next in the new window you can manage the source Event Query and the visualization tipology
The Events Queries

To build up the correct query syntax to match the correct type, please check the next chapter

Table

You can build any type of query to show up as table, only pay attention to the size of the single columns, as in the widget may can be limited or truncated.

Example

The Events Queries

Pie, Cloud

Any number of columns, but the last column must be numeric (used to build up the slice of the pie).

Example

The Events Queries
The Events Queries
SELECT
    $EVENT as evt, count() as cnt
FROM 4 events on all hosts
FINALLY
    GROUP BY evt

or

SELECT
    count() as Count, $EVENT as evt, count() as cnt
FROM 4 events on all hosts
FINALLY
    GROUP BY evt

Column

First column is the value in X axis, other columns must be numeric value, as there are the numeric series.

Example

The Events Queries
SELECT
    $EVENT as evt, count() as cnt
FROM 4 events on all hosts
FINALLY
    GROUP BY evt

or

SELECT
    $EVENT as evt, count() as cnt, count()+100 as cnt100
FROM 4 events on all hosts
FINALLY
    GROUP BY evt

Map

First column must be an IP address value, the second must be numeric (typically count).

Example

The Events Queries
SELECT
    $PARAM:[SourceIP] as SourceIP, count() as cnt
FROM 4 events on all hosts
FINALLY
    GROUP BY SourceIP

Timeline

First column must be a Timestamp value (datetime value), The second is a string value used for the legend, the last column must be a number (used for the point)

Example

The Events Queries
SELECT
    $TIMESTAMP as timestamp, $EVENT as evt, $PARAM:[HttpStatuscode]
FROM 4 events on all hosts

or

SELECT
    $TIMESTAMP as timestamp, $EVENT as Evt, $PARAM:[HTTPSize] as Size, toUInt8($PARAM:[HTTPSize])+100 as avgsize
FROM 4 events on all hosts

Text (Notes)

Fixed value in a row and a column.

Example

The Events Queries
The Events Queries
SELECT
    'test' as text
FROM all events on 127.0.0.1

or

SELECT
    count() as count
FROM 4 events on all hosts