Join between two different sources using Kibana 4

Elastic Stack Elasticsearch
1

Hi,

what would be the right way to join between two data sources using
Kibana 4 interface?
Assume 2 data sources:

  1. source=jobs, fields = {jobid, user, host, exitstatus,
    starttime,finishtime}
    Sample record:
    type = jobs; jobid = 1234; user = john; host = myhost; exitstatus =
    -3002; starttime = 01/01/2015 01:01; finishtime = 01/01/2015 01:15
  2. source=license, fields = {host, user, time, feature, result}
    Sample records:
    type = license; user = john; host = myhost; time = 01/01/2015 01:05;
    feature = AAA; result = DENIED
    type = license; user = john; host = myhost; time = 01/01/2015 01:07;
    feature = BBB; result = APPROVED

I’d like to create a dashboard in Kibana 4 which would show a joint table
combining both sources.
Using pseudo-SQL code, it should do something like:

select
jobs.jobid,jobs.user,jobs.host,license.feature,license.result,count(license.time)
from jobs
LEFT JOIN license
WHERE jobs.exitstatus=-3002 AND license.user=jobs.user AND
license.host=jobs.host AND license.time>=jobs.starttime AND
license.time<=jobs.finishtime
GROUP BY jobs.jobid,jobs.user,jobs.host

Thanks in advance,
Gregory

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/daf3dbf4-7b76-477e-8b10-5ca54cb53bf0%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

2

You either use parent / child

Or index denormalized data in the first place

Elasticsearch isn't meant to be used using the same models as relational
databases

--

Itamar Syn-Hershko
http://code972.com | @synhershko https://twitter.com/synhershko
Freelance Developer & Consultant
Author of RavenDB in Action http://manning.com/synhershko/

On Mon, Jan 12, 2015 at 9:36 PM, Gregory Touretsky <
gregory.touretsky@intel.com> wrote:

Hi,

what would be the right way to join between two data sources using
Kibana 4 interface?
Assume 2 data sources:

  1. source=jobs, fields = {jobid, user, host, exitstatus,
    starttime,finishtime}
    Sample record:
    type = jobs; jobid = 1234; user = john; host = myhost; exitstatus =
    -3002; starttime = 01/01/2015 01:01; finishtime = 01/01/2015 01:15
  2. source=license, fields = {host, user, time, feature, result}
    Sample records:
    type = license; user = john; host = myhost; time = 01/01/2015 01:05;
    feature = AAA; result = DENIED
    type = license; user = john; host = myhost; time = 01/01/2015 01:07;
    feature = BBB; result = APPROVED

I’d like to create a dashboard in Kibana 4 which would show a joint table
combining both sources.
Using pseudo-SQL code, it should do something like:

select
jobs.jobid,jobs.user,jobs.host,license.feature,license.result,count(license.time)
from jobs
LEFT JOIN license
WHERE jobs.exitstatus=-3002 AND license.user=jobs.user AND
license.host=jobs.host AND license.time>=jobs.starttime AND
license.time<=jobs.finishtime
GROUP BY jobs.jobid,jobs.user,jobs.host

Thanks in advance,
Gregory

--
You received this message because you are subscribed to the Google Groups
"elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/daf3dbf4-7b76-477e-8b10-5ca54cb53bf0%40googlegroups.com
https://groups.google.com/d/msgid/elasticsearch/daf3dbf4-7b76-477e-8b10-5ca54cb53bf0%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/CAHTr4ZuW4n8JLyAXsnM%3Dppv_Wjg1SSm0OJrmyVYWKkAtrKTzUw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

3

Is there a way to manage it via Kibana interface just at the query time?
Something like Splunk "transaction" statement, which allows to group events
into transactions

On Monday, January 12, 2015 at 9:38:56 PM UTC+2, Itamar Syn-Hershko wrote:

You either use parent / child
Elasticsearch Platform — Find real-time answers at scale | Elastic

Or index denormalized data in the first place

Elasticsearch isn't meant to be used using the same models as relational
databases

--

Itamar Syn-Hershko
http://code972.com | @synhershko https://twitter.com/synhershko
Freelance Developer & Consultant
Author of RavenDB in Action http://manning.com/synhershko/

On Mon, Jan 12, 2015 at 9:36 PM, Gregory Touretsky <gregory....@intel.com
<javascript:>> wrote:

