# User session duration calculation

**URL:** <https://discuss.elastic.co/t/user-session-duration-calculation/290993>\
**Category:** Elasticsearch\
**Tags:** painless, transforms\
**Created:** [December 5, 2021, 8:57pm UTC](https://discuss.elastic.co/t/user-session-duration-calculation/290993 "2021-12-05T20:57:42Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![ramous-tun](https://avatars.discourse-cdn.com/v4/letter/r/a587f6/32.png) [@ramous-tun](https://discuss.elastic.co/u/ramous-tun)\
**Post date:** [December 5, 2021, 8:57pm UTC](https://discuss.elastic.co/t/user-session-duration-calculation/290993/1 "2021-12-05T20:57:42Z")

</div>

Hi,  
I'm trying to get user session duration by day for the last 30 days from windows logs, so I used Transform but something wrong in my script, I always get duration for all period recorded: from the first login to the last logout.  
I saw all similar subjet, but I didn't find somthing working for me.

```auto
PUT _transform/windows-login-durations
{
  "source": {
    "index": [
      "windows-security-logs-*"
    ],
    "query": {
      "bool": {
        "should": [
          {
            "bool": {
              "should": [
                {
                  "match": {
                    "EventID": "4634"
                  }
                }
              ],
              "minimum_should_match": 1
            }
          },
          {
            "bool": {
              "should": [
                {
                  "match": {
                    "EventID": "4624"
                  }
                }
              ],
              "minimum_should_match": 1
            }
          }
        ],
        "minimum_should_match": 1
      }
   }
  },
  "pivot": {
"group_by": {
    "TargetLogonId": {
      "terms": {
        "field": "TargetLogonId"
      }
    },
    "TargetUserName.keyword": {
      "terms": {
        "field": "TargetUserName.keyword"
      }
    },
    "Hostname.keyword": {
      "terms": {
        "field": "Hostname.keyword"
      }
    },
    "timestamp": {
      "terms": {
        "field": "@timestamp"
      }
    }
  },
  "aggregations": {
    "time_frame.lte": {
      "max": {
        "field": "@timestamp"
      }
    },
    "time_frame.gte": {
      "min": {
        "field": "@timestamp"
      }
    },
    "duration": {
      "bucket_script": {
        "buckets_path": {
            "min": "time_frame.gte.value",
            "max": "time_frame.lte.value"
        },
          "script": "(params.max - params.min)/1000"
      }
    }
  }
  },
  "frequency": "1m",
  "dest": {
    "index": "windows-login-durations"
  }
}

```

---

<div class="post-metadata">

**Author:** ![przemekwitek](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/przemekwitek/32/79526_2.png) [@przemekwitek](https://discuss.elastic.co/u/przemekwitek)\
**Post date:** [December 7, 2021, 9:19am UTC](https://discuss.elastic.co/t/user-session-duration-calculation/290993/2 "2021-12-07T09:19:34Z")

</div>

Hi,  
Could you please try changing `terms` to `date_histogram` in the `pivot.group_by` section?  
It does not seem right to me to use `terms` together with `timestamp` if you need to group "by day".  
I think it should look similar to:

```auto
    "timestamp": {
      "date_histogram": {
        "field": "@timestamp",
        "calendar_interval": "1d"
      }
    }

```

---

<div class="post-metadata">

**Author:** ![ramous-tun](https://avatars.discourse-cdn.com/v4/letter/r/a587f6/32.png) [@ramous-tun](https://discuss.elastic.co/u/ramous-tun)\
**Post date:** [December 8, 2021, 10:20am UTC](https://discuss.elastic.co/t/user-session-duration-calculation/290993/3 "2021-12-08T10:20:46Z")

</div>

Thanks @przemekwitek, that's worked for me.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [January 5, 2022, 10:21am UTC](https://discuss.elastic.co/t/user-session-duration-calculation/290993/4 "2022-01-05T10:21:03Z")

</div>

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