Kibana Query Minus Operator

Elastic Stack Kibana
1

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.

2

just to make sure i understand ....

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 ?

3

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.

4

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