Kusto Query Language (KQL) Tutorial

Kusto Query Language Tutorial Series

Kusto Query Language (KQL) Tutorial Series

Getting Started & Setup

Table Management

Column Operations

Basic Queries

Filtering, Sorting & Aggregations

Joins, Unions & Set Logic

Advanced Functions & Techniques

DateTime part function in Kusto | How to get Year, Month and Day from DateTime | KQL Tutorial 2022

Topic: DateTime part function in Kusto Query Language.

In this article, we are going to learn about datetime_part function, this function is very helpful and we can extract different parts of the data by using this function so it extracts the requested data part as the integer value. Kusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more. The query uses schema entities that are organized in a hierarchy similar to SQL's: databases, tables, and columns.

 //datetime_part -- Extracts the requested date part as an integer value.  
 Part supported  
 Year  
 Quarter  
 Month  
 week_of_year  
 Day  
 DayOfYear  
 Hour  
 Minute  
 Second  
 Millisecond  
 Microsecond  
 Nanosecond


 //Let's create a table Customer  
 //.drop table Customer  
 .create table Customer (CustomerId: long, FName: string,LName:string, Salary: int,hiredate:datetime)   
  .ingest inline into table Customer <|  
 1,Aamir,Shahzad,2000,2021-11-28T04:52:43.8745893Z  
 2,Raza,ALI,4000,2018-11-28T04:52:43.8745893Z  
 3,Lisa,Rivers,50000,2021-12-28T04:52:43.8745893Z  
 4,steve,Ladson,1000,2010-09-28T04:52:43.8745893Z  
 5,Robert,Jr,500,2015-01-20T04:52:43.8745893Z  
 6,aamir,ali,1000,2005-03-01T04:52:43.8745893Z  
   
 Customer  
 | extend   
 year=datetime_part('year',hiredate)  
 ,Quarter=datetime_part('Quarter',hiredate)  
 ,Month=datetime_part('Month',hiredate)  
 ,week_of_year=datetime_part('week_of_year',hiredate)  
 ,Day=datetime_part('Day',hiredate)  
 ,DayOfYear=datetime_part('DayOfYear',hiredate)  
 ,Hour=datetime_part('Hour',hiredate)  
 ,Minute=datetime_part('Minute',hiredate)  
 ,Second=datetime_part('Second',hiredate)  
 ,Millisecond=datetime_part('Millisecond',hiredate)  
 ,Microsecond=datetime_part('Microsecond',hiredate)  
 ,Nanosecond=datetime_part('Nanosecond',hiredate)

Video Demo: DateTime part function in Kusto | How to get Year, Month and Day from DateTime | KQL Tutorial 2022

Join Operator in Kusto Query Language | How to Do inner join ,Left Join, Right Join, Full Outer Join in Kusto

Topic: Join Operator in Kusto Query Language.

In this article, we are going to learn about a different types of joins in Kusto, we are going to learn about the inner join left join, and right join full outer join and some other types of joins will be learned in Kusto Query Language. Kusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more. The query uses schema entities that are organized in a hierarchy similar to SQL's: databases, tables, and columns.

 // .drop table Customer  
 // .drop table Order  
 .create table Customer(CustomerId:int, Name:string)   
   
  .ingest inline into table Customer <|  
 1,Aamir  
 1,Raza  
 2,Lisa  
 3,steve  
   
 .create table Order(OrderId:int, OrderName:string,CustomerId:int)   
 .ingest inline into table Order <|  
 1, 2 Laptops,1  
 1, 2 Computers,1   
 2, 10 TV ,2  
 3,10 cell phones,4

 // By default the type of Join is innerunique  
 Customer   
 | join Order on CustomerId  
   
 Customer  
 | join kind=innerunique Order on CustomerId  
   
 //using the column names  
 Customer  
 | join kind=innerunique Order on $left.CustomerId==$right.CustomerId  
   
 // if we want to get the Records for other left record, then we can specify  
 Customer  
 | join kind=innerunique Order on $left.CustomerId==$right.CustomerId  
 | where Name=="Raza"  
   
   
 // Inner Join will give us all the records  
 Customer  
 | join kind=inner Order on $left.CustomerId==$right.CustomerId  
   
 // show all records from Left , even no match in Right table  
 Customer  
 | join kind=leftouter Order on $left.CustomerId==$right.CustomerId  
   
 //show all records from right, even no matching records in left   
 Customer  
 | join kind=rightouter Order on $left.CustomerId==$right.CustomerId  
   
 //show all records from both tables , matching and not matching   
 Customer  
 | join kind=fullouter  Order on $left.CustomerId==$right.CustomerId  
   
   
 //show all records from left table which have matching records in right table- display only left table columns  
 Customer  
 | join kind=leftsemi Order on $left.CustomerId==$right.CustomerId  
   
   
 //show all records from right table which have matching records in right table - display only right table columns  
 Customer  
 | join kind=rightsemi  Order on $left.CustomerId==$right.CustomerId  
   
 //leftanti -- the records that does not match with right table but does exists in left table  
 Customer  
 | join kind=leftanti  Order on $left.CustomerId==$right.CustomerId  
   
   
   
 //rightanti -- shows the records from right table which does not exists in left table  
 Customer  
 | join kind=rightanti Order on $left.CustomerId==$right.CustomerId

Video Demo: Join Operator in Kusto Query Language | How to Do inner join ,Left Join, Right Join, Full Outer Join in Kusto

Range Operator in Kusto Query Language | Generate Number Sequence or Date Range in Kusto Query Language

Topic: Range Operator in Kusto Query Language (KQL).

In this article, we are going to learn about the range operator, range operator is used to generate a single-column table of values. Kusto Query Language is a powerful tool for exploring your data and discovering patterns, identifying anomalies and outliers, creating statistical modeling, etc. for exploring your data and discovering patterns, identifying anomalies and outliers, creating statistical modeling, single-column The query uses schema entities that are organized in a hierarchy similar to SQL's: databases, tables, and columns.

 //range operator : Generates a single-column table of values.  
 // syntax : range columnName from start to stop step step  
   
 //create sequence no  
 range Steps from 1 to 8 step 1  
   
 //create range of dates  
 range LastWeek from ago(7d) to now() step 1d  
   
   
 //create range of even numbers  
 range Steps from 2 to 100 step 2  
   
   
 //create range of odd numbers  
 range Steps from 1 to 100 step 2  
   
 //Let's create a table Customer  
 //.drop table Customer  
 .create table Customer (CustomerId: long, Name: string)   
  .ingest inline into table Customer <|  
 1,Aamir  
 2,Raza  
 3,Lisa  
 4,steve  
 5,Robert  
   
 //you can use range output in many ways // try to get all records with odd and even CustomerId  
 range CustomerId from 1 to 5 step 2  
 | join kind=inner (Customer ) on $left.CustomerId==$right.CustomerId  
 | project CustomerId,Name

Video Demo: Range Operator in Kusto Query Language | Generate Number Sequence or Date Range in Kusto Query Language

Lookup Operator in Kusto Query (KQL) Kusto Query Language Tutorial 2022

Topic: Lookup Operator in Kusto Query Language (KQL).

