First message is kind of sent message and second is message which confirm that message was delivered.
The difference between them is the suffix which I have separated from "id" and can query it.
I would like to find out which messages were succesfully delivered and which weren't. I am very begginer in elasticsearch so I'm really struggling.
My idea is like match id (number) and matched number suffix. So when id number 1563866656876839 has suffix dn it was delivered. We do not know the id number before we get the log message because it's generated automatically.
In MariaDB SQL I managed it with following query:
SELECT num1
from table4
WHERE suffix IN ('mt', 'dn')
GROUP BY num1
HAVING COUNT(DISTINCT suffix) = 2;
Does anyone have any ideas how to solve it? Any help is appreciated!!
But it doesn't do what I need. How can I manage next step with finding appropriate dn and then I want to filter it so it should show me only dn's which arrived within 72 hours.
What I understand is for the first one you need only the dn in the last 72 hours
For the second one you need the id which has both dn and mt suffix???
For sure you cannot apply the request for the first case on the second case as the ask are different.
when you write this:
is it the result you expect that elastic will return or is it an example of document you have?
Can you please put one question at a time my brain is not enough elastic to compute several case in parallel. Or better put a simple use case with some document, it will help a lot.
Sorry that I couldn't explain my needs right way I'm quite desperate after time I've been trying to solve it I try it this time better.
Well as I'm begginer with elasticsearch I decided to try make query for sql databases sample. The table from rational database I sent is kind of example how id's of log messages are kept.
The SQL query for getting the result I need is
SELECT num1
from table4
WHERE suffix IN ('mt', 'dn')
GROUP BY num1
HAVING COUNT(DISTINCT suffix) = 2;
It gets me all num1's which have 'mt' and 'dn'. This step I want to apply in elasticsearch.
Next thing I need is to find only id's their 'dn's (if they have any) that arrived within 72 hours. It could be done for example with this modification:
SELECT num1
FROM table4
WHERE suffix = 'mt' or (suffix = 'dn' and `date` > NOW() - INTERVAL 72 HOUR)
GROUP BY num1
HAVING COUNT(DISTINCT suffix) = 2;
So I need the same thing to do with elasticsearch and I don't know how
So it shows me numbers that have both suffix. I think that the range question won't do what I want it to do.
I mean that num1 has only 1 suffix and if the same num1 didn't get second suffix within some time e.g one hour it wouldn't show this bucket even if the count_of_distinct_suffix == 2.
Apache, Apache Lucene, Apache Hadoop, Hadoop, HDFS and the yellow elephant
logo are trademarks of the
Apache Software Foundation
in the United States and/or other countries.
I'm quite desperate after time I've been trying to solve it 
I try it this time better.