# Field in MSSQL as ID and the use of versioning

**URL:** <https://discuss.elastic.co/t/field-in-mssql-as-id-and-the-use-of-versioning/332414>\
**Category:** Logstash\
**Created:** [May 3, 2023, 11:36am UTC](https://discuss.elastic.co/t/field-in-mssql-as-id-and-the-use-of-versioning/332414 "2023-05-03T11:36:18Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Ric1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ric1/32/119499_2.png) [@Ric1](https://discuss.elastic.co/u/Ric1)\
**Post date:** [May 3, 2023, 11:36am UTC](https://discuss.elastic.co/t/field-in-mssql-as-id-and-the-use-of-versioning/332414/1 "2023-05-03T11:36:18Z")

</div>

Hello, I hope I've put this in the correct place.

My scenario is:  
I have data accessible via the JDBC input on MSSQL.  
The JDBC input plugin runs a command to find records that match a certain query.  
The output is an Elastic Cloud index.

The records that come in via JDBC come with an ID number and the results contain a large amount of duplicate data as the query regularly matches records that have been updated. Duplicate data being records that have the same ID number.

The ID numbers are not incremental when they are returned.

I'd like to use versioning so that when an ID number that has already been indexed arrives it is treated as a new version. Any ID numbers that arrive that have not been indexed will be indexed as a new document.

I believe I can use Upsert for this. I can't find how to designate the ID number returned from the MSSQL database via JDBC as the ID.

Thanks in advance for any help provided.

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [May 8, 2023, 3:37am UTC](https://discuss.elastic.co/t/field-in-mssql-as-id-and-the-use-of-versioning/332414/2 "2023-05-08T03:37:23Z")

</div>

If you have a unique id on each record then if you set the [document\_id](https://www.elastic.co/guide/en/logstash/current/plugins-outputs-elasticsearch.html#plugins-outputs-elasticsearch-document_id) any new document with the same id will overwrite the document already in elasticsearch. Use a [sprintf reference](https://www.elastic.co/guide/en/logstash/current/event-dependent-configuration.html#sprintf) to set the option to the value of a field.

---

<div class="post-metadata">

**Author:** ![Ric1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ric1/32/119499_2.png) [@Ric1](https://discuss.elastic.co/u/Ric1)\
**Post date:** [May 9, 2023, 6:47pm UTC](https://discuss.elastic.co/t/field-in-mssql-as-id-and-the-use-of-versioning/332414/3 "2023-05-09T18:47:50Z")

</div>

Thanks for the response, @Badger  
That is the behaviour I was expecting too but it isn't working for me. I've pasted my Logstash pipeline code for reference.

```auto
input {
  jdbc {

    jdbc_connection_string => "jdbc:sqlserver://EUdbro.haloitsm.com:7006;databaseName=synergy;encrypt=true;trustServerCertificate=true;"
    jdbc_user => "xxxxxxxxxxxx"
    jdbc_password => "xxxxxxxxxxxx"
    jdbc_driver_library => "/home/ubuntu/Documents/sqljdbc_12.2/enu/mssql-jdbc-12.2.0.jre8.jar"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    statement => "
select
faultid as [Ticket ID],
symptom as [Subject],
uname as [Agent],
aareadesc as [Customer],
sdesc as [Site],
uusername as [User],
rtdesc as [Ticket Type],
tstatusdesc as [Status],
category2 as [Category],
dateoccured as [Date Opened],
case when slaresponsestate='I' then 'Inside'
when slaresponsestate='O' then 'Outside'
when slaresponsestate is null then 'Awaiting Response' end as [Response State],
frespondbydate as [Response Date],
datecleared as [Closed Date]
from faults 
join tstatus on tstatus=status
join requesttype on rtid=requesttypenew 
join area on aarea=areaint
join site on ssitenum=sitenumber
join users on uid=userid
join uname on unum=assignedtoint
WHERE tstatusdesc='Closed'
ORDER BY 'ticket id' ASC 
"
# use_column_value => true
# tracking_column => "ticket id"
# tracking_column_type => numeric
# last_run_metadata_path => "/testdata/test_manual.yml"
# record_last_run => true
    schedule => "*/5 * * * *"
# clean_run => false
  }
}

output {
  elasticsearch {
    hosts => ["https://xxxxxxxxxxxxxx.us-central1.gcp.cloud.es.io:443"]
    user => "xxxxxxx"
    password => "xxxxxxxxxxxxx"    
    index => "shalo-closed-tickets-%{+YYYY.MM}"
    action => "update"
    document_id => "%{ticket id}"
    doc_as_upsert => true
   pipeline => "shalo_tickets"
  }

```

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [May 9, 2023, 7:34pm UTC](https://discuss.elastic.co/t/field-in-mssql-as-id-and-the-use-of-versioning/332414/4 "2023-05-09T19:34:01Z")

</div>

> [@Ric1](#):
>
> `document_id => "%{ticket id}"`

Try `document_id => "%{Ticket ID}"`

---

<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:** [June 6, 2023, 7:34pm UTC](https://discuss.elastic.co/t/field-in-mssql-as-id-and-the-use-of-versioning/332414/5 "2023-06-06T19:34:10Z")

</div>

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