# Using sql server queries in elasticsearch 5.6.3

**URL:** <https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027>\
**Category:** Logstash\
**Created:** [October 15, 2017, 3:23pm UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027 "2017-10-15T15:23:30Z")\
**Posts on this page:** 20\
**Page:** 2

<div class="post-metadata">

**Author:** ![parsa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/parsa/32/23227_2.png) [@parsa](https://discuss.elastic.co/u/parsa)\
**Post date:** [October 20, 2017, 8:39am UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/21 "2017-10-20T08:39:58Z")

</div>

I want to speak with details.  
in another table of mine with "Servers" name I have this column names:

> Id,ServerName,Action,IP,IPValid,DNSName,...

I want to search all fields of table where servername like server1  
so as your recommendation I config the output as below :

```
    index => servers
    document_type => server
    document_id => "%{servername}"

```

with calling `http://localhost:9200/servers/servername/server1`  
the results is:

`{"_index":"servers","_type":"servername","_id":"server1","found":false}`

and with calling `http://localhost:9200/servers`  
the results is like:

> ```
> "Server1-4": {
> "properties": {
> "@timestamp": {
> "type": "date"
> },
> "@version": {
> "type": "text",
> "fields": {
> "keyword": {
> "type": "keyword",
> "ignore_above": 256
> }
> }
> },
> "action": {
> "type": "text",
> "fields": {
> "keyword": {
> "type": "keyword",
> "ignore_above": 256
> }
> }
> },
> "description": {
> "type": "text",
> "fields": {
> "keyword": {
> "type": "keyword",
> "ignore_above": 256
> }
> }
> },
> "dnsname": {
> "type": "text",
> "fields": {
> "keyword": {
> "type": "keyword",
> "ignore_above": 256
> }
> }
> },
> "id": {
> "type": "long"
> },
> "ip": {
> "type": "text",
> "fields": {
> "keyword": {
> "type": "keyword",
> "ignore_above": 256
> }
> }
> },
> "ipvalid": {
> "type": "text",
> "fields": {
> "keyword": {
> "type": "keyword",
> "ignore_above": 256
> }
> }
> },
> "location": {
> "type": "text",
> "fields": {
> "keyword": {
> "type": "keyword",
> "ignore_above": 256
> }
> }
> },
> "port_number": {
> "type": "text",
> "fields": {
> "keyword": {
> "type": "keyword",
> "ignore_above": 256
> }
> }
> },
> "processcount": {
> "type": "long"
> },
> "processsize": {
> "type": "long"
> },
> "servername": {
> "type": "text",
> "fields": {
> "keyword": {
> "type": "keyword",
> "ignore_above": 256
> }
> }
> },
> "switch_device": {
> "type": "text",
> "fields": {
> "keyword": {
> "type": "keyword",
> "ignore_above": 256
> }
> }
> },
> "upload": {
> "type": "boolean"
> },
> "webservice": {
> "type": "text",
> "fields": {
> "keyword": {
> "type": "keyword",
> "ignore_above": 256
> }
> }
> }
> }
> }
> 
> ```

---

<div class="post-metadata">

**Author:** ![parsa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/parsa/32/23227_2.png) [@parsa](https://discuss.elastic.co/u/parsa)\
**Post date:** [October 22, 2017, 5:02pm UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/22 "2017-10-22T17:02:17Z")

</div>

can anyone help me?  
I get this below results from this ur : [http://localhost:9200/servers](http://localhost:9200/servers)  
`{"servers":{"aliases":{},"mappings":{"server":{"properties":{"@timestamp":{"type":"date"},"@version":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"action":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"description":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"dnsname":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"id":{"type":"long"},"ip":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"ipvalid":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"location":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"port_number":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"processcount":{"type":"long"},"processsize":{"type":"long"},"servername":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"subtitlefolderpath":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"switch_device":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"type":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"upload":{"type":"boolean"},"webservice":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}}}}},"settings":{"index":{"creation_date":"1508689638606","number_of_shards":"5","number_of_replicas":"1","uuid":"DoETMJUDR9G5eE4Jzy5ZHg","version":{"created":"5060399"},"provided_name":"servers"}}}}`  
I want to see all rows of table,but I don't know how can I config tha?

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [October 23, 2017, 6:02am UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/23 "2017-10-23T06:02:15Z")

</div>

> I want to see all rows of table

To look at what's in the index use the search API (e.g. [http://localhost:9200/servers/\_search](http://localhost:9200/servers/_search)).

---

<div class="post-metadata">

**Author:** ![parsa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/parsa/32/23227_2.png) [@parsa](https://discuss.elastic.co/u/parsa)\
**Post date:** [October 23, 2017, 3:26pm UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/24 "2017-10-23T15:26:28Z")

</div>

It returns :  
[http://up1.mysd.ir/download/cc2b19f8d7cf5981567282a913886eb4/ServerTable.txt](http://up1.mysd.ir/download/cc2b19f8d7cf5981567282a913886eb4/ServerTable.txt)

sorry,I just changed some columns value for security issues .  
and the results of top link are all results of [http://localhost:9200/servers/\_search](http://localhost:9200/servers/_search) ,  
but not all rows of my table.

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [October 23, 2017, 8:09pm UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/25 "2017-10-23T20:09:03Z")

</div>

Okay. Those results look reasonable. What's the problem again?

> and the results of top link are all results of [http://localhost:9200/servers/\_search](http://localhost:9200/servers/_search) ,  
> but not all rows of my table.

As documented only 10 documents are returned by default but that can be changed with the `size` query parameter. The results you posted indicates that there are 20 matching documents in total.

---

<div class="post-metadata">

**Author:** ![parsa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/parsa/32/23227_2.png) [@parsa](https://discuss.elastic.co/u/parsa)\
**Post date:** [October 25, 2017, 4:02pm UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/26 "2017-10-25T16:02:11Z")

</div>

Thank you, why [http://localhost:9200/servers/server/\_search](http://localhost:9200/servers/server/_search) not work and .with how can search on servername field?

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [October 25, 2017, 7:58pm UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/27 "2017-10-25T19:58:57Z")

</div>

> Thank you, why [http://localhost:9200/servers/server/\_search](http://localhost:9200/servers/server/_search) not work

The query works and returns 10 out of 20 matching documents. Why are you saying that it's not working?

Please understand that "it's not working" is almost always a really bad question.

> and .with how can search on servername field?

[http://localhost:9200/servers/server/\_search?q=servername:whatever-you-want-to-search-for](http://localhost:9200/servers/server/_search?q=servername:whatever-you-want-to-search-for)

But now we're getting off topic for the Logstash group.

---

<div class="post-metadata">

**Author:** ![parsa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/parsa/32/23227_2.png) [@parsa](https://discuss.elastic.co/u/parsa)\
**Post date:** [October 26, 2017, 6:14am UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/28 "2017-10-26T06:14:31Z")

</div>

I didn't know how to call the search url base on I asked,now with your guidances I found that.  
Thank you so much for your helpfully guidances and helps.

---

<div class="post-metadata">

**Author:** ![parsa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/parsa/32/23227_2.png) [@parsa](https://discuss.elastic.co/u/parsa)\
**Post date:** [November 1, 2017, 6:02pm UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/29 "2017-11-01T18:02:49Z")

</div>

How Can I serarch with nest? I used this :

```
    public class Servers
    {
    public string ServerName { get; set; }
    public string Action { get; set; }
    public bool Upload { get; set; }
    }

         var responses = client.Search<Servers>(s => s.Index("servers").Type("server").Query(q => q.Term(t => 
         t.ServerName.ToLower(), "server1-5")));
         var response = client.Search<Servers>(s => s.AllTypes().AllIndices().MatchAll());
         var result = response.Documents.ToList();
         foreach (var server in result)
         {
            Response.Write("ServerName : " + server.ServerName + " Action : " + server.Action + "Upload : " + 
           server.Upload + "<br/>");
         }

```

But no results returned.

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [November 1, 2017, 8:54pm UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/30 "2017-11-01T20:54:45Z")

</div>

I don't think there's a Nest group here so the Elasticsearch group is probably the best place to ask Nest questions.

---

<div class="post-metadata">

**Author:** ![parsa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/parsa/32/23227_2.png) [@parsa](https://discuss.elastic.co/u/parsa)\
**Post date:** [November 2, 2017, 7:11am UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/31 "2017-11-02T07:11:12Z")

</div>

Ok, Thanks.

---

<div class="post-metadata">

**Author:** ![parsa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/parsa/32/23227_2.png) [@parsa](https://discuss.elastic.co/u/parsa)\
**Post date:** [November 5, 2017, 7:47pm UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/32 "2017-11-05T19:47:43Z")

</div>

Dear @magnusbaeck,Any question, for making search on multi fields or columns how I must be set my .conf file for index and type?

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [November 6, 2017, 6:09am UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/33 "2017-11-06T06:09:23Z")

</div>

I don't understand the question. Maybe it's easier to understand if you give an example.

---

<div class="post-metadata">

**Author:** ![parsa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/parsa/32/23227_2.png) [@parsa](https://discuss.elastic.co/u/parsa)\
**Post date:** [November 6, 2017, 6:23am UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/34 "2017-11-06T06:23:29Z")

</div>

I want to search on "servername" column and "action" column concurrently.  
How I must be define index and type for that.  
and calling the search is like this ?? : [http://localhost:9200/\_search?q=servername:name&action:convert](http://localhost:9200/_search?q=servername:name&action:convert)

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [November 6, 2017, 6:26am UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/35 "2017-11-06T06:26:09Z")

</div>

You don't have to do anything in particular. Just make sure both columns are included in the SELECT clause of your query. The query will be `servername:name AND action:convert`.

---

<div class="post-metadata">

**Author:** ![parsa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/parsa/32/23227_2.png) [@parsa](https://discuss.elastic.co/u/parsa)\
**Post date:** [November 6, 2017, 6:31am UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/36 "2017-11-06T06:31:23Z")

</div>

Thank you.  
So calling url is like : [http://localhost:9200/\_search?q=servername:name](http://localhost:9200/_search?q=servername:name) AND action:convert

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [November 6, 2017, 6:54am UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/37 "2017-11-06T06:54:39Z")

</div>

Yes, but spaces will of course have to be encoded as `%20` or `+`.

---

<div class="post-metadata">

**Author:** ![parsa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/parsa/32/23227_2.png) [@parsa](https://discuss.elastic.co/u/parsa)\
**Post date:** [November 6, 2017, 6:57am UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/38 "2017-11-06T06:57:53Z")

</div>

Thank you so much for your helps.  
Thank you.

---

<div class="post-metadata">

**Author:** ![divik](https://avatars.discourse-cdn.com/v4/letter/d/e19b73/32.png) [@divik](https://discuss.elastic.co/u/divik)\
**Post date:** [November 13, 2017, 6:46am UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/39 "2017-11-13T06:46:05Z")

</div>

Im using windows 7. and i installed elasticsearch 5.6.3 its shows

{  
"name" : "5uyIjly",  
"cluster\_name" : "elasticsearch",  
"cluster\_uuid" : "7RUbLqDQRJGLqq-sTYwtWg",  
"version" : {  
"number" : "5.6.3",  
"build\_hash" : "1a2f265",  
"build\_date" : "2017-10-06T20:33:39.012Z",  
"build\_snapshot" : false,  
"lucene\_version" : "6.6.1"  
},  
"tagline" : "You Know, for Search"  
}

after i copied jar file too.. after i run the command in bat file like

{  
"type": "jdbc",  
"jdbc": {  
"url" : "jdbc:sqlserver://192.169.1.101/SCUF2012;instanceName=SQLEXPRESS;databaseName=SHRICITYUNO\_ARCH",^  
"user": "uno",  
"password": "uno",  
"sql": "select Top 10 \* from Igen\_Cust\_details",  
"treat\_binary\_as\_string": true,  
"elasticsearch": {  
"cluster": "elasticsearch",  
"host": "localhost",  
"port": 9200  
},  
"index": "record",  
"type": "record"  
}  
}

when im execute its shows

C:\elasticsearch-5.6.3\elasticsearch-jdbc-2.3.3.1\bin\>mssql-simple-example.bat

C:\elasticsearch-5.6.3\elasticsearch-jdbc-2.3.3.1\bin\>{  
'{' is not recognized as an internal or external command,  
operable program or batch file.

C:\elasticsearch-5.6.3\elasticsearch-jdbc-2.3.3.1\bin\>"type": "jdbc",  
'"type":' is not recognized as an internal or external command,  
operable program or batch file.

C:\elasticsearch-5.6.3\elasticsearch-jdbc-2.3.3.1\bin\>"jdbc": {  
'"jdbc":' is not recognized as an internal or external command,  
operable program or batch file.

C:\elasticsearch-5.6.3\elasticsearch-jdbc-2.3.3.1\bin\>"url" : "jdbc:sqlserver://  
192.169.1.101/SCUF2012;instanceName=SQLEXPRESS;databaseName=SHRICITYUNO\_ARCH",  
"user": "uno",  
'"url"' is not recognized as an internal or external command,  
operable program or batch file.

C:\elasticsearch-5.6.3\elasticsearch-jdbc-2.3.3.1\bin\>"password": "uno",  
'"password":' is not recognized as an internal or external command,  
operable program or batch file.

C:\elasticsearch-5.6.3\elasticsearch-jdbc-2.3.3.1\bin\>"sql": "select Top 10 \* fr  
om Igen\_Cust\_details",  
'"sql":' is not recognized as an internal or external command,  
operable program or batch file.

C:\elasticsearch-5.6.3\elasticsearch-jdbc-2.3.3.1\bin\>"treat\_binary\_as\_string":  
true,  
'"treat\_binary\_as\_string":' is not recognized as an internal or external command  
,  
operable program or batch file.

C:\elasticsearch-5.6.3\elasticsearch-jdbc-2.3.3.1\bin\>"elasticsearch": {  
'"elasticsearch":' is not recognized as an internal or external command,  
operable program or batch file.

C:\elasticsearch-5.6.3\elasticsearch-jdbc-2.3.3.1\bin\>"cluster": "elasticsearch"  
,  
'"cluster":' is not recognized as an internal or external command,  
operable program or batch file.

C:\elasticsearch-5.6.3\elasticsearch-jdbc-2.3.3.1\bin\>"host": "localhost",  
'"host":' is not recognized as an internal or external command,  
operable program or batch file.

C:\elasticsearch-5.6.3\elasticsearch-jdbc-2.3.3.1\bin\>"port": 9200  
'"port":' is not recognized as an internal or external command,  
operable program or batch file.

C:\elasticsearch-5.6.3\elasticsearch-jdbc-2.3.3.1\bin\>},  
'}' is not recognized as an internal or external command,  
operable program or batch file.

C:\elasticsearch-5.6.3\elasticsearch-jdbc-2.3.3.1\bin\>"index": "record",  
'"index":' is not recognized as an internal or external command,  
operable program or batch file.

C:\elasticsearch-5.6.3\elasticsearch-jdbc-2.3.3.1\bin\>"type": "record"  
'"type":' is not recognized as an internal or external command,  
operable program or batch file.

C:\elasticsearch-5.6.3\elasticsearch-jdbc-2.3.3.1\bin\>}  
'}' is not recognized as an internal or external command,  
operable program or batch file.

C:\elasticsearch-5.6.3\elasticsearch-jdbc-2.3.3.1\bin\>}  
'}' is not recognized as an internal or external command,  
operable program or batch file.

C:\elasticsearch-5.6.3\elasticsearch-jdbc-2.3.3.1\bin\>

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [November 13, 2017, 8:15am UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/40 "2017-11-13T08:15:28Z")

</div>

@divik, please start your own topic for your question.

[Previous page](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027.md?page=1)

[Next page](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027.md?page=3)