Kusto Query strcat How to Concatenate Columns in Kusto | Kusto Query Language Tutorial (KQL)

Topic: Kusto Query strcat How to Concatenate Columns in Kusto Query Language


In this article, we are going to learn how to concatenate columns in Kusto Query language or some value that we need to concatenate, 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.

 ///strcat : Concatenates between 1 and 64 arguments. If the arguments aren't of string type, they'll be forcibly converted to a string.  
   
 .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  
   
 ///strcat : Concatenates between 1 and 64 arguments. If the arguments aren't of string type, they'll be forcibly converted to a string.  
   
 //Let's create a table Customer  
 //.drop table Customer  
 .create table Customer (CustomerId: long, FName: string,LName:string )   
  .ingest inline into table Customer <|  
 1,Aamir,Shahzad  
 2,Raza,Ali  
 3,Lisa,River  
 4,steve,Ladson  
 5,Robert,Jr  
   
 //Print and concate multiple values  
 print strcat("Aamir","  ","Shahzad",   "Age", 43) // Notice 43 is added but space is ignored  
   
 print strcat("Aamir"," ","Shahzad"," ","Age ", 43,' Does single quotes work') // yes you can add string in single quotes  
   
 print strcat(Aamir, ' ' , Shahzad) // Can you add string values without quotes?  
   
 print strcat(6, ' ' , 4) // Can you add string values without quotes?  
   
 print strcat('"Aamir"', ' ' , 'Shahzad') // How to Add double quotes in string concatenation  
   
 print strcat('Aamir','Fraction: ',45/2.0,' ','WholeNumber:' ,45,'  decimals' ,56.8888) // What will happen to decimical and fractions  
   
   
 //Use strcat for table columns to generate new columns  
 TotalSale  
 | project id,SalePersonFName,SalePersonLName,FullName=strcat(SalePersonFName,' ',SalePersonLName)  
   
   

Video Demo: Kusto Query strcat How to Concatenate Columns in Kusto | Kusto Query Language Tutorial (KQL)

Min and MinIf in Kusto Query | Azure Data Explorer | Kusto Query Language Tutorial (KQL)

Topic:  Min and MinIf  Functions in Kusto Query Language 

in this article, we are going to learn about two functions in Kusto Query Language one is min and otherone is the minif min function returns the minimum value across the group, and minif function returns the minimum value across the group for which predicates evaluate 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.

 // min and minif - Returns the minimum 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 min(SoldPrice)  
   
 // get max value for each Region  
 TotalSale  
 | summarize min(SoldPrice) by Region  
   
 //using max by using Region by using maxif  
 TotalSale  
 | summarize minif(SoldPrice,Region=="Asia")  
   
 TotalSale  
 | summarize minif(SoldPrice,ItemsSold>3)  

Video Demo:  Min and MinIf in Kusto Query | Azure Data Explorer | Kusto Query Language Tutorial (KQL)

Getschema Operator in Kusto Query Get Data Types of Columns of Table | Kusto Query Language Tutorial

Topic: Getschema Operator, Get Data Types of Columns of Table In Kusto Query Language

In this article, we will learn about getting schema operator, which is very important and very easy. This is one of the things I often use because you often need to get the schema of the table with the column list and getting the schema is going to get us a list, 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.

 // getschema operator : Produce a table that represents a tabular schema of the input.  
   
 //get schema of entire table  
 TotalSale  
 | getschema   
   
   
 //get schema of result set  
 TotalSale  
 | project SalePersonFName,SalePersonLName,ProductName,id  
 | getschema   

Video Demo: Getschema Operator in Kusto Query Get Data Types of Columns of Table

Kusto Query Using Sum and Sumif | Kusto Query Language Tutorial (KQL)

Topic: Sum and Sumif  Functions in Kusto Query Language

In this article we are going to learn about two functions the sum and sum if in Kusto Query Language Sum you can tell from the name itself where it is going to use to sum the values from different columns and then we have Sumif that returns the sum of expressions for which a 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.

 // sum and sumif -- Returns a sum of Expr 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  
   
 // total items sold  
 TotalSale  
 | summarize sum(ItemsSold)  
   
   
 // Native way to perform sum by filtering and summing  
 TotalSale  
 | where Region=="Asia"  
 | summarize sum(ItemsSold)  
   
 // sumif to get results for condition -- perform sum only for Region=Asia  
 TotalSale  
 | summarize sumif(ItemsSold,Region =="Asia")  

