sql-cheatsheet.md

title	description	services	documentationcenter	author	manager	editor	ms.assetid	ms.service	ms.workload	ms.tgt_pltfrm	ms.devlang	ms.topic	ms.date	ms.author	ms.component
SQL to Azure Log Analytics query language cheat sheet | Microsoft Docs	Common functions to use for different scenarios in Log Analytics queries.	log-analytics		bwren	carmonm			log-analytics	na	na	na	conceptual	08/21/2018	bwren	na

SQL to Log Analytics query language cheat sheet

The table below helps users who are familiar with SQL to learn the Log Analytics query language. Have a look at the T-SQL command for solving a common scenarios and the equivalent using Log Analytics.

SQL to Log Analytics

Description	SQL Query	Azure Log Analytics Query
Select all data from a table	SELECT * FROM dependencies	dependencies
Select specific columns from a table	SELECT name, resultCode FROM dependencies	dependencies | project name, resultCode
Select 100 records from a table	SELECT TOP 100 * FROM dependencies	dependencies | take 100
Null evaluation	SELECT * FROM dependencies WHERE resultCode IS NOT NULL	dependencies | where isnotnull(resultCode)
String comparison: equality	SELECT * FROM dependencies WHERE name = "abcde"	dependencies | where name == "abcde"
String comparison: substring	SELECT * FROM dependencies WHERE like "%bcd%"	dependencies | where name contains "bcd"
String comparison: wildcard	SELECT * FROM dependencies WHERE name like "abc%"	dependencies | where name startswith "abc"
Date comparison: last 1 day	SELECT * FROM dependencies WHERE timestamp > getdate()-1	dependencies | where timestamp > ago(1d)
Date comparison: date range	SELECT * FROM dependencies WHERE timestamp BETWEEN '2016-10-01' AND '2016-11-01'	dependencies | where timestamp between (datetime(2016-10-01) .. datetime(2016-10-01))
Boolean comparison	SELECT * FROM dependencies WHERE !(success)	dependencies | where success == "False"
Sort	SELECT name, timestamp FROM dependencies ORDER BY timestamp asc	dependencies | order by timestamp asc
Distinct	SELECT DISTINCT name, type FROM dependencies	dependencies | summarize by name, type
Grouping, Aggregation	SELECT name, AVG(duration) FROM dependencies GROUP BY name	dependencies | summarize avg(duration) by name
Column aliases, Extend	SELECT operation_Name as Name, AVG(duration) as AvgD FROM dependencies GROUP BY name	dependencies | summarize AvgD=avg(duration) by operation_Name | project Name=operation_Name, AvgD
Top n recrods by measure	SELECT TOP 100 name, COUNT(*) as Count FROM dependencies GROUP BY name ORDER BY Count asc	dependencies | summarize Count=count() by name | top 100 by Count asc
Union	SELECT * FROM dependencies UNION SELECT * FROM exceptions	union dependencies, exceptions
Union: with conditions	SELECT * FROM dependencies WHERE value > 4 UNION SELECT * FROM exceptions value < 5	dependencies | where value > 4 | union (exceptions | where value < 5)
Join	SELECT * FROM dependencies JOIN exceptions ON dependencies.operation_Id = exceptions.operation_Id	dependencies | join (exceptions) on operation_Id == operation_Id

Next steps

• Go through a lesson on the writing queries in Log Analytics.