Hi,

what would be the right way to join between two data sources using
Kibana 4 interface?
Assume 2 data sources:

  1. source=jobs, fields = {jobid, user, host, exitstatus,
    starttime,finishtime}
    Sample record:
    type = jobs; jobid = 1234; user = john; host = myhost; exitstatus =
    -3002; starttime = 01/01/2015 01:01; finishtime = 01/01/2015 01:15
  2. source=license, fields = {host, user, time, feature, result}
    Sample records:
    type = license; user = john; host = myhost; time = 01/01/2015
    01:05; feature = AAA; result = DENIED
    type = license; user = john; host = myhost; time = 01/01/2015
    01:07; feature = BBB; result = APPROVED

I’d like to create a dashboard in Kibana 4 which would show a joint table
combining both sources.
Using pseudo-SQL code, it should do something like:

select
jobs.jobid,jobs.user,jobs.host,license.feature,license.result,count(license.time)
from jobs
LEFT JOIN license
WHERE jobs.exitstatus=-3002 AND license.user=jobs.user AND
license.host=jobs.host AND license.time>=jobs.starttime AND
license.time<=jobs.finishtime
GROUP BY jobs.jobid,jobs.user,jobs.host

Thanks in advance,
Gregory

--
You received this message because you are subscribed to the Google Groups
"elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to elasticsearc...@googlegroups.com <javascript:>.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/daf3dbf4-7b76-477e-8b10-5ca54cb53bf0%40googlegroups.com
https://groups.google.com/d/msgid/elasticsearch/daf3dbf4-7b76-477e-8b10-5ca54cb53bf0%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/4a4acea6-8397-4a40-94bf-9eddc269b70f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

4

Without parent/child, you'll need an extra layer to execute 2 queries and
merge the results yourself.

On Monday, January 12, 2015 at 2:10:54 PM UTC-8, Gregory Touretsky wrote:

Is there a way to manage it via Kibana interface just at the query time?
Something like Splunk "transaction" statement, which allows to group
events into transactions

On Monday, January 12, 2015 at 9:38:56 PM UTC+2, Itamar Syn-Hershko wrote:

You either use parent / child
Elasticsearch Platform — Find real-time answers at scale | Elastic

Or index denormalized data in the first place

Elasticsearch isn't meant to be used using the same models as relational
databases

--

Itamar Syn-Hershko
http://code972.com | @synhershko https://twitter.com/synhershko
Freelance Developer & Consultant
Author of RavenDB in Action http://manning.com/synhershko/

On Mon, Jan 12, 2015 at 9:36 PM, Gregory Touretsky <gregory....@intel.com

wrote:

Hi,

what would be the right way to join between two data sources using
Kibana 4 interface?
Assume 2 data sources:

  1. source=jobs, fields = {jobid, user, host, exitstatus,
    starttime,finishtime}
    Sample record:
    type = jobs; jobid = 1234; user = john; host = myhost; exitstatus
    = -3002; starttime = 01/01/2015 01:01; finishtime = 01/01/2015 01:15
  2. source=license, fields = {host, user, time, feature, result}
    Sample records:
    type = license; user = john; host = myhost; time = 01/01/2015
    01:05; feature = AAA; result = DENIED
    type = license; user = john; host = myhost; time = 01/01/2015
    01:07; feature = BBB; result = APPROVED

I’d like to create a dashboard in Kibana 4 which would show a joint
table combining both sources.
Using pseudo-SQL code, it should do something like:

select
jobs.jobid,jobs.user,jobs.host,license.feature,license.result,count(license.time)
from jobs
LEFT JOIN license
WHERE jobs.exitstatus=-3002 AND license.user=jobs.user AND
license.host=jobs.host AND license.time>=jobs.starttime AND
license.time<=jobs.finishtime
GROUP BY jobs.jobid,jobs.user,jobs.host

Thanks in advance,
Gregory

--
You received this message because you are subscribed to the Google
Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to elasticsearc...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/daf3dbf4-7b76-477e-8b10-5ca54cb53bf0%40googlegroups.com
https://groups.google.com/d/msgid/elasticsearch/daf3dbf4-7b76-477e-8b10-5ca54cb53bf0%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/ebedd993-e489-4ed5-885e-48be074df3f4%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.