find events NOT in the lookup list?

Follow

Nicholas Sandmann

• December 05, 2018 19:48
• Edited

I can use the look up command to find all events where an event contains a value that is in my saved list, like this:

(_sourceCategory=mycategory) 
| parse regex "^<\d+>\d+\s+\d{4}-\d{2}-\d{2}[T|t]\d{2}:\d{2}:\d{2}\.\d+[+|-]\d{2}:\d{2}\s+(?<syslog_server>[A-Za-z0-9]+)\s+"
| count syslog_server
| toLowerCase(syslog_server) as syslog_server
| fields -_count
| lookup server as server_match from shared/servers on syslog_server=server_match

However I can't seem to reverse that logic to find servers in my saved list where there are no events in the query output.  Is there way to do a "reverse lookup" in order to identify servers in my saved list that do not appear in the query results?

Use case is I have a list of servers for an application saved into a shared list in Sumo, and if I want to send myself a notification if the query runs and hasn't seen any events from one of the servers.

 

 

0

• Official comment

  

  Timothy

  • December 14, 2018 18:41

  Looking for something that is not there can get a bit tricky. My suggestion would be:
  
  

  1. Create a query that creates a list of servers and schedule it to save to an index once every X days/hours. This will create a baseline that can be used later for comparison. 
  
  https://help.sumologic.com/Dashboards-and-Alerts/Alerts/08-Save-to-Index

   

  2. Create a second query that filters the results based on the index created in the first query. This would be accomplished using the subquery operator. 
  
  https://help.sumologic.com/05Search/Subqueries

  For example something like this (assuming you saved the first query to an index called "serverlist": 

  _index=serverlist
  |where ! ([subquery:  _sourceCategory="somecategory" 
  | <some logic>
  | count by server
  | fields server | compose server ])
  
  In the above we are querying the serverlist index, and then filtering out any hosts that do not show up in subquery. 
  This should return only those servers that existed in the baseline index but no longer appear in the current logs. 
  
  Please note that saving to an index creates additional ingest volume as new data is being generated and saved. 

  Comment actions Permalink

Please sign in to leave a comment.