JDBC Streaming

Elastic Stack Logstash
1

I am trying to use the jdbc streaming plugin to add data to metricbeats logs that are coming in.
But I am getting an error that I do not understand. If I take the JDBC part out it works fine.
I can connect to the database fine and the query works.
Not sure what I am doing wrong.

input {
  beats {
    port => 5044
  }
}
filter  {
## This will query local lookup to get the site information
## Setup JDBC connection
jdbc_streaming {
    jdbc_driver_library => "C:\logstash-7.0.0\bin\sqljdbc42.jar"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    jdbc_connection_string => "jdbc:sqlserver://ltgsql.database.windows.net;databaseName=RHA;user=----;password=---;"
    jdbc_user => "---"
    jdbc_password => "---"
	statement => "select SiteName,Latitude,Longitude,HostedMachineName FROM dbo.Session_Key WHERE HostedMachineName = :code"
	parameters => { "code" => "%{[host][name]}"}
	target => "site_data"
	
}

## This will get the perfmon name and add it to a new field
	if ![windows][perfmon][ica][name] {
		mutate { 
			add_field => { "userid" => "%{[system][process][username]}"}
		}
		mutate {
			gsub => [ "userid", "[\\]", "" ]
			gsub => [ "userid", "RHA", "" ]
		}
	}
	else {
		mutate { 
			add_field => { "userid" => "%{[windows][perfmon][ica][name]}"}
		}
		mutate { 
			gsub => [ "userid", ".*\((.*)\)", "\1" ] 
		}
		mutate {
		 lowercase => [ "userid" ]
		}
	}

## This will get the beat hostname and add it to the a new field
	if ![beat][hostname] {
		mutate { 
			add_field => { "Mhost" => ""}
		}
	}
	else {
		mutate { 
			add_field => { "Mhost" => "%{[beat][hostname]}"}
		}
	}
}

output {
  stdout {codec => rubydebug }
}

this is the error:

Sending Logstash logs to C:/logstash-7.0.0/logs which is now configured via log4j2.properties
[2019-06-12T13:20:09,144][WARN ][logstash.config.source.multilocal] Ignoring the 'pipelines.yml' file because modules or command line options are specified
[2019-06-12T13:20:09,160][INFO ][logstash.runner          ] Starting Logstash {"logstash.version"=>"7.0.0"}
Jun 12, 2019 1:20:38 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 ClientConnectionId: 80d1c512-65b4-48b4-8a7d-93373c6ee561 Prelogin error: host ltgsql.database.windows.net port 1433 Error reading prelogin response: Read timed out ClientConnectionId
:80d1c512-65b4-48b4-8a7d-93373c6ee561
Jun 12, 2019 1:20:57 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:2 ClientConnectionId: 9f211bef-8759-400e-a270-90f4f6f77071 Prelogin error: host ltgsql.database.windows.net port 1433 Error reading prelogin response: Read timed out ClientConnectionId
:9f211bef-8759-400e-a270-90f4f6f77071
[2019-06-12T13:20:57,792][ERROR][logstash.javapipeline    ] Pipeline aborted due to error {:pipeline_id=>"main", :exception=>#<Sequel::DatabaseConnectionError: Java::ComMicrosoftSqlserverJdbc::SQLServerExce
ption: Read timed out ClientConnectionId:9f211bef-8759-400e-a270-90f4f6f77071>, :backtrace=>["com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(com/microsoft/sqlserver/jdbc/SQLServerConnection.java
:1749)", "com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(com/microsoft/sqlserver/jdbc/SQLServerConnection.java:1733)",
2

The error coming back from your SQLServer is that the read took too long and timed out, and before that error is emitted there are additional bytes written by the driver itself that it's also timing out while attempting what it calls a "prelogin":

Prelogin error: host ltgsql.database.windows.net port 1433 Error reading prelogin response: Read timed out

My best guess is that your Logstash node doesn't have a route to the target host. Maybe a firewall or VPN issue?

3

Thanks there was a firewall rule that I had missed.

1 Like
4

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