How do I match empty fields in where/if clauses?

Follow

James White

• April 26, 2013 15:03

In my where/if statements I'd like to be able to match empty fields (in this case I'm looking up the field from a CSV file). I know I could probably do some sort of regular expression but is there a simpler way to say "show entries where myfield <> empty". 

 

Thanks!

 

James

1

• 

  Ben Newton

  • April 26, 2013 15:27

  James,

  If you are looking to exclude empty fields, you could do this:

  | where myfield <> ""

   

  Does that help?

   

  Ben

  0

  Comment actions Permalink
• 

  James White

  • April 26, 2013 15:29

  Thanks, it could be that I'm using a field from a CSV lookup, but it seems like that matches empty strings as well. Here is my search & CSV. I'm trying to either show a matching bot or the useragent if none is found in the lookup.

  _sourceCategory=IISLog
  
  | parse regex "^.(?<timestamp>\S*\s\S*)\s(?<sitename>\S*)\s(?<hostip>\S*)\s(?<httpverb>\S*)\s(?<url>\S*)\s(?<query>\S*)\s(?<port>\S*)\s-\s(?<clientip>\S*)\s(?<httpversion>\S*)\s(?<useragent>\S*)\s(?<referer>\S*)\s(?<status>\S*)\s(?<substatus>\S*)\s(?<win32status>\S*)\s(?<scbytes>\S*)\s(?<csbytes>\S*)\s(?<timetaken>\S*)$"
  
  | lookup bot from https://assets.gbase.com/static/lists/botlist.csv on clientip = ip
  
  | if (bot<>"", bot, useragent) as source
  
  | count_distinct(url) group by clientip, source
  
  | where _count_distinct > 200
  
  | sort _count_distinct
  
  | limit 30

   

  CSV

  "ip","bot"
  
  "220.181.89.146","sogou"
  
  "66.249.73.5","google"
  
  "157.56.92.156","bing"

  0

  Comment actions Permalink
• 

  James White

  • April 26, 2013 15:30

  Oh, and the results (source doesn't show useragent when bot is not matched in the lookup)

  |
  #
  | clientip
  | source
  | _count_distinct
  |
  |
  1
  | 66.249.73.5
  | google
  | 17,062
  |
  |
  2
  | 157.56.92.156
  | bing
  | 1,380
  |
  |
  3
  | 157.55.32.88
  | bing
  | 1,371
  |
  |
  4
  | 157.55.33.50
  | bing
  | 1,345
  |
  |
  5
  | 157.55.33.181
  | bing
  | 1,330
  |
  |
  6
  | 157.55.32.236
  | bing
  | 1,324
  |
  |
  7
  | 65.55.24.218
  | bing
  | 1,305
  |
  |
  8
  | 157.55.34.25
  | bing
  | 1,270
  |
  |
  9
  | 96.224.208.225
  |  
  | 709
  |
  |
  10
  | 66.249.81.143
  |  
  | 394
  |
  |
  11
  | 157.56.93.72
  | bing
  | 377
  |

  0

  Comment actions Permalink
• 

  Ben Newton

  • April 26, 2013 15:38

  Ok. I will load this in Sumo, and see if I can make it work

  0

  Comment actions Permalink
• 

  Ben Newton

  • April 26, 2013 16:32

  Still working on this, but in the meantime you can insert this statement:

  | if (bot in ("bing", "google", "sogou"), bot, useragent) as source

   

  That should work.

   

  0

  Comment actions Permalink
• 

  Kevin

  • April 26, 2013 17:37

  If there is no value in the lookup file the value returned is actually 'null' and is not a string, "", so we need to use one of the following alternatives for the if statement.

  | if (bot matches "*", bot, useragent) as source  //if bot batches any string value then use bot

  or

  | if (isNull(bot), useragent, bot) as source  // Check if bot is null and if so use 'useragent'

  0

  Comment actions Permalink
• 

  Ben Newton

  • April 29, 2013 06:36

  James - Did Kevin's solution work for you?

  0

  Comment actions Permalink
• 

  James White

  • April 30, 2013 00:22

  It did, thanks!

  0

  Comment actions Permalink
• 

  Tilo Sloboda

  • December 18, 2015 17:52

  It would be great if `field = NULL` syntax was supported!

  0

  Comment actions Permalink
• 

  Glory Gadoury

  • December 18, 2015 19:35

  Hi Tilo,

   

  For your use case you'll want to use the isNull operator. We also have  isEmpty and isBlank operators as well. Here are the links to the docs

  • https://service.sumologic.com/help/Default.htm#isEmpty.htm
  • https://service.sumologic.com/help/Default.htm#isNull_Operator.htm
  • https://service.sumologic.com/help/Default.htm#isBlank.htm

  0

  Comment actions Permalink

Please sign in to leave a comment.