Searching and finding data

Welcome to the fifth blog post in the series becoming a Kusto Knight. While the previous blog post was about time in Kusto, this blog post will be about searching and finding data.

The three most used operators are search, where and has.

search is the first operator we will learn about. In the beginning, I used an inefficient query. So let us start to discover the search operator.

search * | where * contains "kustoking"

A more efficient way would be:

search "*kustoking*"

which also runs:

search *
where * contains "kustoking"

but more efficiently. An even better way would be to run

search "kustoking"

because it runs the following query:

search *
where * has "kustoking"

As we have learned in an earlier blog post, the has operator is more efficient than the contains operator.

We can also use the search operator to do a hasprefix or hassuffix search:

search "gianni*"

and

search "*kustoking.com"

We can also state the tables the query should look in with the query:

search in (OfficeActivity, SigninLogs, AAD*) "kustoking"

which runs the following query:

union OfficeActivity, SigninLogs, AAD* 
| where * has "kustoking"

Since we are looking for at AD events, we can also look in all tables for a column that contain a certain value:

search UserPrincipalName:"kustoking"

which runs:

search *
where UserPrincipalName has "kustoking"

When we want to search all Office and Azure AD activity, from the user Gianni and from an Android Device, we can do that with the following search:

search in (OfficeActivity, SigninLogs, AAD*) "gianni" and "android"

which runs:

union OfficeActivity, SigninLogs, AAD* 
| where * has "kustoking" and * has "Android"

where and the has operators are the operators to filter the results and to do some tests we will import some data:

let FruitShipments = externaldata(Fruit:string,Colour:string,Container:string,Customer:string,Region:string)
[
h@"https://raw.githubusercontent.com/KustoKing/SampleData/main/FruitShipments.csv"
]
with(format='csv',ignoreFirstRecord=true);
FruitShipments

which looks like the following:

We can use the where operator to filter our results.

FruitShipments
| where Colour == "Red"

FruitShipments
| where Colour has "Red"

We will also view the results from the contains operator, this way we will see the difference between the has and the contains operator.

FruitShipments
| where Colour contains "Red"

In this blog post, we read about the search operator and we revisited the where and has  operator briefly. The next blog post will be about combining your search results.