Sending data using JDBC input

Elastic Stack Logstash
1

Hi Specialists!
I'm trying to send data from an JDBC input (SQLServer 2008) to a SYSLOG Server, I don't want the column names...just the result set in line mode. When I specify this , the output is shown in the following way:

ogtrust@relayInhouse-psql:~/logstash$ ./bin/logstash -f sqlserver_logstash.conf
!!! Please upgrade your java version, the current version '1.7.0_25-b15' may cause problems. We recommend a minimum version of 1.7.0_51
Settings: Default pipeline workers: 1
Pipeline main started

2016-08-25T23:04:00.414Z %{host} %{message}
2016-08-25T23:04:00.417Z %{host} %{message}
2016-08-25T23:04:00.418Z %{host} %{message}

but when I put json,json_lines or rubydebug I have the correct output with the field names (I just want the data like an SQL query):

{
"autoid" => 207459905,
"autoguid" => "RRSSDF-2AC1-4659-9943-A984BBFECCC1",
"serverid" => "AAPSOS2",
"detectedutc" => "2015-05-05T23:53:25.000Z",
"sourceip" => 0,
"targetip" => 0,
"targetusername" => "D_ANOTA\\jriusb",
"targetfilename" => "C:\\Users\\jriusb\\AppData\\Local\\MICROSOFT\\Windows\\TEMPORARY INTERNET FILES\\desktop.ini",
"sourcehostname" => "_",
"targethostname" => "ALAVERGA",
"threatcategory" => "hip.file",
"threateventid" => 1095,
"threatseverity" => 5,
"threatname" => Protect me please",
"threatactiontaken" => "would deny read",
"threathandled" => false,
"@version" => "1",
"@timestamp" => "2016-08-25T23:07:00.227Z"
}

The .conf is :

input {
jdbc {
jdbc_driver_library => "/home/conf/sqljdbc_4.2/enu/sqljdbc41.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "deleted"
jdbc_user => "sa"
jdbc_password => ""
schedule => "* * * * *"
tracking_column => AutoID
statement => "select AutoID,AutoGUID,ServerID,DetectedUTC,SourceIPV4 as SourceIP,TargetIPV4 as TargetIP,TargetUserName,TargetFileName,SourceHostName,TargetHostName,ThreatCategory,ThreatEventID,ThreatSeverity,ThreatName,ThreatActionTaken,ThreatHandled from ePO_WIN.dbo.EPOEventsMT;"
}
}
output {
stdout { codec => rubydebug }

}

Thanks in advance!!!

2

Perhaps you're looking for the csv output? The default line codec (resulting in "2016-08-25T23:04:00.414Z %{host} %{message}") doesn't include all field values. You can change the format used but then you have to explicitly list the names of the fields you want.

3

Thanks Magnus for the answer!
I don't want csv output , I need just the raw output (like the codec => lines). Could you please tell me how to change the format ? when I put %{message} the output displays %{message} and not the query output.
Thanks in advance

4

But there isn't any "raw" output. What would that even mean in this case?

when I put %{message} the output displays %{message} and not the query output.

Yes, because the jdbc input doesn't produce any message field.

5

By raw I mean just the values of the fields...
But if jdbc doesn't produce any message field then what is created? How can I manipulate the result set?

Thanks!

6

The jdbc input creates one field for each column in each row of the result set. Logstash has various filters for manipulating field values. If you use a stdout { codec => rubydebug } output you'll see exactly what you get.

7

Thanks Magnus! I could access the parameters!

Best Regards