Morning all
I have a simple table
CREATE TABLE [dbo].[DataAuditTable](
[Id] [int] IDENTITY(1,1) NOT NULL,
[SystemAuditId] [int] NOT NULL,
[RevisionStamp] [datetime] NOT NULL,
[TableName] nvarchar NOT NULL,
[Action] nvarchar NOT NULL,
[Mappings] nvarchar NULL,
[UseCustomEncryption] [bit] NOT NULL,
[DataVersion] [tinyint] NOT NULL
)
Which I am trying to index in ElasticSearch. My aim is to use the DataChanged column which contains JSON data in Elasticsearch.
My config file looks like;
input
{
# Define DataAudit input
jdbc
{
# Set JDBC Options
jdbc_driver_library => "C:\ELK\JDBC\sqljdbc_6.0\enu\sqljdbc42.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://dev_sandpit\autotesting;databaseName=INRstarAudit;selectMethod=cursor"
jdbc_user => "auditreader"
jdbc_password => "auditreader"
# Persistence settings
use_column_value => true
tracking_column => id
record_last_run => true
last_run_metadata_path => "c:\ELK\LogStash\logstash-5.0.0\persistence\LastId_DataAudit"
# Schedule (cron-like)
schedule => "* * * * *"
# type
type => "DataAudit"
# Query
statement => "SELECT Id, DataChanged,LEN(Datachanged) as Length FROM [INRstarAudit].[dbo].[DataAudit] WHERE Id > :sql_last_value"
}
}
#filter
#{
json
{
source => "DataChanged "
target => "DataChanged_nojson"
}
mutate
{
remove_field => "patient"
}
#}
output
{
file {
path => "c:\elk\temp\test.out"
}
elasticsearch
{
index => "audit"
document_type => "%{type}"
document_id => "%{id}"
hosts => "localhost:9200"
}
}
When I run the query in RazorSQL using the same jdbc driver it works ok, but in Logstash I get the output;
input
{
# Define DataAudit input
jdbc
{
# Set JDBC Options
jdbc_driver_library => "C:\ELK\JDBC\sqljdbc_6.0\enu\sqljdbc42.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://dev_sandpit\autotesting;databaseName=INRstarAudit;selectMethod=cursor"
jdbc_user => "auditreader"
jdbc_password => "auditreader"
# Persistence settings
use_column_value => true
tracking_column => id
record_last_run => true
last_run_metadata_path => "c:\ELK\LogStash\logstash-5.0.0\persistence\LastId_DataAudit"
# Schedule (cron-like)
schedule => "* * * * *"
# type
type => "DataAudit"
# Query
statement => "SELECT Id, DataChanged,LEN(Datachanged) as Length FROM [INRstarAudit].[dbo].[DataAudit] WHERE Id > :sql_last_value"
}
}
#filter
#{
json
{
source => "DataChanged "
target => "DataChanged_nojson"
}
mutate
{
remove_field => "patient"
}
#}
output
{
file {
path => "c:\elk\temp\test.out"
}
elasticsearch
{
index => "audit"
document_type => "%{type}"
document_id => "%{id}"
hosts => "localhost:9200"
}
}
Any ideas why I am getting Nil?