Video Demo: Kusto Query Using Sum and Sumif | Kusto Query Language Tutorial (KQL)

How to Use Min and Max Function in Kusto Query | Kusto Query Language Tutorial (KQL)

Topic: How to Use Min and Max Function in Kusto Query 


In this article we are going to learn about min and max functions in Kusto Query Language min and max functions are used to find the minimum values and maximum values 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.

 // min and minif - Returns the minimum 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 min(SoldPrice)  
   
 // get max value for each Region  
 TotalSale  
 | summarize min(SoldPrice) by Region  
   
 //using max by using Region by using maxif  
 TotalSale  
 | summarize minif(SoldPrice,Region=="Asia")  
   
 TotalSale  
 | summarize minif(SoldPrice,ItemsSold>3)  

Video Demo: How to Use Min and Max Function in Kusto Query | Kusto Query Language Tutorial (KQL)

How to Use iif for IF ELSE in Kusto Query | Kusto Query Language Tutorial (KQL)

Topic: How to use iif for IF ELSE in Kusto Query Language.

In this article we are going to learn about iif statement term this can be used so for if else the condition is true or false so there are only two possibilities here so it is very useful and a quick way to write the expressions of where we would like to use the if else condition.

 

 //iif - It can be use for IF-ELSE , The condition is true or false.  
   
   
 //Let's create a table Customer  
 //.drop table Customer  
 .create table Customer (CustomerId: long, FName: string,LName:string, Salary: int)   
  .ingest inline into table Customer <|  
 1,Aamir,Shahzad,2000  
 2,Raza,ALI,4000  
 3,Lisa,Rivers,50000  
 4,steve,Ladson,1000  
 5,Robert,Jr,500  
 6,aamir,ali,  
   
   
 Customer  
 | extend SalaryType=iif(Salary<=4000 ,"Very Low Salary" ,"Good Salary")  
   
 //Taking care of Null/Blanks  
 Customer  
 | extend SalaryType=iif(Salary<=4000 or isnull(Salary) ,"Very Low Salary" ,"Good Salary")  
   

Video Demo: How to use iif for IF ELSE in Kusto Query | Kusto Query Language Tutorial (KQL)

Case Function in Kusto Query | Azure Data Explorer | Kusto Query Language Tutorial (KQL)

Topic: Case Function in Kusto Query Language.


In this article we are going to learn about Case Statement in Kusto Query Language, this is pretty much the same way what you have learned in other languages so not a big difference here but we are going to experiment and see how exactly it looks in Kusto Query Language.

 //Case ()  
 //Evaluates a list of predicates and returns the first result expression whose predicate is satisfied.  
 //If neither of the predicates return true, the result of the last expression (the else) is returned.  
   
 //Let's create a table Customer  
 //.drop table Customer  
 .create table Customer (CustomerId: long, FName: string,LName:string, Salary: int)   
  .ingest inline into table Customer <|  
 1,Aamir,Shahzad,2000  
 2,Raza,ALI,4000  
 3,Lisa,Rivers,50000  
 4,steve,Ladson,1000  
 5,Robert,Jr,500  
 6,aamir,ali,  
   
   
 Customer  
 | extend   
 SalaryType=case (Salary<=2000 or isnull(Salary) , 'VeryLow'  
 ,Salary>2000 and Salary<=4000, 'Medium'  
 ,'Good')  
   

Video Demo: Case Function in Kusto Query | Azure Data Explorer | Kusto Query Language Tutorial (KQL)

Kusto Query String Functions with Not | Kusto Query Language Tutorial (KQL)

Topic: Kusto Query String Functions with Not In Kusto Query Language


Not operator returns the reversed logical value of its bool argument, 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.


 //! - Not Operator - When you want to go opposite  
 //Let's create a table Customer  
 //.drop table Customer  
 .create table Customer (CustomerId: long, FName: string,LName:string )   
  .ingest inline into table Customer <|  
 1,Aamir,Shahzad  
 2,Raza,Ali  
 3,Lisa,River  
 4,steve,Ladson  
 5,Robert,Jr  
 ,aamir,ali  
 // Get everything but where FName does not contain aamir  
 Customer  
 | where FName !contains "aamir"  
 // !contain_cs - Case senstive  
 Customer  
 | where FName !contains_cs "Aamir"  
 // Get all but not in the provided list - Case senstive  
 Customer  
 | where FName !in ("aamir")  
 // Get all but not in the provided list - Case Insenstive  
 Customer  
 | where FName !in~("aamir")  
 // startswith_cs and startswith with !  
 Customer  
 | where FName !startswith "aa"  
 Customer  
 | where FName !startswith_cs "Aa"  
 //endswith and endswith_cs with !  
 Customer  
 | where FName !endswith "IR"  
 Customer  
 | where FName !endswith_cs "ir"  
 //usefull link  
 //https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/datatypes-string-operators  

