Need help using Group by field1,field2 using Elasticsearch DSL su

Plugins Alerting
1

I want to convert the following sql into Elasticsearch dsl query:

SELECT count(winlog.event_id) AS 'Event_Count' FROM winlogbeat-7.1.1-2019.09.09 WHERE ['winlog.event_id']='7040' AND ['winlog.provider_name']='Service Control Manager' group by host.name, winlog.provider_name

I tried above sql in Kibana and it is working and giving the right output and i want to get the same output using Elasticsearch dsl as it has more options.

It returned two hosts computer1 , computer2 and all the rest is under a specific host.

{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 21,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "host.name" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "computer1",
          "doc_count" : 19,
          "winlog.provider_name" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "Service Control Manager",
                "doc_count" : 19,
                "'Event_Count'" : {
                  "value" : 19
                }
              }
            ]
          }
        },
        {
          "key" : "computer2",
          "doc_count" : 2,
          "winlog.provider_name" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "Service Control Manager",
                "doc_count" : 2,
                "'Event_Count'" : {
                  "value" : 2
                }
              }
            ]
          }
        }
      ]
    }
  }
}
2

I tried in the following way but i am not able to figure out how can i get the same output as i got from SQL

{
    "size": 0,
    "query": {
        "bool": {
            "must": [
                {
                    "match": {
                        "winlog.event_id": {
                            "query": "7040"
                        }
                    }
                }
            ],
            "filter": [
                {
                    "term": {
                        "winlog.provider_name": {
                            "value": "Service Control Manager"
                        }
                    }
                },
                {
                    "range": {
                        "@timestamp": {
                            "from": "now-150d",
                            "to": "now"
                        }
                    }
                }
            ]
        }
    },
    "aggregations": {
        "host.name": {
            "terms": {
                "field": "host.name"
            },
            "aggregations": {
               
                "event_filter": {
                    "bucket_selector": {
                        "buckets_path": {
                            "the_doc_count": "_count"
                        },
                        "script": {
                            "source": "params.the_doc_count >=100"
                        }
                    }
                     
                }
            }
        },
                "group_by_providername": {
            "terms": {
                "field": "winlog.provider_name"
            }
        }
    }
}

Following is the output: see how it has listed both the hosts inside the same bucket instead of creating two separate keys with their respective group_by_providername bucket.

{
“_shards”: {
“total”: 198,
“failed”: 0,
“successful”: 198,
“skipped”: 56
},
“hits”: {
“hits”: ,
“total”: {
“value”: 903,
“relation”: “eq”
},
“max_score”: null
},
“took”: 9,
“timed_out”: false,
“aggregations”: {
“group_by_providername”: {
“doc_count_error_upper_bound”: 0,
“sum_other_doc_count”: 0,
“buckets”: [
{
“doc_count”: 903,
“key”: “Service Control Manager”
}
]
},
“host.name”: {
“doc_count_error_upper_bound”: 0,
“sum_other_doc_count”: 0,
“buckets”: [
{
“doc_count”: 776,
“key”: “computer1”
},
{
“doc_count”: 127,
“key”: “computer2”
}
]
}
}
}

3

Please can someone help.
How can i convert the following sql query to Elasticsearch DSL

SELECT count(winlog.event_id) AS 'Event_Count', host.name, winlog. provider_name FROM winlogbeat-7.1.1-2019.09.09 WHERE ['winlog.event_id']='7040' AND ['winlog.provider_name']='Service Control Manager' WHERE count(winlog.event_id) >= 100 GROUP BY host.name,winlog.provider_name

4

Resolved.
The whole reason for doing this was to access the bucket in trigger condition and in mustache templates.
My extraction query was correct, all i did; created a multi level loop to access the last bucket.

2 Likes