Kusto Query Language (KQL) Tutorial

 

  1. How to Install Azure Data Explorer and Start Writing Kusto Query
  2. Tools to Connect to Azure Data Explorer and Write Kusto Query
  3. Database Management Commands For Azure Data Explorer by using Kusto Query
  4. Data Types and How to Create Table by using Kusto Query
  5. How to Add or Remove Columns in Table by Kusto Query
  6. Docstring in Kusto Add descriptive information for Table, Column or Function Kusto
  7. How to move Tables in Folders and Subfolders by Using Kusto 
  8. How to Create Empty table by Using Definition of Another Existing Table in Kusto Query
  9. How to Create Table, Drop Table and Rename Table in Kusto
  10. How to Create Single or Multiple Tables in Single Statement by using Kusto
  11. How to get List of Tables, Schema of Table and Definition of Tables in Kusto
  12. How to Recover Dropped Table in Kusto
  13. How to Clear Table Data by using Kusto Query Language
  14. How to Change the Data Type of a Column in Table by Using Kusto Query 
  15. How to Drop Single or Multiple Columns from Table by using Kusto Query 
  16. How to Rename Column of a Table by Using Kusto Query 
  17. How to use StormEvents Sample Table for Kusto Queries
  18. How to Select Data from Table by Using Kusto Query
  19. How to use Where Clause in Kusto Query 
  20. Kusto String Functions in Kusto Query Language 
  21. How to use Search Operator in Kusto to find Records for specific Keywords
  22. How to Use Count Operator in Kusto Query 
  23. Kusto Limit Operator and Take Alias 
  24. How to use Find Operator to Search Data in Multiple Tables in Azure Data Explorer DB by using Kusto
  25. Print Operator in Kusto Query 
  26. How to Use Ago and Now Functions in Kusto 
  27. How to Use Sort Operator in Kusto 
  28. How to Use Datatable Operation in Kusto to Create Temporary Table 
  29. How to Use Distinct Operator in Kusto to Get Unique Records 
  30. How to Use Extend to Add Calculated Columns in Kusto 
  31. How to Use Top Operator in Kusto Query to Get Top N Records 
  32. Serialize Operator Add Row Numbers to Result Sets 
  33. Kusto String Functions with Case Sensitivity 
  34. Kusto Query String Functions with Not
  35. Case Function in Kusto Query | Azure Data Explorer | Kusto Query Language Tutorial (KQL)
  36. How to use iif for IF ELSE in Kusto Query 
  37. How to Use Min and Max Function in Kusto Query 
  38. Kusto Query Using Sum and Sumif 
  39. Getschema Operator in Kusto Query Get Data Types of Columns of Table 
  40. Min and MinIf in Kusto Query | Azure Data Explorer 
  41. Kusto Query strcat How to Concatenate Columns in Kusto 
  42. How to Use Between and Not Between in Kusto Query 
  43. How to use project operator to select required columns in Kusto
  44. Next Function in Kusto Query 
  45. Parse Operator In Kusto Query 
  46. How To Use dccount get estimate in Kusto Query Language 
  47. Max and Maxif to get max values in Kusto Query Language 
  48. Using Let in Kusto Query language to Declare Variable, Functions and Views 
  49. How to Find Duplicate Records in Kusto Query Language 
  50. How to Calculate Running Total in Kusto | Row cumsum function in Kusto Query Language | KQL Tutorial
  51. How to find Running Total in Kusto | Prev Function in Kusto Query | Kusto Query Language Tutorial
  52. How to Generate Row Numbers in Kusto | Row Number Function in Kusto | Kusto Query Language Tutorial
  53. How to Use Row Rank Function in Kusto | Row Rank Function | Kusto Query Language Tutorial 
  54. How to Use To Scalar Function in Kusto | To Scalar Function in Kusto Query Language | KQL Tutorial
  55. How to Generate Series in Kusto | Kusto Make series function | Kusto Query Language Tutorial 
  56. Standard Deviation of Series in Kusto | Series Stats Get the Min, Max, Avg, | Kusto Query Tutorial
  57. External Table Read Data From File Blob Storage in Kusto Query | Kusto Query Language Tutorial 
  58. Facet Operator in Kusto Query (KQL) Generate Tables for each Column in Kusto Query Language 
  59. Split Function in Kusto Query (KQL) How to split string into values in Kusto Query Language 
  60. Lookup Operator in Kusto Query (KQL) 
  61. Range Operator in Kusto Query Language | Generate Number Sequence or Date Range in Kusto Query 
  62. Join Operator in Kusto Query | How to Do inner join ,Left Join, Right Join, Full Outer Join (KQL)
  63. DateTime part function in Kusto | How to get Year, Month and Day from DateTime 
  64. How to Format Date and Time in Kusto Query 
  65. Summarize Aggregate Functions in Kusto Query Language 
  66. Cross Cluster and Cross Database Queries | Join Data Across Different Databases in Kusto Query (KQL)
  67. IsNull and IsEmpty Functions in Kusto Query Language 
  68. How to use Union Operator in Kusto Query Language 

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, etcfor 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