Video Demo: Kusto Query String Functions with Not | Kusto Query Language Tutorial (KQL)

Kusto String Functions with Case Sensitivity | Kusto Query Language Tutorial (KQL)

Topic: Kusto String Functions with Case Sensitivity In Kusto Query Language.

In this article, we are going to learn about case sensitive data often we have data in the table that's start with the uppercase lowercase and all that and sometimes we really want to find out that data or get the data that is specific to that case sensitivity, Kusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more. 


 //contains_cs - Case Senstive  
 //Let's create a table Customer  
 //.drop table Customer  
 .create table Customer (CustomerId: long, FName: string,LName:string )   
  .ingest inline into table Customer <|  
 1,Aamir,Shahzad  
 2,Raza,Ali  
 3,Lisa,River  
 4,steve,Ladson  
 5,Robert,Jr  
 ,aamir,ali  
 // Get you all by ignoring case senstivity  
 Customer  
 | where FName contains "aamir"  
 //How to make sure you get what you want - Case senstive  
 Customer  
 | where FName contains_cs "aamir"  
 // Get only what exactly matches in Case  
 Customer  
 | where FName in ("aamir")  
 // in clause with ignoring case senstivity  
 Customer  
 | where FName in~("aamir")  
 // startswith_cs and startswith  
 Customer  
 | where FName startswith "aa"  
 Customer  
 | where FName startswith_cs "Aa"  
 //endswith and endswith_cs  
 Customer  
 | where FName endswith "IR"  
 Customer  
 | where FName endswith_cs "ir"  

Video Demo: Kusto String Functions with Case Sensitivity | Kusto Query Language Tutorial (KQL)

Let Operator in Kusto Query Language (KQL)

 Topic: Let Operator in Kusto Query Language (KQL)

In this article we are going to learn about let operator in Kusto, so uses the let statement to set a variable name equal to an expression or a function or  to create a view, so that's a very powerful and very helpful operator so let's go ahead and experiment this operator and see how it works in Kusto by using the below provided statements, in the bottom we have the Video tutorial for the Let Operator.

 

 // let operator: Use the let statement to set a variable name equal to an expression or a function, or to create views.  
 //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 set a variable Region  
 let RegionName="Asia";  
 TotalSale  
 | where Region ==RegionName  
  
 
 // use multiple variables in query   
 let varCountry="Pakistan";  
 let varCity="Karachi";  
 TotalSale  
 | where Country ==varCountry and City ==varCity  
   

 // print the values  
 let varCountry="Pakistan";  
 let varCity="Karachi";  
 print strcat(varCountry,' ',varCity)  
   

 // Save entire data sets to variables and then use them.  
 let AsiaRegion= TotalSale  
 | where Region =="Asia";  
   
 let EuropRegion=TotalSale  
 | where Region=="Europe";  
   
 union withsource="TableName"  
 AsiaRegion,EuropRegion  
   
 
  
 // let can hold your function definition  
 let fullname=(FName:string,LName:string)  
 {strcat('MyFullName is:',FName,' ',LName)};  
 print fullname('Aamir','Shahzad')  
  
 
 //use the function for Table Results  
 let fullname=(FName:string,LName:string)  
 {strcat('CustomerFullName: ',FName,' ',LName)};  
 TotalSale  
 | project SalePersonFName,SalePersonLName,FullName=fullname(SalePersonFName,SalePersonLName)  
  
 
   
 // use let to create view  
 //Views are virtual tables based on the result-set of a Kusto Query Language query.   
 //Just like a real table, a view contains rows and columns. Unlike a real table, a view doesn't hold its own data storage.  
   
 let Range10 = view () { range IdColumn from toint(1) to toint(10) step 1 };  
 let TotalSaleofAsia = view () { TotalSale | where Region=="Asia" };  
 TotalSaleofAsia  
 | join kind=inner Range10 on $left.id==$right.IdColumn  
   

