Skip to content

Instantly share code, notes, and snippets.

View Curts0's full-sized avatar

Curtis Stallings Curts0

11 followers · 6 following
View GitHub Profile
@Curts0
Curts0 / kusto_results_load_time.csv
Last active April 13, 2022 22:33
Example Data: Avg & Sum Load Time with Total Number of Queries by Report & Visual Ids
We can make this file beautiful and searchable if this error is corrected: It looks like row 8 should actually have 6 columns, instead of 4. in line 7.
Avg_Load_Time_Secs,Sum_Load_Time_Secs,Number_Of_Queries,Report_Id,Visual_Id,Report_Url
1.25,9751,2174,00000000-0000-0000-0000-000000000001,odearuaxawjodukotyle,https://app.powerbi.com/groups/me/reports/00000000-0000-0000-0000-000000000001
2.39,9784,5416,00000000-0000-0000-0000-000000000002,ejlgzymegcvjuflpkikx,https://app.powerbi.com/groups/me/reports/00000000-0000-0000-0000-000000000002
0.64,8069,2369,00000000-0000-0000-0000-000000000003,ytkoajtdresrwfxspmuk,https://app.powerbi.com/groups/me/reports/00000000-0000-0000-0000-000000000003
0.1,8065,4086,00000000-0000-0000-0000-000000000004,qsopqwqoykwxemqhbxqw,https://app.powerbi.com/groups/me/reports/00000000-0000-0000-0000-000000000004
0.26,8363,9932,00000000-0000-0000-0000-000000000005,vboqdszyvjfnvkhdpygs,https://app.powerbi.com/groups/me/reports/00000000-0000-0000-0000-000000000005
4.04,7577,8832,00000000-0000-0000-0000-000000000006,cgxambpvacxgsngycued,https://app.powerbi.com/groups/me/reports/00000000-0000-0000-0000-000000000006
1.43,7437,3284,00000000-00
@Curts0
Curts0 / kusto_column_queried_count.csv
Last active April 13, 2022 22:55
Example Data: 'Table'[Column] Reference by Total Number of Times Queried
Table_Column Total_Number_Queried
Table1'[Column1] 36861
Table1'[Column2] 48516
Table1'[Column3] 36861
Table2'[Column2] 43768
Table2'[Column5] 43566
Table3'[Column2] 52209
Table3'[Column1] 36840
Table3'[Column6] 80697
Table4'[Column2] 36865
@Curts0
Curts0 / partition_time_rows.kql
Created April 14, 2022 19:43
Gets Duration (Seconds) and Total Rows Refreshed in Partition by Partition and Table
let process_table =
AzureDiagnostics
| where OperationName == "ProgressReportEnd"
| where EventSubclass_s == 1
| where Success_s == 1
| extend Object_Json = parse_xml(ObjectReference_s)
| extend Database = tostring(Object_Json.Object.Database)
| extend Model = tostring(Object_Json.Object.Model)
| extend Table = tostring(Object_Json.Object.Table)
| extend Partition = tostring(Object_Json.Object.Partition)
@Curts0
Curts0 / column_usage.kql
Created April 14, 2022 19:48
Total Column Usage Count
AzureDiagnostics
| where OperationName == "QueryEnd"
| where EventSubclass_s == 3
| where Resource == "ENTERAZURERESOURCE" //Enter Azure Resource
| where ApplicationName_s == "PowerBI"
| where TimeGenerated > ago(30d) //Remove if you would prefer to use the time selection in Azure
| extend extract_column = extract_all(@"('.*?'\[.*?\])",TextData_s)
| mv-expand extract_column
| summarize Total_Number_Queried=dcount(SPID_s) by Table_Column= tostring(extract_column)
@Curts0
Curts0 / avg_sum_load_time.kql
Created April 14, 2022 19:50
Avg & Total Load Time by Visual & Report
AzureDiagnostics
| where OperationName == "QueryEnd"
| where EventSubclass_s == 3
| where Resource == "ENTERAZURERESOURCE" //Enter Azure Resource
| where ApplicationName_s == "PowerBI"
| where TimeGenerated > ago(30d) //Remove if you would prefer to use the time selection in Azure
| summarize
avg_load_time_sec = avg(toint(Duration_s)) / 1000,
sum_load_time_sec = sum(toint(Duration_s)) / 1000,
count_queries = count()
@Curts0
Curts0 / partition_processing_example.csv
Created April 14, 2022 19:57
Total Time and Total Rows Processed by Table and Partition
We can make this file beautiful and searchable if this error is corrected: It looks like row 7 should actually have 11 columns, instead of 4. in line 6.
Resource_Group,Resource,Database,Model,Table,Partition,User,Duration_Seconds,Number_Of_Rows,Start_Time [Local Time],End_Time [Local Time]
Resource-Group-1,Resource1,Database1,Model,Orders,Current Fiscal Year,serviceuser@company.com,7605.87,30195643,"4/14/2022, 10:56:57.137 AM","4/14/2022, 10:57:10.871 AM"
Resource-Group-1,Resource1,Database1,Model,Orders,Current Fiscal Year,serviceuser@company.com,7334.06,72588126,"4/14/2022, 10:57:14.828 AM","4/14/2022, 10:57:14.938 AM"
Resource-Group-1,Resource1,Database2,Model,Orders,Previous Fiscal Year,serviceuser@company.com,2260.44,16791661,"4/14/2022, 10:57:22.172 AM","4/14/2022, 10:57:24.000 AM"
Resource-Group-1,Resource1,Database2,Model,Orders,Previous Fiscal Year,serviceuser@company.com,871.33,21860928,"4/14/2022, 11:04:17.646 AM","4/14/2022, 11:07:05.377 AM"
Resource-Group-2,Resource2,Database3,Model,Product,Partition,userwhoshouldnthaveaccess@company.com,74.5,41816142,"4/14/2022, 10:52:34.949 AM","4/14/2022, 10:53:47.419 AM"
Resource-Group-2,Resource2,Database3,M
@Curts0
Curts0 / processing_errors.kql
Created April 14, 2022 20:13
List Processing Errors with Error Details
let error_table =
AzureDiagnostics
| where ResourceProvider == 'MICROSOFT.ANALYSISSERVICES'
| where OperationName == 'Error'
| where isempty(User_s) == false;
let process_table =
AzureDiagnostics
| where ResourceProvider == 'MICROSOFT.ANALYSISSERVICES'
| where Error_s == -1055784777
| where OperationName == 'CommandEnd'
@Curts0
Curts0 / processing_errors.csv
Created April 14, 2022 20:22
Lists Processing Errors
End_Time [Local Time] Database Details Error Server Resource User Application
4/14/2022, 10:57:32.583 AM Database1 Processing Info Like DB, Table, Partition, Max Parallelism, Type Error Text Server Resource serviceuser@company.com PowerBi
4/14/2022, 10:45:22.583 AM Database1 Processing Info Like DB, Table, Partition, Max Parallelism, Type Error Text Server Resource serviceuser@company.com TabularEditor
4/14/2022, 10:59:32.583 AM Database1 Processing Info Like DB, Table, Partition, Max Parallelism, Type Error Text Server Resource serviceuser@company.com PowerBi
@Curts0
Curts0 / re_encoding.kql
Created April 16, 2022 19:37
Re Encoding Logs
AzureDiagnostics
| where OperationName == "ProgressReportEnd"
| where EventSubclass_s == 55
| project-keep Resource, DatabaseName_s, ObjectReference_s, TextData_s, Duration_s, StartTime_t, EndTime_t
| extend Object_Json = parse_xml(ObjectReference_s)
| extend Database = tostring(Object_Json.Object.Database)
| extend Model = tostring(Object_Json.Object.Model)
| extend Table = tostring(Object_Json.Object.Table)
| extend Partition = tostring(Object_Json.Object.Partition)
| extend Duration_Seconds = toint(Duration_s)/1000.00
@Curts0
Curts0 / re_encoding_examples.csv
Created April 16, 2022 19:43
Re-Encoding Examples
Start_Time [Local Time] End_Time [Local Time] Table Column Duration_Seconds Text Database Model Partition Resource
Start Time End Time Sales QUANTITY 1.828 Encoding Details Database Model CFY Resource1
Start Time End Time Sales TOTAL_AMT 0.063 Encoding Details Database Model CFY Resource1
Start Time End Time Product PRODUCT_SKU 0.203 Encoding Details Database Model Partition Resource1
Start Time End Time Product PRODUCT_SKU 0.203 Encoding Details Database Model Partition Resource1