Subquery with elasticsearch

[sabdoul]

Hello,
how to simulate this subquery with elasticsearch:

 Select NOM from Table where NOM not in (select NOM from Table where Prenom='Paul')

Thanks!

[Ayush_Mathur]

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.

[sabdoul]

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!

[Ayush_Mathur]

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" ] }'

[sabdoul]

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!

[Ayush_Mathur]

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.

[sabdoul]

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:

Thank you for your help

[Ayush_Mathur]

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

[sabdoul]

Hello, thank you for answering me,
here is the snapshot where system.process.name is "is":

And where he is at 'is not':

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!

[Ayush_Mathur]

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

[sabdoul]

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