Video Demo: Using Let in Kusto Query language to Declare Variable, Functions and Views | Kusto Query Tutorial

Serialize Operator Add Row Numbers to Result Sets | Kusto Query Language Tutorial (KQL)

Topic: Serialize Operator Add Row Numbers to Result Sets in Kusto Query Language


In this article, we are going to learn how to use a serialized operator in Kusto Query Language, Serialize Operator marks that the order of the input row set is safe to use for windows functions. 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.


 //serialize - Marks that the order of the input row set is safe to use for window functions.  
 // .drop table Customer  
 .create table Customer(CustomerId:int, Name:string)   
  .ingest inline into table Customer <|  
 1,Aamir  
 1,Raza  
 2,Lisa  
 3,steve  
 int(null),aamirtest  
 4,aamir  
 // Adding Row numbers to results  
 Customer   
 |serialize rn=row_number()  
 Customer   
 | order by Name asc   
 |serialize rn=row_number()  
 Customer   
 | order by tolower(Name) asc   
 |serialize rn=row_number()  

Video Demo: Serialize Operator Add Row Numbers to Result Sets | Kusto Query Language Tutorial (KQL)


How to Use Datatable Operation in Kusto to Create Temporary Table | Kusto Query Language Tutorial

Topic: How to Use Datatable Operation in Kusto to Create Temporary Table

How to use Datatable Operation in Kusto to create temporary table | Kusto Query Language Tutorial (KQL) In this article, we are going to learn about datatable operation in Kusto, so think about that you want to create some temporary table insert some values and then use that in the different queries that's possible by using datatable operation, so datatable returns a table of whose schema and values are defined in the query itself Kusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more.

 //datatable operation : Returns a table whose schema and values are defined in the query itself.  
 datatable (id:int,name:string)  
 [1,"aamir",  
 2,"Shahzad",  
 3,"Lisa"]  
 | where id>1   
   

 //Give a name to your Temporary table  
 let TempData= datatable (id:int,name:string)  
 [1,"aamir",  
 2,"Shahzad",  
 3,"Lisa"];  
 // get the data from datatable  
 TempData  
 | where id>1  
   
  
 
 // use them where you need to  
 let TempData= datatable (id:int,name:string)  
 [1,"aamir",  
 2,"Shahzad",  
 3,"Lisa"];   
 union TempData,TotalSale  

Video Demo: How to Use Datatable Operation in Kusto to Create Temporary Table

How to Use Sort Operator in Kusto | Kusto Query Language Tutorial (KQL)

 Topic: How to Use Sort Operator in Kusto Query Language 


How to use Sort Operator in Kusto Query | Kusto Query Language Tutorial (KQL) In this article we are going to learn about the sort operator in a Kusto Query Language, sort the rows of input table into order by one or more columns that's what the sort operator does for us. 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. 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.

 // Sort Command : Sort the rows of the input table into order by one or more columns.  
   
 .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  
 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  
   
 //By Default sort by is desc  
 TotalSale  
 | project SalePersonFName,SalePersonLName,ProductName  
 | sort by SalePersonFName   
   
   
 //use asc  
 TotalSale  
 | project SalePersonFName,SalePersonLName,ProductName  
 | sort by SalePersonFName asc  
   
 //use asc and desc together for multiple columns  
 //use asc  
 TotalSale  
 | project SalePersonFName,SalePersonLName,ProductName  
 | sort by SalePersonFName asc,SalePersonLName desc  
   
   
 // How to handle case senstive in sorting  
 //use asc  
 TotalSale  
 | project SalePersonFName,SalePersonLName,ProductName  
 | sort by tolower(SalePersonFName) asc // notice about lower case , tolower() or toupper() function can help  
   
   
 //Show Null values first  
 TotalSale  
 | project SalePersonFName,SalePersonLName,ProductName,id  
 | sort by id asc nulls first // Show Null values first   
   
   
 //If you want to exclude Null values   
 TotalSale  
 | where isnotnull(id)   
 | project SalePersonFName,SalePersonLName,ProductName,id  
 | sort by tolower(SalePersonFName) asc  

Video Demo: How to Use Sort Operator in Kusto | Kusto Query Language Tutorial (KQL)

How to Use Ago and Now Functions in Kusto | Kusto Query Language Tutorial (KQL)

 Topic: How to Use Ago and Now Functions in Kusto


