Configure query 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.
- SGBox version 5.1.5
From SGBox menu, go to LM> Analysis > Events Queries and select New Query.
Use SQL syntax to write query sections.
Placeholders: $TIMESTAMP, $HOST, $EVENT, $PARAM:[parameter] can be used to refer to event fields.
$LIST:[list] placeholder allows you to search the value of a parameter into one of the lists defined in SCM.
FROM section allows you to select hosts and events on which to perform the query.
Join query correlates two queries to extract information.
Example 1 – Simple query – Extracts how many times each user did log on the selected host, and from which IP.
In this example, we select two parameters of the [UNIX] Logon SSH Geo event, and we use SQL syntax to perform count and group by.
Example 2 – Join query – Joins UNIX logon and logoff events to extract user sessions
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.