Using sql server queries in elasticsearch 5.6.3

Elastic Stack Logstash
21

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
						}
					}
				}
			}
		}
22

can anyone help me?
I get this below results from this ur : 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?

23

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).

24

It returns :
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 ,
but not all rows of my table.

25

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 ,
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.

26

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

27

Thank you, why 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

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

28

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.

29

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.

30

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

31

Ok, Thanks.

32

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

33

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

34

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

35

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.

36

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

37

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

38

Thank you so much for your helps.
Thank you.

39

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>

40

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