How to use Ago and Now functions in Kusto Query | Kusto Query Language Tutorial (KQL) Kusto Query Language In this article we are going to learn about two functions one is ''now'' another one is ''ago'', now function returns the current utc clock time optionally offset by a given time span so you can provide different time spans and get the value out of that, this function can be used multiple times in a statement and the clock time being reference will be same for all the instances, so you can reference ''now'' in multiple times in the one statement and it's going to return you the same value, let's talk about ago function ago subtract the given time span from the current utc clock time so you can use ago minus 2d or minus 3d so you can subtract some days or minutes hours and all that from there go and it will return you that data.

 //Now() Function  
 //Returns the current UTC clock time, optionally offset by a given timespan. This function can be used multiple times in a statement a  
 //and the clock time being referenced will be the same for all instances.  
   
 print now()  
 //2021-11-24T23:07:15.0607137Z  
   
 print now()-2d  
   
 print now(-2d)  
   
 //ago() function -- Subtracts the given timespan from the current UTC clock time.  
   
 print now=now() //current date time  
 ,0minuteago=ago(0m) // current datetime by using ago function  
 , 1dayago=ago(1d) // one day ago   
 ,1hourago=ago(1h) //one hour ago from current datetime  
 ,1minuteago=ago(1m) // one minute ago from current datetime  
 ,1secondago=ago(1s)// one second ago from current datetime  
 ,10milisecondsago=ago(10ms) //10 mili second ago from current datetime  

Video Demo: How to Use Ago and Now Functions in Kusto | Kusto Query Language Tutorial (KQL)

Print Operator in Kusto Query | Kusto Query Language Tutorial (KQL)

 Topic: Print Operator in Kusto Query Language

Print Operator in 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.

 //Print Function   
 // Can be used to print out values  
   
 print 'TechbrothersIT'  
   
 print "Aamir Shahzad"  
   
 //Perform some calculations and print results  
 print 4*3  
   
 // Give name to results ( Column Name)  
 print results= 4*3  
   
 //Use Variable and Print Variable Value  
 let abc=4;  
 print abc;  
   
 //Save results of Query in variable and then print  
 let RecordCount = TotalSale  
  | count;  
  //print TotalRecords = toscalar(RecordCount)  
   
  //Save results of Query in variable and then print  
 let TotalItemsSold = TotalSale  
  | summarize sum(ItemsSold);  
  //print TotalRecords = toscalar(TotalItemsSold)  
   
 //Print Multiple Variables  
  print ItemCount = toscalar(RecordCount), ItemSold = toscalar(TotalItemsSold)  

Video demo: Print Operator in Kusto Query | Kusto Query Language Tutorial (KQL)

How to Use Distinct Operator in Kusto to Get Unique Records | Kusto Query Language (KQL)

Topic: How to Use Distinct Operator in Kusto to Get Unique Records | Kusto Query Language (KQL)

In this Article, we are going to learn about distinct operator distinct operator produce a table with a distinct combination of the provided columns of the input table. 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.

 // distinct operator : Produces a table with the distinct combination of the provided columns of the input table.  
 .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  
 //Get distinct records for all columns  
 TotalSale  
 | distinct *  
 //distinct for specific columns  
 TotalSale  
 | distinct SalePersonFName,SalePersonLName  
 //how to ignore case senstive data for distinct  
 TotalSale  
 | distinct tolower(SalePersonFName),tolower(SalePersonLName)  

Video Demo: How to Use Distinct Operator in Kusto to Get Unique Records Kusto Query Language (KQL)

How to use Find Operator to Search Data in Multiple Tables in Azure Data Explorer DB by using Kusto

Topic: How to use Find Operator to Search Data in Multiple Tables in Azure Data Explorer DB by using Kusto