In this article, we are going to learn about the lookup operator in Kusto Query Language, the lookup operator extends the columns of effect table with the values looked up in the dimension table so the lookup operator pretty much works with the same as the join type. Kusto Query Language is a powerful tool for exploring your data and discovering patterns, identifying anomalies and outliers, creating statistical modeling, etc. The query uses schema entities that are organized in a hierarchy similar to SQL's: databases, tables, and columns.

 //Lookup Operator: The lookup operator extends the columns of a fact table with values looked-up in a dimension table.  
 //kind: An optional instruction on how to treat rows in LeftTable that have no match in RightTable. By default, leftouter is used,   
 //which means all those rows will appear in the output with null values used for the missing values of RightTable columns added by the operator.  
 // If inner is used, such rows are omitted from the output. (Other kinds of join are not supported by the lookup operator.)  
   
 // .drop table Customer  
 // .drop table Order  
 .create table Customer(CustomerId:int, Name:string)   
   
  .ingest inline into table Customer <|  
 1,Aamir  
 1,Raza  
 2,Lisa  
 3,steve  
   
 .create table Order(OrderId:int, OrderName:string,CustomerId:int)   
 .ingest inline into table Order <|  
 1, 2 Laptops,1  
 1, 2 Computers,1   
 2, 10 TV ,2  
 3,10 cell phones,4


 // Lookup works like Joins, if you have Fact and Dimension table then you can use between them  
 Customer  
 | lookup  Order on CustomerId  
   
 Customer  
 | lookup  Order on $left.CustomerId==$right.CustomerId  
   
 Customer  
 | lookup kind=leftouter Order on $left.CustomerId==$right.CustomerId  
   
 Customer  
 | lookup kind=inner Order on $left.CustomerId==$right.CustomerId  
   
 https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/lookupoperator

Video Demo: Lookup Operator in Kusto Query (KQL) Kusto Query Language Tutorial 2022

How to Use Extend to Add Calculated Columns in Kusto | Kusto Query Language Tutorial (KQL)

Topic: How to Use Extend to Add Calculated Columns in Kusto Query Language.

In this article, we are going to learn about the extend operator that creates a calculated columns and append them to the result set. Azure Data Explorer is a fast, fully managed data analytics service for real-time analysis on large volumes of data streaming from applications, websites, IoT devices, and more. Ask questions and iteratively explore data on the fly to improve products, enhance customer experiences, monitor devices, and boost operations. Quickly identify patterns, anomalies, and trends in your data.

 // extend operator : Create calculated columns and append them to the result set.  
 .clear table TotalSale data   
 .create table TotalSale (  
   id: int   
   ,SalePersonFName: string  
   ,SalePersonLName : string  
   ,ProductName : string  
   ,ItemsSold : int  
   ,SoldPrice :real  
   ,SoldDate: date  
   ,City : string  
   ,State : string  
   ,Country : string  
   ,Region : string  
   )


      //Insert data  
       .ingest inline into table TotalSale <|  
 11,Tamara,Tony,Cell Phone,2,1200,2015-03-03,Frankfurt,Hesse,Germany,Europe  
 11,Tamara,Tony,Cell Phone,2,1200,2015-03-03,Frankfurt,Hesse,Germany,Europe  
 9,Petra,Henry,TV,10,5000,2015-04-08,Paris,Île-de-France,France,Europe  
 3,Christy,Ladson,TV,3,1600,2015-04-02,High Point,NC,USA,North America  
 7,Chirag,Patel,Cell Phone,5,1500,2015-06-23,AhmadAbad,Gujrat,India,Asia  
 2,M,Raza,Cell Phone,2,800,2015-07-15,Charlotte,NC,USA,North America  
 5,Najaf,Ali,Computer,1,300,2015-06-20,Karachi,Sindh,Pakistan,Asia  
 6,Sukhjeet,Singh,TV,2,900,2015-06-21,ChandiGar,Punjab,India,Asia  
 4,John,Rivers,Laptop,5,2400,2014-03-09,Jersey City,NJ,USA,North America  
 8,Aleena,Aman,Laptop,2,800,2015-05-25,Lahore,Punjab,Pakistan,Asia  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe  
 1,Aamir,Shahzad,TV,1,700,2015-07-15,Charlotte,NC,USA,North America  
 12,aamir,shahzad,TV,1,7000,2015-07-15,Charlotte,NC,USA,North America  
 id=Null,bisma,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe

 // extend operator : Create calculated columns and append them to the result set.  
 //get all columns with data from table  
 TotalSale

 //add a calculated columns  
 TotalSale  
 | extend fullname=strcat(SalePersonLName,' ',SalePersonLName),DayofYear=dayofyear(SoldDate)

 // extend the columns to result set  
 TotalSale  
 | project id,SalePersonFName,SalePersonLName,ItemsSold, SoldDate  
 | extend fullname=strcat(SalePersonLName,' ',SalePersonLName),DayofYear=dayofyear(SoldDate)

Video Demo: How to Use Extend to Add Calculated Columns in Kusto | Kusto Query Language Tutorial (KQL)

Split Function in Kusto Query (KQL) How to split string into values in Kusto Query Language - 2022

Topic: Split Function in Kusto Query (KQL) How to split string into values in Kusto Query Language

In this article, we are going to learn about Split function in Kusto Query Language, Splits a given string according to a given delimiter and returns a string array with the contained substrings, optionally, a specific substring can be returned if exists. Kusto Query Language is a powerful tool for exploring your data and discovering patterns, identifying anomalies and outliers, creating statistical modeling, etc.

 //split  
 // Splits a given string according to a given delimiter and returns a string array with the contained substrings.  
 //Optionally, a specific substring can be returned if exists.  
   
 //Let's create a table Customer  
 //.drop table Customer  
 .create table Customer (CustomerId: long, FName: string,LName:string,FileLocation:string )   
  .ingest inline into table Customer <|  
 1,Aamir,Shahzad,C:\Documents\Newsletters\Summer2018.pdf  
 2,Raza,ALI,D:\Documents\MyTestFileLocation\Tax_2019.pdf  
 3,Lisa,  
 4,steve,Ladson  
 5,Robert,Jr  
 ,aamir,ali


   
 // split the values into an Array  
 Customer  
 | extend split(FileLocation,'\\')


 // Let's divide the results into sub arrays  
 Customer  
 | extend DriveName=split(FileLocation,'\\',0), MainFolder=split(FileLocation,'\\',1),Subfolder=split(FileLocation,'\\',2),FileName=split(FileLocation,'\\',3)

  
 //Let's get the value from the arrary and print in nice readable way  
 Customer  
 | extend MyArrary=split(FileLocation,'\\')  
 | extend DriveName=MyArrary[0],Mainfolder=MyArrary[1],SubFolder=MyArrary[2],FileName=MyArrary[3]

Facet Operator in Kusto Query (KQL) Generate Tables for each Column in Kusto Query Language 2022

Topic: Facet Operator in Kusto Query (KQL) Generate Tables for each Column in Kusto Query Language

In this article, we are going to learn about facet Operator in Kusto Query Language, facet operator returns a set of tables, one for each specified column, Each table specifies the list of values taken by its column an additional table can be created by using the ‘’With’’ clause. Kusto Query Language is a powerful tool for exploring your data and discovering patterns, identifying anomalies and outliers, creating statistical modeling, etc.

 // facet operator : Returns a set of tables, one for each specified column. Each table specifies the list of values taken by its column  
 //. An additional table can be created by using the with clause.  
   
 TotalSale  
 | facet by SalePersonFName,SalePersonLName,ProductName


   
 //Add new table by using with  
 TotalSale  
 | facet by ProductName,SalePersonLName  
  with (where Region =="Asia" | take 2 )

Video Demo: Facet Operator in Kusto Query (KQL) Generate Tables for each Column in Kusto Query Language

External Table Read Data From File Blob Storage in Kusto Query | Kusto Query Language Tutorial 2022

Topic: External Table Read Data From File Blob Storage in Kusto Query

