Creating a subquery - similar to SQL "not in or not exists"


Hello. I am using Kibana 4.5. I am trying to create a new custom filter based on a condition of another query. For example, values in query 1 -> animal: cat dog pig horse rabbit bird Now, in query 2, I will select from my table containing 100+ animals but exclude values from query 1. How can I accomplish this and save as a filter or visualization so that I can apply to my dashboard?


(Mark Walkom) #2

You cannot do that in ES at all, so you cannot do it in KB.


Bummer. Thank you for your response, Mark.


Hi Mark,
One more question...
Is this possible in Kibana 4.5 - I am tracking customer transactions in stores. I want to count the store only if the number of transactions is >1. Do you know of a way to accomplish this?


(Mark Walkom) #5

Are you adding the transactions up with an aggregation?


I selected distinct count and chose (terms=store) in the visualization. I have a job that runs every 30 minutes that adds new data to today's index (health-%{+YYYY.MM.dd}). The Discover panel contains all of the records accumulated thus far. The only thing that keeps them from being classified as duplicates is the timestamp. I want to count 1 record for store 00101. However, it counts all lines, so instead of the count =1, it returns 4. Also, if 3 lines = 0 and 1 line = a number >0, then I do not want to include this store in my total counts. For 100,000+ records, it returns 9671 distinct stores when it should return 83. Any ideas?

May 27th 2016, 12:31:34.3434 00101 - US 0 - -
May 27th 2016, 12:34:55.5555 00101 - US 0 - -
May 27th 2016, 12:35:29.2929 00101 - US 0 - -
May 27th 2016, 12:52:28.2828 00101 - US 0 - -

(system) #7