AADay1.kql

AADay1.kql

https://github.com/Azure/ADX-in-a-Day-Lab2

// Create table command
////////////////////////////////////////////////////////////
.create table ['Logistics']  (['deviceId']:string, ['messageSource']:string, ['telemetry']:dynamic, ['schema']:string, ['enrichments']:dynamic, ['templateId']:string, ['applicationId']:guid, ['enqueuedTime']:datetime, ['messageProperties']:dynamic, ['NumOfTagsCalculated']:int, ['Shock']:real, ['Temp']:real, ['TransportationMode']:string, ['Status']:string, ['Location_alt']:real, ['Location_lon']:real, ['Location_lat']:real)

// Create mapping command
////////////////////////////////////////////////////////////
.create table ['Logistics'] ingestion json mapping 'Logistics_mapping' '[{"column":"deviceId", "Properties":{"Path":"$[\'deviceId\']"}},{"column":"messageSource", "Properties":{"Path":"$[\'messageSource\']"}},{"column":"telemetry", "Properties":{"Path":"$[\'telemetry\']"}},{"column":"schema", "Properties":{"Path":"$[\'schema\']"}},{"column":"enrichments", "Properties":{"Path":"$[\'enrichments\']"}},{"column":"templateId", "Properties":{"Path":"$[\'templateId\']"}},{"column":"applicationId", "Properties":{"Path":"$[\'applicationId\']"}},{"column":"enqueuedTime", "Properties":{"Path":"$[\'enqueuedTime\']"}},{"column":"messageProperties", "Properties":{"Path":"$[\'messageProperties\']"}},{"column":"NumOfTagsCalculated", "Properties":{"Path":"$[\'NumOfTagsCalculated\']"}},{"column":"Shock", "Properties":{"Path":"$[\'Shock\']"}},{"column":"Temp", "Properties":{"Path":"$[\'Temp\']"}},{"column":"TransportationMode", "Properties":{"Path":"$[\'TransportationMode\']"}},{"column":"Status", "Properties":{"Path":"$[\'Status\']"}},{"column":"Location_alt", "Properties":{"Path":"$[\'Location_alt\']"}},{"column":"Location_lon", "Properties":{"Path":"$[\'Location_lon\']"}},{"column":"Location_lat", "Properties":{"Path":"$[\'Location_lat\']"}}]'




Logistics
| count


Logistics
| getschema 

.show commands-and-queries | where StartedOn > ago(1h)


Logistics
| where messageSource == "telemetry"
| count 

Logistics
| where deviceId startswith "x"
| take 10

Logistics
| where enqueuedTime > ago(2m) // You might get 0 records if data is old. Use above query to check enqueuedTime in data
| take 10 


Logistics
| where enqueuedTime >= todatetime('2022-07-28')
| take 10 

Logistics
| summarize count() // or: count

Logistics
| where enqueuedTime > ago(180d) // You might get 0 records if data is old. Take any timespan based on enqueuedTime in data
| summarize count()

Logistics
| where deviceId startswith "x"
| summarize count()

Logistics
| where deviceId startswith "x"
| summarize count() by deviceId


Logistics
| where deviceId startswith "x"
| summarize count() by deviceId
| render piechart 

Logistics
// | where enqueuedTime > ago(10d) 
| extend h = toint(telemetry.Humidity)
| summarize avg(h) by bin(enqueuedTime, 1h)
| render timechart 

//3
Logistics
| project deviceId, enqueuedTime, Temp
| take 10

//4
Logistics
| project deviceId, enqueuedTime, Temp
| where enqueuedTime > ago(160d)
| count


Logistics
| project deviceId, enqueuedTime, Temp
| where enqueuedTime between (datetime('2022-07-28')..datetime('2022-07-29'))
| count

//5
Logistics
| sort by Temp
| summarize max(Temp) by deviceId
| take 5

Logistics
| summarize min(Temp) by deviceId
| sort by min_Temp
| take 5

//6
Logistics
| take 5
| project Temp, C=(Temp-32)*(5.0/9.0)

//7
Logistics
|count


//8
Logistics
| where deviceId startswith 'x'
| count

Logistics
| where deviceId startswith 'x'
| summarize count() by deviceId

Logistics
| where deviceId startswith 'x'
| summarize count() by deviceId
| render piechart 

//10
Logistics
// | where deviceId startswith 'x'
| summarize count() by bin(enqueuedTime,10m)
| render timechart 

//11
Logistics
// | where deviceId startswith 'x'
| summarize avg(Temp) by bin(enqueuedTime, 30m)
| render timechart 



// Creates a calculated column NumofTagsCalculated = TotalTags - LostTags
// Projects only 4 columns - deviceId, enqueuedTime, NumofTagsCalculated, Temp
// Hint 1: Even if NumofTagsCalculated field is present, try to extract the required columns TotalTags and LostTags from 'telemetry' column and recalculate NumofTagsCalculated for this exercise.

Logistics
| extend LostTags = toint(telemetry.LostTags), TotalTags = toint(telemetry.TotalTags)
| extend NumofTagsCalculated = TotalTags - LostTags
| project deviceId, enqueuedTime, NumofTagsCalculated, Temp
| take 10


.create function CalculateTags()  {
    Logistics
    | extend LostTags = toint(telemetry.LostTags), TotalTags = toint(telemetry.TotalTags)
    | extend NumofTagsCalculated = toint(TotalTags - LostTags)
    | project deviceId, enqueuedTime, NumofTagsCalculated, Temp
}


.create table target ( deviceId:string, enqueuedTime:datetime, NumOfTagsCalculated:int, Temp:real)

.alter table target policy update @'[{"IsEnabled": true, "Source": "Logistics", "Query": "CalculateTags()"}]'


.set-or-append Logistics <| Logistics | take 1000

target
| count

target
| getschema 


//the last record for each deviceId, based on the enqueuedTime column)

.create materialized-view LastestTarget on table target
{
    target | summarize arg_max(enqueuedTime, *) by deviceId
}

target | summarize arg_max(enqueuedTime, *) by deviceId | count

.show operations | where StartedOn > ago(15m) | 

LastestTarget | count //0 because no new data ingested


.drop materialized-view LastestTarget 


.create materialized-view with (backfill=true) LastestTarget on table target
{
    target | summarize arg_max(enqueuedTime, *) by deviceId
}


LastestTarget | count

materialized_view('LastestTarget') | count 

// https://learn.microsoft.com/en-us/azure/data-explorer/kusto/management/materialized-views/materialized-view-overview#examples

.show materialized-view LastestTarget