Wilson Mar bio photo

Wilson Mar

Hello!

Email me Calendar Skype call

LinkedIn Twitter Gitter Instagram Youtube

Github Stackoverflow Pinterest

Adds filters missing in GraphQL. Adds graphs missings in SQL. The one language to rule them all.

US (English)   Español (Spanish)   Français (French)   Deutsch (German)   Italiano   Português   Cyrillic Russian   中文 (简体) Chinese (Simplified)   日本語 Japanese   한국어 Korean

Overview

DOCS:

Kusto is named after pioneering Oceanographer Jacque Custou (pronounced “Kusto”). Like the language, he dove deep into a vast ocean for new insights.

Compared to SQL, KQL is less verbose but is proprietary to Microsoft.

The language is read-only, of up to 5 GB.

KQL is used in several Azure products, include the ADX managed SaaS service (GA 2019) for big data exploration in NRT (Near Real Time):

References:

Example:

This example of a KQL query draws data from the Heartbeat dataset. rendered as a timechart:

*

kql-logs-query-example-2237x1597

Hands-on:

VIDEO

  1. Open demo data from Microsoft:

    https://aka.ms/LADemo which resolves to:
    https://portal.loganalytics.io/demo

  2. Click the book icon at the right and select “Online course” for:

    4 hr VIDEO COURSE: Kusto Query Language (KQL) from scratch by Robert Cain, who also has an Advanced course.

  3. Click the book icon at the right and select “Language Reference” for:

    https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/

    Observe that KQL is part of Azure Data Explorer.

  4. Click “Query explorer” tab at the right.
  5. Expand “Saved Queries”
  6. Double-click on “Pluralsight” to expand the category.
  7. Click to open “m2-table-80-percent”.

    OBSERVE: Clicking completely replaces the existing KQL entry, without needing to clear it first.

    // precede all comments in code.

  8. Highlight a query, click blue “Run” or hold down Shift and press Enter.

    CAUTION: “No result” will be returned if there is no data.

    “Perf” is the table name to search in. This table has these fields visible:

    TenantId Computer ObjectName ObjectName CounterName InstanceName
  9. To see more columns, drag the horizonal bar or on the Touchpad move two fingers to the left:

    CounterValue CounterPath StandardDeviation Type
    _ResourceID TenantID SourceSystem MG
  10. PROTIP: The full list of columns for this and all other tables is:

    https://docs.microsoft.com/en-us/azure/azure-monitor/reference/tables/perf

  11. Set the Time Frame or set in the script text:

    | where TimeGenerated >= ago(1h)

    PROTIP: Several where statements can be stacked.

    Operators

  12. For operators, click on the KQL query text area and press command+Enter:

    • where - filter
    • count
    • extend - creates a calculated column in the result set (before project)
    • join
    • limit
    • lookup
    • order
    • project - select a subset of columns (instead of all columns from table)
    • project-away - remove column

  13. To scroll for more, mouse over the list and slide two-fingers on the Mac Touchpad:

    • sort
    • summarize
    • search

    • distinct - to not repeat values shown
    • make-series
    • mv-apply
    • mv-expand
    • take 5 // return 5 records
    • top 20 // rows
    • top-nested …

    The Pluralsight video covers search, where, take, count, summarize, extend, project, distinct.

    Examples to limit too much being returned (and wasting time):

    | search kind=case_sensitive "memory"
    | search in (Perf, Event, Alert) "Contoso"
  14. Use colon to search text wildcard:

    | search CounterName:"MBytes"
  15. Limit column:

    | search * starswith "Bytes"
    | search * endswith "Bytes"
    | search "Free*Bytes" // Any that Begins with free or ends with bytes
    
    | search InstanceName matches regex "[A-Z]:*"
    
    
    
  16. Click “m3-demo-scalar” explained by this VIDEO covering Scalar Operators:

    print, now() UTC, ago(-7d), sort by asc, extract, parse, datetime, Timespan Artithmetic, startof…, endof…, between, todynamic, format_datetime, format_timespan, datetime_aart, case, iif, isempty/isnull, split, String Operators, strcat

  17. Click “m4-demo-advanced-aggregations” explained by this VIDEO

    • summarize arg_max/arg_min( column ),
    • makelist - flaten a hierarchy to a JSON array, allowing dup. values
    • makeset - flaten a hierarchy to a JSON array, removing dup. values

    For a list of PCs with low disk space:

    Perf
    | where CounterName == "% Free Space"
    |   and CounterValue <= 30
    | summarize Computers = makeset(Computer)
    
    • mvexpand, percentiles, dcount (distinct count, accuracy), dcountif, countif, pivot, top-nested, max/min, sum/sumif, any

    Datasets

  18. Click “m5-demo-working-with-datasets” explained by this VIDEO

    • let, join (tables), union (combine) with source, kind=outer
    • datatable, prev/next, toscalar, row_cumsum, materialize

    Time Series

  19. Click “m6-demo-time-series” explained by this VIDEO

    Machine Learning

  20. Click “m7-data-machine-learning” explained by this VIDEO Machine Learning

    • evaulate basket(threshold) - for the most frequently appearing combination of attributes, given the threshold for minimum frequency (default 0.05 or 5%)
    • autocluster
    • evaulate diffpatterns(EventLevelName, ‘Error’, ‘Warning’) // splits dataset to identify differences as “Error” or “Warning”. Use iif to flag metrics before and after the incident.
    • reduce by Computer with threshold = 0.6 // to determine pattern, with default threshold of 0.1.

    Exporting data to CSV

  21. Click “m8-exporting-data” explained by this VIDEO

  22. To export to CSV file, run query and click the export icon.
  23. Select Export to CSV - all columns or display columns.
  24. In the pop-up at the bottom, click Save As.
  25. Specify the folder and file name.

    Run KSL in PowerBI Desktop

  26. Copy the Query to your Clipboard.
  27. Download and install PowerBI Desktop from https://powerbi.microsoft.com/desktop
  28. Open PowerBI
  29. In Home group, Get Data - Blank Query
  30. Advanced Editor
  31. Paste the query (command+V). Done runs the query.
  32. Close and apply changes.
  33. Create visualizations, etc.

