Initial JDBC input for a tracking column type of timestamp

Elastic Stack Logstash
1

I am trying to pull data from an MS SQL Server database. The data is refreshed on the hour and I want to pull at 15 minutes after the top of the hour. The input looks like this:

input {
  jdbc {
    jdbc_driver_library => "C:\logstash-7.11.0\drivers\mssql-jdbc-9.2.1.jre8.jar"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    jdbc_connection_string => "jdbc:sqlserver://xxxxx;database=VNAggr;user=dba;password=xxxx;"
    jdbc_user => "dba"
    jdbc_password => "xxxx"
	    jdbc_default_timezone => "UTC"
        tracking_column => "dttm"
		tracking_column_type => "timestamp"
        use_column_value => "true"
        schedule => "30 * * * *"
        last_run_metadata_path => "C:\logstash-7.11.0\inputs\.wan64dttm"	
    statement => "select
 round(Da.inPeak, 2, 1) inpeak
 ,round(Da.outPeak, 2, 1) outpeak
 ,Da.Dttm
 ,Da.inBps
 ,Da.outBps
 ,Da.inErrors
 ,Da.outErrors
 ,Da.inDiscards
 ,Da.outDiscards
 ,Da.inUcastpkts
 ,Da.outUcastpkts
 ,Da.inNUcastpkts
 ,Da.outNUcastpkts 
	  ,Res.displayName
	  ,Dom.DomainName
	  ,ParDom.DomainName
	  ,ResView.devID
	  ,ResView.devName
	  ,ResView.ifSpeed
	  ,ResView.CircuitSpeed1	  
      ,Dev.devType
      ,Dev.devIPString
      ,Dev.devIPDither
      ,Dev.devLastNIP
      ,Dev.devAddrType
      ,Dev.devAddr1
      ,Dev.devAddr2
      ,Dev.devAddrDither
      ,Dev.devCommunity
      ,Dev.devWriteCommunity
      ,Dev.sysDescr
      ,Dev.sysObjectID
      ,Dev.sysLocation
      ,Dev.sysName
      ,Dev.sysContact
      ,Dev.DNSName
      ,Dev.devOrgName
      ,Dev.devTimeZone
      ,Dev.devComment
      ,Dev.devStartTime
      ,Dev.devLastModified
      ,Dev.devStatus

from VNAggr.DBA.wan64HourlyStats Da
  LEFT OUTER JOIN VNAggr.DBA.resTable Res
    ON Da.resID = Res.resID
  LEFT OUTER JOIN VNAggr.DBA.DomainTable Dom 
    ON Res.DomainID = Dom.DomainID  
  LEFT OUTER JOIN VNAggr.DBA.DomainTable ParDom 
    ON Dom.ParentDomainID = ParDom.DomainID
  LEFT OUTER JOIN VNAggr.DBA.ResourceView ResView
    ON Res.resID = ResView.resID
  LEFT OUTER JOIN VNAggr.DBA.devTable Dev
    ON ResView.devID = Dev.devID

where Dttm > :sql_last_value"
    add_field => { "resource_type" => "wan64" }
  }
}

I cannot pull all the data on the initial run because we have a capacity problem with the parent server. We are having to work carefully with the hourly pull. I just need to get the data moving into elastic. My last run file .wan64dttm looks like this:

--- '2021-04-03 15:00:00.000000000 Z'

My starting trials have looked like this:

from VNAggr.DBA.lan64HourlyStats Da
  LEFT OUTER JOIN VNAggr.DBA.resTable Res
    ON Da.resID = Res.resID
  LEFT OUTER JOIN VNAggr.DBA.DomainTable Dom 
    ON Res.DomainID = Dom.DomainID  
  LEFT OUTER JOIN VNAggr.DBA.DomainTable ParDom 
    ON Dom.ParentDomainID = ParDom.DomainID
  LEFT OUTER JOIN VNAggr.DBA.ResourceView ResView
    ON Res.resID = ResView.resID
  LEFT OUTER JOIN VNAggr.DBA.devTable Dev
    ON ResView.devID = Dev.devID
 
where Dttm > 0
[2021-04-02T07:05:07,653][ERROR][logstash.filters.ruby    ][main][99e650e267de0eff640edd10f211eaaa20dacb2d8b4c140a0ca427518732de8f] Ruby exception occurred: undefined method `+' for nil:NilClass
[2021-04-02T07:05:07,675][ERROR][logstash.filters.ruby    ][main][99e650e267de0eff640edd10f211eaaa20dacb2d8b4c140a0ca427518732de8f] Ruby exception occurred: undefined method `+' for nil:NilClass

or this

where Dttm > '1970-01-01T00:00:00.000'
[2021-04-02T07:15:08,388][ERROR][logstash.filters.ruby    ][main][df9781fbe34a0df112fda75edd55a95c87beaad564c1dbeb7b025aa720f141af] Ruby exception occurred: undefined method `+' for nil:NilClass
[2021-04-02T07:15:08,755][ERROR][logstash.filters.ruby    ][main][df9781fbe34a0df112fda75edd55a95c87beaad564c1dbeb7b025aa720f141af] Ruby exception occurred: undefined method `+' for nil:NilClass

It looks like it wants to start at 'zero'. Am I not configuring the .wan64ddtm file properly or is there something else I am overlooking?

2

Sorry I have 30 minutes on the scheduler and said 15 minutes. I've been working with the DBAs on a better time, 30 or 15. Still up in the air.

3

I have worked it out. My initial formatting was proper. It was the parent database. The refresh of the aggregate table is very erratic. I've had to configure around that. Once I got that correct, all is running well. Thanks

4

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.