Database Command Filter

Definition

Description

If event matches Condition execute SQL Expression, and set FieldName to its output, FieldName to its error output. Log into DatabaseLogin.

 

 

Comments

If Expression or DatabaseLogin is missing, blank or invalid, the filter will be disabled.

If the first FieldName is missing, blank or invalid, make its default ev:msg.

In the result FieldName , the "pipe" symbol '|' is used to separate fields in a database record, and multiple records are separated by newline characters.

When the SQL command completes, the event is released into the stream after creating the following event fields:

ev:SQLCommand.StartTime = StartingTime
ev:SQLCommand.EndTime = EndingTime
ev:SQLCommand.ExecutionSecs = time in seconds for SQLcommand to execute
ev:SQLCommand.CommandString = the SQL command string that was executed
ev:SQLCommand.ProcessId = process ID, if available

Only one SQL command will be executing at a time. The prior SQL command must complete its execution before the next event can be processed, possibly filling up the incoming event queue if SQL command execution is slower than event arrival.

 

Example

<filter objectId="DbCommand.one" type="DBMSCommandFilter">
	<parameter type="Condition">
		<negatePrimaryCondition>false</negatePrimaryCondition>
		<conditionRelation>Any</conditionRelation>
	</parameter>
	<parameter type=”Expression”>
		<type>String</type>
		<formatString>select * from %1% where host = ‘ %2%'</formatString>
		<formatParmSpec>
			<type>EventType</type>
			<name>ev:msg</name>
		</formatParmSpec>
		<formatParmSpec>
			<type>EventType</type>
			<name>ev:host</name>
		</formatParmSpec>
	</parameter>
	<parameter type="FieldName">ev:msg</parameter>
	<parameter type="TimeInterval">
		<time>10</time>
		<units>min</units>
	</parameter>
</filter>

Event Mapping

Resulting XML:

<event xmlns:ev="http://www.eventgnosis.com/">
	<ev:host>filipov</ev:host>
	<ev:app> select * from hostJZ where event.host = ‘JP'</ev:app>
	<ev:log>SQL Command</ev:log>
	<ev:shellCommand.StartTime> Feb 25 2003 12:34:56 </ev:shellCommand.StartTime >
	<ev:shellCommand.EndTime> Feb 20, 2003 13:45:53 </ev:shellCommand.EndTime >
	<ev:shellCommand.ExecutionSecs>13</ev:shellCommand.ExecutionSecs>
	<ev:shellCommand.CommandString>select * from hostJZ where event.host = ‘JP'</ev:shellCommand.CommandString>
	<ev:shellCommand.ProcessId>32786</ev:shellCommand.ProcessId>
	<ev:shellCommand.ErrorStatus>-32</ev:shellCommand.ErrorStatus>
	<ev:shellCommand.ExitCode>1</ev:shellCommand.ExitCode>
	<ev:protocol>SQL Command Log</ev:protocol>
	<ev:msg>hostJZ.txt</ev:msg>
</event>