# Logstash create many zero document indexes

**URL:** <https://discuss.elastic.co/t/logstash-create-many-zero-document-indexes/345522>\
**Category:** Logstash\
**Created:** [October 22, 2023, 9:10am UTC](https://discuss.elastic.co/t/logstash-create-many-zero-document-indexes/345522 "2023-10-22T09:10:20Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Amzath\_Khan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/amzath_khan/32/126747_2.png) [@Amzath\_Khan](https://discuss.elastic.co/u/Amzath_Khan)\
**Post date:** [October 22, 2023, 9:10am UTC](https://discuss.elastic.co/t/logstash-create-many-zero-document-indexes/345522/1 "2023-10-22T09:10:20Z")

</div>

I'm sending data from a Microsoft SQL Server database into elasticsearch using logstash 8.x. It functions well. However, logstash multiplies indexes with no documents and raises the shared. It takes over an hour to reach the 3000 total shards.  
To retrieve the data from the SQL Server database, I am using a view. The database field will be used to generate the indexes name.

My Input configuration

```auto
input {
  jdbc {
    jdbc_driver_library => "${jdbc_path}"
    jdbc_driver_class => "${jdbc_driver_class}"
    jdbc_connection_string => "${jdbc_connection_string}"
    jdbc_user => "${jdbc_user}"
    jdbc_password => "${jdbc_pwd}"
    statement_filepath => "/opt/logstash/logstash-jdbc-sqls/view_call/transaction_customers.sql"
    tracking_column => "unix_ts_in_secs"
    use_column_value => true
    tracking_column_type => numeric
    schedule => "*/2 * * * *"
   last_run_metadata_path => "/opt/logstash/metadata/customers.txt"
    jdbc_page_size => 5000

  }
}

```

My output configuration is below -  
The database field "index\_day" (string field) have the format MM-YYYY .

```auto
output {
  elasticsearch {
    hosts => ["${es_node1}", "${es_node2}" , "${es_node3}"]
    index => "customer-%{index_day}"
    ssl_certificate_authorities => "/etc/logstash/certs/ca.crt"
    ssl_enabled => true
    api_key => "${api_key}"
    document_id => "%{document_id}"
    data_stream => false
    doc_as_upsert => true
    template_name => "customer-template"
  }
}

```

As per the documentation the cluster settings action.auto\_create\_index set default to true. i have made some changes to allow only to required formats. It works. but i am not sure how to avoid this zero document indexes with default settings. Logstash logs i have verified i did not get any clue in the debug mode.

I am running 8 different pipeline on the same machine to pull data from different tables at different scheduled times. My total data is about ~50k transactions per day.

Is there a way i can figure out what cause the issues.?

 ![zerodocument](https://us1.discourse-cdn.com/elastic/original/3X/5/b/5bc8453bab6475fe0b63eefa7a63931d7363a7c6.png)

> [@Logstash create many zero document indexes](https://discuss.elastic.co/t/logstash-create-many-zero-document-indexes/345522):
>
> I'm sending data from a Microsoft SQL Server database into elasticsearch using logstash 8.x. It functions well. However, logstash multiplies indexes with no documents and raises the shared. It takes over an hour to reach the 3000 total shards. To retrieve the data from the SQL Server database, I am using a view. The database field will be used to generate the indexes name. My Input configuration input { jdbc { jdbc\_driver\_library =\> "${jdbc\_path}" jdbc\_driver\_class =\> "${jdbc\_driver…

---

<div class="post-metadata">

**Author:** ![PodarcisMuralis](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/podarcismuralis/32/122606_2.png) [@PodarcisMuralis](https://discuss.elastic.co/u/PodarcisMuralis)\
**Post date:** [October 22, 2023, 11:31am UTC](https://discuss.elastic.co/t/logstash-create-many-zero-document-indexes/345522/2 "2023-10-22T11:31:27Z")

</div>

Can you please share your customer-template also?

---

<div class="post-metadata">

**Author:** ![leandrojmp](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/leandrojmp/32/107231_2.png) [@leandrojmp](https://discuss.elastic.co/u/leandrojmp)\
**Post date:** [October 22, 2023, 12:46pm UTC](https://discuss.elastic.co/t/logstash-create-many-zero-document-indexes/345522/3 "2023-10-22T12:46:27Z")

</div>

Could be something else that is creating those index?

The index name does not match the format that you said logstash is using.

You said that the `index_day` is a string with the format `MM-YYYY`, so you would have something like `10-2023` and logstash would create `customer-10-2023`.

But the screenshot you shared does not have this format, it seems to be `customer-EPOCH_TIME`.

Do you haver any `customer-10-2023` indices?

Also, as already asked please share your `customer-template`.

---

<div class="post-metadata">

**Author:** ![Amzath\_Khan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/amzath_khan/32/126747_2.png) [@Amzath\_Khan](https://discuss.elastic.co/u/Amzath_Khan)\
**Post date:** [October 29, 2023, 12:28pm UTC](https://discuss.elastic.co/t/logstash-create-many-zero-document-indexes/345522/4 "2023-10-29T12:28:25Z")

</div>

Sorry for late reply. I figured out the issue. The issue on the view side as it was using \* to select all the fields from the tables. Sometimes the results (values only) shifted in columns and thus logstash was creating the indexes with different values assigned to the index\_day column.  
Solved this issue by removing the wild card "\*" and use require column name from the table

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [November 26, 2023, 12:29pm UTC](https://discuss.elastic.co/t/logstash-create-many-zero-document-indexes/345522/5 "2023-11-26T12:29:04Z")

</div>

Closed on November 26, 2023, 12:29pm UTC

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