User session duration calculation

Elastic Stack Elasticsearch
,
1

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.

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"
  }
}
2

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:

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

Thanks @przemekwitek, that's worked for me.

1 Like
4

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