Powershell and ElasticSearch

As we need to calculter user / CCU ratio, We were asked how many different users logged in the new env ?
With our nxlog and graylog configuration ( here ), we start filtering on 8001 gralog_8001

  What can we do with those events ? It will be very interesting to get from this query the user count. As we use nxlog, we dont need graylog extrator, indeed, events are sended to graylog in GELF format. With Graylog2 we generate quickvalue from PrincipalSameName, but due to unmodifable option, results are truncated… We’ll need to directly query ElasticSearch

Query ElasticSearch with Powershell

From version 3, Powershell have the Invoke-RESTMethod cmdlet, who allows to use the REST API of a webservice

Invoke-RestMethod -URI $URI -Method $method -Body $body 

So, in out case, we want to query ES to fetch akk 8001 events, we can use :

 
Invoke-RestMethod -URI "http://eshost:9200/_search?pretty=1" -Method 'POST' -ContentType 'application/json' -Body '{
    "from": 0,
    "size": 5000,
    "query": {
        "query_string": {
            "query": "_exists_:AccountName AND EventID:8001",
            "allow_leading_wildcard": false
        }
    },
    "post_filter": {
        "bool": {
            "must": {
                "range": {
                    "timestamp": {
                        "from": "2014-09-11 06:30:00.000",
                        "to": "2014-09-19 08:20:00.000",
                        "include_lower": true,
                        "include_upper": true
                    }
                }
          }
        }
    }
}'

We fetch all, events, but we prefere to get all used logonname. We used an “term facets” to create an equivalent to SQL “distinct”.

Invoke-RestMethod -URI "http://eshost:9200/_search?pretty=1&search_type=count" -Method 'POST' -ContentType 'application/json' -Body '{
    "from": 0,
    "size": 100,
    "query": {
        "query_string": {
            "query": "_exists_:AccountName AND EventID:8001",
            "allow_leading_wildcard": false
        }
    },
    "post_filter": {
        "bool": {
            "must": {
                "range": {
                    "timestamp": {
                        "from": "2014-09-11 06:30:00.000",
                        "to": "2014-09-19 08:20:00.000",
                        "include_lower": true,
                        "include_upper": true
                    }
                }
          }
        }
    },
    "facets" : {
        "users" : { "terms" : {
            "field" : "PrincipalSamName",
            "size"  :  1000
            }
        }
    }
}'

We change the query type to count to accelerated ES query process.

Our final PSH is :

@($(Invoke-RestMethod -URI "http://eshost:9200/_search?pretty=1&search_type=count" -Method 'POST' -ContentType 'application/json' -Body '{
    "from": 0,
    "size": 100,
    "query": {
        "query_string": {
            "query": "_exists_:AccountName AND EventID:8001",
            "allow_leading_wildcard": false
        }
    },
    "post_filter": {
        "bool": {
            "must": {
                "range": {
                    "timestamp": {
                        "from": "2014-09-11 06:30:00.000",
                        "to": "2014-09-19 08:20:00.000",
                        "include_lower": true,
                        "include_upper": true
                    }
                }
          }
        }
    },
    "facets" : {
        "users" : { "terms" : {
            "field" : "PrincipalSamName",
            "size"  :  1000
            }
        }
    }
}').facets.users.terms.term | select -uniq ).Count

Script Execution Time : 290ms, Events Recorded 45 000 000+ !