In this article, we are going to learn about the Eeternaldata operator. The externaldata operator returns a table whose schema is defined in the query itself, and whose data is read from an external storage artifact, such as a blob in Azure Blob Storage or a file in Azure Data Lake Storage. Kusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more.

 // externaldata operator : The externaldata operator returns a table whose schema is defined in the query itself,   
 //and whose data is read from an external storage artifact, such as a blob in Azure Blob Storage or a file in Azure Data Lake Storage.  
   
 //Sample Code  
 externaldata (UserID:string) [  
   @"https://storageaccount.blob.core.windows.net/storagecontainer/users.txt"   
    h@"?...SAS..." // Secret token needed to access the blob  
   ]  
   
   //Generate SAS for File in Blob Storage  
 externaldata (Id:int,FName:string,LName:string) [  
   @"https://ashahzadblobstorage.blob.core.windows.net/input/Customer.txt"   
    h@"?sp=r&st=2021-11-26T18:08:41Z&se=2021-11-27T02:08:41Z&spr=https&sv=2020-08-04&sr=b&sig=zwx8IKwChrcCNwVi73fGrn29nXHlAbAfSz%2FAJHaYGwE%3D" // Secret token needed to access the blob  
   ]  
   with (ignoreFirstRecord=true)

Video Demo: External Table Read Data From File Blob Storage in Kusto Query

Standard Deviation of Series in Kusto | Series Stats Get the Min, Max, Avg, | Kusto Query Tutorial

Topic: Standard Deviation of Series in Kusto | Series Stats Get the Min, Max, Avg,

In this article, we are going to learn about series_stats, Series stats returns statistics for a numerical series using multiple columns. Kusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more.

 //series_stats - series_stats() returns statistics for a numerical series using multiple columns.  
 //   
 //The series_stats() function takes an expression returning a dynamical numerical array as input, and calculates the following statistics:  
   
 Statistic           Description  
 min               Minimum value in the input array.  
 min_idx             The first position of the minimum value in the input array.  
 max               Maximum value in the input array.  
 max_idx             First position of the maximum value in the input array.  
 avg               Average value of the input array.  
 variance           Sample variance of input array.  
 stdev             Sample standard deviation of the input array.

   
 //create Table and Insert Sample Data in Azure Data Explorer DB for Testing  
   
 .create table TotalSale (  
   id: int   
   ,SalePersonFName: string  
   ,SalePersonLName : string  
   ,ProductName : string  
   ,ItemsSold : int  
   ,SoldPrice :real  
   ,SoldDate: date  
   ,City : string  
   ,State : string  
   ,Country : string  
   ,Region : string  
   )

    
      //Insert data  
       .ingest inline into table TotalSale <|  
 11,Tamara,Tony,Cell Phone,2,1200,2015-03-03,Frankfurt,Hesse,Germany,Europe  
 9,Petra,Henry,TV,10,5000,2015-04-08,Paris,Île-de-France,France,Europe  
 3,Christy,Ladson,TV,3,1600,2015-04-02,High Point,NC,USA,North America  
 7,Chirag,Patel,Cell Phone,5,1500,2015-06-23,AhmadAbad,Gujrat,India,Asia  
 2,M,Raza,Cell Phone,2,800,2015-07-15,Charlotte,NC,USA,North America  
 5,Najaf,Ali,Computer,1,300,2015-06-20,Karachi,Sindh,Pakistan,Asia  
 6,Sukhjeet,Singh,TV,2,900,2015-06-21,ChandiGar,Punjab,India,Asia  
 4,John,Rivers,Laptop,5,2400,2014-03-09,Jersey City,NJ,USA,North America  
 8,Aleena,Aman,Laptop,2,800,2015-05-25,Lahore,Punjab,Pakistan,Asia  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe  
 1,Aamir,Shahzad,TV,1,700,2015-07-15,Charlotte,NC,USA,North America  
 12,aamir,Shahzad,TV,1,7000,2015-07-15,Charlotte,NC,USA,North America  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe  
   
 print x=dynamic([23,46,23,87,4,8,3,75,2,56,13,75,32,16,29])   
 | project series_stats(x), series_stats_dynamic(x)  
   
 TotalSale | make-series TotalItemsSold=sum(ItemsSold) default=0 on SoldDate from datetime(2014-03-09T00:00:00Z) to datetime(2015-07-15T00:00:00Z) step 1d by Region  
   
 TotalSale   
 | make-series TotalItemsSold=sum(ItemsSold) default=0 on SoldDate from datetime(2014-03-09T00:00:00Z) to datetime(2015-07-15T00:00:00Z) step 1d by Region  
 | project Region,TotalItemsSold,series_stats(TotalItemsSold),series_stats_dynamic(TotalItemsSold)


 // show only selected values  
   
 TotalSale   
 | make-series TotalItemsSold=sum(ItemsSold) default=0 on SoldDate from datetime(2014-03-09T00:00:00Z) to datetime(2015-07-15T00:00:00Z) step 1d by Region  
 | extend series_stats(TotalItemsSold),arryofvalues=series_stats_dynamic(TotalItemsSold)  
 | project Region,arryofvalues.min,arryofvalues.max,arryofvalues.avg

Video Demo: Standard Deviation of Series in Kusto | Series Stats Get the Min, Max, Avg, | Kusto Query Tutorial

How to Generate Series in Kusto | Kusto Make series function | Kusto Query Language Tutorial 2022

Topic: How to Generate Series in Kusto | Kusto Make Series Function

In this article, we are going to learn about the ''Make series'' in Kusto Query Language, we will learn how to create a series of specified aggregated values along with a specified Axis. Kusto Query Language is a powerful tool for exploring your data and discovering patterns, identifying anomalies and outliers, creating statistical modeling, etc.

 //make-series - Create series of specified aggregated values along a specified axis.  
 // Make series take values and create array out for those values.  
   
 //create Table and Insert Sample Data in Azure Data Explorer DB for Testing  
   
 .create table TotalSale (  
   id: int   
   ,SalePersonFName: string  
   ,SalePersonLName : string  
   ,ProductName : string  
   ,ItemsSold : int  
   ,SoldPrice :real  
   ,SoldDate: date  
   ,City : string  
   ,State : string  
   ,Country : string  
   ,Region : string  
   )


      //Insert data  
       .ingest inline into table TotalSale <|  
 11,Tamara,Tony,Cell Phone,2,1200,2015-03-03,Frankfurt,Hesse,Germany,Europe  
 9,Petra,Henry,TV,10,5000,2015-04-08,Paris,Île-de-France,France,Europe  
 3,Christy,Ladson,TV,3,1600,2015-04-02,High Point,NC,USA,North America  
 7,Chirag,Patel,Cell Phone,5,1500,2015-06-23,AhmadAbad,Gujrat,India,Asia  
 2,M,Raza,Cell Phone,2,800,2015-07-15,Charlotte,NC,USA,North America  
 5,Najaf,Ali,Computer,1,300,2015-06-20,Karachi,Sindh,Pakistan,Asia  
 6,Sukhjeet,Singh,TV,2,900,2015-06-21,ChandiGar,Punjab,India,Asia  
 4,John,Rivers,Laptop,5,2400,2014-03-09,Jersey City,NJ,USA,North America  
 8,Aleena,Aman,Laptop,2,800,2015-05-25,Lahore,Punjab,Pakistan,Asia  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe  
 1,Aamir,Shahzad,TV,1,700,2015-07-15,Charlotte,NC,USA,North America  
 12,aamir,Shahzad,TV,1,7000,2015-07-15,Charlotte,NC,USA,North America  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe  
   
   
 TotalSale | make-series TotalItemsSold=sum(ItemsSold) default=0 on SoldDate from datetime(2014-03-09T00:00:00Z) to datetime(2015-07-15T00:00:00Z) step 1d by Region  
   
 TotalSale | make-series TotalItemsSold=sum(ItemsSold) default=0 on SoldDate from datetime(2014-03-09T00:00:00Z) to datetime(2015-07-15T00:00:00Z) step 1d by Region  
 | render timechart    
   
   
 TotalSale | make-series TotalItemsSold=sum(ItemsSold) default=0, avg(ItemsSold) default=0 on SoldDate from datetime(2014-03-09T00:00:00Z) to datetime(2015-07-15T00:00:00Z) step 1d by Region, Country  
 | render timechart