More examples

Events
| where StartTime >= datetime(2018-11-01) and StartTime < datetime(2018-12-01)
| where State == "FLORIDA"  
| count
   

### KQL in Data Explorer

  1. VIDEO course Microsoft Azure Data Explorer - Advanced KQL by Robert Cain.

  2. Download and expand microsoft-azure-data-explorer-advanced-query-capabilities.zip to view folder module-05-performing-diagnostic-and-root-cause-analysis.

  3. ??? Load into Azure

    covers functions,

    inline Python & R code (converted to KQL string by highlighting then Ctrl+K & Ctrl+S).

    Analyze data using geospatial analysis,

    Root Cause Analysis Diagnostics

    clustering algorithms, time series analysis.

    Exploring data using Kusto

  4. Exploring Data in Microsoft Azure Using Kusto Query Language and Azure Data Explorer, download exercise file microsoft-azure-data-exploring.zip (to your Downloads folder) and unzip. In folder coursfiles, ??

JMESPath

The https://github.com/jmespath/jmespath.terminal Python package offers an interactive environment to work with queries. Data is piped as input, and then queries are written and run in the editor:

pip install jmespath-terminal
az vm list --output json | jpterm
   

From: https://docs.microsoft.com/en-us/cli/azure/query-azure-cli

KQL References

  • https://docs.microsoft.com/en-us/sharepoint/dev/general-development/keyword-query-language-kql-syntax-reference

CxE KQL hands-on lab exercises videos:

More on Azure

This is one of a series on Azure:

  1. DevOps_2.0
  2. ci-cd (Continuous Integration and Continuous Delivery)
  3. User Stories for DevOps
  4. Enterprise Software)

  5. Git and GitHub vs File Archival
  6. Git Commands and Statuses
  7. Git Commit, Tag, Push
  8. Git Utilities
  9. Data Security GitHub
  10. GitHub API
  11. TFS vs. GitHub

  12. Choices for DevOps Technologies
  13. Pulumi Infrastructure as Code (IaC)
  14. Java DevOps Workflow
  15. Okta for SSO & MFA

  16. AWS DevOps (CodeCommit, CodePipeline, CodeDeploy)
  17. AWS server deployment options
  18. AWS Load Balancers

  19. Cloud services comparisons (across vendors)
  20. Cloud regions (across vendors)
  21. AWS Virtual Private Cloud

  22. Azure Cloud Onramp (Subscriptions, Portal GUI, CLI)
  23. Azure Certifications
  24. Azure Cloud

  25. Azure Cloud Powershell
  26. Bash Windows using Microsoft’s WSL (Windows Subsystem for Linux)
  27. Azure KSQL (Kusto Query Language) for Azure Monitor, etc.

  28. Azure Networking
  29. Azure Storage
  30. Azure Compute
  31. Azure Monitoring

  32. Digital Ocean
  33. Cloud Foundry

  34. Packer automation to build Vagrant images
  35. Terraform multi-cloud provisioning automation
  36. Hashicorp Vault and Consul to generate and hold secrets

  37. Powershell Ecosystem
  38. Powershell on MacOS
  39. Powershell Desired System Configuration

  40. Jenkins Server Setup
  41. Jenkins Plug-ins
  42. Jenkins Freestyle jobs
  43. Jenkins2 Pipeline jobs using Groovy code in Jenkinsfile

  44. Docker (Glossary, Ecosystem, Certification)
  45. Make Makefile for Docker
  46. Docker Setup and run Bash shell script
  47. Bash coding
  48. Docker Setup
  49. Dockerize apps
  50. Docker Registry

  51. Maven on MacOSX

  52. Ansible

  53. MySQL Setup

  54. SonarQube & SonarSource static code scan

  55. API Management Microsoft
  56. API Management Amazon

  57. Scenarios for load
  58. Chaos Engineering