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
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
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)
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
Use eventstats, not stats in your final filter
| eventstats count by Domain
| where count > 2
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
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.
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.
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