Video Demo:

How to Use To Scalar Function in Kusto | To Scalar Function in Kusto Query Language | KQL Tutorial

Topic: How to Use To Scalar Function in Kusto Query Language.

In this article, we are going to learn about toscalar function, toscalar function returns a scalar constant value of the evaluated expression, Kusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more.

 //to_scalar()- Returns a scalar constant value of the evaluated expression.  
   
 //create Table and Insert Sample Data in Azure Data Explorer DB for Testing  
 .drop table TotalSale  
 .create table TotalSale (  
   id: int   
   ,SalePersonFName: string  
   ,SalePersonLName : string  
   ,ProductName : string  
   ,ItemsSold : int  
   ,SoldPrice :real  
   ,SoldDate: date  
   ,City : string  
   ,State : string  
   ,Country : string  
   ,Region : string  
   )


      //Insert data  
       .ingest inline into table TotalSale <|  
 11,Tamara,Tony,Cell Phone,2,1200,2015-03-03,Frankfurt,Hesse,Germany,Europe  
 9,Petra,Henry,TV,10,5000,2015-04-08,Paris,Île-de-France,France,Europe  
 3,Christy,Ladson,TV,3,1600,2015-04-02,High Point,NC,USA,North America  
 7,Chirag,Patel,Cell Phone,5,1500,2015-06-23,AhmadAbad,Gujrat,India,Asia  
 2,M,Raza,Cell Phone,2,800,2015-07-15,Charlotte,NC,USA,North America  
 5,Najaf,Ali,Computer,1,300,2015-06-20,Karachi,Sindh,Pakistan,Asia  
 6,Sukhjeet,Singh,TV,2,900,2015-06-21,ChandiGar,Punjab,India,Asia  
 4,John,Rivers,Laptop,5,2400,2014-03-09,Jersey City,NJ,USA,North America  
 8,Aleena,Aman,Laptop,2,800,2015-05-25,Lahore,Punjab,Pakistan,Asia  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe  
 1,Aamir,Shahzad,TV,1,700,2015-07-15,Charlotte,NC,USA,North America  
 12,aamir,Shahzad,TV,1,7000,2015-07-15,Charlotte,NC,USA,North America  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe


 // Let's get min and max value // study the error  
 let minid= (TotalSale | summarize min(id));  
 print minid  
   
   
   
 let minid= toscalar(TotalSale | summarize min(id));  
 let maxid= toscalar(TotalSale | summarize max(id)-5);  
 print minid,maxid


 //use the value into next queries  
 let minid= toscalar(TotalSale | summarize min(id));  
 let maxid= toscalar(TotalSale | summarize max(id)-5);  
 TotalSale  
 | where id>minid and id<=maxid

 
 // let's generate list of SalePersonFistName and use in query  
 TotalSale  
 | where SalePersonFName in ("Najaf","John")  
   
 let ListofNames=TotalSale | where id<5 | summarize makeset(SalePersonFName);  
  print ListofNames // it will not print because it is not scalar value


   
  //Let's save a single scalar value then it should be able to print  
  let ListofNames=toscalar(TotalSale | where id<5 | summarize makeset(SalePersonFName));  
  print ListofNames // As it is scalar value, it should print fine.


  // use this in clause  
  let ListofNames=toscalar(TotalSale | where id<5 | summarize makeset(SalePersonFName));  
  //print ListofNames  
  TotalSale  
  | where SalePersonFName in(ListofNames)

Video Demo: How to Use To Scalar Function in Kusto Query Language

How to Use Row Rank Function in Kusto | Row Rank Function | Kusto Query Language Tutorial 2022

Topic: How to Use Row Rank Function in Kusto.

In this article, we are going to learn about row rank function in Kusto row rank function returns the current rows rank in a serialized row set the row index starts by default at one for the first row and it increments by one whenever the provided term is different than the previous rows term. Kusto Query Language is a powerful tool for exploring your data and discovering patterns, identifying anomalies and outliers, creating statistical modeling, etc.

 //row_rank()- Returns the current row's rank in a serialized row set. The row index starts by default at 1 for the first row,  
 // and is incremented by 1 whenever the provided Term is different than the previous row's Term.  
   
 //create Table and Insert Sample Data in Azure Data Explorer DB for Testing  
 .drop table TotalSale  
 .create table TotalSale (  
   id: int   
   ,SalePersonFName: string  
   ,SalePersonLName : string  
   ,ProductName : string  
   ,ItemsSold : int  
   ,SoldPrice :real  
   ,SoldDate: date  
   ,City : string  
   ,State : string  
   ,Country : string  
   ,Region : string  
   )

    //Insert data  
       .ingest inline into table TotalSale <|  
 11,Tamara,Tony,Cell Phone,2,1200,2015-03-03,Frankfurt,Hesse,Germany,Europe  
 9,Petra,Henry,TV,10,5000,2015-04-08,Paris,Île-de-France,France,Europe  
 3,Christy,Ladson,TV,3,1600,2015-04-02,High Point,NC,USA,North America  
 7,Chirag,Patel,Cell Phone,5,1500,2015-06-23,AhmadAbad,Gujrat,India,Asia  
 2,M,Raza,Cell Phone,2,800,2015-07-15,Charlotte,NC,USA,North America  
 5,Najaf,Ali,Computer,1,300,2015-06-20,Karachi,Sindh,Pakistan,Asia  
 6,Sukhjeet,Singh,TV,2,900,2015-06-21,ChandiGar,Punjab,India,Asia  
 4,John,Rivers,Laptop,5,2400,2014-03-09,Jersey City,NJ,USA,North America  
 8,Aleena,Aman,Laptop,2,800,2015-05-25,Lahore,Punjab,Pakistan,Asia  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe  
 1,Aamir,Shahzad,TV,1,700,2015-07-15,Charlotte,NC,USA,North America  
 12,aamir,Shahzad,TV,1,7000,2015-07-15,Charlotte,NC,USA,North America  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe


   
 // Giving the rank to each Region   
 TotalSale  
 | order by Region  
 | extend Rank=row_rank(Region)


   
 //let's try to play little more and include Country with it  
 // Giving the rank to each Region   
 TotalSale  
 | extend RegionCountry=strcat(Region,'',Country)  
 | order by Region,Country  
 | extend Rank=row_rank(RegionCountry)

Video Demo: How to Use Row Rank Function in Kusto | Row Rank Function

How to Generate Row Numbers in Kusto | Row Number Function in Kusto | Kusto Query Language Tutorial

Topic: How to Generate Row Numbers in Kusto Query Language

