Help with visualizations, multiple indexes, and a common field

Elastic Stack Kibana
,
1

I am having some confusion on how to properly do this. Most of my "database" experience comes from relational databases. So my mind immediately goes to inner/outer joins, which I know is not applicable here. I have read through many posts here, and have found lots of different types of information...

HOWEVER, I have several indexes, mainly to do with information on computers in specific systems. I have made an API class using the elastic PHP agent, and I have configured the ability to add a common field. So each index has what I call a "CommonField" to capture another field. So each of my 5 indexes have this field with the same data (And all lowercase).

So now, I am trying to create a table that uses the multiple indexes that shows two fields (in this case timestamps) of each "CommonField" (Which is a computername). For the most part, in each of the indexes each "CommonField" or computer name has exactly 1 row.

So I discovered runtime lookup fields. This seemed like it solved the issue, but then I found out that this does not really work for visualizations if I read that correctly.

Then I found transforms. So I am reading into this, and it might be a good solution. However I am considering just joining all of the datasets together. However my supervisor is against "Duplicating data" and thinks we should be able to use the existing datasets (Which makes sense to me, but perhaps isnt ideal in this situation).

I am new to EKS, and it's such a powerful tool, and I have been down so many rabbits holes that I decided to reach out some folks that may no better. Does anyone have any guidance on the proper path forward here? For my current data we are talking around 1000 rows, which is not too many, but for larger datasets does this recommendation change? Thank you!

1 Like
2

Hi @icsy7867

welcome to the Kibana community.
I think the transform is best route here as data needs to be denormalized in Elasticsearch in order to create visualizations which require query who spans to multiple "tables".

3

I have the same issue, im trying to calculate the delta between two timestamp.
I tried runtime field and scripted field without success. Using Kibana dashboard is hard to display graph or metrics using "formula field".

You can try scripted field too or i discovered today the Canvas (under Analytics) where you can use something similar to SQL.

Another noobie advice is to practice with the dev console.

Good Luck :slight_smile:

4

@Samuele_Lolli that looks like a different question. Would you mind create a separate thread for it so we could keep answers clean?

5

If the two time stamp fields are in different indexes, it sounds like a similar problem to me. I ultimately will want to do this :smiley:

1 Like
6

We need more information about the mapping to define it as same problem. But I would still like to split the two questions into 2 distinct thread to answer them better.

7

Here is my current attempt. I am missing something.

PUT _transform/my_transform
{
  "source": {
    "index": ["my-data-computers", "my-backup-computers"]
  },
  "latest": {
    "unique_key": ["CommonName.keyword"],
    "sort": "CommonName.keyword"
  },
  "description": "Transformation Test",
  "dest": {
    "index": "my-transform"
  },
  "frequency": "5m"
}

So my-data-computers has a bunch of fields with information on various computers, and my-backup-computers has lots of fields with information on a machines last backup.

I know that "CommonName.keyword" exists in both and has an identical string.

This transformation runs fine. But when I look at it, there are rows that either have fields from "my-data-computers" or all the fields from "my-backup-computers", but they arent combined liek I would have thought they would be.

What am I missing?

8

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