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

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

Comments

10 comments

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 Keech

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
0

Comment actions Permalink

Please sign in to leave a comment.

Comments

Didn't find what you were looking for?