# Kibana Query Minus Operator

**URL:** <https://discuss.elastic.co/t/kibana-query-minus-operator/104053>\
**Category:** Kibana\
**Created:** [October 16, 2017, 7:21am UTC](https://discuss.elastic.co/t/kibana-query-minus-operator/104053 "2017-10-16T07:21:15Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![ChrisChua](https://avatars.discourse-cdn.com/v4/letter/c/8baadc/32.png) [@ChrisChua](https://discuss.elastic.co/u/ChrisChua)\
**Post date:** [October 16, 2017, 7:21am UTC](https://discuss.elastic.co/t/kibana-query-minus-operator/104053/1 "2017-10-16T07:21:16Z")

</div>

Hi,  
I am new to ELK , I have data inside elasticsearch and each record has these fields "Status", "Policy", "Testcase","OS",etc.  
I want to find out those "Policy","Testcase","OS" which have a "Status:start testing" but don't have "Status:finished testing", in traditional SQL query will be look like (Left Join/Outer Join):

```
SELECT OS_Name, Testcase_Name, Policy_Name
FROM logstash-*
WHERE Status="Start testing "
AND container_name="x"
AND @timestamp >= 'y'
AND @timestamp <= 'z'
LIMIT 1000
minus
SELECT OS_Name, Testcase_Name, Policy_Name
FROM logstash-*
WHERE Status="Finished testing"
AND container_name="x"
AND @timestamp >= 'y'
AND @timestamp <= 'z'
LIMIT 1000

```

Each "Testcase" can have different "Policy" and "OS" so 3 of them combine together become a unique key. But how to do this in kibana? I did some search in Google, some people propose using "bool must & must not" (performs as a filter)but that is not what I want (subtracts one result set from another result set), any help will be appreciated.

---

<div class="post-metadata">

**Author:** ![ppisljar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ppisljar/32/11588_2.png) [@ppisljar](https://discuss.elastic.co/u/ppisljar)\
**Post date:** [October 17, 2017, 7:14am UTC](https://discuss.elastic.co/t/kibana-query-minus-operator/104053/2 "2017-10-17T07:14:50Z")

</div>

just to make sure i understand ....

```auto
SELECT OS_Name, Testcase_Name, Policy_Name
FROM logstash-*
WHERE Status="Start testing "
AND Status!='Finished testing'
AND container_name="x"
AND @timestamp >= 'y'
AND @timestamp <= 'z'
LIMIT 1000

```

is **not** what you want ?

---

<div class="post-metadata">

**Author:** ![ChrisChua](https://avatars.discourse-cdn.com/v4/letter/c/8baadc/32.png) [@ChrisChua](https://discuss.elastic.co/u/ChrisChua)\
**Post date:** [October 17, 2017, 8:25am UTC](https://discuss.elastic.co/t/kibana-query-minus-operator/104053/3 "2017-10-17T08:25:21Z")

</div>

Yeah, what I am trying to do is monitoring the log, if a record has Status, it will be either "Start testing" or "Finished testing", so when it is "Start testing" then it will never be "Finished testing".

```
[2017-10-17T01:21:41.545+08:00 23 I WINDOWS2012R2 9.30.80.101 policy_windows_guestaccount_check changed] > start testing for recipeName:policy_windows_log_retention, testSuite:default, osName:WINDOWS2012R2
[2017-10-17T01:22:17.616+08:00 23 W WINDOWS2012R2 9.30.80.101 policy_windows_guestaccount_check changed] > Warning: change threadName, from WINDOWS2012R2 9.30.80.101 policy_windows_guestaccount_check changed to 

```

For example, when I read the first line, during the filter stage in logstash I will set the Status for the first record to "Start testing" with the following information (OS, Policy, Testcase), and when a testcase was test successfully, it will end with:

```
[2017-10-17T01:23:51.408+08:00 23 I WINDOWS2012R2 9.30.80.101 policy_windows_log_retention default] > Finished destroying <default-WINDOWS2012R2--9-30-80-101> (0m0.00s).
[2017-10-17T01:23:51.408+08:00 23 I WINDOWS2012R2 9.30.80.101 policy_windows_log_retention default] > Finished testing <default-WINDOWS2012R2--9-30-80-101> (1m32.21s).

```

When I getting this log I will mark the Status to "Finished Testing" and its OS, Policy, Testcase information.

Now I want to find out those "OS, Policy, Testcase" have "Start testing" but don't have "Finished testing".  
For each query in Kibana is (Status:"Start testing") or (Status:"Finished testing") then select the "OS,Policy,Testcase" field, I don't know how to perform the query I mentioned in my question in Kibana.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [November 14, 2017, 8:25am UTC](https://discuss.elastic.co/t/kibana-query-minus-operator/104053/4 "2017-11-14T08:25:24Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.