Hi Rod,
What you need to use is multi level terms aggregation. General format of
such query is as following.
{
"aggs": { "agg1": { "terms": { "field": "field1" }, "aggs": { "agg2": {
"terms": { "field": "field2" }, "aggs": { "agg3": { "terms": { "field":
"field3" }
} } } } } }
}
In your case you can use fleeing query
{ "aggs": { "users": { "terms": { "field": "username" }, "aggs": {
"workstations": { "terms": { "field": "workstation" } } } } } }
Just to understand how it works you can play with sequence of aggs, users
and workstations and see how the output changes.
Regards
Sachin Divekar
--
Sent from phone
On Tue, Dec 16, 2014, 9:14 PM Rod Clayton <[email protected]> wrote:
> Dear Sachin,
>
> I want to aggregate them by username and workstation and get a count. I
> need to produce a report if there are too many failures for an account.
>
> I figured out how to limit the search to a particular day by saying http://
> http://localhost:9200/logstash-2014.11.19/_search?q=status:%20FAILURE&pretty
>
> I am looking for an example to aggregate on a couple of fields and get a
> count by value.
>
> Is that possible?
>
> Thanks,
> Rod
>
>
> On Tuesday, December 16, 2014 9:38:12 AM UTC-5, Sachin Divekar wrote:
>
>> I had mistakenly put extra space in the URL. Corrected URL is
>> http://localhost:9200/_search?q=status:FAILURE&pretty
>>
>> Regards
>> Sachin Divekar
>>
>> On Tue Dec 16 2014 at 8:01:37 PM Sachin Divekar <[email protected]> wrote:
>>
> Hi Rod,
>>>
>>> Try following URL
>>>
>>> http://localhost:9200/_search?q=status: FAILURE&pretty
>>>
>>> In output you will find something like following
>>>
>>> --------------------------------
>>>
>>> "hits": {
>>> "total": 7,
>>> "max_score": 1,
>>> "hits": [
>>>
>>> ---------------------------------
>>>
>>> So in "hits" block value of "total" field is your count of failed logon
>>> requests.
>>>
>>> For understanding search API and output of search query refer
>>> http://www.elasticsearch.org/guide/en/elasticsearch/reference/
>>> current/_the_search_api.html
>>>
>>> Regards
>>> Sachin Divekar
>>>
>>> On Tue Dec 16 2014 at 7:01:02 PM Rod Clayton <[email protected]> wrote:
>>>
>> Dear Sachin,
>>>>
>>>> Here is the GIST with the output you requested:
>>>>
>>>> ka3bhy <https://gist.github.com/ka3bhy> / *gist:082a5410d36264521ccb
>>>> <https://gist.github.com/ka3bhy/082a5410d36264521ccb>*
>>>>
>>>>
>>>>
>>>> On Monday, December 15, 2014 10:13:02 PM UTC-5, Sachin Divekar wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>> Share output of http://localhost:9200/foo/_search?pretty=true&q=*:*
>>>>> substitute foo with name of your index.
>>>>> Use gist to share the output. I suggest, read
>>>>> http://www.elasticsearch.org/help/
>>>>>
>>>>> Sachin Divekar
>>>>>
>>>>> On Tue, Dec 16, 2014, 1:38 AM Rod Clayton <[email protected]> wrote:
>>>>>
>>>> The logstash debug for the input logs look like:
>>>>>>
>>>>>> {
>>>>>> "message" => "37208057\tSecurity\tMicrosoft
>>>>>> -Windows-Security-Auditing\tSUCCESS AUDIT\tserver.myorg.org\t11/18/2014
>>>>>> 12:13:32 AM\t4776\tNone\t\"The computer attempted to validate the
>>>>>> credentials for an account. Authentication Package:
>>>>>> MICROSOFT_AUTHENTICATION_PACKAGE_V1_0 Logon Account: joe Source
>>>>>> Workstation: joescomputer Error Code: 0x0 \"",
>>>>>> "@version" => "1",
>>>>>> "@timestamp" => "2014-11-18T05:13:32.000Z",
>>>>>> "host" => "0:0:0:0:0:0:0:1:51947",
>>>>>> "type" => "logons",
>>>>>> "recno" => "37208057",
>>>>>> "logtype" => "Security",
>>>>>> "status" => "SUCCESS",
>>>>>> "hostname" => "server.myorg.org",
>>>>>> "eventCode" => "4776",
>>>>>> "username" => "joe",
>>>>>> "workstation" => "joescomputer",
>>>>>> "retcd" => "0x0",
>>>>>> "received_at" => "2014-12-15 19:25:49 UTC",
>>>>>> "received_from" => "0:0:0:0:0:0:0:1:51947"
>>>>>> }
>>>>>>
>>>>>> I have obscured the host names and accounts, but the fields are the
>>>>>> same.
>>>>>>
>>>>>> I am hoping for output like:
>>>>>>
>>>>>> username workstation name error code Count
>>>>>> root maryscomputer 6a 100
>>>>>> joe lab1 6a 5
>>>>>> joe lab2 6a 2
>>>>>> mary maryscomputer 6a 1
>>>>>>
>>>>>> This assumes that the detail records were all dated the same day.
>>>>>> I am expecting that this is going to come back in a JSON format that
>>>>>> I will have to format to look like above.
>>>>>>
>>>>>> Is this what you wanted?
>>>>>>
>>>>>>
>>>>>>
>>>>>> On Monday, December 15, 2014 1:03:07 PM UTC-5, Sachin Divekar wrote:
>>>>>>
>>>>>>> Hi,
>>>>>>>
>>>>>>> Can you share some sample data and desired output?
>>>>>>>
>>>>>>> Sachin Divekar
>>>>>>>
>>>>>>> On Mon, Dec 15, 2014, 10:00 PM Rod Clayton <[email protected]>
>>>>>>> wrote:
>>>>>>>
>>>>>> I have loaded login data into Elasticsearch using Logstash.
>>>>>>>>
>>>>>>>> I have fields: username retcd workstation.
>>>>>>>>
>>>>>>>> I want to query and get a count of failed logon requests by
>>>>>>>> username and workstation on a given day.
>>>>>>>>
>>>>>>>> The indexes are named like logstash-2014.11.18.
>>>>>>>>
>>>>>>>> What would a query for this look like on the day listed above?
>>>>>>>>
>>>>>>>> Thanks,
>>>>>>>> Rod
>>>>>>>>
>>>>>>>> --
>>>>>>>> You received this message because you are subscribed to the Google
>>>>>>>> Groups "elasticsearch" group.
>>>>>>>>
>>>>>>> To unsubscribe from this group and stop receiving emails from it,
>>>>>>>> send an email to [email protected].
>>>>>>>
>>>>>>>
>>>>>>>> To view this discussion on the web visit
>>>>>>>> https://groups.google.com/d/msgid/elasticsearch/dd8ca3ed-c9e
>>>>>>>> 6-478a-ad77-9418e5822296%40googlegroups.com
>>>>>>>> <https://groups.google.com/d/msgid/elasticsearch/dd8ca3ed-c9e6-478a-ad77-9418e5822296%40googlegroups.com?utm_medium=email&utm_source=footer>
>>>>>>>> .
>>>>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>>>>>
>>>>>>> --
>>>>>> You received this message because you are subscribed to the Google
>>>>>> Groups "elasticsearch" group.
>>>>>> To unsubscribe from this group and stop receiving emails from it,
>>>>>> send an email to [email protected].
>>>>>>
>>>>> To view this discussion on the web visit https://groups.google.com/d/
>>>>>> msgid/elasticsearch/f6d94667-d81d-40de-a927-de088e2bee69%40goo
>>>>>> glegroups.com
>>>>>> <https://groups.google.com/d/msgid/elasticsearch/f6d94667-d81d-40de-a927-de088e2bee69%40googlegroups.com?utm_medium=email&utm_source=footer>
>>>>>> .
>>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>>>
>>>>> --
>>>> You received this message because you are subscribed to the Google
>>>> Groups "elasticsearch" group.
>>>> To unsubscribe from this group and stop receiving emails from it, send
>>>> an email to [email protected].
>>>>
>>> To view this discussion on the web visit https://groups.google.com/d/ms
>>>> gid/elasticsearch/8267fbc1-63cd-400d-a969-5f6191203991%40goo
>>>> glegroups.com
>>>> <https://groups.google.com/d/msgid/elasticsearch/8267fbc1-63cd-400d-a969-5f6191203991%40googlegroups.com?utm_medium=email&utm_source=footer>
>>>> .
>>>> For more options, visit https://groups.google.com/d/optout.
>>>>
>>> --
> You received this message because you are subscribed to the Google Groups
> "elasticsearch" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/elasticsearch/21cb8c2a-d9bc-497c-a217-bea52dcc2632%40googlegroups.com
> <https://groups.google.com/d/msgid/elasticsearch/21cb8c2a-d9bc-497c-a217-bea52dcc2632%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
> For more options, visit https://groups.google.com/d/optout.
>
--
You received this message because you are subscribed to the Google Groups
"elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/CA%2BA8H7mshmZQ_QQBTrpxigc3atYUJb3E0CeBCL4VcWbyQ%2BztrQ%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
