Adx Tour.kql

Adx Tour.kql

let _startTime = datetime(2016-01-01T17:38:00Z);
let _endTime = datetime(2023-09-19T18:38:00Z);
// baseQuery results
let results = () {
demo_make_series1
| where TimeStamp between (['_startTime'] .. ['_endTime'])
};
results
| make-series Cardinality=count() on TimeStamp step 1h by OsVer
| extend (anomalies, score, baseline) = series_decompose_anomalies(Cardinality, 2.0)
| mv-expand anomalies to typeof(double), score to typeof(double), baseline to typeof(int), TimeStamp to typeof(datetime), Cardinality to typeof(long)
| where anomalies in (1,-1)
| summarize LatestOccurance=max(TimeStamp), Baseline=max(Cardinality) by Device=OsVer



demo_make_series1
| where TimeStamp between (datetime(2016-01-01T17:38:00Z) .. datetime(2023-09-19T18:38:00Z))
| make-series Cardinality=count() on TimeStamp step 1h by OsVer //, Country
// | where OsVer == 'Windows 10'
| extend (anomalies, score, baseline) = series_decompose_anomalies(Cardinality, 2.0)
// | render anomalychart 
| mv-expand anomalies to typeof(double), score to typeof(double), baseline to typeof(int), TimeStamp to typeof(datetime), Cardinality to typeof(long)
| where anomalies in (1,-1)
| summarize max(TimeStamp) by OsVer //, Country

let _startTime = datetime(2016-01-01T17:38:00Z);
let _endTime = datetime(2023-09-19T18:38:00Z);
// baseQuery results
let results = () {
demo_make_series1
| where TimeStamp between (['_startTime'] .. ['_endTime'])
| where OsVer == 'Windows 7'
};
let anum = results
| make-series Cardinality=count() on TimeStamp step 1h by Device=OsVer
| extend anomalies = series_decompose_anomalies(Cardinality,2.0)
| mv-expand anomalies to typeof(int), TimeStamp to typeof(datetime), Cardinality to typeof(int)
| where anomalies in (1,-1)
| project TimeStamp, anomalies, Note=tostring(Device), metric=Cardinality;
results
| make-series Cardinality=count() on TimeStamp step 1h by Device=OsVer
| mv-expand TimeStamp to typeof(datetime)
| lookup anum on TimeStamp
| where anomalies in (1,-1)
| summarize sum(metric) by bin(TimeStamp,1h), Device, Note
| render barchart