AADay2.kql

AADay2.kql

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

//5.2

.show database policies 

.show table target details | project RetentionPolicy 

.alter table target policy retention 
```
{
    "SoftDeletePeriod": "60.00:00:00",
    "Recoverability": "Enabled"
}
```

.show table target details | project RetentionPolicy 

//6.2

.show queries 
| where User == current_principal_details().UserPrincipalName
| where StartedOn >= ago(7d)
| count 

print upn=current_principal_details().UserPrincipalName;

.show running queries

//https://learn.microsoft.com/en-us/azure/data-explorer/kusto/management/queries

//6.3
.show journal | take 10 

.show journal 
| where Event == 'CREATE-MATERIALIZED-VIEW' and UpdatedEntityName == 'LastestTarget'
| summarize arg_max(EventTimestamp,*)
| project EventTimestamp

//6.4

.show commands 
| where User == current_principal_details().UserPrincipalName
| where StartedOn >= ago(7d)
| count 

// https://learn.microsoft.com/en-us/azure/data-explorer/kusto/management/commands

// 6.5

.show tables details

.show tables 
| extend Comp = format_bytes(TotalExtentSize, 0, "MB")
| extend Org = format_bytes(TotalOriginalSize, 0, "MB")
| project TableName, Comp, Org


// 7.1

Logistics
| take 10

Logistics
| summarize max(Shock) by deviceId
| sort by max_Shock
| take 10 
| summarize sum(max_Shock)

Logistics
| summarize max(Shock) by deviceId
| sort by max_Shock
| top 10 by max_Shock
| summarize sum(max_Shock)


Logistics
| summarize max(Shock) by deviceId
| sort by max_Shock
| top 10 by deviceId
| summarize sum(max_Shock)


let x=Logistics
    | summarize max(Shock) by deviceId
    | sort by max_Shock desc
    | project deviceId | take 10;
Logistics
| where deviceId in (x)
| summarize avg(Temp) by deviceId;


//7.2

Logistics
| summarize count() by TransportationMode, bin(enqueuedTime,10m)
| render timechart 


// 7.3

Logistics
| where enqueuedTime >=ago(90d)
| count 

Logistics
| summarize max(enqueuedTime), min(enqueuedTime)
//2022-07-28T10:49:14.051Z	2022-07-26T17:53:55.543Z

Logistics
| top 10 by Temp desc
| project Location_lon, Location_lat, deviceId, enqueuedTime, Temp
| where enqueuedTime > datetime('2022-07-26T17:53:55.543Z') //ago(90d)
| render scatterchart with (kind=map, series=deviceId, enqueuedTime, Temp)


//7.5

let min_t = (toscalar(Logistics | summarize min(enqueuedTime)));
let max_t = (toscalar(Logistics | summarize max(enqueuedTime)));
let step_interval = 10m;
Logistics
| make-series avg_shock_series=avg(Shock) on (enqueuedTime) from (min_t) to (max_t) step step_interval 
| extend anomalies_flags = series_decompose_anomalies(avg_shock_series, 1) 
| render anomalychart  with(anomalycolumns=anomalies_flags, title='avg shock anomalies') 


//8.1
//build a dashboard using outputs of any 5 queries on Logistics
//Add filter on timespan
//Add filter on transportation mode


//F to C
let min_t = (toscalar(Logistics | summarize min(enqueuedTime)));
Logistics
| where enqueuedTime > min_t
| project Temp, C=(Temp-32)*(5.0/9.0), deviceId,NumOfTagsCalculated
| take 5

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

Logistics
| summarize avg(Temp) by bin(enqueuedTime,1h)
| render timechart 

let x=Logistics
    | summarize max(Shock) by deviceId
    | sort by max_Shock desc
    | project deviceId | take 10;
Logistics
| where deviceId in (x)
| summarize avg(Temp) by deviceId, bin(enqueuedTime,10m)
| render timechart


Logistics
| top 1 by Shock desc 
| project Shock


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