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?