Use common field to filter 2 indexes

[byoungman (Bill Youngman)]

I'm new to Lens and this is what I'm hoping to accomplish

I have one index that contains transaction request information from clients and another index that contains the transaction response information for those clients. When things are operating normally there is a client id associated with each record that can be used as an identifier.

The issue is that if there is a problem with the backend processing system a value of 'UNKNOWN' gets entered into the client id for the response thus breaking the client chain for a transaction.

There is however a unique record identifier that is created by the transaction processing system that gets injected into each record that we can use when it is just a couple of clients that have a problem. We can take the uuid from the error response and go into the Kibana Discover space and search on that uuid and find out who the client was and what that passed in that could have caused an issue.

The problem is that when we have any sort of an outage window and we have to determine client impact it's very problematic to do this when there are possibly 1,000's of errors.

So my question then becomes can I create a Lens visualizations where I filter on the uuid's of the response error transactions and apply it to the request index so that I can get the corresponding client id?

TIA,
Bill Youngman

[flash1293 (Joe Reuter)]

This kind of request chaining (using the response of one request to build the second request) is not possible in Lens today.

The only place I'm aware of supporting something like this in Kibana at the moment is Canvas and using the results of SQL queries to build new SQL queries:

filters
| essql query={string "SELECT * FROM \"kibana_sample_data_logs\" WHERE extension in (" {filters | essql query="SELECT extension FROM otherindex WHERE filter='yes'" | joinRows column="extension" separator=","} ")"}
| table
| render

The "inner" query on "otherindex" is fetching the terms to search for for the rendered response (in this case allowed "extension" terms).

[byoungman (Bill Youngman)]

Thanks Joe I'll give this a try and see what happens.

Bill

[byoungman (Bill Youngman)]

Joe,

So this is what I have so far...

I created a dropdown element for my work pad and it is configured this way--

essql query="SELECT podId FROM \"filebeat-response-*\" GROUP BY podId"
| dropdownControl valueColumn="podId" filterColumn="podId"
| render

This will return a list of pods like this...
Pod1
Pod2
Pod3
...
UNKNOWN

Then I have created a data table this way--

filters
| essql 
  query={string "SELECT * FROM \"filebeat-request-*\" WHERE extension in (" {filters | essql query="SELECT extension FROM \"filebeat-response-*\" WHERE filter='UNKNOWN'" | joinRows column="extension" separator=","} ")"}
| table
| render

My intent is to get the client id's from the first index where the selection from the dropdown is 'UNKNOWN' - this denotes a backend error and we have to find out the clients who received the error.

When I run this I'm getting the following error--

Expression failed with the message:

[essql] > [string] > [essql] > Unexpected error from Elasticsearch: [verification_exception] Found 1 problem line 1:51: Unknown column [filter], did you mean [file.owner]?

Am I even remotely close to what you tried to explain to me.

Thanks,
Bill

[flash1293 (Joe Reuter)]

Yes, but it seems like you are not using your actual field names - extension and filter where just exemplary field names, you need to use your own field names instead (the "client id" you were talking about in your original post)

[byoungman (Bill Youngman)]

DOH! I'm an idiot - actually this is my first foray into Canvas creation.

Anyway I'm making progress - I think

I'm now to the getting parsing exception phase and not the 'wtf are you doing' phase.

Here's my query

SELECT clientId FROM \"filebeat-request-*\" WHERE trustedId HAVING (" {filters | essql query="SELECT clientId  FROM \"filebeat-response-*\" WHERE podId = 'pod3'" |joinRows column="trustedId"} ")"

Exception

Expression failed with the message:

[essql] > Couldn't parse Elasticsearch SQL query. You may need to add double quotes to names containing special characters. Check your query and try again. Error: [parsing_exception] line 1:1: mismatched input ')' expecting {'(', 'DEBUG', 'DESC', 'DESCRIBE', 'EXPLAIN', 'SELECT', 'SHOW', 'SYS', 'WITH'}

Work continues but wanted to give an update as to where I'm at.

Thanks,
Bill

[flash1293 (Joe Reuter)]

Hey, sorry for the late reply. Could you post your full expression? Just to make sure nothing is wrong there.

Instead of WHERE trustedId HAVING it should be WHERE trustedId IN

[byoungman (Bill Youngman)]

Made your recommended change and here is the full Canvas filters configuration

filters
| essql 
  query="SELECT trustedId FROM \"filebeat-request-*\" WHERE trustedId IN (" {filters | essql query="SELECT trustedId FROM \"filebeat-response-*\" WHERE podId = 'pod3'" |joinRows column="trustedId"} ")"
| table
| render

Running it gives the following error

On a side note - getting this to run will be a HUGE value add for my team. We can then take this concept and apply to other things.

Bill

[flash1293 (Joe Reuter)]

The string function call to concatenate the different parts are missing. Try this:

filters
| essql 
  query={string "SELECT trustedId FROM \"filebeat-request-*\" WHERE trustedId IN (" {filters | essql query="SELECT trustedId FROM \"filebeat-response-*\" WHERE podId = 'pod3'" |joinRows column="trustedId"} ")"}
| table
| render

Let me know whether that works.

[byoungman (Bill Youngman)]

Tried this and getting something new

[flash1293 (Joe Reuter)]

Looks like the inner query from filebeat-response is not returning any results?

You can check the value of parts of your expression by doing this in a separate element, for example:

filters | essql query="SELECT trustedId FROM \"filebeat-response-*\" WHERE podId = 'pod3'" |joinRows column="trustedId" | render as="debug"

This way you can also check how the outer query looks like:

string "SELECT trustedId FROM \"filebeat-request-*\" WHERE trustedId IN (" {filters | essql query="SELECT trustedId FROM \"filebeat-response-*\" WHERE podId = 'pod3'" |joinRows column="trustedId"} ")" | render as="debug"