Microsoft Azure Application Insights

SQL to Analytics language cheat sheet

Download this document at https://aka.ms/sql-analytics

SQL Query Analytics Query

SELECT * FROM dependencies dependencies
Select data from
table SELECT name, resultCode FROM dependencies dependencies | project name, resultCode
dependencies | project-away name

SELECT TOP 100 * FROM dependencies dependencies | take 100

SELECT * FROM dependencies dependencies | where isnotnull(resultCode)
Null evaluation WHERE resultCode IS NOT NULL
SELECT * FROM dependencies dependencies
Comparison WHERE timestamp > getdate()-1 | where timestamp > ago(1d)
operators
(date) SELECT * FROM dependencies dependencies
WHERE timestamp | where timestamp > datetime(2016-10-01)
BETWEEN '2016-10-01' AND '2016-11-01' and timestamp <= datetime(2016-11-01)
SELECT * FROM dependencies dependencies
Comparison WHERE type = "Azure blob" | where type == "Azure blob"
Operators
(string) --substring //substring
SELECT * FROM dependencies dependencies
WHERE type like "%blob%" | where type contains "blob"
--wildcard dependencies
SELECT * FROM dependencies | where type startswith "Azure"
WHERE type like "Azure%"
dependencies
| where type matches regex "^Azure.*"
Comparison SELECT * FROM dependencies dependencies
(boolean) WHERE !(success) | where success == "False"
SELECT DISTINCT name, type dependencies
Distinct FROM dependencies | summarize by name, type
SELECT name, AVG(duration) dependencies
Grouping, FROM dependencies | summarize avg(duration) by name
Aggregation GROUP BY name
SELECT operation_Name as Name, dependencies
Column aliases, AVG(duration) as AvgD | summarize AvgD=avg(duration) by operation_Name
Extending FROM dependencies | project Name=operation_Name, AvgD
GROUP BY name
SELECT name, timestamp dependencies
Ordering FROM dependencies | project name, timestamp
ORDER BY timestamp asc | order by timestamp asc nulls last
SELECT TOP 100 name, dependencies
Top n COUNT(*) as Count | summarize Count=count() by name
by measure FROM dependencies | top 100 by Count desc
GROUP BY name
ORDER BY Count desc
SELECT * FROM dependencies union dependencies, exceptions
Union UNION
SELECT * FROM exceptions
SELECT * FROM dependencies WHERE timestamp>.. dependencies | where timestamp > ago(1d)
UNION | union
SELECT * FROM exceptions WHERE timestamp>.. (exceptions | where timestamp > ago(1d) )
SELECT * FROM dependencies dependencies
Join LEFT OUTER JOIN exception | join kind=leftouter
ON dependencies.operation_Id = (exceptions)
exceptions.operation_Id on $left.operation_Id == $right.operation_Id

These are just subset of the operators available. Please refer to https://aka.ms/AIAnalyticsReference for a complete reference.

Try Analytics yourself by instrumenting with Azure Application Insights, or in the Analytics demo environment: https://aka.ms/AIAnalyticsDemo!

https://www.azure.com AIAnalyticsOutreach@microsoft.com
© 2016 Microsoft Corporation. All rights reserved. @Azure
 Application Insights Analytics – useful operators
Category Relevant Analytics functions
Selection and Column aliases project, project-away, extend
Temporary tables and constants let scalar_alias_name = …;
let table_alias_name = (){ … | … | … };
Comparison and String Operators startswith, !startswith
has*, !has
contains, !contains, containscs
hasprefix, !hasprefix, hassuffix, !hassuffix
in, !in
matches regex
==, =~, !=, !~

*has is more performant than contains

Common string functions strcat(), replace()
tolower()*, toupper()*
substring(), strlen()

*for a more performant solution than converting case when comparing strings use:
"aBc" =~ "abc"
Common math functions sqrt(), abs()
exp(), exp2(), exp10(), log(), log2(), log10()
pow()
gamma(), gammaln()
Parsing text extract(), extractjson(), parse*, split()

*parse is more performant

Limiting output take, limit, top, sample

hash
Date functions now(), ago()
datetime(), datepart(), timespan
startofday(), startofweek(), startofmonth(), startofyear()
endofday(), endofweek(), endofmonth(), endofyear()
dayofweek(), dayofmonth(), dayofyear()
getmonth(), getyear(),
weekofyear(), monthofyear()
Grouping and aggregation summarize by
max(), min(), count(), dcount(), avg(), sum(), stddev()
countif(), dcountif()
by argmax(), argmin()
top, count(), min(), max(), bin() percentiles(), percentile_array()

top, top-nested
Joins and Unions join kind=leftouter, inner, rightouter, fullouter, leftanti

union
Sort, order sort, order
Dynamic object (JSON and array) parsejson()
operators and functions makeset(), makelist()
split(), arraylength()
zip(), pack()
Logical operators iff(condition, value_t, value_f)

binary_and(), binary_or(), binary_not(), binary_xor()

Machine learning evaluate

autocluster, basket, diffpatterns, extractcolumns

More info about these and other functions and operators is available on our language reference: https://aka.ms/AIAnalyticsReference

https://www.azure.com AIAnalyticsOutreach@microsoft.com
© 2016 Microsoft Corporation. All rights reserved. @Azure