SIEM Collector SQL Log plugin fails to set the initial bookmark when the SQL query has a WHERE clause.
I’m using the SIEM collector to get logging from a SQL Server database. For one of the queries I need a WHERE clause in the SQL statement. The Query from the xml config looks like this:
<Query>SELECT [dbo].[tblLogs].[strDateTimeUTC] AS strDateTimeUTC,[dbo].[tblLogs].[strComputerName] AS strComputerName,[dbo].[tblLogs].[strComputerIP] AS strComputerIP, [dbo].[tblLogs].[strUser] AS strUser FROM [dbo].[tblLogs] WHERE lngClassId = 23</Query>
When the plugins runs for the first time it (or there is no bookmark file) it determines the bookmark value. The BookmarkDBField in the xml config file looks like this:
<BookmarkDBField CompleteBookmarkFieldName="[dbo].[tblLogs].[strDateTimeUTC]" BookmarkFieldName="strDateTimeUTC" WhereBy="Complete" OrderBy="Complete" />
The SQL query to determine to bookmark value (from the debug logfile) looks like this:
Max Query is: select max([strDateTimeUTC]) from [dbo].[tblLogs]
It does not take the WHERE clause into account so it sets the wrong bookmark value.
As a workaround I created the bookmark file manually. The plugin works fine that way.
Is there a better solution to this? Maybe in the WhereBy attribute of the BookmarkDBField element?