In this article, we are going to learn about row number function in Kusto so row number returns the index of the current row in a serialized, row set so the row number index starts with the default one, and then if you want to increment by one that's what's going to happen by default but there is an option you can start with the different number as well, Kusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more.

 //row_number() Returns the current row's index in a serialized row set.  
 // The row index starts by default at 1 for the first row, and is incremented by 1 for each additional row.   
 //Optionally, the row index can start at a different value than 1. Additionally, the row index may be reset according to some provided predicate.  
 //Syntax  
 //row_number ( [StartingIndex [, Restart]] )  
   
 //create Table and Insert Sample Data in Azure Data Explorer DB for Testing  
 .drop table TotalSale  
 .create table TotalSale (  
   id: int   
   ,SalePersonFName: string  
   ,SalePersonLName : string  
   ,ProductName : string  
   ,ItemsSold : int  
   ,SoldPrice :real  
   ,SoldDate: date  
   ,City : string  
   ,State : string  
   ,Country : string  
   ,Region : string  
   )

   
      //Insert data  
       .ingest inline into table TotalSale <|  
 11,Tamara,Tony,Cell Phone,2,1200,2015-03-03,Frankfurt,Hesse,Germany,Europe  
 9,Petra,Henry,TV,10,5000,2015-04-08,Paris,Île-de-France,France,Europe  
 3,Christy,Ladson,TV,3,1600,2015-04-02,High Point,NC,USA,North America  
 7,Chirag,Patel,Cell Phone,5,1500,2015-06-23,AhmadAbad,Gujrat,India,Asia  
 2,M,Raza,Cell Phone,2,800,2015-07-15,Charlotte,NC,USA,North America  
 5,Najaf,Ali,Computer,1,300,2015-06-20,Karachi,Sindh,Pakistan,Asia  
 6,Sukhjeet,Singh,TV,2,900,2015-06-21,ChandiGar,Punjab,India,Asia  
 4,John,Rivers,Laptop,5,2400,2014-03-09,Jersey City,NJ,USA,North America  
 8,Aleena,Aman,Laptop,2,800,2015-05-25,Lahore,Punjab,Pakistan,Asia  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe  
 1,Aamir,Shahzad,TV,1,700,2015-07-15,Charlotte,NC,USA,North America  
 12,aamir,Shahzad,TV,1,7000,2015-07-15,Charlotte,NC,USA,North America  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe


 //Add RowNumber to results set  
 TotalSale  
 | order by id  
 | extend rn=row_number()

 
 //Add RowNumber to results set by start from specific number  
 TotalSale  
 | order by id  
 | extend rn=row_number(100)


 // let's see if we can use partiton of row_number  
 TotalSale  
 | order by Region,id asc  
 | extend rn=row_number(1,Region!=prev(Region))

Video Demo: How to Generate Row Numbers in Kusto | Row Number Function in Kusto

How to find Running Total in Kusto | Prev Function in Kusto Query | Kusto Query Language Tutorial

Topic: How to find Running Total in Kusto | Prev Function in Kusto Query.

In this video we are going to learn about the previous function in Kusto, previous function returns the value of a specific column in a specified row the specified row is a specified offset from a current row in the serialized row set so there are three different options we have with the previous we can simply provide the column or we can provide the offset value as well or there is a third one if there is a default value you would like to put for empty values, also you are going to learn how to find or calculate the running total by entire table or by a specific group

 //prev() - Returns the value of a specific column in a specified row. The specified row is at a specified offset from the current row in a serialized row set.  
 //Syntax  
 //prev(column)  
 //prev(column, offset)  
 //prev(column, offset, default_value)  
   
 //create Table and Insert Sample Data in Azure Data Explorer DB for Testing  
 .drop table TotalSale  
 .create table TotalSale (  
   id: int   
   ,SalePersonFName: string  
   ,SalePersonLName : string  
   ,ProductName : string  
   ,ItemsSold : int  
   ,SoldPrice :real  
   ,SoldDate: date  
   ,City : string  
   ,State : string  
   ,Country : string  
   ,Region : string  
   )

  
      //Insert data  
       .ingest inline into table TotalSale <|  
 11,Tamara,Tony,Cell Phone,2,1200,2015-03-03,Frankfurt,Hesse,Germany,Europe  
 9,Petra,Henry,TV,10,5000,2015-04-08,Paris,Île-de-France,France,Europe  
 3,Christy,Ladson,TV,3,1600,2015-04-02,High Point,NC,USA,North America  
 7,Chirag,Patel,Cell Phone,5,1500,2015-06-23,AhmadAbad,Gujrat,India,Asia  
 2,M,Raza,Cell Phone,2,800,2015-07-15,Charlotte,NC,USA,North America  
 5,Najaf,Ali,Computer,1,300,2015-06-20,Karachi,Sindh,Pakistan,Asia  
 6,Sukhjeet,Singh,TV,2,900,2015-06-21,ChandiGar,Punjab,India,Asia  
 4,John,Rivers,Laptop,5,2400,2014-03-09,Jersey City,NJ,USA,North America  
 8,Aleena,Aman,Laptop,2,800,2015-05-25,Lahore,Punjab,Pakistan,Asia  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe  
 1,Aamir,Shahzad,TV,1,700,2015-07-15,Charlotte,NC,USA,North America  
 12,aamir,Shahzad,TV,1,7000,2015-07-15,Charlotte,NC,USA,North America  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe

   
 //using next() to get next value from row  
 TotalSale  
 | order by id asc  
 | project id,SalePersonFName,SalePersonLName,prev(id)

  
   
 //using next() to get next value from row -- using offset  
 TotalSale  
 | order by id asc  
 | project id,SalePersonFName,SalePersonLName,prev(id,2)   
   
 TotalSale  
 | order by id asc  
 | project id,SalePersonFName,SalePersonLName,prev(SalePersonLName,2)

  
 // using the third parameter to fill the blanks  
 TotalSale  
 | order by id asc  
 | project id,SalePersonFName,SalePersonLName,prev(SalePersonLName,2)   
   
 TotalSale  
 | order by id asc  
 | project id,SalePersonFName,SalePersonLName,prev(SalePersonLName,2,"Fill the Blanks")

 
 // More real time scenario  
   
   
 //Running total by Region  
 TotalSale  
 | order by Region,ItemsSold  
 | project id,SalePersonFName,SalePersonLName,ItemsSold,SoldPrice,SoldDate,Region,RunningTotal=row_cumsum(ItemsSold)

 
 // Getting Running total for Region base  
 TotalSale  
 | order by Region,ItemsSold  
 | project id,SalePersonFName,SalePersonLName,ItemsSold,SoldPrice,SoldDate,Region,RunningTotal=row_cumsum(ItemsSold, Region != prev(Region))

Video Demo: How to find Running Total in Kusto | Prev Function in Kusto Query | Kusto Query Language Tutorial

How to Calculate Running Total in Kusto | Row cumsum function in Kusto Query Language

Topic: How to Calculate Running Total in Kusto

