public async Task <IEnumerable <T> > ExecuteQuery <T>(string query)
{
var reader = await _queryClient.ExecuteQueryAsync(
_kustoSettings.DbName,
query,
new ClientRequestProperties()
{
ClientRequestId = Guid.NewGuid().ToString()
});
return(Read <T>(reader));
}
/// <summary>
/// Execute monitored ADX query.
/// </summary>
/// <param name="client">Query provider.</param>
/// <param name="query">ADX query string.</param>
/// <param name="clientRequestProperties">An object that represents properties that will be sent to Kusto.</param>
/// <param name="metrics">Prometheus query duration metric.</param>
/// <returns>Tuple of timeTaken and the reader result.</returns>
public static async Task <(TimeSpan timeTaken, IDataReader reader)> ExecuteMonitoredQueryAsync(
this ICslQueryProvider client,
string query,
ClientRequestProperties clientRequestProperties,
Metrics metrics)
{
Ensure.IsNotNull(client, nameof(client));
Ensure.IsNotNull(metrics, nameof(metrics));
Ensure.IsNotNullOrEmpty(query, nameof(query));
// Timer to be used to report the duration of a query to.
var stopwatch = new Stopwatch();
stopwatch.Start();
var reader = await client?.ExecuteQueryAsync(string.Empty, query, clientRequestProperties);
stopwatch.Stop();
var duration = stopwatch.Elapsed;
if (metrics != null)
{
metrics.AdxQueryDurationMetric.Observe(duration.TotalSeconds);
}
return(duration, reader);
}
public async Task Test_KustoTestEvent_StreamsToKusto()
{
KustoQueryClient.Should().NotBeNull();
var bb = new BigBrother("", "");
bb.UseKusto(KustoName, KustoLocation, KustoDatabase, KustoTenantId);
var evt = new KustoTestEvent();
bb.Publish(evt);
await Policy.Handle <Exception>()
.WaitAndRetryAsync(new[]
{
TimeSpan.FromSeconds(3),
TimeSpan.FromSeconds(10),
TimeSpan.FromSeconds(30)
})
.ExecuteAsync(async() =>
{
var reader = await KustoQueryClient.ExecuteQueryAsync(
KustoDatabase,
$"{nameof(KustoTestEvent)} | where {nameof(KustoTestEvent.Id)} == \"{evt.Id}\" | summarize count()",
ClientRequestProperties.FromJsonString("{}"));
reader.Read().Should().BeTrue();
reader.GetInt64(0).Should().Be(1);
});
}
public async Task <DateTimeOffset?> GetLatestTimelineBuild(string project)
{
try
{
using IDataReader result = await _query.ExecuteQueryAsync(
_database,
// This isn't use controlled, so I'm not worried about the Kusto injection
$"TimelineBuilds | where Project == '{project}' | summarize max(FinishTime)",
new ClientRequestProperties ()
);
if (!result.Read())
{
return(null);
}
else
{
return(result.GetDateTime(0));
}
}
catch (SemanticException e) when(e.SemanticErrors == "'where' operator: Failed to resolve column or scalar expression named 'Project'")
{
// The Project column isn't there, we probably reinitalized the tables
return(null);
}
}
public static async Task <ObjectReader <T> > QuerySingleResultSetAsync <T>(this ICslQueryProvider cslQueryProvider, string query, string database, ClientRequestProperties?requestProperties = null)
{
Contract.RequiresNotNullOrEmpty(query);
Contract.RequiresNotNullOrEmpty(database);
requestProperties ??= new ClientRequestProperties();
var dataReader = await cslQueryProvider.ExecuteQueryAsync(database, query, requestProperties);
return(new ObjectReader <T>(dataReader, disposeReader: true, nameBasedColumnMapping: true));
}
public async Task <IEnumerable <T> > ExecuteQuery <T>(string query, TimeSpan timeout = default, CancellationToken cancellationToken = default)
{
logger.LogInformation($"kusto query:\n{query}");
var stopWatch = Stopwatch.StartNew();
var reader = await queryClient.ExecuteQueryAsync(
kustoSettings.DbName,
query,
GetClientRequestProps(timeout));
var records = Read <T>(reader, cancellationToken);
stopWatch.Stop();
logger.LogInformation($"it took {stopWatch.Elapsed} to query {records.Count()} records from kusto");
return(records);
}
public async Task <IDataReader> ExecuteQueryAsync(String dbName, string query, TelemetryClient telemetry)
{
if (_isUsable)
{
ClientRequestProperties clientRequestProperties = CreateRequestProperties();
try
{
return(await Task.Run(() => adx.ExecuteQueryAsync(dbName, query, clientRequestProperties)));
}
catch (Exception ex)
{
telemetry.TrackException(ex);
}
_isUsable = false;
}
return(null);
}
public static async Task <DatabaseResult> RunQuery(string connection, string database, string query, ICslQueryProvider queryProvider)
{
try
{
var clientRequestProperties = new ClientRequestProperties()
{
ClientRequestId = Guid.NewGuid().ToString()
};
var reader = await queryProvider.ExecuteQueryAsync(database, query, clientRequestProperties);
DatabaseResult dr = new DatabaseResult();
dr.ConnectionString = connection;
dr.Database = database;
dr.Query = query;
dr.Headers = new string[reader.FieldCount];
for (int i = 0; i < reader.FieldCount; i++)
{
dr.Headers[i] = reader.GetName(i);
}
dr.Results = new System.Collections.ArrayList();
while (reader.Read() && reader.FieldCount > 0)
{
object[] result = new object[reader.FieldCount];
for (int i = 0; i < reader.FieldCount; i++)
{
result[i] = reader.GetValue(i);
}
dr.Results.Add(result);
}
return(dr);
}
catch (Exception e)
{
throw e;
}
}
public async Task <IEnumerable <TestTable> > GetLatestNDaysData(int days)
{
if (days <= 0)
{
throw new ArgumentOutOfRangeException("days should be positive value");
}
DateTime today = DateTime.UtcNow;
DateTime startPeriodDateTime = today.AddDays(-days);
string query = $"{_table} | where TimeStamp between (datetime({startPeriodDateTime.ToString("s")}) .. datetime({today.ToString("s")}))";
try
{
var result = new List <TestTable>();
using (var reader = await _kustoClient.ExecuteQueryAsync(_database, query, null) as DataTableReader2)
{
// Can be better performance with IAsyncEnumerable or other way
while (reader.Read())
{
result.Add(new TestTable()
{
// Need to investigate better way to convert table data to appropriate model with low operation/computing cost
TimeStamp = DateTime.Parse(reader[0].ToString()),
Name = reader[1].ToString(),
Metric = int.Parse(reader[2].ToString()),
Source = reader[3].ToString()
});
}
}
return(result);
}
catch (Exception ex)
{
// Will be able to add specific catch statement throught debugging
throw;
}
}
private static ReadResponse CreateResponse(ReadRequest readrequest, ILogger log)
{
ReadResponse result = new ReadResponse();
List <Task <IDataReader> > tasklist = new List <Task <IDataReader> >();
foreach (var aQuery in readrequest.Queries)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine($"SearchTimeseries({aQuery.StartTimestampMs}, {aQuery.EndTimestampMs})");
sb.AppendLine("| where " + String.Join(" and ", aQuery.Matchers.Select(aMatch => $"({GenerateValueExpression(aMatch.Name, aMatch.Type, aMatch.Value)})")));
sb.AppendLine("| project timeseries");
log.LogInformation($"KQL: {sb.ToString()}");
tasklist.Add(adx.ExecuteQueryAsync(databaseName: "sensordata", query: sb.ToString(), null));
}
Task.WaitAll(tasklist.ToArray());
result.Results.AddRange(tasklist.Select(aTask => CreateQueryResult(aTask.Result)));
return(result);
}
private async Task RunProject(
AzureDevOpsClient azureServer,
string project,
int buildBatchSize,
AzureDevOpsTimelineOptions options,
CancellationToken cancellationToken)
{
DateTimeOffset latest;
try
{
using (ICslQueryProvider query =
KustoClientFactory.CreateCslQueryProvider(options.KustoQueryConnectionString))
using (IDataReader result = await query.ExecuteQueryAsync(
options.KustoDatabase,
// This isn't use controlled, so I'm not worried about the Kusto injection
$"TimelineBuilds | where Project == '{project}' | summarize max(FinishTime)",
new ClientRequestProperties()
))
{
if (!result.Read())
{
latest = DateTimeOffset.UtcNow.Subtract(TimeSpan.FromDays(30));
_logger.LogWarning($"No previous time found, using {latest.LocalDateTime:O}");
}
else
{
latest = result.GetDateTime(0);
_logger.LogInformation($"... fetched previous time of {latest.LocalDateTime:O}");
}
}
}
catch (SemanticException e) when(e.SemanticErrors == "'where' operator: Failed to resolve column or scalar expression named 'Project'")
{
// The Project column isn't there, we probably reinitalized the tables
latest = DateTimeOffset.UtcNow.Subtract(TimeSpan.FromDays(30));
_logger.LogWarning($"No table column 'Project' found, assumed reinialization: using {latest.LocalDateTime:O}");
}
_logger.LogInformation("Reading project {project}", project);
Build[] builds = await GetBuildsAsync(azureServer, project, latest, buildBatchSize, cancellationToken);
_logger.LogTrace("... found {builds} builds...", builds.Length);
if (builds.Length == 0)
{
_logger.LogTrace("No work to do");
return;
}
List <(int buildId, BuildRequestValidationResult validationResult)> validationResults = builds
.SelectMany(
build => build.ValidationResults,
(build, validationResult) => (build.Id, validationResult))
.ToList();
_logger.LogTrace("Fetching timeline...");
Dictionary <Build, Task <Timeline> > tasks = builds
.ToDictionary(
build => build,
build => azureServer.GetTimelineAsync(project, build.Id, cancellationToken)
);
await Task.WhenAll(tasks.Select(s => s.Value));
_logger.LogTrace("... finished timeline");
var records = new List <AugmentedTimelineRecord>();
var issues = new List <AugmentedTimelineIssue>();
_logger.LogTrace("Aggregating results...");
foreach ((Build build, Task <Timeline> timelineTask) in tasks)
{
Timeline timeline = await timelineTask;
if (timeline?.Records == null)
{
continue;
}
var recordCache =
new Dictionary <string, AugmentedTimelineRecord>();
var issueCache = new List <AugmentedTimelineIssue>();
foreach (TimelineRecord record in timeline.Records)
{
var augRecord = new AugmentedTimelineRecord(build.Id, record);
recordCache.Add(record.Id, augRecord);
records.Add(augRecord);
if (record.Issues == null)
{
continue;
}
for (int iIssue = 0; iIssue < record.Issues.Length; iIssue++)
{
var augIssue =
new AugmentedTimelineIssue(build.Id, record.Id, iIssue, record.Issues[iIssue]);
augIssue.Bucket = GetBucket(augIssue);
issueCache.Add(augIssue);
issues.Add(augIssue);
}
}
foreach (AugmentedTimelineRecord record in recordCache.Values)
{
FillAugmentedOrder(record, recordCache);
}
foreach (AugmentedTimelineIssue issue in issueCache)
{
if (recordCache.TryGetValue(issue.RecordId, out AugmentedTimelineRecord record))
{
issue.AugmentedIndex = record.AugmentedOrder + "." + issue.Index.ToString("D3");
}
else
{
issue.AugmentedIndex = "999." + issue.Index.ToString("D3");
}
}
}
if (string.IsNullOrEmpty(options.KustoIngestConnectionString))
{
_logger.LogError("No KustoIngestConnectionString set");
return;
}
IKustoIngestClient ingest =
KustoIngestFactory.CreateQueuedIngestClient(options.KustoIngestConnectionString);
_logger.LogInformation("Saving TimelineBuilds...");
await KustoHelpers.WriteDataToKustoInMemoryAsync(
ingest,
options.KustoDatabase,
"TimelineBuilds",
_logger,
builds,
b => new[]
{
new KustoValue("BuildId", b.Id.ToString(), KustoDataTypes.Int),
new KustoValue("Status", b.Status, KustoDataTypes.String),
new KustoValue("Result", b.Result, KustoDataTypes.String),
new KustoValue("Repository", b.Repository?.Name ?? b.Repository?.Id, KustoDataTypes.String),
new KustoValue("Reason", b.Reason, KustoDataTypes.String),
new KustoValue("BuildNumber", b.BuildNumber, KustoDataTypes.String),
new KustoValue("QueueTime", b.QueueTime, KustoDataTypes.DateTime),
new KustoValue("StartTime", b.StartTime, KustoDataTypes.DateTime),
new KustoValue("FinishTime", b.FinishTime, KustoDataTypes.DateTime),
new KustoValue("Project", b.Project?.Name, KustoDataTypes.String),
new KustoValue("DefinitionId", b.Definition?.Id.ToString(), KustoDataTypes.String),
new KustoValue("Definition", $"{b.Definition?.Path}\\{b.Definition?.Name}", KustoDataTypes.String),
});
_logger.LogInformation("Saving TimelineValidationMessages...");
await KustoHelpers.WriteDataToKustoInMemoryAsync(
ingest,
options.KustoDatabase,
"TimelineIssues",
_logger,
validationResults,
b => new[]
{
new KustoValue("BuildId", b.buildId.ToString(), KustoDataTypes.Int),
new KustoValue("RecordId", null, KustoDataTypes.String),
new KustoValue("Index", null, KustoDataTypes.Int),
new KustoValue("Path", null, KustoDataTypes.String),
new KustoValue("Type", b.validationResult.Result, KustoDataTypes.String),
new KustoValue("Category", "ValidationResult", KustoDataTypes.String),
new KustoValue("Message", b.validationResult.Message, KustoDataTypes.String),
new KustoValue("Bucket", "ValidationResult", KustoDataTypes.String),
});
_logger.LogInformation("Saving TimelineRecords...");
await KustoHelpers.WriteDataToKustoInMemoryAsync(
ingest,
options.KustoDatabase,
"TimelineRecords",
_logger,
records,
b => new[]
{
new KustoValue("BuildId", b.BuildId.ToString(), KustoDataTypes.Int),
new KustoValue("RecordId", b.Raw.Id, KustoDataTypes.String),
new KustoValue("Order", b.Raw.Order.ToString(), KustoDataTypes.Int),
new KustoValue("Path", b.AugmentedOrder, KustoDataTypes.String),
new KustoValue("ParentId", b.Raw.ParentId, KustoDataTypes.String),
new KustoValue("Name", b.Raw.Name, KustoDataTypes.String),
new KustoValue("StartTime", b.Raw.StartTime, KustoDataTypes.DateTime),
new KustoValue("FinishTime", b.Raw.FinishTime, KustoDataTypes.DateTime),
new KustoValue("Result", b.Raw.Result, KustoDataTypes.String),
new KustoValue("ResultCode", b.Raw.ResultCode, KustoDataTypes.String),
new KustoValue("ChangeId", b.Raw.ChangeId.ToString(), KustoDataTypes.Int),
new KustoValue("LastModified", b.Raw.LastModified, KustoDataTypes.DateTime),
new KustoValue("WorkerName", b.Raw.WorkerName, KustoDataTypes.String),
new KustoValue("Details", b.Raw.Details?.Url, KustoDataTypes.String),
new KustoValue("ErrorCount", b.Raw.ErrorCount.ToString(), KustoDataTypes.Int),
new KustoValue("WarningCount", b.Raw.WarningCount.ToString(), KustoDataTypes.Int),
new KustoValue("Url", b.Raw.Url, KustoDataTypes.String),
new KustoValue("LogId", b.Raw.Log?.Id.ToString(), KustoDataTypes.Int),
new KustoValue("LogUri", b.Raw.Log?.Url, KustoDataTypes.String),
new KustoValue("TaskId", b.Raw.Task?.Id, KustoDataTypes.Int),
new KustoValue("TaskName", b.Raw.Task?.Name, KustoDataTypes.String),
new KustoValue("TaskVersion", b.Raw.Task?.Version, KustoDataTypes.String),
new KustoValue("Attempt", b.Raw.Attempt.ToString(), KustoDataTypes.Int),
});
_logger.LogInformation("Saving TimelineIssues...");
await KustoHelpers.WriteDataToKustoInMemoryAsync(
ingest,
options.KustoDatabase,
"TimelineIssues",
_logger,
issues,
b => new[]
{
new KustoValue("BuildId", b.BuildId.ToString(), KustoDataTypes.Int),
new KustoValue("RecordId", b.RecordId, KustoDataTypes.String),
new KustoValue("Index", b.Index.ToString(), KustoDataTypes.Int),
new KustoValue("Path", b.AugmentedIndex, KustoDataTypes.String),
new KustoValue("Type", b.Raw.Type, KustoDataTypes.String),
new KustoValue("Category", b.Raw.Category, KustoDataTypes.String),
new KustoValue("Message", b.Raw.Message, KustoDataTypes.String),
new KustoValue("Bucket", b.Bucket, KustoDataTypes.String),
});
}
private static void AddLineageInformation(ICslQueryProvider adx, Lineage lineage)
{
var databases = adx.ExecuteQuery <String>(".show databases | project DatabaseName");
List <Task <IDataReader> > allTablesQueries = new List <Task <IDataReader> >();
List <Task <IDataReader> > externalTableQueries = new List <Task <IDataReader> >();
List <Task <IDataReader> > continousExportQueries = new List <Task <IDataReader> >();
//Dictionary<string, Task<IDataReader>> allTables = new Dictionary<string, Task<IDataReader>>();
#region get Tables
foreach (var aDatabase in databases)
{
#region internal tables
var tableTask = adx.ExecuteQueryAsync(aDatabase, ".show tables | project TableName, Database=current_database()", CreateRequestProperties());
allTablesQueries.Add(tableTask);
//allTables.Add(aDatabase, tableTask);
#endregion
#region external tables
externalTableQueries.Add(adx.ExecuteQueryAsync(aDatabase,
@".show external tables
| project TableName, Database=current_database()",
CreateRequestProperties()));
continousExportQueries.Add(adx.ExecuteQueryAsync(aDatabase,
@".show continuous-exports
| project Name, ExternalTableName, Query, CursorScopedTables=todynamic(CursorScopedTables), Database=current_database()
| mv-expand CursorScopedTables to typeof(string)",
CreateRequestProperties()));
#endregion
}
Task.WaitAll(allTablesQueries.ToArray());
#endregion
List <Task <IDataReader> > updatePolicyQueries = new List <Task <IDataReader> >();
List <Task <IDataReader> > tableDetailsQueries = new List <Task <IDataReader> >();
#region get update policies
foreach (var aTableTask in allTablesQueries)
{
IDataReader tableResult = aTableTask.Result;
while (tableResult.Read())
{
var tableName = tableResult.GetString(0);
var databaseName = tableResult.GetString(1);
lineage.AddTable(databaseName, tableName);
//get update policy
updatePolicyQueries.Add(adx.ExecuteQueryAsync(databaseName,
@".show table " + tableName + @" policy update
| mv-expand Policy=todynamic(Policy)
| project EntityName, Policy",
CreateRequestProperties()));
//get table details
tableDetailsQueries.Add(adx.ExecuteQueryAsync(databaseName,
@".show table " + tableName + @" details
| project-away *Policy, AuthorizedPrincipals",
CreateRequestProperties()));
}
}
Task.WaitAll(updatePolicyQueries.ToArray());
foreach (var aTask in updatePolicyQueries)
{
IDataReader updatePolicyResult = aTask.Result;
while (updatePolicyResult.Read())
{
var policy = JsonConvert.DeserializeObject <UpdatePolicy>(updatePolicyResult["Policy"].ToString());
var Entity = updatePolicyResult.GetString(0);
var entitySplit = Entity.Split("].[");
var database = entitySplit[0].Replace("[", "");
var table = entitySplit[1].Replace("]", "");
lineage.AddUpdatePolicy(database, table, policy);
}
}
#endregion
#region table details
Task.WaitAll(tableDetailsQueries.ToArray());
foreach (var aTask in tableDetailsQueries)
{
IDataReader tableDetailResult = aTask.Result;
while (tableDetailResult.Read())
{
var database = tableDetailResult.GetString(1);
var table = tableDetailResult.GetString(0);
var rowCount = tableDetailResult.GetInt64(7);
lineage.Databases[database].Tables[table].RowCount = rowCount;
}
}
#endregion
#region external tables
Task.WaitAll(externalTableQueries.ToArray());
foreach (var aTask in externalTableQueries)
{
IDataReader externalTableResult = aTask.Result;
while (externalTableResult.Read())
{
var tableName = externalTableResult.GetString(0);
var databaseName = externalTableResult.GetString(1);
lineage.AddExternalTable(databaseName, tableName);
}
}
#endregion
#region continous export
Task.WaitAll(continousExportQueries.ToArray());
foreach (var aTask in continousExportQueries)
{
IDataReader continousExportResult = aTask.Result;
while (continousExportResult.Read())
{
var continousExportName = continousExportResult.GetString(0);
var externalTableName = continousExportResult.GetString(1);
var query = continousExportResult.GetString(2);
var curserScopedQuery = continousExportResult.GetString(3);
var databaseName = continousExportResult.GetString(4);
var ce = new ContinousExport(continousExportName, externalTableName, query, curserScopedQuery);
lineage.AddContinousExport(databaseName, ce);
}
}
#endregion
}
} // - InitializeKustoClient
private static ReadResponse CreateResponse(ReadRequest readrequest, ILogger log)
{
var result = new ReadResponse();
var taskList = new List<Task<IDataReader>>();
const string metricQueryTemplate = @"
Metrics
| where (EndDatetime >= unixtime_milliseconds_todatetime({0})) and (StartDatetime <= unixtime_milliseconds_todatetime({1})) and ( {2} )
| summarize Labels=tostring(any(Labels)), Samples=make_list( Samples ) by LabelsHash
| mv-apply Samples = Samples on
(
order by tolong(Samples['Timestamp']) asc
| summarize Samples=make_list(pack('Timestamp', Samples['Timestamp'], 'Value', Samples['Value']))
)
";
var timer = new Stopwatch();
timer.Start();
foreach (var aQuery in readrequest.Queries)
{
var kql = string.Format(
metricQueryTemplate,
aQuery.StartTimestampMs,
aQuery.EndTimestampMs,
string.Join(
" and ",
aQuery.Matchers.Select(
item => KustoManipulations.ToKustoExpression(item.Name, item.Type, item.Value)
)
)
);
log.LogInformation($"KQL: {kql}");
taskList.Add(
_adx.ExecuteQueryAsync(
Environment.GetEnvironmentVariable("kustoDatabase"),
kql,
null
)
);
} // - foreach readrequest.Queries
log.LogInformation("[PrometheusRead] [ExecuteQueries] Queries count: " + taskList.Count());
Task.WaitAll(taskList.ToArray());
timer.Stop();
log.LogInformation("[PrometheusRead] [ExecuteQueries] Execution time: " + timer.Elapsed);
log.LogInformation("[PrometheusRead] [CreateQueryResult] Start serializing results");
timer.Reset();
result.Results.AddRange(taskList.Select(aTask => CreateQueryResult(aTask.Result, log)));
timer.Stop();
log.LogInformation(
"[PrometheusRead] [CreateQueryResult] Serializing done. Execution time: " + timer.Elapsed);
return result;
} // - ReadResponse
