contains | has | startswith | endswith
contains
contains command is case-insensitive, its similar to the '==' sign but the difference is that the '==' sign expect exact mathces while contains returns parts as well.
example
cluster('help').database('SecurityLogs').InboundBrowsing
| where user_agent contains 'firefox'this query will return rows with the example below:
'firefox'
'abcfirefox'
'firefox/1'
'abcfirefox/1'
while == would return only fields with exact match.
has
has command is also case-insensitive, it is similar to contains, but does not return delimeters like contains does.
Delimeter examples: / . [ { ( , < space
Strings in KQL are tokenized, meaning if you use fire,fox this is read by kql as
so == would not work here if we dont enter the exact string.
example
this query will return rows with the example below:
'firefox' ✅
'abcfirefox' ❌
'firefox/1' ✅
'abcfirefox/1' ❌
Why has does not return abcfirefox/1 but return firefox/1?
beacuse kql reads the words like this because of the delimeters:
So has will return the rows that contain firefox/1 but not abcfirefox/1.
startswith & endswith
this commands are case-insensitive (not case-sensitive) and are the same thing as if you use asteriks (*) in your searches.
example
this will return all the rows that have the user_agent field string starting whit "fire"
this will return all the rows that have the user_agent field string ending whit "fox"
negatives
all of the comands above can be used with the ! symbol to search for queries that DO NOT contain what we search for.
case-sensitive
To make the contains and has commands case sensitive just add '_cs' to the commands syntax.
This method is faster since it uses less resources.
practice
Lets write a query that will return all the websites that use https from the table InboundBrowsing from the 'url' field.
It does not make sense to use == here since the url field never contains only http.
It also makes no sense in using contains since it includes everything after http, this it will return all the https websites as well.
That's why we will use has command, since it searches for the exact string we enter no matter if there are other strings, and it wont return fields containing https.
do it yourself
from the SecurityLogs database, from the InboundBrowsing table, find all the iphone users.
Last updated