How can kibana compare two fields?

[rodri.gz (RGZ-ELK)]

hello!

I want to compare two fields in the query bar. is it posible to do it in kibana ?

example:

field_1 == value_1
field_2 == value_2

thanks in advance!

[lukas (Lukas Olson)]

Hi! This is actually something we discussed when creating KQL. Unfortunately it's not currently possible, but there aren't any technical limitations why it couldn't be implemented. I've opened an enhancement request here: [KQL] Allow comparing the values for different fields · Issue #110699 · elastic/kibana · GitHub

[ghudgins (Graham Hudgins)]

you may be able to use a runtime field here...although it's a bit tricky to set up.

take a look at the example below: the first if checks to see if the data exists (in my metric data the nginx data isn't always there...) and, if it does, the next if block returns a 1 when the active connections field is greater than the waiting.
you don't have to emit a 1 or a 0 you could emit the field itself, the difference between the two, or something else entirely.

Hope this helps. If you find this is really important to your data, I recommend you process this runtime field during indexing for improved performance.

The runtime field UI was added to Kibana in 7.13+

//Return a 1 when one field is greater than another in the same document
if (doc['nginx.stubstatus.active'].size()==0) {
    emit(0);
}
else {
    if (doc['nginx.stubstatus.active'].value > doc['nginx.stubstatus.waiting'].value   ) {
        emit(1);
    }
    else {
        emit(0);
    }
}

Setup in Kibana 7.13+

[rodri.gz (RGZ-ELK)]

First of all, thanks for your response!

Few days ago i tried something similar with scripted fields but the results were not as expected.

this script worked correctly returning a 1 if its true and a 0 if it isn´t but it was not good enough because the data was not real because i need to do that with sum.fields.

i tried something like sum['backups_ok'] < sum['backups_req'] but it seems that kibana does not accept sum fields in queries.

Does runtime fields works with sum aggregations ??

thanks in advanced!

[Marco_Liberati]

Hi @rodri.gz

If you want to operate with aggregations Lens formula are probably a better option than runtime fields.
The only limitation I see is that Lens formulas currently do not support conditional operations.

As for the greater than operation, have some strong assumptions as all numbers involved are integers, you may approximate the gt operation with some equation: it will be very verbose but doable.
The linked equation returns 0 if x < k, but returns x otherwise.

Here I am replicating the linked equation in Lens formula with the bytes (the k) and machine.ram (the x) fields:

(sum(machine.ram)/2) * 
( 
    (sum(machine.ram) - sum(bytes) ) / 
    clamp(
      abs(sum(machine.ram) - sum(bytes))
      , 0.0001
      , sum(machine.ram) + sum(bytes)
    ) + 1
)
/ sum(machine.ram)

If you have noted there are 2 small differences from the linked equation in this formula:

• clamp( ..., 0.0001, sum(machine.ram) + sum(bytes)) this is due to the case where the two sum aggregations are the same number, so I'm limiting the abs result to be within 0.0001 and a high value - I've taken sum(machine.ram) + sum(bytes) as higher extreme - it cannot be higher than the sum of both
• / sum(machine.ram) at the end divides result in case sum(machine.ram) is higher than sum(bytes) - the case where x in returned in the original formula.

This formula should return:

• 0 if sum(machine.ram) <= sum(bytes)
• 1 if sum(machine.ram) > sum(bytes)

[rodri.gz (RGZ-ELK)]

i tried something like this:

(sum(backups_req)/2) * 

( 

    (sum(backups_req) - sum(backups_ok) ) / 

    clamp(

      abs(sum(backups_req) - sum(backups_ok))

      , 0.0001

      , sum(backups_req) + sum(backups_ok)

    ) + 1

)

/ sum(backups_req)

but when i try to save it returns this error:

[Marco_Liberati]

It looks like you've put the formula into the runtime_fields editor.

Formulas are a Lens specific feature:

[rodri.gz (RGZ-ELK)]

I want to do that but with maps.

My intention is to create a map that colors only those countries in which the backups_ok are less than the required backups and I need the sum of the fields because otherwise it will only work for a specific day.

i tried this filter and it work for unique days

{
  "constant_score": {
    "filter": {
      "script": {
        "script": "doc['backups_ok'].value < doc['backups_req'].value"
      }
    }
  }
}

thanks for answering!