April2017_PowerBI.kql

April2017_PowerBI.kql

#connect cluster('demo12.westus.kusto.windows.net').database('sqlbi')

.show tables details
| where TableName startswith "BIAzure"
| summarize sum(TotalOriginalSize), sum(TotalExtentSize) by TableName
| extend CompressionRatio = sum_TotalOriginalSize/sum_TotalExtentSize  
// ~30TB of data in 3 different tables (traces and perormance counters)
// Data is compressed and indexed (x10.2 for traces, x21-34 for performance counters)
// Compressed data and index are persisted to blob and cached on SSD

BIAzureTraceMsitScus | count
// The trace table contains 40.9 billion records from 40 days (~1 billion records/day)

// For starters, let's get an idea of how many errors we have had in a single day
// Aggregate trace records by trace level over 24 hours 
// Using: Group By/ Aggregate over huge data size
BIAzureTraceMsitScus 
| where TraceTimeStamp > datetime(2015-03-31 14:00) and TraceTimeStamp < datetime(2015-04-01 14:00) 
| summarize count() by Level
// Total of 800 million records split into 5 levels

// Sample trace lines
BIAzureTraceMsitScus 
| where TraceTimeStamp > datetime(2015-03-31 14:00) and TraceTimeStamp < datetime(2015-04-01 14:00) 
| where Level == 2 
| take 10

// Error distribution for 1day, by role type
let startTime = datetime(2015-03-31 14:00);
BIAzureTraceMsitScus 
| where TraceTimeStamp >= startTime and TraceTimeStamp < startTime + 1d
| where Level == 2  
| parse Role with RoleType "_" *
| summarize count() by RoleType, bin(TraceTimeStamp, 10min)
| render timechart 

// Find the most 'frustrated' user
let startTime = datetime(2015-03-31 14:00);
let LookupInput =
  BIAzureTraceMsitScus 
  | where TraceTimeStamp >= startTime and TraceTimeStamp < startTime + 1d
  | where ClientActivityId != "00000000-0000-0000-0000-000000000000";
LookupInput  
| where EventText has "Event: NotifyAccessTokenCreated (token=<User=" 
| extend UserID= extract(@'User=(\w+)', 1, EventText) 
| project UserID, ClientActivityId  
| join hint.shufflekey=ClientActivityId kind=inner (
  LookupInput 
  | where Level == 2 
  | distinct ClientActivityId 
) on ClientActivityId  
| summarize Errors=count() by UserID
| top 10 by Errors desc

// That user's error distribution
let startTime = datetime(2015-03-31 14:00);
let LookupInput =
  BIAzureTraceMsitScus 
  | where TraceTimeStamp between(startTime .. 1d)
  | where ClientActivityId != "00000000-0000-0000-0000-000000000000";
LookupInput
| where EventText has "Event: NotifyAccessTokenCreated (token=<User=3309D7035B54096D748C83889EA8CE87CBA253736BECAFDF2D00AB99BF06317D" 
| project ClientActivityId 
| join kind=innerunique (
  LookupInput
  | where Level == 2
  | where EventText has "Exception object created"  
  | extend ExceptionType = extract("Exception object created: (.*?):", 1, EventText)
  | distinct ClientActivityId, ExceptionType
) on ClientActivityId 
| summarize count() by ExceptionType   
| render barchart with (legend = hidden)

// Analyzing the flow of a specific error
BIAzureTraceMsitScus 
| where ClientActivityId == '1cf69634-9bb4-42bf-bc01-b71813062d38'
| extend Trace_Level = iff(Level == 2, "Error" , "Info")
| project TraceTimeStamp, Trace_Level, EventText 




// 30K time series of performance counters data over a month
// 35b records overall
// Three machines with highest CPU and their behavior over a day
BIAzureAdditionalRawCounterFiveMinuteMsitScus 
| take 10 

let startTime = datetime(2015-03-31 14:00);
let LookupInput =
  BIAzureAdditionalRawCounterFiveMinuteMsitScus 
  | where TIMESTAMP >= startTime and TIMESTAMP < startTime + 1d
  | where CounterName == @"\Processor(_Total)\% Processor Time";
let Instances = toscalar(
LookupInput
| summarize max_CPU=max(CounterValue) by RoleInstance
| top 3 by max_CPU desc 
| summarize make_list(RoleInstance));
LookupInput
| where RoleInstance in (Instances)
| summarize avg_CPU=avg(CounterValue) by RoleInstance, bin(TIMESTAMP, 5m)
| project TIMESTAMP, RoleInstance, avg_CPU 
| render timechart 


//
// Detect jump shapes across all 98 instances
//
let startTime = datetime(2015-03-31 14:00);
let LookupInput =
  BIAzureAdditionalRawCounterFiveMinuteMsitScus 
  | where TIMESTAMP >= startTime and TIMESTAMP < startTime + 1d;
LookupInput
| where CounterName == @"\Processor(_Total)\% Processor Time"   
| make-series cval=avg(CounterValue) default=0 on TIMESTAMP in range(startTime, startTime+1d-15m, 15m) by RoleInstance 
| extend shapes=series_shapes(cval, false)
| extend jump=todouble(shapes.jump)
| where  jump > 0.3
| render timechart