0 Replies Latest reply on Feb 3, 2016 2:44 AM by rheemskerk

    SIEM Collector SQL Log plugin fails to set the initial bookmark when the SQL query has a WHERE clause

    rheemskerk

      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?