In this video we are going to learn about row underscore com sum function this function calculates the cumulative sum of a column in a serialized row set that means we can find the running total, Kusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more. The query uses schema entities that are organized in a hierarchy similar to SQL's: databases, tables, and columns. A Kusto query is a read-only request to process data and return results.

 // row_cumsum() - Calculates the cumulative sum of a column in a serialized row set.  
 //create Table and Insert Sample Data in Azure Data Explorer DB for Testing  
 .drop table TotalSale  
 .create table TotalSale (  
   id: int   
   ,SalePersonFName: string  
   ,SalePersonLName : string  
   ,ProductName : string  
   ,ItemsSold : int  
   ,SoldPrice :real  
   ,SoldDate: date  
   ,City : string  
   ,State : string  
   ,Country : string  
   ,Region : string  
   )


      //Insert data  
       .ingest inline into table TotalSale <|  
 11,Tamara,Tony,Cell Phone,2,1200,2015-03-03,Frankfurt,Hesse,Germany,Europe  
 9,Petra,Henry,TV,10,5000,2015-04-08,Paris,Île-de-France,France,Europe  
 3,Christy,Ladson,TV,3,1600,2015-04-02,High Point,NC,USA,North America  
 7,Chirag,Patel,Cell Phone,5,1500,2015-06-23,AhmadAbad,Gujrat,India,Asia  
 2,M,Raza,Cell Phone,2,800,2015-07-15,Charlotte,NC,USA,North America  
 5,Najaf,Ali,Computer,1,300,2015-06-20,Karachi,Sindh,Pakistan,Asia  
 6,Sukhjeet,Singh,TV,2,900,2015-06-21,ChandiGar,Punjab,India,Asia  
 4,John,Rivers,Laptop,5,2400,2014-03-09,Jersey City,NJ,USA,North America  
 8,Aleena,Aman,Laptop,2,800,2015-05-25,Lahore,Punjab,Pakistan,Asia  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe  
 1,Aamir,Shahzad,TV,1,700,2015-07-15,Charlotte,NC,USA,North America  
 12,aamir,Shahzad,TV,1,7000,2015-07-15,Charlotte,NC,USA,North America  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe


 //how to use running total in Kusto  
 TotalSale  
 | order by SoldDate asc  
 | serialize RunningTotal=row_cumsum(ItemsSold)  
 | project id,SalePersonFName,SalePersonLName,ItemsSold,SoldPrice,RunningTotal


   
 //Running total by Region  
 TotalSale  
 | order by Region,ItemsSold  
 | project id,SalePersonFName,SalePersonLName,ItemsSold,SoldPrice,SoldDate,Region,RunningTotal=row_cumsum(ItemsSold, Region != prev(Region))

Video Demo: How to Calculate Running Total in Kusto | Row cumsum function in Kusto Query Language

How to Find Duplicate Records in Kusto Query Language | Kusto Query Language

Topic: How to Find Duplicate Records in Kusto Query Language.

In this article, we are going to learn how to find duplicate records in table by using Kusto query Language, that's very simple and easy to find the duplicate records, you can easily learn how to find duplicate records by using the below provided scripts. Kusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more. The query uses schema entities that are organized in a hierarchy similar to SQL's: databases, tables, and columns. A Kusto query is a read-only request to process data and return results.

 // How to find duplicate records in table by using Kusto Query  
 //create Table and Insert Sample Data in Azure Data Explorer DB for Testing  
 .drop table TotalSale  
 .create table TotalSale (  
   id: int   
   ,SalePersonFName: string  
   ,SalePersonLName : string  
   ,ProductName : string  
   ,ItemsSold : int  
   ,SoldPrice :real  
   ,SoldDate: date  
   ,City : string  
   ,State : string  
   ,Country : string  
   ,Region : string  
   )


      //Insert data  
       .ingest inline into table TotalSale <|  
 11,Tamara,Tony,Cell Phone,2,1200,2015-03-03,Frankfurt,Hesse,Germany,Europe  
 9,Petra,Henry,TV,10,5000,2015-04-08,Paris,Île-de-France,France,Europe  
 3,Christy,Ladson,TV,3,1600,2015-04-02,High Point,NC,USA,North America  
 7,Chirag,Patel,Cell Phone,5,1500,2015-06-23,AhmadAbad,Gujrat,India,Asia  
 2,M,Raza,Cell Phone,2,800,2015-07-15,Charlotte,NC,USA,North America  
 5,Najaf,Ali,Computer,1,300,2015-06-20,Karachi,Sindh,Pakistan,Asia  
 6,Sukhjeet,Singh,TV,2,900,2015-06-21,ChandiGar,Punjab,India,Asia  
 4,John,Rivers,Laptop,5,2400,2014-03-09,Jersey City,NJ,USA,North America  
 8,Aleena,Aman,Laptop,2,800,2015-05-25,Lahore,Punjab,Pakistan,Asia  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe  
 1,Aamir,Shahzad,TV,1,700,2015-07-15,Charlotte,NC,USA,North America  
 12,aamir,Shahzad,TV,1,7000,2015-07-15,Charlotte,NC,USA,North America  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe


   
 // Find duplicate records by   
 TotalSale  
 |  summarize count() by id,SalePersonFName,SalePersonLName  
 | where count_ >1  
   
   
 TotalSale  
 |  summarize rn=count() by tolower(SalePersonFName),tolower(SalePersonLName)  
 | where rn>1

Video Demo: How to Find Duplicate Records in Kusto Query Language | Kusto Query Language

Max and Maxif to get max values in Kusto Query Language | Kusto Query Language Tutorial KQL 2022

Topic: Max and Maxif to get max values in Kusto Query Language

In this video we are going to learn about max and maxif functions, this function returns the max value across the group for which your predicate evaluates to true, Kusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more. The query uses schema entities that are organized in a hierarchy similar to SQL's: databases, tables, and columns.

 // max and maxif - Returns the maximum value across the group for which Predicate evaluates to true.  
 //create Table and Insert Sample Data in Azure Data Explorer DB for Testing  
 .drop table TotalSale  
 .create table TotalSale (  
   id: int   
   ,SalePersonFName: string  
   ,SalePersonLName : string  
   ,ProductName : string  
   ,ItemsSold : int  
   ,SoldPrice :real  
   ,SoldDate: date  
   ,City : string  
   ,State : string  
   ,Country : string  
   ,Region : string  
   )  
        
      //Insert data  
       .ingest inline into table TotalSale <|  
 11,Tamara,Tony,Cell Phone,2,1200,2015-03-03,Frankfurt,Hesse,Germany,Europe  
 9,Petra,Henry,TV,10,5000,2015-04-08,Paris,Île-de-France,France,Europe  
 3,Christy,Ladson,TV,3,1600,2015-04-02,High Point,NC,USA,North America  
 7,Chirag,Patel,Cell Phone,5,1500,2015-06-23,AhmadAbad,Gujrat,India,Asia  
 2,M,Raza,Cell Phone,2,800,2015-07-15,Charlotte,NC,USA,North America  
 5,Najaf,Ali,Computer,1,300,2015-06-20,Karachi,Sindh,Pakistan,Asia  
 6,Sukhjeet,Singh,TV,2,900,2015-06-21,ChandiGar,Punjab,India,Asia  
 4,John,Rivers,Laptop,5,2400,2014-03-09,Jersey City,NJ,USA,North America  
 8,Aleena,Aman,Laptop,2,800,2015-05-25,Lahore,Punjab,Pakistan,Asia  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe  
 1,Aamir,Shahzad,TV,1,700,2015-07-15,Charlotte,NC,USA,North America  
 12,aamir,Shahzad,TV,1,7000,2015-07-15,Charlotte,NC,USA,North America  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe  
   
   
 //Get the Max value across table  
 TotalSale  
 | summarize max(SoldPrice)  
   
 // get max value for each Region  
 TotalSale  
 | summarize max(SoldPrice) by Region  
   
 //using max by using Region by using maxif  
 TotalSale  
 | summarize maxif(SoldPrice,Region=="Asia")  
   
 TotalSale  
 | summarize maxif(SoldPrice,ItemsSold>3)

Video Demo: Max and Maxif to get max values in Kusto Query Language | Kusto Query Language Tutorial KQL 2022

How To Use dccount get estimate in Kusto Query Language | Kusto Query Language Tutorial KQL 2022

Topic: How To Use dccount get estimate in Kusto Query Language

