Correlation/updation of document score on timestamp, during query time in kibana search

Rahul_Lao · June 6, 2017, 1:01pm

Hello,

In Kibana say i am able to populate data using a Tile Map as heat map.

Consider below few lines of data that I index in elastic search

3078.48,-96.7991,33.0579,148.39,109.44,0.0,a808c4,,false,1.496753069E9,2017-06-06 14:44:29,United States
9159.24,-87.9397,34.6983,220.73,268.93,-11.38,a086d8,FDX476  ,false,1.496753004E9,2017-06-06 14:41:58,United States
5661.66,-84.9921,38.679,164.45,221.07,13.0,ac1c13,N88GA   ,false,1.496753068E9,2017-06-06 14:44:28,United States
518.16,-96.5803,33.2091,61.27,40.91,5.53,a798d0,N589RB  ,false,1.496753062E9,2017-06-06 14:44:22,United States
22.86,113.9047,22.313,5.92,115.0,null,89906e,        ,false,1.496753068E9,2017-06-06 14:43:17,Taiwan
11277.6,19.0861,51.7587,244.74,55.27,0.0,4240ec,UTA800  ,false,1.496753069E9,2017-06-06 14:44:29,United Kingdom
2019.3,-95.2651,30.0928,126.04,89.77,-6.18,ac52ee,ASH6048 ,false,1.496753069E9,2017-06-06 14:44:29,United States
441.96,-97.2186,33.1877,46.31,181.27,-4.23,a621f8,N49447  ,false,1.496753069E9,2017-06-06 14:44:29,United States
null,-73.8739,40.7753,1.03,258.0,null,a52911,4685    ,true,1.496752788E9,2017-06-06 14:39:48,United States
1638.3,-95.1905,30.0929,117.85,88.5,-8.45,ac52f2,ASH6129 ,false,1.496753068E9,2017-06-06 14:44:22,United States

The second last field is the lastupdatedtimestamp that means the time at which data for that row was updated.

Is there a way by which I can query in kibana search bar such that the most recent records are fetched with their updated scores? That is the most recent one should have highest score and gradually decreasing.

Finally I am trying to achieve is whenever I give a Geo bounding query in kibana search bar (that is two location coordinates) i get records in decreasing order of lastupdatedtimestamp with score of most recent one the maximum.

Please suggest.
Thanks

weltenwort · June 6, 2017, 4:24pm

Hi @Rahul_Lao,

it is not possible to influence the sorting via the query bar right now.

If you intend to use the query in Discover, you can add the column to the table and sort by it by clicking on the icons in the header of that column.

Rahul_Lao · June 8, 2017, 7:28am

Hello @weltenwort,

Would this sorting via query feature be added in near future?

Rahul_Lao · June 8, 2017, 8:10am

Say i use below query in the kibana query bar

{"bool":{"must":{"match_all":{}},"filter":{"geo_bounding_box":{"location":{"top_left":{"lon":-23.965,"lat":65.555801},"bottom_right":{"lon":-66.330299,"lat":47.990799}}}}}}

Then the result i get is set of records that lie between those coordinates.

But the score of these records is not effected that is it remains 1.

Shouldn't the score be updated?

Even when i use the below query to fetch me records that lie within specific distance, i get results but with score being unchanged.

{"bool":{"must":{"match_all":{}},"filter":{"geo_distance":{"distance":"50km","location":{"lon":-23.965,"lat":65.555801}}}}}

Shouldn't the score for the most nearest one be more than the one that is far?

Also in the browser address bar i observe that the url has not condition for sorting on score

http://10.128.7.110:5601/app/kibana#/discover?_g=(filters:!(),refreshInterval:(display:Off,pause:!f,value:0),time:(from:now-30d,mode:quick,to:now))&_a=(columns:!(_source),index:flights_dataset,interval:auto,query:(bool:(filter:(geo_bounding_box:(location:(bottom_right:(lat:47.990799,lon:-66.330299),top_left:(lat:65.555801,lon:-23.965)))),must:(match_all:()))),sort:!(_score,desc))

As you can see from the above url in the last sort:!(_score,desc))

Therefore finally two doubts

How can i make the above queries work such that the result that are fetched have updated scores and not just random constant score?
How can we then sort the result according to relevancy?

weltenwort · June 8, 2017, 9:57am

Hi @Rahul_Lao,

queries executed in the filter context do not influence the score. But there are two way of using the geo distance query to influence the sorting as explained in The Definitive Guide:

Use the _geo_distance clause in the sort array to sort the result by distance. When using a sort clause, the score is not calculated unless explicitly enabled.
Use a function_score query to explicitly include the distance in the score as demonstrated in the guide chapter on decay functions and don't explicitly specify the sorting.

Only the second solution can currently be used in Kibana's Discover app at the moment, because it is not possible to specify the _geo_distance clause in the sort array. When sorting by the _score field, the query could look similar to:

{
  "query": {
    "bool": {
      "must": {
        "function_score": {
          "${YOUR_DECAY_FUNCTION}": {
            "geo.coordinates": {
              "origin": {
                "lat": ${YOUR_LATITUDE},
                "lon": ${YOUR_LONGITUDE}
              },
              "offset": "${YOUR_OFFSET}km",
              "scale": "${YOUR_SCALE}km"
            }
          }
        }
      },
      "filter": {
        "geo_distance": {
          "distance": "${YOUR_CUTOFF_DISTANCE}km",
          "geo.coordinates": {
            "lat": ${YOUR_LATITUDE},
            "lon":${YOUR_LONGITUDE}
          }
        }
      }
    }
  }
}

