Elasticsearch query vs. SQL query

Elastic Stack Elasticsearch
#1

Is it possible to do this sql query:

SELECT  icID, icmID, message from logstash*
WHERE @timesstamp  in (
  SELECT @timestamp from logstash*
  WHERE icID = 8676 )

with a query from elasticsearch? The problem is, that i can't do a subquery with elasticsearch if I don't want to type in my special @timestamp value

{
"query": {
   "bool": {
      "must": {
            "bool": {
                  "should": [
                      {
                        "match": {
		            "@timestamp": {
				"query": "2016-04-27T05:01:20.055Z",
				"type": "phrase"
			     }
			}
		   },
		   {
               ...
		
	"_source": {
		"includes": [
			"icID",
			"message"
		],
		"excludes": []
	}
}

This is want I don't want.

Can someone help me out with this problem? Or is it impossible to do a "subquery" in elasticsearch?

Thanks for your help!

#2

You can't do an in like that, no.
But why not just filter based on that icID anyway?

#3

Thank you @warkolm! I think I'll show you my logs and maybe you can see, why that's not working for me.
Here are my Logs:

2016-04-27 07:02:22,000 -2- [TEST]     ic=0007
2016-04-27 07:01:20,024 -2- [TEST]     ic=7211
...
2016-04-27 07:01:20,024 -3- [HELLOCLASS]     hello.
...
2016-04-27 07:02:59,999 -2- [TEST]     ic=8888
2016-04-27 07:03:00,000 -2- [TEST]     ic=9999

So as you can see I've different icIDs, but thats not the problem. My problem is, how I get this line with hello. In this line I've nothing else as the same timestamp as in line 2. That's why i can't only filter on icID.
This is only an example, there are a lot more lines than this hello.-line with different content, but with the same timestamp.
The best solution is, if I can type in the icID and get all results with the same timestamp.
Any suggestions? Thanks for your help!

#4

Keep in mind, ES is a search engine, not a relational database engine so when you try to make ES works like a DB, in some cases, it's challenging. You need to look at the model that is indexed in ES and might need to adjust that model in order to achieve what you could do with a DB.

So, based on your post, the indexed document in ES seems to have two fields: icID and message. Based on your logs, each line is a document where message field holds the entire line, correct?

  • To get the hello line, you can simple search for hello

  • To get a specific line, you can search icID:[value]

If you break each line into multiple fields to hold the timestamp, the value "-2- or -3-", the value "[TEST] or [HELLOCLASS]", the value "ic=####", etc... it means you are adjusting the model, this allows you to perform other type of search and hopefully you'll be able to get what you want.

#5