Combine queries on different indices in kibana

I'd like to combine 2 queries in kibana to get unused websites.

I have the following 2 datasets:

website-state

{siteId: 1, state: 'active'}
{siteId: 2, state: 'disabled'}
{siteId: 3, state: 'disabled'}
{siteId: 4, state: 'active'}

website-actions

{siteId: 1, action: '...', @timestamp: 2019-03-28 19:12:33.000},
{siteId: 1, action: '...', @timestamp: 2019-03-28 12:25:31.000},
{siteId: 2, action: '...', @timestamp: 2019-03-27 13:05:27.000},
{siteId: 2, action: '...', @timestamp: 2019-03-27 11:34:40.000},
{siteId: 2, action: '...', @timestamp: 2019-03-27 15:23:10.000},
{siteId: 2, action: '...', @timestamp: 2019-03-26 19:38:12.000},
{siteId: 1, action: '...', @timestamp: 2019-03-26 13:32:14.000},
{siteId: 1, action: '...', @timestamp: 2019-03-25 20:41:25.000},
{siteId: 2, action: '...', @timestamp: 2019-03-25 16:03:52.000},
{siteId: 1, action: '...', @timestamp: 2019-03-24 14:16:38.000},
{siteId: 2, action: '...', @timestamp: 2019-03-24 12:43:09.000},
{siteId: 3, action: '...', @timestamp: 2019-03-23 22:25:16.000},
{siteId: 4, action: '...', @timestamp: 2019-03-21 20:10:21.000},
{siteId: 1, action: '...', @timestamp: 2019-03-21 10:24:50.000},

Now i'd like to have a list (i.e. datatable) with:

  • Max(@timestamp)
  • Split on term "siteId"
  • Show only the sites with state 'active'

Is this possible?

For performance reasons, Elasticsearch doesn't support joins. In order to do something like this, you need to store your docs in a single index, maybe something like: { siteId, state, action, @timestamp }.

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