Query to get unique fields

[WillemDH]

Hello,

Is it possible to make a query which only show rows with a unique field? We need this for creating an alert on some AD messages.

Code: Select all

The SAM database was unable to lockout the account of <username> due to a resource error, such as a hard disk write failure (the specific error code is in the error data) . Accounts are locked after a certain number of bad passwords are provided so please consider resetting the password of the account mentioned above.

For the above message I created a grok filter:

Code: Select all

if [sourcename] == "Microsoft-Windows-Directory-Services-SAM" and [eventid] == 12294 {
    grok {
        match => [ "message", "\AThe SAM database was unable to lockout the account of %{USERNAME:ad_username}%{GREEDYDATA}" ]
    }
    mutate {
      add_tag => "mutated_microsoft-windows-directory-services-sam_12294"  
    }
}

And now I would need to make an alert if more then 50 unique usernames are found. At the moment my query returns all 12294 messages:

Code: Select all

type:"eventlog" AND channel:system AND hostname:*dc* AND eventid:12294

Please advice how to make a query which returns only the messages with an unique ad_username. Tx

Willem

[mcapra]

I can certainly think of a few ways to do this, but there isn't anything built-in to Nagios Log Server (or elasticsearch to my knowledge) that can tackle this very effectively.

In MySQL, you would essentially need to do a COUNT on a nested SELECT DISTINCT to accomplish this. Might look something like this:

Code: Select all

SELECT COUNT(SELECT DISTINCT name FROM table) AS name_count FROM table;

Which would return the total number of distinct "name" items as the "name_count" field. However, there doesn't really exist a COUNT function in elasticsearch that behaves in that fashion. There are options for doing the equivalent of a SELECT DISTINCT and counting the occurrences of those distinct values:

Code: Select all

[root@localhost ~]# curl -XGET 'http://localhost:9200/_all/_search?search_type=count&pretty' -d '{"aggs":{"distinct_name":{"terms":{"field":"name"}}}}'
{
  "took" : 3,
  "timed_out" : false,
  "_shards" : {
    "total" : 41,
    "successful" : 41,
    "failed" : 0
  },
  "hits" : {
    "total" : 274844,
    "max_score" : 0.0,
    "hits" : [ ]
  },
  "aggregations" : {
    "distinct_name" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [ {
        "key" : "dave",
        "doc_count" : 6
      }, {
        "key" : "bob",
        "doc_count" : 5
      }, {
        "key" : "alice",
        "doc_count" : 1
      }, {
        "key" : "jeff",
        "doc_count" : 1
      }, {
        "key" : "kim",
        "doc_count" : 1
      } ]
    }
  }
}

What this API call has done is ask for the count of every distinct value for the "name" field. While there are several occurrences of the name "dave" for example, only one single buckets entry is created for "dave" occurrences. Ideally elasticsearch would have some method by which we could tally up the count of unique keys for each bucket, but this functionality does not exist.

It's more of a limitation within elasticsearch than it is Nagios Log Server, though it could be solved within Nagios Log Server. If I had to solve this problem, I would write a PHP script to parse that JSON object as a PHP array and get the size of the buckets array.

I've filed a feature request for this (ID 9839). I can see where that sort of information/alerting would be useful, with the example of "how many unique users are accessing ContentX" coming to mind when considering DDOS detection.