Logstash data unload

Elastic Stack Logstash
1

Hello, I am newbie here.. I have a bunch of ELK indexes that returns below output when querying via POSTMAN GET request.
</>
{
"took": 5,
"timed_out": false,
"_shards": {
"total": 1,
"successful": 1,
"skipped": 0,
"failed": 0
},
"hits": {
"total": {
"value": 1,
"relation": "eq"
},
"max_score": 9.650149,
"hits": [
{
"_index": "inx-mon-2020.11.45.311-v1",
"_type": "_doc",
"_id": "8wxU8uclg3bUmpdjyAIw",
"_score": 9.650149,
"_source": {
"createDate": "2020-11-06T12:00:00.000+1100",
"createdBy": "user1",
"aZoneId": "Zone1",
"itemId": "ITEMXYZ",
"mId": "MU",
"pickQty": 28,
"searchBaseType": "uomSearchable",
"searchType": "uomPickedSearchable",
"domain": {
"version": "2020.1.4"
}
}
}
]
}
}
</>
Then I want the above Elasticsearch output inserted into a table in sql server
Below my ELK config file.
</>
input
{
elasticsearch {
hosts => ["localhost:9200"]
index => "inx-mon-2020.11.45.311-v1"
user => "root"
password => "****"
}
}
output {
jdbc{
driver_jar_path =>"/usr/share/logstash/bin/sqljdbc_7.4/enu/mssql-jdbc-7.4.1.jre11.jar"
connection_string => "jdbc:sqlserver://localhost:1433;databaseName=mydatabase;user=myuser;password=mypass"
statement => ["INSERT into mydatabase.dbo.mytable ([timestamp]
,[elkindex]
,[id]
,[createDate]
,[createdBy]
,[aZoneId]
,[itemId]
,[mId]
,[pickQty]) VALUES(?,?,?,?,?,?,?,?,?)", "@timestamp", "[hits][_index]", "[hits][_id]", "createDate", "createdBy", "aZoneId", "itemId", "mId", "pickQty" ]
}

stdout { codec => rubydebug }
}
</>
All working but I want the "_index" which has the value "inx-mon-2020.11.45.311-v1" captured in the my insert statement under the column elkindex.

How would do this?

I get the below debug info.. See there is no reference to the header data in insert statement, such as below three fields.

"_index": "inx-mon-2020.11.45.311-v1",
"_type": "_doc",
"_id": "8wxU8uclg3bUmpdjyAIw",

INSERT into mytable ([timestamp]\n ,[elkindex]\n ,[id]\n ,[createDate]\n ,[createdBy]\n ,[aZoneId]\n ,[itemId]\n ,[uomId]\n ,[pickQty]) VALUES(?,?,?,?,?,?,?,?,?)", :event=>"{"aZoneId":"Zone1","searchBaseType":"uomSearchable","searchType":"uomPickedSearchable","itemId":"ITEMXYZ","@version":"1","mId":"MU","createdBy":"user1","domain":{"version":"2020.1.4"},"pickQty":28,"createDate":"2020-11-06T12:00:00.000+1100","@timestamp":"2021-08-15T11:48:51.805Z"}"}

Thank you for your reply.

2

Please note, the above config file is for Logstash.

I found a similar issue under the below thread.

I have to now figure out how to create a [@metadata] field and sprintf to at least access the index name. Any pointers would be highly appreciated.

I think I am having the below issue in logstash compared to Postman where it outputs the additional metadata. How would include them in logstash?

3

You should learn a little bit of markdown if you are going to ask for help here. You taking a few minutes to do that saves the people helping you a lot more than a few minutes.

Using multiple logins and replying to yourself is not a good look. It will discourage people from replying, because it looks like spam.

Your links are confusing. Are you talking about creating [@metadata] fields in filebeat or in logstash? sprintf references in logstash are documented here.

4

Thanks Badger, Apologies for multiple logins. I thought the original login wouldn't work as the confirmation email was blocked by the company server earlier. Then ended up creating another one. In the end both got working and seems to have logged in with different tabs of the browser with both logins. Going forward I will be using only one. I am slowly getting my head around this.

It's the logstash fields that aren't visible in the output that I'm after. Specifically the _index, _id fields as in the JSON output above with the tag "hits". Even though those two fields are visible via the GET request, the logstash seems to be dropping them. Thanks again.

5

Please edit your original post. Select the document returned by elasticsearch and click on </> in the toolbar above the edit pane. Then do the same for the logstash configuration, then do the same for the debug info. Make sure you do not have the regular text selected when you do this.

When you click on </> you will see the format change from

input
{
elasticsearch {
hosts => ["localhost:9200"]
index => "inx-mon-2020.11.45.311-v1"

to

input
    {
    elasticsearch {
        hosts => ["localhost:9200"]
        index => "inx-mon-2020.11.45.311-v1"

which is far easier to read.

6

It seems the editing of the original post is disabled. I tried it under my other login too and there is no audit option. Cheers.

7

At the bottom of the post, two icons to the left of Reply, there should be a pencil icon that allows you to edit the post.

8

See the attached file

elasticsearch-post
elasticsearch-post1291×404 46 KB

The pencil icon is missing in the list. It's the same under if I use my other login(onlinekap) which was used to post the original question. But all the subsequent posting under the login: online has the pencil icon.

9

Thank you Badger, The issue is sorted and the answer is too trivial to mention here as its one liner.

10

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