How do I match empty fields in where/if clauses?
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
-
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 30CSV
"ip","bot"
"220.181.89.146","sogou"
"66.249.73.5","google"
"157.56.92.156","bing" -
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
| -
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'
-
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
Please sign in to leave a comment.
Comments
10 comments