Table visualization using few logs in the index

[poewyd]

I am new with Kibana visualizations and need help with current task.

I have 2 logs in same index with different data.
Both has message with different log data.
1st one stores information about locations used for search and has fields with this data. (fields.SearchLocations.keyword)
2nd one stores information about number of search results. (fields.NumberOfSearchResults.keyword)

Both has common fields named like fields.Gds.keyword and fields.TenantName.keyword.

I need combined info from both of them to display using Table Visualization and table must have next structure:
|TenantName|GDS|SearchLocations|Count|

I tried to use Buckets for it and have added next fields to the bucket:
fields.TenantName.Keyword
fields.Gds.Keyword
fields.SearchLocations.keyword

As metric i used Filter and calculate how much fields.NumberOfSearchResults.keyword with 0 value

But it shows correct data per Gds and TenantName, but not per SearchLocations. I think it happens because information about NumberOfSearchResults and SearchLocations are placed in different logs inside same index.

I can add metric for SearchLocations, but in this case all routes will be listed in one row, but i need separate stat per requested route.

If i enable metric for every bucket field, then i have next data in table:

It is correct for first two calculations. But third one is always 0.
How i can change my table and receive collected data for visualization?

Thank you.

[poewyd]

Still have problems with the topic

[poewyd]

Bump

[poewyd]

Bump

[RainTown (Kevin Maguire)]

I try to help, I dont think what you are trying to do is that difficult, looks solvable to me with a simple kibana Table, but my understanding of your data is limited as I don't find your explanation/description clear, sorry. Others might understand better.

Can you maybe share circa: 5 docs of each type into the chat. That will help us see what can be done with that actual data. The docs only need contain the fields relevant to this task, i.e. just the 4 fields you mention

fields.SearchLocations.keyword
fields.NumberOfSearchResults.keyword
fields.Gds.keyword
fields.TenantName.keyword

btw, fields.NumberOfSearchResults looks to me like it would be better mapped as a number than a keyword?

My first guess is that you need really just change your filter - a filter on fields.TenantName.keyword will only match docs with that field, specifically excluding the other logs from the table aggregation.

[poewyd]

Good day.
Sorry for long reply i had some high priority tasks.

Log messages with changes with removed personal data and changes for names for confidentiality:
1st log:

Summary

{
  "_source": {
    "message": "Requested route: Oneway \"LON-PAR\", \"02-04-2025\", \"1Adult\", \"Airline\"",
    "fields": {
      "RouteType": "Oneway",
      "SearchLocations": "LON-PAR",
      "SearchDates": "02-04-2025",
      "SearchNumberOfPassengers": "1Adult",
      "Gds": "Airline",
      "RequestId": "1111222233333:00000099",
      "TenantName": "Tenant1"
    }
  },
  "fields": {
    "fields.TenantName.keyword": [
      "Tenant1"
    ],
    "fields.SearchDates.keyword": [
      "02-04-2025"
    ],
    "fields.SearchNumberOfPassengers.keyword": [
      "1Adult"
    ],
    "message.keyword": [
      "Requested route: Oneway \"LON-PAR\", \"02-04-2025\", \"1Adult\", \"Airline\""
    ],
    "fields.Gds": [
      "Airline"
    ],
    "fields.RouteType.keyword": [
      "Oneway"
    ],
    "fields.TenantName": [
      "Tenant1"
    ],
    "fields.SearchLocations": [
      "LON-PAR"
    ],
    "fields.Gds.keyword": [
      "Airline"
    ],
    "fields.SearchNumberOfPassengers": [
      "1Adult"
    ],
    "message": [
      "Requested route: Oneway \"LON-PAR\", \"02-04-2025\", \"1Adult\", \"Airline\""
    ],
    "fields.RequestId.keyword": [
      "1111222233333:00000099"
    ],
    "fields.RequestId": [
      "1111222233333:00000099"
    ],
    "fields.SearchLocations.keyword": [
      "LON-PAR"
    ]
  }
}

2nd log:

Summary

{
  "_source": {
    "message": "Airline results has 102 results.",
    "fields": {
      "Gds": "Airline",
      "NumberOfSearchResults": 102,
      "RequestId": "1111222233333:00000099",
      "TenantName": "Tenant1"
    }
  },
  "fields": {
    "fields.TenantName.keyword": [
      "Tenant1"
    ],
    "message.keyword": [
      "Airline results has 102 results."
    ],
    "fields.Gds": [
      "Airline"
    ],
    "fields.TenantName": [
      "Tenant1"
    ],
    "fields.Gds.keyword": [
      "Airline"
    ],
    "fields.NumberOfSearchResults": [
      102
    ],
    "message": [
      "Airline results has 102 results."
    ],
    "fields.RequestId.keyword": [
      "1111222233333:00000099"
    ],
    "fields.RequestId": [
      "1111222233333:00000099"
    ]
  }
}

3rd log:

Summary

{
  "_source": {
    "message": "Airline results not match expectations. Total of 1 were filtered.",
    "fields": {
      "Gds": "Airline",
      "NumberOfFilteredSearchResults": 1,
      "RequestId": "1111222233333:00000099",
      "TenantName": "Tenant1"
    }
  },
  "fields": {
    "fields.TenantName.keyword": [
      "Tenant1"
    ],
    "message.keyword": [
      "Airline results not match expectations. Total of 1 were filtered."
    ],
    "fields.Gds": [
      "Airline"
    ],
    "fields.TenantName": [
      "Tenant1"
    ],
    "fields.Gds.keyword": [
      "Airline"
    ],
    "message": [
      "Airline results not match expectations. Total of 1 were filtered."
    ],
    "fields.RequestId.keyword": [
      "1111222233333:00000099"
    ],
    "fields.RequestId": [
      "1111222233333:00000099"
    ]
  }
}

btw, fields.NumberOfSearchResults looks to me like it would be better mapped as a number than a keyword?

It is something that can be tuned in the future. Appreciate for the advice.

Thank you for your help in advance.

[RainTown (Kevin Maguire)]

Hi

Sorry, I’m on holiday. And I asked for a few more than 3 total documents

And it looks to me like fields.NumberOfSearchResults is ALREADY mapping as an integer, please check this, though it’s probably not that important here.

@poewyd

As metric i used Filter and calculate how much fields.NumberOfSearchResults.keyword with 0 value

I’m still not clear to what you want. Do you want a count, per-tenant, per-gds, per-searchlocations, how many of those docs which had some value for ALL 3 of those fields, also had fields.NumberOfSearchResults equal zero? So your desired table has 4 columns, tenantname, gds, searchlocations, and count. Just search for

fields.NumberOfSearchResults: 0

And apply to a table using Terms aggregation for each of the 3 fields. You don’t need to sum anything. I think I must be missing something here, as this is a very very straightforward table to build. If so, maybe try just create say in excel a table that shows what you desire.

If you effectively want a JOIN between the 2 doc “styles” then this is likely possible too, via correctly/carefully constructed ES|QL query, but others are better placed to suggest the correct query.

[poewyd]

Hi.

In perfect it should look like this:

Table schema in first post - base structure that stopped my work.

Responses with no offers means calls with 0 NumberOfSearchResults or cases when this log is absent.
For single call there always log with SearchLocations, but for this call log with NumberOfSearchResults can be absent.

According to my logic, as all logs have RequestId, we can use it for grouping.

Long story short: i need to know number of calls that returned no offers for the route per Tenant grouped by GDS and how many % of calls had no offers.

I should not attach 3 logs. Third one is about filtered results and it something that would be nice to include to the table, but it is optional.