Using multiple operators in a single query

Follow

Aaron Stratton

• March 01, 2021 18:35

Hello,

We are attempting to create an alert that would find login failures based on IP addresses. In the alert we would like to know how many servers(beyond 2) the IP is failing on as well as how many total failures across all servers the IP has gotten. We have a search and a lookup table that gets us the IP addresses and how many failures they have on each individual server. We are looking to simplify this to one line per IP with just the IP, the sum of their failures across all servers, and the number of servers they are failing on. Our search is basically something like:

_sourceCatagory
_sourceName
| parse ip_address
| parse server
| count ip_address, server

This populates our table and then we need to query the table and ask both:

| sum(_count) by ip_address

and

| count_distinct(server) by ip_address
| where _count_distinct >=2

Both of these queries need to be combined into one alert that we can then send one alert. We can send these separately without issue but we would like them to be consolidated.

0

• Official comment

  

  Graham Watts

  • March 02, 2021 02:11

  Hi Arron,
  
  It's hard to confirm without testing but Yit seems like you may be able to use the accum and total operators here, can you try this approach?
  
  _sourceCatagory=test _sourceName=test
  | parse ip_address...
  | parse server...
  | count by ip_address,_sourcehost
  | sort ip_address
  | total _count by ip_address
  | 1 as counter
  | accum counter by server
  | where _accum >1
  
  

  Comment actions Permalink
• 

  Aaron Stratton

  • March 02, 2021 18:27

  Hi Graham,

  Thank you this is brilliant. I implemented your counter and used accum and total like you did in your example and we have exactly what we were looking for. This has saved me a lot of headaches.

  0

  Comment actions Permalink
• 

  Graham Watts

  • March 03, 2021 01:43

  Happy to help Aaron, I am a fan of doing it all in one search if possible! 

  0

  Comment actions Permalink

Please sign in to leave a comment.