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?

You are here:
< Back

Examples of queries on SGBox events

This article explain how to configure the Events Queries functionality, that allows you to obtain any data on any event from SGBox. This queries can later be shown in a dashboard with different graphs.

Requirements:

  • SGBox version 5.3.1

From SGBox menu, go to LM> Analysis > Events Queries and select New Query
Use SQL syntax to write query sections:

  • SELECT: you can use placeholders $TIMESTAMP, $HOST, $EVENT, $PARAM:[parameter].
  • FROM: you to select hosts and events on which to perform the query.
  • WHERE: you can filter some values.
  • FINALLY: additional information to compleate the query like GROUP or LIMIT

You can also JOIN two queries to extract information.

The Events Queries (examples)

SELECT:    $PARAM:[UserName], $PARAM:[SourceIP], count() as count

FINALLY:   group by $PARAM:[UserName],$PARAM:[SourceIP]

The Events Queries (examples)

Click on TEST to run your query.

The Events Queries (examples)

After finished you can SAVE your query and produce a dashboard.

SELECT:    $PARAM:[SourceIP] as SourceIP, count() as count

FROM:   [UNIX] Logon fail via SSH on all hosts

FINALLY:   group by SourceIP having count >= 5

After configured your query you can select Show scheduling options and choose TIMEINTERVAL and the ACTIONS

  • TIMEINTERVAL: the period of time (in minutes) where the events occur. If we choose 1 the in the previous example it means: 5 unix logon fail in 1 minutes
  • ACTION: What the system do if this query is verified: send an email, generate an event, add a parameter to a list

The Events Queries (examples)

Send an email
The Events Queries (examples)

Generate an event
Remember that you need to map the SQL variables with a specific SGBox parameter. The Events Queries (examples)

Add parameter to a list
Remember that you need to specify a list and the parameter you want to add to the list.
The Events Queries (examples)

Configure the fist query (Q1) on Unix logon

SELECT:   $TIMESTAMP, $HOST,$PARAM:[UserName], $PARAM:[PIDLogon]

FROM:   [UNIX] Logon SSH on 10.250.2.20

The Events Queries (examples)

Switch on Join query and configure the second query query (Q2) on Unix logoff

SELECT:   $TIMESTAMP, $HOST,$PARAM:[UserName], $PARAM:[PIDLogon]

FROM:   [UNIX] Logoff SSH on 10.250.2.20

The Events Queries (examples)

In this example, in each of the two queries, we use $TIMESTAMP, $HOST and $PARAM placeholders to extract the fields of interest from logon and logoff events.

Then, in the ON section, we use SQL JOIN…ON syntax to say that we want logon events and logoff events to be on the same host, from the same user and the same logonPID – that’s a session). Also, we use SQL syntax in the SELECT session, to display the fields of interest from the events, and add a dateDiff sql function to display session duration.

Q1 JOIN Q2:   Q1.$PARAM:[Username], Q1.$HOST, Q1.$TIMESTAMP as logon, Q2.$TIMESTAMP as logoff, dateDiff('second',Q1.$TIMESTAMP, Q2.$TIMESTAMP) as duration

ON:   Q1.$PARAM:[PIDLogon] = Q2.$PARAM:[PIDLogon] AND Q1.$PARAM:[UserName] = Q2.$PARAM:[UserName]

The Events Queries (examples)
Click on TEST to show the result. If it’s ok you can SAVE your query.
The Events Queries (examples)