How to use the find operator to search Data in multiple tables in Azure Data Explorer DB by using Kusto Query Language | 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.

 // Using Search operator  
 .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 TotalSaleAsia <|  
 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  
 14,aamir,Shahzad,TV,1,7000,2015-07-15,Charlotte,NC,USA,South America  
 17,Chirag,Patel,Charger Cell Phone,5,1500,2015-06-23,AhmadAbad,Gujrat,India,Asia  
   
 //Find operator  
 Finds rows that match a predicate across a set of tables.  
   
 //Find value in all tables in the current database  
 find "aamir"  
   
 //find Column Value in All Tables with Different Columns to display from each Table  
 find SalePersonFName == 'aamir' or CustomerName == 'Aamir' project SalePersonFName,SalePersonLName,CustomerName,CustomerAddress,Salary  
   
 //find the records in which column has the value  
 find in (TotalSale,TotalSaleAsia) where SalePersonFName =="aamir"  
   
 //find the records in all the columns in all given tables  
 find in (TotalSale,TotalSaleAsia,Customer) where * =="aamir"  
 find in (TotalSale,TotalSaleAsia,Customer) where * =~"aamir"  
   
   
 // Find in entire database for specific column value  
 find in (database('techbrothersdb').*) where SalePersonFName == "aamir"  
   
 // Find in entire database for specific column value in all columns  
 find in (database('techbrothersdb').*) where * == "North America"  


Video Demo: How to use Find Operator to Search Data in Multiple Tables in Azure Data Explorer DB by using Kusto

Kusto Limit Operator and Take Alias | Kusto Query Language Tutorial (KQL)

Topic: Kusto Limit Operator and Take Alias


Kusto Limit Operator and Take Alias | 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.

 // Limit Operator / TAKE Operator   
   
 //Definition : Return up to the specified number of rows. There is no guranttee which records will be returned unless order by is used.  
   
   
 // Get 5 Random records from a table  
 TotalSale  
 | limit 5  
   
 // Take 5 records by using order by and limit operator  
 TotalSale  
 | order by id asc | limit 5  
   
   
 // Get 5 Random records from a table by using Take Alias  
 TotalSale  
 | take 5  
   
   
 // Take 5 records by using order by   
 TotalSale  
 | order by id asc | take 5  
   
 TotalSale  
 | order by SalePersonFName desc | take 5  

Video Demo: Kusto Limit Operator and Take Alias | Kusto Query Language

How to Use Count Operator in Kusto Query | Kusto Query Language Tutorial (KQL)

 Topic: How to Use Count Operator in Kusto Query


How to use count operator in Kusto Query Language | 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.

 //Returns the number of records in the input record set.  
   
 //Get the Row Count of Table  
   
 TotalSale  
 | count   
   
   
 //Get the Record Count for Filtered Expressions  
 TotalSale  
 | where SalePersonFName =="aamir" | count  
   
 // using Distinct with Count - Single column or Multiple columns  
 TotalSale  
 | distinct City | count  

Video Demo: How to Use Count Operator in Kusto Query

How to use Search Operator in Kusto to find Records for specific Keywords | Kusto Tutorial (KQL)

Topic: How to use Search Operator in Kusto to find Records for specific Keywords


