Comparing two terms

Elastic Stack Kibana
1

In Kibana (e.g Visualise), is it possible to compare one term with another?

For example, I have indexed records that contain an indoorTemp value and a setpointTemp value - I have a line chart that shows the indoorTemp over time (using the Date histogram on the x-axis), but I would like to find just those times (e.g. hour buckets), where the value of indoorTemp exceeded that of setpointTemp.

I would settle for being able to show buckets where indoorTemp was greater than a constant (I can't see how do do that either), but would prefer to use setpointTemp.

Many thanks,
Andrew

P.S. I am using the Elastic Cloud trial at the moment.

2

You can create a scripted field that takes the difference between those two fields (assume each document has both fields). And then you can filter data like tempDiff: >0 or <0 depending on which way you subtract them.

  1. Go to Settings
  2. Select your index pattern name near the upper-left corner
  3. click the "Scripted fields" tab
  4. There's an example on the page. Just take the difference of the value of your two fields and then try using a filter on that scripted field.

Regards,
Lee

3

Hi Lee - thanks for the suggestion. Unfortunately, although I have successfully created the scripted field and can see it has valid values, I can't get it to filter. Please take a look at my screenshots and see if you can work out what I have missed.
Regards,
Andrew

scriptedField.PNG705×216 11.7 KB

noFilter.PNG1000×627 47 KB

filteredNoResults.PNG999×366 28 KB

4

Can you try to create a filter on a Visualization by clicking and then pin the filter. Here's an example. Create a histogram of your HeatDiff, and click on the bar where HeatDiff = 0. Then just edit the filter.

filter.gif2032×1489 2.44 MB

5

My filter definition looks very different to yours:

{
"script": {
"script": "(doc['indoorTemp'].value - doc['heatSetPoint'].value) == value",
"lang": "expression",
"params": {
"value": 3
}
}
}

How do I modify that to do the 'greater than' test?

6

Actually, I didnt have a histogram. I now get:

{
  "script": {
    "script": "(doc['indoorTemp'].value - doc['heatSetPoint'].value)>=gte && (doc['indoorTemp'].value - doc['heatSetPoint'].value)<lt",
    "params": {
      "gte": 0,
      "lt": 5,
      "value": ">=0 <5"
    },
    "lang": "expression"
  }
}

I am still not clear how that helps me.

7

Hi Andrew,

I think you want to edit your filter like this so that you only get results where your temp difference is greater than 0. You can pin this filter and go back to Discover tab and see the individual documents.

{
  "script": {
    "script": "(doc['indoorTemp'].value - doc['heatSetPoint'].value)>gt",
    "params": {
      "gt": 0,
      "value": ">0"
    },
    "lang": "expression"
  }
}
8

Lee, that seems to work, but I don't understand the syntax. Why is there a 'gt' in the script field, and also in the params node? Why, in the params node, do I have to specify the threshold (0 in this case) against the 'gt' field and the 'value' field?

Thanks,
Andrew

9

Hi Andrew,

I'm pretty new on this scripting but it looks like the "gt" in the script is a variable and it's value is set to 0 in the params.

But I'm confused why the params would have the "value": ">0" part. It wouldn't make sense to substitute ">0" in place of value in the script. I'll try to find some documentation or expert help on it.

Here's one reference of scripting which does not have the value param;
https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-script-fields.html

In a quick test I just did, I removed the value param and still had the same results.

The more I think about it, the more I think that "value" param is just naming the result.

Regards,
Lee

10

But this query syntax isn't even using the heatDiff scripted field I created earlier. Lets go back to basics - if I just wanted to filter on my indoorTemp field being greater than 70, how would I format that in the query bar?
Thanks,
Andrew

11

Hi Andrew,

It took the script doc['indoorTemp'].value - doc['heatSetPoint'].value of your scripted field and put it in the filter. I would guess that's because Kibana knows about your heatDiff field but Elasticsearch doesn't.

If you just want to filter based on numeric fields > or < than something you can just put indoorTemp:>70 in the query bar, or create/edit a filter like this;

{
  "range": {
    "bytes": {
      "gt": 70
    }
  }
}
12

Thanks Lee - that helps. I hadn't appreciated that query bar just applied to elastic search, but it kind of makes sense.
Andrew

13

Hi Lee,

I am not a technical person. The question I had is -

I want to compare two fields which have string like data in it. I tried creating a script - "doc['country_1'].value != doc['country_2'].value" both these fields are present in 1 index. When I tried to create a visualisation using the script I got shard failure message. Can you suggest how do we go ahead with this comparison.

14

Hi Pankaj,

Is your script Language: painless and your Type: boolean?
Selecting Format: Boolean didn't seem to make any difference in my test.

Does every doc in your index have that field? If not, I think you would have to test them both in your Painless script.
Let me know if you need help with that.

Regards,
Lee

15

Thanks Lee. I am not sure, let me check with My tech team and get back to you.