Rahul_Lao · June 9, 2017, 10:58am

Thanks, this was really helpful.

Rahul_Lao · June 9, 2017, 12:10pm

Referring the above example, I constructed the below query.

I am trying to define an origin location with a scale of 2km. And i want to get me all records that exists within a specific distance (45km) from a geo point (the one specified in filter section)

But the result set is zero records.

Query

curl -XGET 'localhost:9200/test/_search?pretty' -H 'Content-Type: application/json' -d'
{
  "query": {
    "bool": {
      "must": {
        "function_score": {
          "gauss": {
            "geoip.location": {
              "origin": {
                "lat": 46.5768,
                "lon": 11.1508
              },
              "scale": "2km"
            }
          }
        }
      },
      "filter": {
        "geo_distance": {
          "distance": "45km",
          "geoip.location": {
            "lat": 46.5768,
            "lon": 11.1508
          }
        }
      }
    }
  }
}
'

Output

{
  "took" : 4,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "failed" : 0
  },
  "hits" : {
    "total" : 0,
    "max_score" : null,
    "hits" : [ ]
  }
}

Please can you suggest what is wrong?

weltenwort · June 12, 2017, 2:09pm

In your first examples you used location as the field while you used geoip.location in latest message. Is that change intentional?

If that is not the problem, have you tried removing the function_score query and just tried to get the geo_distance filter to work on its own?

Rahul_Lao · June 13, 2017, 12:53pm

Sorry that was my mistake. Have to use location instead of geoip.location.

Secondly when running the query with the score function and just with filter context, the score will not be influenced as stated by you earlier. And in actual also it does not.

But the strange thing is that when running the corrected query that is with location attribute, i do get the results but without any score in kibana and in terminal score of 0 for the results.

Result from terminal

{
  "took" : 2,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "failed" : 0
  },
  "hits" : {
    "total" : 3,
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "test",
        "_type" : "logs",
        "_id" : "AVxUOuMaCj46ZjiRI0pq",
        "_score" : 1.0,
        "_source" : {
          "altitude" : "9982.2",
          "myscore" : 22,
          "heading" : "345.11",
          "lastvelocityupdate" : "1.49519517E9",
          "velocity" : "260.3",
          "onground" : "false",
          "path" : "/home/vagrant/dataset/small_dataset.csv",
          "@timestamp" : "2017-05-29T12:42:44.171Z",
          "verticalrate" : "8.13",
          "@version" : "1",
          "callsign" : "EZY87VZ",
          "icao" : "400fe2",
          "location" : {
            "lon" : 11.1508,
            "lat" : 46.5768
          },
          "lastpositionupdate" : "2017-05-19 13:59:45",
          "origincountry" : "United Kingdom"
        }
      },
      {
        "_index" : "test",
        "_type" : "logs",
        "_id" : "AVxUOuMaCj46ZjiRI0pp",
        "_score" : 0.0,
        "_source" : {
          "altitude" : "8702.04",
          "myscore" : 6,
          "heading" : "345.4",
          "lastvelocityupdate" : "1.49519499E9",
          "velocity" : "255.17",
          "onground" : "false",
          "path" : "/home/vagrant/dataset/small_dataset.csv",
          "@timestamp" : "2017-05-29T12:42:44.169Z",
          "verticalrate" : "4.23",
          "@version" : "1",
          "callsign" : "EZY87VZ",
          "icao" : "400fe2",
          "location" : {
            "lon" : 11.3045,
            "lat" : 46.1689
          },
          "lastpositionupdate" : "2017-05-19 13:56:30",
          "origincountry" : "United Kingdom"
        }
      },
      {
        "_index" : "test",
        "_type" : "logs",
        "_id" : "AVxUOuMaCj46ZjiRI0pr",
        "_score" : 0.0,
        "_source" : {
          "altitude" : "10972.8",
          "myscore" : 51,
          "heading" : "337.59",
          "lastvelocityupdate" : "1.49519535E9",
          "velocity" : "250.97",
          "onground" : "false",
          "path" : "/home/vagrant/dataset/small_dataset.csv",
          "@timestamp" : "2017-05-29T12:42:44.178Z",
          "verticalrate" : "0.33",
          "@version" : "1",
          "callsign" : "EZY87VZ",
          "icao" : "400fe2",
          "location" : {
            "lon" : 10.9876,
            "lat" : 46.9772
          },
          "lastpositionupdate" : "2017-05-19 14:03:30",
          "origincountry" : "United Kingdom"
        }
      }
    ]
  }
}

As you can see the score is 1.0 for result 1 and then 0 for result 2 and 3.

Result from kibana

I guess its because of filter context used in the query? Not sure though.
Can you clarify?

weltenwort · June 13, 2017, 1:22pm

Elasticsearch does not track the _score by default if a sort parameter is present. Since the Discover table is sorted by the time field by default, there are no scores. Adding the _score column to the table and sorting by it should cause Elasticsearch to calculate the scores.

weltenwort · June 13, 2017, 1:28pm

In regards to the 0 score in the console: I saw that you are using a pretty low scale factor of 2km for the gauss decay function compared to the 45km filter radius. Looking at the decay function documentation, it is not surprising that the score approaches 0 pretty quickly. You might have to tune the scale, offset and decay parameters of your decay function to match your filter distance or choose a different function altogether.

Rahul_Lao · June 13, 2017, 1:38pm

Yes, i was thinking the same.

Finally thanks for your kind help. I will mark this as resolved.

system · July 11, 2017, 1:38pm

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