In this article, we are going to learn about dccount function, dccount function returns, and estimate for a number of distinct values that are taken by scalar expression in the summary group, Kusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more. The query uses schema entities that are organized in a hierarchy similar to SQL's: databases, tables, and columns.

 // dcount - Returns an estimate for the number of distinct values that are taken by a scalar expression in the summary group.  
 //create Table and Insert Sample Data in Azure Data Explorer DB for Testing  
 .drop table TotalSale  
 .create table TotalSale (  
   id: int   
   ,SalePersonFName: string  
   ,SalePersonLName : string  
   ,ProductName : string  
   ,ItemsSold : int  
   ,SoldPrice :real  
   ,SoldDate: date  
   ,City : string  
   ,State : string  
   ,Country : string  
   ,Region : string  
   )  
        
      //Insert data  
       .ingest inline into table TotalSale <|  
 11,Tamara,Tony,Cell Phone,2,1200,2015-03-03,Frankfurt,Hesse,Germany,Europe  
 9,Petra,Henry,TV,10,5000,2015-04-08,Paris,Île-de-France,France,Europe  
 3,Christy,Ladson,TV,3,1600,2015-04-02,High Point,NC,USA,North America  
 7,Chirag,Patel,Cell Phone,5,1500,2015-06-23,AhmadAbad,Gujrat,India,Asia  
 2,M,Raza,Cell Phone,2,800,2015-07-15,Charlotte,NC,USA,North America  
 5,Najaf,Ali,Computer,1,300,2015-06-20,Karachi,Sindh,Pakistan,Asia  
 6,Sukhjeet,Singh,TV,2,900,2015-06-21,ChandiGar,Punjab,India,Asia  
 4,John,Rivers,Laptop,5,2400,2014-03-09,Jersey City,NJ,USA,North America  
 8,Aleena,Aman,Laptop,2,800,2015-05-25,Lahore,Punjab,Pakistan,Asia  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe  
 1,Aamir,Shahzad,TV,1,700,2015-07-15,Charlotte,NC,USA,North America  
 12,aamir,Shahzad,TV,1,7000,2015-07-15,Charlotte,NC,USA,North America  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe  
   
   
 // get distinct rows  
 TotalSale  
 | distinct Country,City  
   
 // get distinct count by using distinct, Distinct is expencive when we have large amount of data  
 TotalSale  
 | distinct Country,City  
 | summarize CityCount=count(City) by Country  
   
 // we can use dcount to get estimated counts.   
 TotalSale  
 | summarize CityCount=dcount(City) by Country  
   
 //Accuracy     Error (%)     -- By default dcount uses 1 Accuracy  
 //0         1.6       
 //1         0.8       
 //2         0.4       
 //3         0.28       
 //4         0.2       
 // Second parameter let you set the accuracy  
 TotalSale  
 | summarize CityCount=dcount(City,0) by Country  
   
 TotalSale  
 | summarize CityCount=dcount(City,1) by Country  
   
 TotalSale  
 | summarize CityCount=dcount(City,2) by Country  
   
 TotalSale  
 | summarize CityCount=dcount(City,3) by Country  
   
 TotalSale  
 | summarize CityCount=dcount(City,4) by Country

Video Demo: How To Use dccount get estimate in Kusto Query Language | Kusto Query Language Tutorial KQL 2022

Parse Operator In Kusto Query | Kusto Query Language Tutorial KQL 2022

Topic: How to Use Parse Operator in Kusto Query Language.

In This article, we are going to learn about Parse operator, parse operator evaluates string expression and parses it value into one or more calculated columns so this is a very powerful operator that can help us to break the data into multiple columns Kusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more. The query uses schema entities that are organized in a hierarchy similar to SQL's: databases, tables, and columns.

 // Parse Operator : Evaluates a string expression and parses its value into one or more calculated columns.   
   
 .create table Customer (  
   id: int ,FName:string ,LName: string,address: string   
   )  
   
       .ingest inline into table Customer <|  
 1,Aamir,Shahzad,"streetAddress:126,city:San Jone,state:CA,postalCode:394221,othernote: this is fake address,weather:Good"  
 2,Raza,Ali,"streetAddress:1401 Anderson St,city:Charlotte,state:NC,postalCode:28205,othernote: this is fake address,weather:Ok"  
 3,List,Robert,"streetAddress:5817 Hunters Crossing Ln #19,city:Charlotte,state:NC,postalCode:28215,othernote: this is fake address,weather:Ok"  
   
 //.drop table Customer  
   
 //Check all records  
 Customer  
   
 // Let's parse Add Column into multiple columns House#,City,StateZip  
 Customer  
 | parse address with "streetAddress:" StreedAddress  
            ",city:" cityName  
            ",state:" stateCode  
            ",postalCode:" ZipCode  
            ",othernote:" othernote   
 | project id,FName,LName,StreedAddress,cityName,stateCode,ZipCode,othernote  
   
   
 .drop table Customer  
 //Let's add some garbage data into good data and try to parse  
 .create table Customer (  
   id: int ,FName:string ,LName: string,address: string   
   )  
   
       .ingest inline into table Customer <|  
 1,Aamir,Shahzad," This is address streetAddress:126,city:San Jone,state:CA,Some people call it zip code postalCode:394221,othernote: this is fake address,weather:Good"  
 2,Raza,Ali,"streetAddress:1401 Anderson St,city:Charlotte,state:NC,postalCode:28205,othernote: this is fake address,weather:Ok"  
 3,List,Robert,"This is downloaded from zillowstreetAddress:5817 Hunters Crossing Ln #19,city:Charlotte,state:NC,Some Countries don't use zipcode postalCode:28215,othernote: this is fake address,weather:Ok"  
   
   
 Customer  
 | parse address with * "streetAddress:" StreedAddress ",city:" cityName ",state:" stateCode:string "," * "postalCode:" ZipCode ",othernote:" othernote   
 | project id,FName,LName,StreedAddress,cityName,stateCode,ZipCode,othernote

Video Demo: Parse Operator In Kusto Query | Kusto Query Language Tutorial KQL 2022

Next Function in Kusto Query | Kusto Query Language Tutorial (KQL) 2022

Topic: Next Function in Kusto Query Language

In this article, we are going to learn about next function in Kusto Query Language next returns the value of a column in a row that is at some offset following the current row in the serialize row set, Kusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more. The query uses schema entities that are organized in a hierarchy similar to SQL's: databases, tables, and columns.

 //next() - Returns the value of a column in a row that is at some offset following the current row in a serialized row set.  
 //Syntax  
 //next(column)  
 //next(column, offset)  
 //next(column, offset, default_value)  
   
 //create Table and Insert Sample Data in Azure Data Explorer DB for Testing  
 .drop table TotalSale  
 .create table TotalSale (  
   id: int   
   ,SalePersonFName: string  
   ,SalePersonLName : string  
   ,ProductName : string  
   ,ItemsSold : int  
   ,SoldPrice :real  
   ,SoldDate: date  
   ,City : string  
   ,State : string  
   ,Country : string  
   ,Region : string  
   )  
        
      //Insert data  
       .ingest inline into table TotalSale <|  
 11,Tamara,Tony,Cell Phone,2,1200,2015-03-03,Frankfurt,Hesse,Germany,Europe  
 9,Petra,Henry,TV,10,5000,2015-04-08,Paris,Île-de-France,France,Europe  
 3,Christy,Ladson,TV,3,1600,2015-04-02,High Point,NC,USA,North America  
 7,Chirag,Patel,Cell Phone,5,1500,2015-06-23,AhmadAbad,Gujrat,India,Asia  
 2,M,Raza,Cell Phone,2,800,2015-07-15,Charlotte,NC,USA,North America  
 5,Najaf,Ali,Computer,1,300,2015-06-20,Karachi,Sindh,Pakistan,Asia  
 6,Sukhjeet,Singh,TV,2,900,2015-06-21,ChandiGar,Punjab,India,Asia  
 4,John,Rivers,Laptop,5,2400,2014-03-09,Jersey City,NJ,USA,North America  
 8,Aleena,Aman,Laptop,2,800,2015-05-25,Lahore,Punjab,Pakistan,Asia  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe  
 1,Aamir,Shahzad,TV,1,700,2015-07-15,Charlotte,NC,USA,North America  
 12,aamir,Shahzad,TV,1,7000,2015-07-15,Charlotte,NC,USA,North America  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe  
   
 //using next() to get next value from row  
 TotalSale  
 | order by id asc  
 | project id,SalePersonFName,SalePersonLName,next(id)   
   
   
 //using next() to get next value from row -- using offset  
 TotalSale  
 | order by id asc  
 | project id,SalePersonFName,SalePersonLName,next(id,2)   
   
 TotalSale  
 | order by id asc  
 | project id,SalePersonFName,SalePersonLName,next(SalePersonLName,2)   
   
 // using the third parameter to fill the blanks  
 TotalSale  
 | order by id asc  
 | project id,SalePersonFName,SalePersonLName,next(SalePersonLName,2)   
   
 TotalSale  
 | order by id asc  
 | project id,SalePersonFName,SalePersonLName,next(SalePersonLName,2,"Fill the Blanks")

