Elasticsearch bucket_script to subtract two timestamps

Elastic Stack Elasticsearch
1

Unable to subtract two time stamps using bucket_script aggregation:

Below is my query:

POST xxxxxxxxxxxxxxxxxxxxxxxxxx/_search?size=0
{
"query": {
"bool": {
"must": [
{
"match_phrase": {
"projectId.keyword": "xxxxxxxxxxxxxxxxxxxxxx"
}
}
],
"filter": {
"range": {
"page.time": {
"gte": "now-w",
"lte": "now"
}
}
}
}
},
"aggs": {
"sessionLengthData": {
"terms": {
"field": "sId.keyword",
"size": 10
},
"aggs": {
"firstUrl":{
"terms":{
"field":"page.time",
"size":1,
"order":{
"_term":"asc"
}
}
},
"lastUrl":{
"terms":{
"field":"page.time",
"size":1,
"order":{
"_term":"desc"
}
}
},
"sessionLength": {
"bucket_script": {
"buckets_path": {
"startTime": "firstUrl._bucket_key",
"endTime": "lastUrl._bucket_count"
},
"script": "params.endTime - params.startTime"

                        }
                    }
        }
      }
    }
    }

In bucket_script query i am taking time stamp values from upper aggregation query.

Below are my sample records:

"_source": {
          "projectId": "*************************",
          "sId": "6b7xisp971521692338655",
          "pId": "6b7xisp971521692338655.1",
          "startTime": 1521692338655,
          "page": {
            "sid": "6b7xisp971521692338655",
            "time": 1521700149947,
            "url": "********************************",
            "pageMinUrl": "************************",
            "host": "**************************************",
            "start": 0,
            "pageLoad": 1523,
            "network": 668,
            "dom": 669,
          }
}

 "_source": {
          "projectId": "*****************************",
          "sId": "6b7xisp971521692338655",
          "pId": "6b7xisp971521692338655.2",
          "startTime": 1521692338655,
          "page": {
            "sid": "6b7xisp971521692338655",
            "time": 1521700349782,
            "url": "********************************",
            "pageMinUrl": "******************************",
            "host": "****************************************",
            "start": 0,
            "pageLoad": 1537,
            "network": 212,
            "dom": 1109,
            
          }
}

Give me some suggestions how to use bucket_script to subtract to time stamps values

2

Do you need those firstUrl and lastUrl aggregations to be terms aggregations? Because if you change those to min and max aggregations the solution is simple:

{
  "query": {
    "bool": {
      "filter": {
        "range": {
          "page.time": {
            "gte": "now-w",
            "lte": "now"
          }
        }
      }
    }
  },
  "aggs": {
    "sessionLengthData": {
      "terms": {
        "field": "sId.keyword",
        "size": 10
      },
      "aggs": {
        "firstUrl": {
          "min": {
            "field": "page.time"
          }
        },
        "lastUrl": {
          "max": {
            "field": "page.time"
          }
        },
        "sessionLength": {
          "bucket_script": {
            "buckets_path": {
              "startTime": "firstUrl",
              "endTime": "lastUrl"
            },
            "script": "params.endTime - params.startTime"
          }
        }
      }
    }
  }
}
3

@abdon, Thanks for the reply. I need firstUrl and lastUrl aggregation to be terms aggregations only.

4

Alright. Well, you can still retrieve those terms aggs in addition to the min and max metric aggregations:

{
  "query": {
    "bool": {
      "filter": {
        "range": {
          "page.time": {
            "gte": "now-w",
            "lte": "now"
          }
        }
      }
    }
  },
  "aggs": {
    "sessionLengthData": {
      "terms": {
        "field": "sId.keyword",
        "size": 10
      },
      "aggs": {
        "firstUrl": {
          "terms": {
            "field": "page.time",
            "size": 1,
            "order": {
              "_term": "asc"
            }
          }
        },
        "lastUrl": {
          "terms": {
            "field": "page.time",
            "size": 1,
            "order": {
              "_term": "desc"
            }
          }
        },
        "min": {
          "min": {
            "field": "page.time"
          }
        },
        "max": {
          "max": {
            "field": "page.time"
          }
        },
        "sessionLength": {
          "bucket_script": {
            "buckets_path": {
              "startTime": "min",
              "endTime": "max"
            },
            "script": "params.endTime - params.startTime"
          }
        }
      }
    }
  }
}
5

@abdon, Thank you . It worked :grinning:

1 Like
6

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