Implications of database queries

Scenario 1 — Query error

If an application queries the database at some point during the tuning exercise there is a remote chance that during the transition to (or from) the temporary tables, the SQL query will result in an error. If an SQL query error occurs, simply retry the query.

Scenario 2 — Query occurs while tuning is underway

If an SQL query is run during the tuning exercise the response and behavior would look the exact same as it would today. However, given the query has been made to the valid iv_alert and iv_packetlog tables that have just been created, there is now the likelihood that some records will be missed as in the case below:

The SIEM product has forwarded alerts up to uuid x.
Additional n alerts, x+1 to x+n are received prior to database tuning and before the application had a chance to forward them.
The SIEM product starts accepting alerts from the newer temporary alert table and forwards x+n+1 and so on.
When the merge occurs, the SIEM product is not aware of x+1 through x+n and they would never be forwarded.

To determine if the iv_alert and iv_packetlog tables are freshly created tables needed to enable online database tuning, you should include an additional query for table size with the standard query. If the table size is less than 100 records it can be concluded that a tuning exercise is underway and you must apply further logic to future queries to ensure no records are missed. Note that records forwarded during these queries are perfectly valid.

It is recommended that, upon determining that a query has just been made during tuning, the first query after determining a full-sized database (that is, tables have merged again) include records uuid x-200 to x+(whatever increment is typically used). This query will include records that have already been forwarded, however it will also include any records that may have been missed during the tuning process. Duplicate records should be discarded.

Example queries

Following query can provide Sensor, interface, policy name, attack name for selected set of alerts.

select
	alrt.uuid,
	atk.name,	
	sen.name,
	vids.name,
	pol.policy_name

from
	alert_sample alrt,
	iv_sensor sen,
	iv_vids vids,
	iv_policy pol,
	iv_attack atk

where
	alrt.sensorid = sen.sensor_id and
	alrt.policyid = pol.policy_id and	
	concat("0x",hex(alrt.attackid), "00") = atk.id and
	alrt.vidsid = vids.vids_id;

Attacks included in policy

select 
	pol.policy_name, 
	list.attack_id, 
	atk.name 
from 
	iv_policy pol, 
	iv_filtered_attack_list list, 
	iv_attack atk 
where 
	pol.policy_id = list.owner_id and 
	atk.id = list.attack_id;

Finding list of policies that is including given attack id

select 
	pol.policy_name,
	list.attack_id,
	atk.name 
from 
	iv_policy pol, 
	iv_filtered_attack_list list, 
	iv_attack atk 
where 
	pol.policy_id = list.owner_id and 
	atk.id = list.attack_id and 
	list.attack_id = “0x41a01e00”;

Fetching only NTBA alerts

Just add the following clause to any query involving iv_alert table: AND deviceType = 1