# Subquery with elasticsearch **URL:** \ **Category:** Elasticsearch\ **Created:** [February 14, 2018, 3:25pm UTC](https://discuss.elastic.co/t/subquery-with-elasticsearch/119832 "2018-02-14T15:25:50Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![sabdoul](https://avatars.discourse-cdn.com/v4/letter/s/74df32/32.png) [@sabdoul](https://discuss.elastic.co/u/sabdoul)\ **Post date:** [February 14, 2018, 3:25pm UTC](https://discuss.elastic.co/t/subquery-with-elasticsearch/119832/1 "2018-02-14T15:25:50Z")
Hello, how to simulate this subquery with elasticsearch: ``` Select NOM from Table where NOM not in (select NOM from Table where Prenom='Paul') ``` Thanks! ---
**Author:** ![Ayush\_Mathur](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ayush_mathur/32/77134_2.png) [@Ayush\_Mathur](https://discuss.elastic.co/u/Ayush_Mathur)\ **Post date:** [February 14, 2018, 4:02pm UTC](https://discuss.elastic.co/t/subquery-with-elasticsearch/119832/2 "2018-02-14T16:02:50Z")
Hi, you can probably use filtered search in Kibana: _exists_:NOM AND NOT Prenom.keyword:"Paul" If you're creating any watcher/ using cURL, you can just use must-exist NOM and filter with must\_not-match {Prenom : Paul} Hope this answers your question. ---
**Author:** ![sabdoul](https://avatars.discourse-cdn.com/v4/letter/s/74df32/32.png) [@sabdoul](https://discuss.elastic.co/u/sabdoul)\ **Post date:** [February 15, 2018, 9:15am UTC](https://discuss.elastic.co/t/subquery-with-elasticsearch/119832/3 "2018-02-15T09:15:23Z")
Thank you for your reply. What I want to do is to know which software is not used by a PC over a given period of time with the logs collected by METRICBEAT. Example which are PCs that did not use WORD. This does not work with a simple NOT because the software was used in the second minute and in the fourth minute it was not. So if I do NOT the PC will appear to have not used WORD because in the fourth minute it was not used when it was used in the second minute. So to solve this I thought to do as in SQL where I filter in a subquery the PCs who have used software over a period and then I excluded: ``` Select PC from INDEX where PC not in (select PC from INDEX where system.process.name='WINWORD.EXE') ``` thanks! ---
**Author:** ![Ayush\_Mathur](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ayush_mathur/32/77134_2.png) [@Ayush\_Mathur](https://discuss.elastic.co/u/Ayush_Mathur)\ **Post date:** [February 15, 2018, 9:50am UTC](https://discuss.elastic.co/t/subquery-with-elasticsearch/119832/4 "2018-02-15T09:50:22Z")
Try this cURL command and change the time interval range as per your need: curl -s -XGET --user "$USER:$PASS" "$ES/$INDEX/\_search?pretty" -H "Content-Type: application/json" --data '{ "query": { "bool": { "must\_not": [{ "match": { "system.process.name": "WINWORD.EXE" } }, { "range" : { "field": { "lte": "value", "gte": "value" } } }] } }, "size" : 10000, "\_source": ["field list goes here"] }' ---
**Author:** ![sabdoul](https://avatars.discourse-cdn.com/v4/letter/s/74df32/32.png) [@sabdoul](https://discuss.elastic.co/u/sabdoul)\ **Post date:** [February 15, 2018, 10:42am UTC](https://discuss.elastic.co/t/subquery-with-elasticsearch/119832/5 "2018-02-15T10:42:47Z")
Ayush\_Mathur bring me your help. The solution does not work because I have PCs that used WORD that appear when I do the opposite, ie when I make the request to know which PCs have used WORD, there are PCs that I, who were in the list of PCs not using WORD. thanks! ---
**Author:** ![Ayush\_Mathur](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ayush_mathur/32/77134_2.png) [@Ayush\_Mathur](https://discuss.elastic.co/u/Ayush_Mathur)\ **Post date:** [February 15, 2018, 1:14pm UTC](https://discuss.elastic.co/t/subquery-with-elasticsearch/119832/6 "2018-02-15T13:14:46Z")
Well. by logic must\_not - match should work on cases when you don't watch some value to be present on the queried field and must - match works other way round. ---
**Author:** ![sabdoul](https://avatars.discourse-cdn.com/v4/letter/s/74df32/32.png) [@sabdoul](https://discuss.elastic.co/u/sabdoul)\ **Post date:** [February 15, 2018, 1:33pm UTC](https://discuss.elastic.co/t/subquery-with-elasticsearch/119832/7 "2018-02-15T13:33:38Z")
Yes normally but I find PCs that have used word during the chosen period that are listed with the mehode that query with must\_not. Here is the opposite request to have the PCs that have used WORD: ``` GET INDEX/_search?pretty { "query": { "bool": { "must": [ { "match": { "system.process.name": "WINWORD.EXE" } } ] } }, "size": 10000, "_source": [ "beat.hostname" ] } ``` In Kibana: ![Capturen](https://us1.discourse-cdn.com/elastic/original/3X/a/1/a1f314b5f5a94ee7d6cfb0ca705a7e1b71b5aa48.PNG) Thank you for your help ---
**Author:** ![Ayush\_Mathur](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ayush_mathur/32/77134_2.png) [@Ayush\_Mathur](https://discuss.elastic.co/u/Ayush_Mathur)\ **Post date:** [February 15, 2018, 6:57pm UTC](https://discuss.elastic.co/t/subquery-with-elasticsearch/119832/8 "2018-02-15T18:57:38Z")
Hi, Can you provide a snapshot where Kibana filter is "is not" on system.process.name ? Doest it return any result in Kibana ? My guess is records are being stored in index incorrectly since must\_not won't include results where system.process.name IS WINWORD.EXE ---
**Author:** ![sabdoul](https://avatars.discourse-cdn.com/v4/letter/s/74df32/32.png) [@sabdoul](https://discuss.elastic.co/u/sabdoul)\ **Post date:** [February 16, 2018, 8:35am UTC](https://discuss.elastic.co/t/subquery-with-elasticsearch/119832/9 "2018-02-16T08:35:39Z")
Hello, thank you for answering me, here is the snapshot where system.process.name is "is": ![Captureb](https://us1.discourse-cdn.com/elastic/original/3X/a/5/a53d2d087d4f8f06e866c9519dd9acc226127f59.PNG) And where he is at 'is not': ![Capturev](https://us1.discourse-cdn.com/elastic/original/3X/2/a/2a3733b76770ddb93076ed791dd0b7f9b9a98c24.PNG) We see that PCs appear in both cases (example of the PC framed in red). This is normal because in the filter a system.process.name can over a period of 15mn as is the case here, have a value at 2mn which is different from WINWORD.EXE and at 4mn have a value equal to WINWORD .EXE. So the PC will appear in both cases because one of its system.process.name had a different value and equal to WINWORD.EXE at a given time. That's why I wanted to filter the PCs that had at one time a system.process.name equal WINWORD.EXE over a period (example 15mn) either in a subquery and then I exclude them from the list. This will allow me to have PCs that have never used WORD on the chosen period. Thank you for your help! ---
**Author:** ![Ayush\_Mathur](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ayush_mathur/32/77134_2.png) [@Ayush\_Mathur](https://discuss.elastic.co/u/Ayush_Mathur)\ **Post date:** [February 16, 2018, 4:31pm UTC](https://discuss.elastic.co/t/subquery-with-elasticsearch/119832/10 "2018-02-16T16:31:30Z")
GET INDEX/\_search?pretty { "query": { "bool": { "must\_not": [ { "match": { "system.process.name": "WINWORD.EXE" } } ], "filter": { "range": { "@timestamp": { "gte": "now-15m", "lte": "now" } } } } }, "size": 10000, "\_source": [ "beat.hostname" ] } Give this a try 🙂 ---
**Author:** ![sabdoul](https://avatars.discourse-cdn.com/v4/letter/s/74df32/32.png) [@sabdoul](https://discuss.elastic.co/u/sabdoul)\ **Post date:** [February 16, 2018, 4:57pm UTC](https://discuss.elastic.co/t/subquery-with-elasticsearch/119832/11 "2018-02-16T16:57:04Z")
Thank you for answering me I tested but I still have pc with the request "must\_not" which are found in the query with "is". Thank you for help ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [March 16, 2018, 4:57pm UTC](https://discuss.elastic.co/t/subquery-with-elasticsearch/119832/12 "2018-03-16T16:57:10Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.