How to use Search Operator in Kusto to find Records for specific Keywords | 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.

 // Using Search operator  
 .create table TotalSaleAsia (  
   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 TotalSaleAsia <|  
 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  
 14,aamir,Shahzad,TV,1,7000,2015-07-15,Charlotte,NC,USA,South America  
 17,Chirag,Patel,Charger Cell Phone,5,1500,2015-06-23,AhmadAbad,Gujrat,India,Asia  
   
 //How to use Search in Kusto  
 // Search for a term in entire Database  
 search "aamir"  
   
 // Search in entire Table  
 TotalSale  
 | search "aamir"  
   
 // Searching by keeping case senstive in mind  
 TotalSale  
 | search kind=case_sensitive "aamir"  
   
 //Search in Multiple Tables in all columns for value   
 search in (TotalSaleAsia,TotalSale) "aamir"  
   
 //Search for value in column has the value  
 TotalSale  
 | search Region:"South"  
   
 //Search for wild characters by using * , works like has hasprefix or hassuffix  
 TotalSale  
 | search "*ce"  
   
 TotalSale  
 | search "aa*"  
   
   
 // Use search to find values which contain  
 TotalSale  
 | search "*to*"  
   
 //Search regex -   
 TotalSale  
 | search "P*b"   
   
   
 //Search in a column  
 TotalSale  
 | search SalePersonFName matches regex "ir"  
   
 //Search multiple criteria  
 TotalSale  
 | search "Asia" and ("Lahore" or "Karachi")  
   
 TotalSale  
 | search "Asia" or ("Lahore" and "Karachi")  
   
 TotalSale  
 | search "Asia" and (ItemsSold>1 and id<7)  

Video Demo: How to use Search Operator in Kusto to find Records for specific Keywords


Kusto String Functions in Kusto Query | Kusto Query Language Tutorial

 Topic: String Function in Kusto Query Language.

String function in Kusto Query Language | 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.

 // String Functions in Kusto  
 .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  
 14,aamir,Shahzad,TV,1,7000,2015-07-15,Charlotte,NC,USA,South America  
 17,Chirag,Patel,Charger Cell Phone,5,1500,2015-06-23,AhmadAbad,Gujrat,India,Asia  
   
 //Kusto String Functions  
   
 //Equal Operator ==  
 TotalSale  
 | where SalePersonFName =="Aamir"  
   
   
 //Not Equal Operator !=  
 TotalSale  
 | where SalePersonFName !="Aamir"  
   
   
 //Equal Operator =~ Ignore case senstive  
 TotalSale  
 | where SalePersonFName =~"Aamir"  
   
   
 //Has to Look for the whole term  
 TotalSale  
 | where Region has "America"  
   
 TotalSale  
 | where ProductName has "Cell"  
   
 // !has ignore the entire record if term exists  
 TotalSale  
 | where ProductName !has "Cell"  
   
 // hasprefix and hassuffix   
 TotalSale  
 | where ProductName hasprefix "Cel"  
   
 TotalSale  
 | where ProductName hassuffix "one"  
   
 //contains and !contain  
 TotalSale  
 | where Region contains "i"  
   
 TotalSale  
 | where Region !contains "As"  
   
 //in, !in, in~,!in~  
 TotalSale  
 | where SalePersonFName in ('aamir','Najaf') // it is case senstive  
   
 TotalSale  
 | where Country !in ('USA','France')  
   
 TotalSale  
 | where SalePersonFName in~ ('aamir','Najaf') //ignore the case senstivity  
   
 TotalSale  
 | where SalePersonFName !in~ ('aamir','Najaf')  
   
   
 // has_any element value equal to what we are looking  
 TotalSale  
 | where Region  has_any ("South","America")  

Video Demo: Kusto String Functions in Kusto Query 

How to use Where Clause in Kusto Query | Kusto Query Language Tutorial (KQL)

Topic: How to use Where Clause in Kusto Query Language.


How to use the Where clause in Kusto Query Language | 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.

 How to use the Where Clause in Kusto   
   
 //How to use Where with equal   
 TotalSale  
 | where SalePersonFName =="Aamir"  
   
 //How to get data with Where Clause By ignoring Case Senstivity  
 TotalSale  
 | where SalePersonFName =~"Aamir"  
   
 // Where clause with multiple columns  
 TotalSale  
 | where ProductName =="TV" and City =="High Point"  
   
 TotalSale  
 | where ProductName =="TV" | where City =="High Point"  
   
 //Where Clause with > or < or >= or <=  
 TotalSale  
 | where id>10  
   
 TotalSale  
 | where id<10  
   
 TotalSale  
 | where id>=10  
   
 TotalSale  
 | where id<=10  
   
   
 // Where Clause with Like operator  
 TotalSale  
 | where SalePersonFName contains "aa"  
   
 //Where clause with "%abc"  
 TotalSale  
 | where SalePersonFName startswith "Ch"  
   
 // Where clause where "abc%"  
 TotalSale  
 | where SalePersonFName endswith "ty"  
   
 //using Regex , should start with LA and ends with anything  
 TotalSale  
 | where ProductName matches regex "^La.*"  

Video Demo: How to use Where Clause in Kusto Query

How to Select Data from Table by Using Kusto Query | Kusto Query Language Tutorial (KQL)

Topic: How to Select Data from Table by Using Kusto Query.  


How to Select Data from 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.

 https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/sqlcheatsheet  
   
 .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  
   
 //Select everything from Table  
 TotalSale  
   
 //Provide the list of Columns  
 TotalSale  
 | project id, SalePersonFName, SalePersonLName, ProductName, ItemsSold, SoldPrice, SoldDate, City, State, Country, Region  
   
 //Column Name as New Column Name  
 TotalSale  
 | project CustomerId=id,FName=SalePersonFName  
   
 //How to Concate two Columns  
 TotalSale  
 | project FullName=strcat(SalePersonFName ,"  ", SalePersonLName)  
   
 //Adding Constant Value for a Column  
 TotalSale  
 | project SalePersonFName,DerivedColumn='Test',RegionId=6,CurrentDate=now()  
   
   
 //Top N Rows -- You can use Take or Limit  
 TotalSale  
 | take 5  
   
   
 //Top N row with Order by   
 TotalSale  
 | order by id asc   
 | limit 5  

Video Demo: How to Select Data from Table by Using Kusto Query 

How to use StormEvents Sample Table for Kusto Queries | Kusto Query Language Tutorial (KQL)

Topic: How to Use StormEvents Sample Table for Kusto Queries.

How to use StormEvents Sample Table for Kusto Queries | 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.


 // how to use sample stormevent table   
 cluster("https://help.kusto.windows.net").database("Samples").StormEvents  
 | take 10  
   
 cluster("https://help.kusto.windows.net").database("Samples").StormEvents  
 | where State =="FLORIDA"  
   
 // get record by state  
 cluster("https://help.kusto.windows.net").database("Samples").StormEvents  
 | summarize count() by State   

Video Demo: How to Use StormEvents Sample Table for Kusto Queries.

How to Rename Column of a Table by Using Kusto Query | Kusto Query Language Tutorial (KQL)

 Topic: How to Rename The Column of a Table by Using Kusto Query Language.


How to rename the column of 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.

 //.rename column  
 // .rename column can be used to rename columns  
   
 //Let's create sampel table TotalSale and insert some 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.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 table  
 TotalSale  
   
 //Let's Rename SalePersonFName to FName only  
 //Syntax: .rename column [DatabaseName .] TableName . ColumnExistingName to ColumnNewName  
 .rename column TotalSale.SalePersonFName to FirstName  
 .rename column TotalSale.SalePersonLName to LastName  
   
 //Check the data in table  
 TotalSale  

Video Demo: How to Rename Column of a Table by Using Kusto Query Language

How to Drop Single or Multiple Columns from Table by using Kusto Query | Kusto Query Tutorial (KQL)

Topic: How to Drop Single or Multiple Columns from Table by Using Kusto Query Language.


How to drop single or multiple columns from the 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.

 How to drop single or multiple Columns from a table by using Kusto  
 //.drop column  
 //This command does not physically delete the data, and does not reduce   
 //the cost of storage for data that was already ingested.  
   
 //This command is irreversible. All data in the column that is removed will no longer be queryable.  
 // Future commands to add that column back will not be able to restore the data.  
   
 //Let's create sampel 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  
   
 //Check the data in table  
 TotalSale  
   
 // Let's say we want to drop column Region  
 //Syntax : .drop column TableName . ColumnName  
 .drop column TotalSale.Region  
   
 //Check the data in table  
 TotalSale  
   
 //If you want to drop multiple columns  
 .drop table TotalSale columns (City,State,Country)  
   

Video Demo: How to Drop Single or Multiple Columns from Table by using Kusto Query Language


How to Clear Table Data by using Kusto Query | Kusto Query Language Tutorial (KQL)

 Topic: How to Clear Table Data by Using Kusto Query Language.


How to clear Table data by using Kusto Query Language | 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.


 //.clear table data  
 //Clears the data of an existing table, including streaming ingestion data.  
 //Let's create sampel table TotalSale and insert some 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  
 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  
   
 //select data from table  
 TotalSale  
   
 //.clear table TableName data   
   
 .clear table TotalSale data  

Video Demo: How to Clear Table Data by Using Kusto Query Language.

How to Recover Dropped Table in Kusto | Kusto Query Language Tutorial (KQL)

 Topic: How to Recover Dropped Table in Kusto. 


How to recover dropped table in Kusto Query Language | 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.


 //How to Recover Dropped Table by using Kusto  
   
 //.undo drop table can be used to get the table back. Important points to remember  
 //1)If a Purge command was executed on this database, the undo drop table command can't be executed to a version earlier   
 //to the purge execution.  
 //2)Extent can be recovered only if the hard delete period of the extent container it resides in wasn't reached yet.  
   
 //Let's create sampel table TotalSale and insert some 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  
 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 drop the the TotalSale data  
 .drop table TotalSale  
   
 TotalSale  
 // get the list the dropped tables  
 .show database techbrothersdb journal | where Event == "DROP-TABLE" and EntityName == "TotalSale" | project OriginalEntityVersion  
   
 // undo drop table  
 // syntax: .undo drop table TableName [as NewTableName] version=v DB_MajorVersion.DB_MinorVersion  
   
 .undo drop table TotalSale as TotalSale version ="v105.1"  
   
 // check if table is recovered and have data  
 TotalSale  

Video Demo: How to Recover Dropped Table in Kusto