Chaining two queries, grouping and paritioning/windowing


(Konrads Smelkovs) #1

Hello,

I am investigating windows security logs. One query has identified a set of logins which contain a field - "Logon ID". I would now like to have a second query chained/pipelined which finds all the log entries that contain the LogonID, partition/window by this parameter and show me the earliest entry and latest (how long the session lasted).

So,

  • Query 1 outputs fields "Logon ID",
  • Query 2 takes these logon IDs and outputs fields LogonID, EarliestbyLogonID and LatestByLogonID

Is this possible at all in ES or is it a case of plugins/download and analyse?


(Adrien Grand) #2

You should be able to do something with a query that looks like this (just replace the query with your query and timestamp with the field that stores the date of your log lines):

GET logs/_search
{
  "size": 0,
  "query": "query that identifies interesting documents",
  "aggs": {
    "ids": {
      "terms": {
        "field": "LogonID"
      },
      "aggs": {
        "earliest_login": {
          "min": {
            "field": "timestamp"
          }
        },
        "latest_login": {
          "max": {
            "field": "timestamp"
          }
        },
      }
    }
  }
}

(system) #3

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