Video Demo: Next Function in Kusto Query | Kusto Query Language Tutorial (KQL) 2022

How to use project operator to select required columns in Kusto | Kusto Query Language Tutorial KQL

Topic: How to use project operator to select required columns in Kusto Query Language

In this article, we are going to learn about project Operator so it is very important for us to select the required data from a table sometimes we need to select a couple of columns sometimes we need to select all the columns except a few of them so with the projector it give us all those different options that we can use to select the required data from the table and especially this is a happening as you have hundreds of columns sometimes in a table or even you know let's say you even have your 50 columns but still get to very messy when you need to select a few of them and then exclude some of them, Kusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more. The query uses schema entities that are organized in a hierarchy similar to SQL's: databases, tables, and columns.

 //project -- Select the columns to include, rename or drop, and insert new computed columns.  
 //  
   
 //Let's create a table Customer  
 //.drop table Customer  
 .create table Customer (CustomerId: long, FName: string,LName:string, Salary:int,hiredate:datetime)   
  .ingest inline into table Customer <|  
 1,Aamir,Shahzad,2000,2021-11-28T04:52:43.8745893Z  
 2,Raza,ALI,4000,2018-11-28T04:52:43.8745893Z  
 3,Lisa,Rivers,50000,2021-12-28T04:52:43.8745893Z  
 4,steve,Ladson,1000,2010-09-28T04:52:43.8745893Z  
 5,Robert,Jr,500,2015-01-20T04:52:43.8745893Z  
 6,aamir,ali,1000,2005-03-01T04:52:43.8745893Z  
   
   
 // project  
   
 Customer  
 //Add some new derived Columns  
 Customer  
 | project CustomerId,FirstName=FName,FullName=strcat(FName,' ',LName)  
   
 //Exclude the columns by using project-away  
 Customer  
 | project-away hiredate,Salary  
   
 // Provide the list of Columns you want to keep, it is different from project as derived columns does not work  
 Customer  
 | project-keep *Name,CustomerId  
   
 // Get all the columns and Rename the once you want  
 Customer  
 | project-rename FirstNAme=FName,LastName=LName  
   
   
 //project-reorder, it reorder the columns which you want and leave the rest as is.  
 Customer  
 | project-reorder hiredate,Salary

Video Demo: How to use project operator to select required columns in Kusto | Kusto Query Language Tutorial KQL

How to Change the Data Type of a Column in Table by Using Kusto Query | Kusto Query Tutorial (KQL)

Topic: How to Change The Type of a Column in Table by Using Kusto Query Language.

How to change the type of a column in a table by using Kusto Query | Kusto Query Language Tutorial (KQL) Kusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more. The query uses schema entities that are organized in a hierarchy similar to SQL's: databases, tables, and columns. A Kusto query is a read-only request to process data and return results. The request is stated in plain text, using a data-flow model that is easy to read, author, and automate. Kusto queries are made of one or more query statements.

 //Let's create sample table TotalSale and insert some data.  
 //.drop table TotalSale  
 .create table TotalSale (  
   id: int   
   ,SalePersonFName: string  
   ,SalePersonLName : string  
   ,ProductName : string  
   ,ItemsSold : int  
   ,SoldPrice :real  
   ,SoldDate: date  
   ,City : string  
   ,State : string  
   ,Country : string  
   ,Region : string  
   )  
        
      //Insert data  
       .ingest inline into table TotalSale <|  
 11,Tamara,Tony,Cell Phone,2,1200.50,2015-03-03,Frankfurt,Hesse,Germany,Europe  
 9,Petra,Henry,TV,10,5000.89,2015-04-08,Paris,Île-de-France,France,Europe  
 3,Christy,Ladson,TV,3,1600,2015-04-02,High Point,NC,USA,North America  
 7,Chirag,Patel,Cell Phone,5,1500,2015-06-23,AhmadAbad,Gujrat,India,Asia  
   
   
 //select data from table  
 TotalSale  
   
 // Let's say if we want to change the value of SoldPrice to int instead of real  
 //Syntax: .alter column ['Table'].['ColumnX'] type=string  
 .alter column TotalSale.SoldPrice type=int   
   
 //select data from table  
 TotalSale  
 // notice that the SoldPrice is blank for any history data.  
 // you can insert new data   
  .ingest inline into table TotalSale <|  
 100,Tamara,Tony,Cell Phone,2,1201,2015-03-03,Frankfurt,Hesse,Germany,Europe  
 900,Petra,Henry,TV,10,5001,2015-04-08,Paris,Île-de-France,France,Europe  
 //check the data  
 TotalSale  
   
 // How to keep the history data, as if we change the data type of column we are losing the data.  
 // we can make a copy of the table and then use union   
   
 //clear the table  
 .clear table TotalSale data  
   
      //Insert data  
       .ingest inline into table TotalSale <|  
 11,Tamara,Tony,Cell Phone,2,1200.50,2015-03-03,Frankfurt,Hesse,Germany,Europe  
 9,Petra,Henry,TV,10,5000.89,2015-04-08,Paris,Île-de-France,France,Europe  
 3,Christy,Ladson,TV,3,1600,2015-04-02,High Point,NC,USA,North America  
 7,Chirag,Patel,Cell Phone,5,1500,2015-06-23,AhmadAbad,Gujrat,India,Asia  
   
 //check the data in TotalSale  
 TotalSale  
   
 // let's create History Total Sale table  
 .create table H_TotalSale(  
   id: int   
   ,SalePersonFName: string  
   ,SalePersonLName : string  
   ,ProductName : string  
   ,ItemsSold : int  
   ,SoldPrice :real  
   ,SoldDate: date  
   ,City : string  
   ,State : string  
   ,Country : string  
   ,Region : string  
   )  
   
 //Check the data for TotalSale and H_TotalSale  
 TotalSale  
 H_TotalSale  
   // swap the TotalSale table to H_TotalSale  
 .rename tables H_TotalSale=TotalSale, TotalSale=H_TotalSale  
   
 //Check the data for TotalSale and H_TotalSale  
 TotalSale  
 H_TotalSale  
   
 // Change the data type of column  
 .alter column TotalSale.SoldPrice type=int  
 // let's insert new records in TotalSale  
  .ingest inline into table TotalSale <|  
 100,Tamara,Tony,Cell Phone,2,1201,2015-03-03,Frankfurt,Hesse,Germany,Europe  
 900,Petra,Henry,TV,10,5001,2015-04-08,Paris,Île-de-France,France,Europe  
   
 // Get History Data and new data  
 //Check the data for TotalSale and H_TotalSale  
 TotalSale  
 H_TotalSale  
   
 // You will be using Union to get old and new data  
 H_TotalSale  
 | union TotalSale