Search question - where clause...

[nbcohen]

I'm new to this, and not sure exactly what I'm doing yet - making progress, but still have a ways to go... I have managed to create a search string that gets most of the information I'm looking for. It looks like this:

index="mydata" |stats dc(Hostname) as NumHost, count as EventCount by supportGroup

This produces a table that looks like:

supportGroup    NumHost     EventCount
AAAAA              3               22
BBBBB              1                2
CCCCC             22              222
DDDDD              8               16
EEEEE             43             4670

So far, so good. But in fact, I only want to select hosts where the event count FOR THOSE HOSTS is > some threshold (say 100 events). If I redo the query:

index="mydata" |stats dc(Hostname) as NumHost, count as EventCount by supportGroup | where EventCount > 100

That would eliminate rows AAAAA, BBBBB, and DDDDD from the table, but in group BBBBB, I may have 21 hosts with one event each, and one host with 201 events - I want to select only that one host (and the count should be 201, not 222). I have not been able to figure out how to state my query to make this happen - can someone point me at a tutorial that works out something like this, or give me a clue on how to structure such a query?

Thanks in advance,

nbc

[gkanapathy · Accepted solution]

Seems like you chould:

index=mydata 
| stats count, first(supportGroup) as supportGroup 
  by Hostname 
| where count> 100 
| stats dc(Hostname) as NumHost, sum(count) as EventCount 
  by supportGroup

[nbcohen]

That looks like it does what I want - Thanks!

nbc