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

Comments

10 comments

  • Avatar
    Ben Newton

    James,

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

    | where myfield <> ""

     

    Does that help?

     

    Ben

    0
    Comment actions Permalink
  • Avatar
    James White

    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
  • Avatar
    James White

    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
  • Avatar
    Ben Newton

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

    0
    Comment actions Permalink
  • Avatar
    Ben Newton

    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
  • Avatar
    Kevin Keech

    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
  • Avatar
    Ben Newton

    James - Did Kevin's solution work for you?

    0
    Comment actions Permalink
  • Avatar
    James White

    It did, thanks!

    0
    Comment actions Permalink
  • Avatar
    Tilo Sloboda

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

    0
    Comment actions Permalink
  • Avatar
    Glory Gadoury

    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

    0
    Comment actions Permalink

Please sign in to leave a comment.