Handling open-form SQL statement queries to retrieve data from the event source

Mandatory SELECT statement

To enable the probe to receive any events from the data source, the SelectSqlFile property must be set to a file containing an SQL SELECT statement. The way in which you configure and prepare that SELECT statement depends on what table you are interested in and what data from that table you want to receive as an event.

The mandatory query in the file specified by SelectSqlFile property must contain a SELECT statement that can return data.

Example SELECT statements

The following examples show simple SELECT statements.

Example 1

The following example returns events that contain the eventid, event_name, event_desc, severity, and category fields from all records in the active_alarms table that have the resolved field set to 0.

SELECT eventid, event_name, event_desc, severity, category FROM active_alarms WHERE resolved = 0;

Example 2

The following example retrieves all data from the SensorData1 table (the main table used by ISS SiteProtector to store sensor data).

SELECT * FROM SensorData1;

The following examples retrieve data from the SensorData1 table, and enrich and convert it using SQL clauses.

Example 3

This example uses convert clauses:

SELECT convert(int,SensorDataRowID) as SensorDataRowID, convert(varchar(64),SensorDataID) as SensorDataID, convert(varchar(120),AlertName) as AlertName, AlertDateTime, AlertID, convert(varchar(100),SensorName) as SensorName, ProductID, AlertTypeID, AlertPriority, AlertFlags, convert(varchar(10),SensorAddressInt) as SensorAddressInt, convert(varchar(10),SrcAddressInt) as SrcAddressInt, convert(varchar(10),DestAddressInt) as DestAddressInt, ProtocolID, SourcePort, convert(varchar(64),SourcePortName) as SourcePortName, convert(varchar(64),DestPortName) as DestPortName, convert(varchar(64),UserName) as UserName, ProcessingFlag, Cleared, HostGUID, convert(varchar(64),HostDNSName) as HostDNSName, convert(varchar(64),HostNBName) as HostNBName, convert(varchar(64),HostNBDomain) as HostNBDomain, convert(varchar(64),HostOSName) as HostOSName, convert(varchar(64),HostOSVersion) as HostOSVersion, HostOSRevisionLevel, ObservanceID, VulnStatus, AlertCount, convert(varchar(64),ObjectName) as ObjectName, ObjectType, OSGroupID, ComponentID, SensorGUID, LicModule, convert(varchar(64),VLan) as VLan, convert(varchar(64),VirtualSensorName) as VirtualSensorName from SensorData1

Example 4

This example uses CASE and INNER JOIN clauses.

SELECT CASE WHEN sd.SensorDataID IS NULL THEN '-1' ELSE sd.SensorDataID END AS 'SensorDataID',CASE WHEN sd.AlertName IS NULL THEN '' ELSE sd.AlertName END AS 'AlertName',CASE WHEN sd.AlertDateTime IS NULL THEN '-1' ELSE sd.AlertDateTime END AS 'AlertDateTime',CASE WHEN sd.AlertID IS NULL THEN '-1' ELSE sd.AlertID END AS 'AlertID',CASE WHEN sd.AlertPriority IS NUL L THEN '-1' ELSE sd.AlertPriority END AS 'AlertPriority',case WHEN sd.Cleared IS NULL THEN '' ELSE sd.Cleared END AS 'Cleared',CASE WHEN sd.ProtocolID IS NULL THEN '-1' ELSE sd.ProtocolID END AS 'ProtocolID',CASE WHEN sd.SrcAddressInt IS NULL THEN '-1' ELSE sd.SrcAddressInt END AS 'SourceAddressLong',CASE WHEN sd.DestAddressInt IS NULL THEN '-1' ELSE sd.DestAddressInt END AS 'DestAddressLong',CASE WHEN sd.SourcePort IS NULL THEN '-1' ELSE sd.SourcePort END AS 'SourcePort',CASE WHEN sd.ObjectName IS NULL THEN '' ELSE sd.ObjectName END AS 'ObjectName',CASE WHEN sd.SensorName IS NULL THEN '' ELSE sd.SensorName END AS 'SensorName',CASE WHEN sd.SensorAddressInt IS NULL THEN '-1' ELSE sd.SensorAddressInt END AS 'SensorAddressLong',CASE WHEN secchk.ChkName IS NULL THEN '' ELSE secchk.ChkName END AS 'ChkName',CASE WHEN secchk.ChkBriefDesc IS NULL THEN '' ELSE secchk.ChkBriefDesc END AS 'ChkBriefDesc',CASE WHEN secchk.SecChkID IS NULL THEN '-1' ELSE secchk.SecChkID END AS 'SecChkID' FROM SensorData sd INNER JOIN Observances obs ON sd.ObservanceID = obs.ObservanceID INNER JOIN SecurityChecks secchk ON obs.SecChkID = secchk.SecChkID

Optional SQL statements

As well as the mandatory SELECT statement, you can also specify SQL statements for the probe to perform before and after this statement. This processing is optional. You can specify what SQL statements the probe performs using the following properties: