Logstash JDBC Input not working, no error or output

Elastic Stack Logstash
1

Hi,
I have some problem with JDBC input plugin. Database connection seem to work (no error), but I don´t get any output.

In the Logstash logfile I only get the SQL Statement, but no error or output.

The logstash log:

[INFO ][logstash.inputs.jdbc     ][main][ea6df597b8caf94d9db17081caab76ecd7a3471cc6eb6c6e1121a5d08b9daeb9] (0.004006s) Select TOP 800 dbo.tblAssets.AssetID, dbo.tblAssets.AssetName, dbo.tsysAssetTypes.AssetTypename As Type, dbo.tsysAssetTypes.AssetTypeIcon10 As icon, dbo.tblAssets.IPAddress, dbo.tblAssetCustom.Manufacturer, dbo.tblAssetCustom.Model, dbo.tblAssets.Domain, Case dbo.tsysAssetTypes.AssetTypename When 'Windows' Then dbo.tsysOS.OSname When 'Linux' Then dbo.tblLinuxSystem.OSRelease When 'Apple Mac' Then dbo.tblMacOSInfo.KernelVersion Else '' End As OS, dbo.tblAssets.Mac As [MAC Address], dbo.tblADComputers.OU, dbo.tblState.Statename, dbo.tblAssets.Description, dbo.tblAssetCustom.PurchaseDate, dbo.tblAssetCustom.Warrantydate, dbo.tblAssets.FQDN, dbo.tblAssetCustom.DNSName, dbo.tblAssetCustom.LastPatched, dbo.tblAssetCustom.LastFullbackup, dbo.tblAssetCustom.LastFullimage, dbo.tblAssetCustom.Location, dbo.tblAssetCustom.Building, dbo.tblAssetCustom.Department, dbo.tblAssetCustom.Branchoffice, dbo.tblAssetCustom.BarCode, dbo.tblAssetCustom.Contact, dbo.tblAssetCustom.Serialnumber, dbo.tblAssetCustom.OrderNumber, dbo.tblAssetCustom.Custom1, dbo.tblAssetCustom.Custom2, dbo.tblAssetCustom.Custom3, dbo.tblAssetCustom.Custom4, dbo.tblAssetCustom.Custom5, dbo.tblAssetCustom.Custom6, dbo.tblAssetCustom.Custom7, dbo.tblAssetCustom.Custom8, dbo.tblAssetCustom.Custom9, dbo.tblAssetCustom.Custom10, dbo.tblAssetCustom.Custom11, dbo.tblAssetCustom.Custom12, dbo.tblAssetCustom.Custom13, dbo.tblAssetCustom.Custom14, dbo.tblAssetCustom.Custom15, dbo.tblAssetCustom.Custom16, dbo.tblAssetCustom.Custom17, dbo.tblAssetCustom.Custom18, dbo.tblAssetCustom.Custom19, dbo.tblAssetCustom.Custom20, dbo.tblAssets.Firstseen, dbo.tblAssets.Lastseen, dbo.tblAssets.Lasttried From dbo.tblAssets Left Join dbo.tblAssetRelations On dbo.tblAssetRelations.ChildAssetID = dbo.tblAssets.AssetID Left Join dbo.tsysAssetRelationTypes On dbo.tsysAssetRelationTypes.RelationTypeID = dbo.tblAssetRelations.Type Inner Join dbo.tblAssetCustom On dbo.tblAssets.AssetID = dbo.tblAssetCustom.AssetID Inner Join dbo.tsysAssetTypes On dbo.tsysAssetTypes.AssetType = dbo.tblAssets.Assettype Left Join dbo.tblOperatingsystem On dbo.tblAssets.AssetID = dbo.tblOperatingsystem.AssetID Left Join dbo.tblADComputers On dbo.tblAssets.AssetID = dbo.tblADComputers.AssetID Inner Join dbo.tblState On dbo.tblState.State = dbo.tblAssetCustom.State Left Join dbo.tblLinuxSystem On dbo.tblAssets.AssetID = dbo.tblLinuxSystem.AssetID Left Join dbo.tsysOS On dbo.tblAssets.OScode = dbo.tsysOS.OScode Left Join dbo.tblMacOSInfo On dbo.tblAssets.AssetID = dbo.tblMacOSInfo.AssetID Order By dbo.tblAssets.AssetName

Thats the logstash config:

input {
  jdbc {
    jdbc_driver_library => "/etc/logstash/jdbc/mssql-jdbc-7.2.2.jre11.jar"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    jdbc_connection_string => "jdbc:sqlserver://server3535:1433;databasename=sqldb;integratedSecurity=false;encrypt=false;"
    jdbc_validate_connection => "true"
    jdbc_user => "sqluser"
    jdbc_password => "sqlpassword"
    type => "jdbc"
    schedule => "*/3 * * * *"
    statement => "Select TOP 800 dbo.tblAssets.AssetID, dbo.tblAssets.AssetName, dbo.tsysAssetTypes.AssetTypename As Type, dbo.tsysAssetTypes.AssetTypeIcon10 As icon, dbo.tblAssets.IPAddress, dbo.tblAssetCustom.Manufacturer, dbo.tblAssetCustom.Model, dbo.tblAssets.Domain, Case dbo.tsysAssetTypes.AssetTypename When 'Windows' Then dbo.tsysOS.OSname When 'Linux' Then dbo.tblLinuxSystem.OSRelease When 'Apple Mac' Then dbo.tblMacOSInfo.KernelVersion Else '' End As OS, dbo.tblAssets.Mac As [MAC Address], dbo.tblADComputers.OU, dbo.tblState.Statename, dbo.tblAssets.Description, dbo.tblAssetCustom.PurchaseDate, dbo.tblAssetCustom.Warrantydate, dbo.tblAssets.FQDN, dbo.tblAssetCustom.DNSName, dbo.tblAssetCustom.LastPatched, dbo.tblAssetCustom.LastFullbackup, dbo.tblAssetCustom.LastFullimage, ..............."
  }
}
output {
 if [type] == "jdbc" {
  #elasticsearch {
   #hosts => "http://elasticserver:9200"
   #index => "jdbc-%{+YYYY.MM.dd}"
   #manage_template => false
  #}
  file {
   path => "/tmp/jdbc.log"
  }
 }
}

I have also tried with "statement_filepath", but it´s also not working. Same as with "statement", no error or output to file.

2

Have you tried a simple SQL statement? SELECT TOP 1 * FROM tblAssets?

Have you seen an ERROR or WARN in your logs before? Check log levels.

3

I have tried with SELECT TOP 5 FROM dbo.tblAssets it´s working.

Now I have tried with SQL Debug level and I get this error:

[ERROR][logstash.inputs.jdbc     ][main][54839a319490f9a0880e8b3db25cd7c7a0b480f99cf80b3a28868a59071c33a0] Java::ComMicrosoftSqlserverJdbc::SQLServerException: The identifier that starts with 'SELECT TOP 5000 dbo.tblAssets.AssetID, dbo.tblAssets.AssetName, dbo.tsysAssetTypes.AssetTypename As Type, dbo.tsysAssetTypes.Ass' is too long. Maximum length is 128.: "SELECT TOP 5000 dbo.tblAssets.AssetID, dbo.tblAssets.AssetName, dbo.tsysAssetTypes.AssetTypename As Type, dbo.tsysAssetTypes.AssetTypeIcon10 As icon, dbo.tblAssets.IPAddress, dbo.tblAssetCustom.Manufacturer,....

The identifier that starts with 'SELECT TOP 5000 dbo.tblAssets.AssetID, dbo.tblAssets.AssetName, dbo.tsysAssetTypes.AssetTypename As Type, dbo.tsysAssetTypes.Ass' is too long. Maximum length is 128.

So the SQL Statement is too long? But it´s working fine if I use the statement with a SQL tool connected remotely to the SQL Server.

4

I've never cracked the code on long complex SQL statements. Maybe someone else can jump in.

What I've always ended up doing is doing CTE's vs joins and it always seems to work. But I am not sure why.

You could also try to move this to a stored procedure and just execute that vs running the query.

5

I´ve created a stored procedure, but still not working. Do I need to configure "statement" in logstash.conf to use stored procedure?

6

I really don´t understand it.

This simple statement without "sql joins" is working.

[DEBUG] 2020-09-07 12:12:00.979 [Ruby-0-Thread-23: :1] jdbc - (0.001491s) SELECT TOP (1) count(*) AS [COUNT] FROM (SELECT TOP 5 dbo.tblAssets.AssetName FROM dbo.tblAssets) AS [T1]
[DEBUG] 2020-09-07 12:12:00.990 [Ruby-0-Thread-23: :1] jdbc - (0.000792s) SELECT TOP (1) count(*) AS [COUNT] FROM (SELECT TOP 5 dbo.tblAssets.AssetName FROM dbo.tblAssets) AS [T1]
[DEBUG] 2020-09-07 12:12:00.991 [Ruby-0-Thread-23: :1] jdbc - Executing JDBC query {:statement=>"SELECT TOP 5 dbo.tblAssets.AssetName FROM dbo.tblAssets", :parameters=>{:sql_last_value=>2020-09-07 10:09:00 UTC}, :count=>5}
[DEBUG] 2020-09-07 12:12:00.997 [Ruby-0-Thread-23: :1] jdbc - (0.000699s) SELECT TOP 5 dbo.tblAssets.AssetName FROM dbo.tblAssets
[DEBUG] 2020-09-07 12:12:01.277 [[main]>worker1] file - File, writing event to file. {:filename=>"/tmp/jdbc-query.debug"}
[DEBUG] 2020-09-07 12:12:01.277 [[main]>worker0] file - File, writing event to file. {:filename=>"/tmp/jdbc-query.debug"}
[DEBUG] 2020-09-07 12:12:01.278 [[main]>worker0] file - File, writing event to file. {:filename=>"/tmp/jdbc-query.debug"}
[DEBUG] 2020-09-07 12:12:01.280 [[main]>worker3] file - File, writing event to file. {:filename=>"/tmp/jdbc-query.debug"}
[DEBUG] 2020-09-07 12:12:01.283 [[main]>worker2] file - File, writing event to file. {:filename=>"/tmp/jdbc-query.debug"}
[INFO ] 2020-09-07 12:12:01.285 [[main]>worker0] file - Opening file {:path=>"/tmp/jdbc-query.debug"}
[DEBUG] 2020-09-07 12:12:01.852 [Ruby-0-Thread-7: :1] file - Starting flush cycle
[DEBUG] 2020-09-07 12:12:01.852 [Ruby-0-Thread-7: :1] file - Flushing file {:path=>"/tmp/jdbc-query.debug", :fd=>#<IOWriter:0x16d2bc4 @active=true, @io=#<File:/tmp/jdbc-query.debug>>}

And this one with joins don´t work. But I don´t get any error message.

[DEBUG] 2020-09-07 12:09:00.819 [Ruby-0-Thread-23: :1] jdbc - (0.001437s) SELECT TOP (1) count(*) AS [COUNT] FROM (SELECT TOP 1 dbo.tblAssets.AssetID, dbo.tblAssets.AssetName, dbo.tsysAssetTypes.AssetTypename As Type, dbo.tblAssets.IPAddress, dbo.tblAssetCustom.Manufacturer, dbo.tblAssetCustom.Model, dbo.tblAssets.Domain, Case dbo.tsysAssetTypes.AssetTypename When 'Windows' Then dbo.tsysOS.OSname When 'Linux' Then dbo.tblLinuxSystem.OSRelease When 'Apple Mac' Then dbo.tblMacOSInfo.KernelVersion Else '' End As OS, dbo.tblAssets.Mac As MAC_Address, dbo.tblADComputers.OU, dbo.tblState.Statename, dbo.tblAssets.Description, dbo.tblAssetCustom.PurchaseDate, dbo.tblAssetCustom.Warrantydate, dbo.tblAssets.FQDN, dbo.tblAssetCustom.DNSName, dbo.tblAssetCustom.LastPatched, dbo.tblAssetCustom.LastFullbackup, dbo.tblAssetCustom.LastFullimage, dbo.tblAssetCustom.Location, dbo.tblAssetCustom.Building, dbo.tblAssetCustom.Department, dbo.tblAssetCustom.Branchoffice, dbo.tblAssetCustom.BarCode, dbo.tblAssetCustom.Contact, dbo.tblAssetCustom.Serialnumber, dbo.tblAssetCustom.OrderNumber, dbo.tblAssets.Firstseen, dbo.tblAssets.Lastseen, dbo.tblAssets.Lasttried From dbo.tblAssets Left Join dbo.tblAssetRelations On dbo.tblAssetRelations.ChildAssetID = dbo.tblAssets.AssetID Left Join dbo.tsysAssetRelationTypes On dbo.tsysAssetRelationTypes.RelationTypeID = dbo.tblAssetRelations.Type Inner Join dbo.tblAssetCustom On dbo.tblAssets.AssetID = dbo.tblAssetCustom.AssetID Inner Join dbo.tsysAssetTypes On dbo.tsysAssetTypes.AssetType = dbo.tblAssets.Assettype Left Join dbo.tblOperatingsystem On dbo.tblAssets.AssetID = dbo.tblOperatingsystem.AssetID Left Join dbo.tblADComputers On dbo.tblAssets.AssetID = dbo.tblADComputers.AssetID Inner Join dbo.tblState On dbo.tblState.State = dbo.tblAssetCustom.State Left Join dbo.tblLinuxSystem On dbo.tblAssets.AssetID = dbo.tblLinuxSystem.AssetID Left Join dbo.tsysOS On dbo.tblAssets.OScode = dbo.tsysOS.OScode Left Join dbo.tblMacOSInfo On dbo.tblAssets.AssetID = dbo.tblMacOSInfo.AssetID Order By dbo.tblAssets.AssetName) AS [T1]
[DEBUG] 2020-09-07 12:09:00.868 [pool-3-thread-1] jvm - collector name {:name=>"ParNew"}
[DEBUG] 2020-09-07 12:09:00.868 [pool-3-thread-1] jvm - collector name {:name=>"ConcurrentMarkSweep"}
[DEBUG] 2020-09-07 12:09:00.869 [Ruby-0-Thread-23: :1] jdbc - (0.003633s) SELECT TOP (1) count(*) AS [COUNT] FROM (SELECT TOP 1 dbo.tblAssets.AssetID, dbo.tblAssets.AssetName, dbo.tsysAssetTypes.AssetTypename As Type, dbo.tblAssets.IPAddress, dbo.tblAssetCustom.Manufacturer, dbo.tblAssetCustom.Model, dbo.tblAssets.Domain, Case dbo.tsysAssetTypes.AssetTypename When 'Windows' Then dbo.tsysOS.OSname When 'Linux' Then dbo.tblLinuxSystem.OSRelease When 'Apple Mac' Then dbo.tblMacOSInfo.KernelVersion Else '' End As OS, dbo.tblAssets.Mac As MAC_Address, dbo.tblADComputers.OU, dbo.tblState.Statename, dbo.tblAssets.Description, dbo.tblAssetCustom.PurchaseDate, dbo.tblAssetCustom.Warrantydate, dbo.tblAssets.FQDN, dbo.tblAssetCustom.DNSName, dbo.tblAssetCustom.LastPatched, dbo.tblAssetCustom.LastFullbackup, dbo.tblAssetCustom.LastFullimage, dbo.tblAssetCustom.Location, dbo.tblAssetCustom.Building, dbo.tblAssetCustom.Department, dbo.tblAssetCustom.Branchoffice, dbo.tblAssetCustom.BarCode, dbo.tblAssetCustom.Contact, dbo.tblAssetCustom.Serialnumber, dbo.tblAssetCustom.OrderNumber, dbo.tblAssets.Firstseen, dbo.tblAssets.Lastseen, dbo.tblAssets.Lasttried From dbo.tblAssets Left Join dbo.tblAssetRelations On dbo.tblAssetRelations.ChildAssetID = dbo.tblAssets.AssetID Left Join dbo.tsysAssetRelationTypes On dbo.tsysAssetRelationTypes.RelationTypeID = dbo.tblAssetRelations.Type Inner Join dbo.tblAssetCustom On dbo.tblAssets.AssetID = dbo.tblAssetCustom.AssetID Inner Join dbo.tsysAssetTypes On dbo.tsysAssetTypes.AssetType = dbo.tblAssets.Assettype Left Join dbo.tblOperatingsystem On dbo.tblAssets.AssetID = dbo.tblOperatingsystem.AssetID Left Join dbo.tblADComputers On dbo.tblAssets.AssetID = dbo.tblADComputers.AssetID Inner Join dbo.tblState On dbo.tblState.State = dbo.tblAssetCustom.State Left Join dbo.tblLinuxSystem On dbo.tblAssets.AssetID = dbo.tblLinuxSystem.AssetID Left Join dbo.tsysOS On dbo.tblAssets.OScode = dbo.tsysOS.OScode Left Join dbo.tblMacOSInfo On dbo.tblAssets.AssetID = dbo.tblMacOSInfo.AssetID Order By dbo.tblAssets.AssetName) AS [T1]
[DEBUG] 2020-09-07 12:09:00.871 [Ruby-0-Thread-23: :1] jdbc - Executing JDBC query {:statement=>"SELECT TOP 1 dbo.tblAssets.AssetID, dbo.tblAssets.AssetName, dbo.tsysAssetTypes.AssetTypename As Type, dbo.tblAssets.IPAddress, dbo.tblAssetCustom.Manufacturer, dbo.tblAssetCustom.Model, dbo.tblAssets.Domain, Case dbo.tsysAssetTypes.AssetTypename When 'Windows' Then dbo.tsysOS.OSname When 'Linux' Then dbo.tblLinuxSystem.OSRelease When 'Apple Mac' Then dbo.tblMacOSInfo.KernelVersion Else '' End As OS, dbo.tblAssets.Mac As MAC_Address, dbo.tblADComputers.OU, dbo.tblState.Statename, dbo.tblAssets.Description, dbo.tblAssetCustom.PurchaseDate, dbo.tblAssetCustom.Warrantydate, dbo.tblAssets.FQDN, dbo.tblAssetCustom.DNSName, dbo.tblAssetCustom.LastPatched, dbo.tblAssetCustom.LastFullbackup, dbo.tblAssetCustom.LastFullimage, dbo.tblAssetCustom.Location, dbo.tblAssetCustom.Building, dbo.tblAssetCustom.Department, dbo.tblAssetCustom.Branchoffice, dbo.tblAssetCustom.BarCode, dbo.tblAssetCustom.Contact, dbo.tblAssetCustom.Serialnumber, dbo.tblAssetCustom.OrderNumber, dbo.tblAssets.Firstseen, dbo.tblAssets.Lastseen, dbo.tblAssets.Lasttried From dbo.tblAssets Left Join dbo.tblAssetRelations On dbo.tblAssetRelations.ChildAssetID = dbo.tblAssets.AssetID Left Join dbo.tsysAssetRelationTypes On dbo.tsysAssetRelationTypes.RelationTypeID = dbo.tblAssetRelations.Type Inner Join dbo.tblAssetCustom On dbo.tblAssets.AssetID = dbo.tblAssetCustom.AssetID Inner Join dbo.tsysAssetTypes On dbo.tsysAssetTypes.AssetType = dbo.tblAssets.Assettype Left Join dbo.tblOperatingsystem On dbo.tblAssets.AssetID = dbo.tblOperatingsystem.AssetID Left Join dbo.tblADComputers On dbo.tblAssets.AssetID = dbo.tblADComputers.AssetID Inner Join dbo.tblState On dbo.tblState.State = dbo.tblAssetCustom.State Left Join dbo.tblLinuxSystem On dbo.tblAssets.AssetID = dbo.tblLinuxSystem.AssetID Left Join dbo.tsysOS On dbo.tblAssets.OScode = dbo.tsysOS.OScode Left Join dbo.tblMacOSInfo On dbo.tblAssets.AssetID = dbo.tblMacOSInfo.AssetID Order By dbo.tblAssets.AssetName", :parameters=>{:sql_last_value=>2020-09-07 10:03:00 UTC}, :count=>1}
[DEBUG] 2020-09-07 12:09:00.876 [Ruby-0-Thread-23: :1] jdbc - (0.001505s) SELECT TOP 1 dbo.tblAssets.AssetID, dbo.tblAssets.AssetName, dbo.tsysAssetTypes.AssetTypename As Type, dbo.tblAssets.IPAddress, dbo.tblAssetCustom.Manufacturer, dbo.tblAssetCustom.Model, dbo.tblAssets.Domain, Case dbo.tsysAssetTypes.AssetTypename When 'Windows' Then dbo.tsysOS.OSname When 'Linux' Then dbo.tblLinuxSystem.OSRelease When 'Apple Mac' Then dbo.tblMacOSInfo.KernelVersion Else '' End As OS, dbo.tblAssets.Mac As MAC_Address, dbo.tblADComputers.OU, dbo.tblState.Statename, dbo.tblAssets.Description, dbo.tblAssetCustom.PurchaseDate, dbo.tblAssetCustom.Warrantydate, dbo.tblAssets.FQDN, dbo.tblAssetCustom.DNSName, dbo.tblAssetCustom.LastPatched, dbo.tblAssetCustom.LastFullbackup, dbo.tblAssetCustom.LastFullimage, dbo.tblAssetCustom.Location, dbo.tblAssetCustom.Building, dbo.tblAssetCustom.Department, dbo.tblAssetCustom.Branchoffice, dbo.tblAssetCustom.BarCode, dbo.tblAssetCustom.Contact, dbo.tblAssetCustom.Serialnumber, dbo.tblAssetCustom.OrderNumber, dbo.tblAssets.Firstseen, dbo.tblAssets.Lastseen, dbo.tblAssets.Lasttried From dbo.tblAssets Left Join dbo.tblAssetRelations On dbo.tblAssetRelations.ChildAssetID = dbo.tblAssets.AssetID Left Join dbo.tsysAssetRelationTypes On dbo.tsysAssetRelationTypes.RelationTypeID = dbo.tblAssetRelations.Type Inner Join dbo.tblAssetCustom On dbo.tblAssets.AssetID = dbo.tblAssetCustom.AssetID Inner Join dbo.tsysAssetTypes On dbo.tsysAssetTypes.AssetType = dbo.tblAssets.Assettype Left Join dbo.tblOperatingsystem On dbo.tblAssets.AssetID = dbo.tblOperatingsystem.AssetID Left Join dbo.tblADComputers On dbo.tblAssets.AssetID = dbo.tblADComputers.AssetID Inner Join dbo.tblState On dbo.tblState.State = dbo.tblAssetCustom.State Left Join dbo.tblLinuxSystem On dbo.tblAssets.AssetID = dbo.tblLinuxSystem.AssetID Left Join dbo.tsysOS On dbo.tblAssets.OScode = dbo.tsysOS.OScode Left Join dbo.tblMacOSInfo On dbo.tblAssets.AssetID = dbo.tblMacOSInfo.AssetID Order By dbo.tblAssets.AssetName

The simple statement writes events to the file defined in logstash output, but the other statement doesn´t and also doesn´t show any error!?

7

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