How to filter on 'count' without losing original results?

[ttovarzoll]

I am trying to build an Alert which will trigger whenever one of our AWS-hosted Active Directory domains get replacement Domain Controllers, i.e., we don't control if/when they replace the servers. I already have a simple Alert which counts how many unique DCs it sees per-hosted domain, and then I can do a simple:

index=os sourcetype="xmlwineventlog
# here I perform some clean-up to identify the 2 desired fields... #
stats count Domain, DC_hostname
stats count Domain
where count>2

(and where the default number of DCs = 2, i.e., if there are more than that, AWS is in the process of replacing one or both.)

The problem is that I lose the list of DCs.

How can I filter-out all the domains that just have the typical 2 DCs while still keeping the complete list of DCs from the non-typical domain?

-------------------------

FYI  - this is what the search looks like before my final filter:

Domain         DC_hostname
----------     -----------------------------
domain1        DC1
domain1        DC2
domain2        DC3
domain2        DC4
domain2        DC5

My current Alert returns simply:
domain2

whereas I want it to return:

domain2        DC3
domain2        DC4
domain2        DC5

[inventsekar]

Hi @ttovarzoll ... actually, bit difficult to understand this issue..

it will be helpful if you could pls copy paste your current full SPL Search query (pls remove any hostnames, etc)

[ttovarzoll]

I'm surprised, I honestly thought I provided enough details.  I really don't see how this will help but here it is:

index=os sourcetype="xmlwineventlog" 
``` extract and normalize the Domain name ```
| rex field=source "/aws/directoryservice/(?<ds_name>.+):"
| eval Domain = if( lower( substr(ds_name,0,2))="zo", lower( substr(ds_name, 0, 8)), lower( substr(ds_name, 14, len(ds_name) - 12)))
``` extract and normalize the Domain Controller hostname ```
| rex field=Computer "(?<DC_extract>.+).z"
| eval DC_hostname = if( isnull( DC_extract ), upper(Computer), upper( DC_extract ) )
``` count how many messages from each DC ```
| stats count by Domain, DC_hostname
``` now count how many DCs per-domain
| stats count by Domain
`` finally, notify of any domains with more than 2 DCs ```
| where count>2

[bowesmana]

Use eventstats, not stats in your final filter

| eventstats count by Domain
| where count > 2

[PickleRick · Accepted solution]

That's one possible solution. Another one is to use values() with the stats command

| stats count values(DC_hostname) by domain
| where count>2

It depends on your use case which one is more appropriate for you

[ttovarzoll]

Thank you! Both of those solutions worked. I decided to use the 'stats count values() by' as my solution because I liked how it created a single multi-value result.

[PickleRick]

It's not even that it produces results in a multivalued format or not (because you can easily do mvexpand, for example), but the stats solution might be a bit faster (it is distributable and can take advantage of map/reduce) whereas the eventstats doesn't lose the original event contents (or at least might retain them). So these are two different approaches, both producing a bit different results but both containing the end results you need.

[inventsekar]

Ohk now i got it ..

Please try to run your search query without that 

| stats count by Domain

Edit ... i mean, please try running this SPL:

index=os sourcetype="xmlwineventlog" 
``` extract and normalize the Domain name ```
| rex field=source "/aws/directoryservice/(?<ds_name>.+):"
| eval Domain = if( lower( substr(ds_name,0,2))="zo", lower( substr(ds_name, 0, 8)), lower( substr(ds_name, 14, len(ds_name) - 12)))
``` extract and normalize the Domain Controller hostname ```
| rex field=Computer "(?<DC_extract>.+).z"
| eval DC_hostname = if( isnull( DC_extract ), upper(Computer), upper( DC_extract ) )
``` count how many messages from each DC ```
| stats count by Domain, DC_hostname
``` now count how many DCs per-domain
``` | stats count by Domain - commented out, for testing```
``` finally, notify of any domains with more than 2 DCs ```
| where count>2