public async Task <int> ExecuteCountQueryAsync(
string databaseName,
string query,
Dictionary <string, string> queryParameter)
{
int count = 0;
try
{
var clientRequestProperties = new ClientRequestProperties();
if (queryParameter != null)
{
clientRequestProperties = new ClientRequestProperties(
options: null,
parameters: queryParameter);
}
clientRequestProperties.ClientRequestId = Guid.NewGuid().ToString();
using var reader = await this.client.ExecuteQueryAsync(databaseName, query, clientRequestProperties);
while (reader.Read())
{
count = (int)reader.GetInt64(0);
}
return(count);
}
catch (Exception e)
{
this.logger.LogError(e, "Error reading from kusto database {database}", databaseName);
throw;
}
}
public async Task<List<TDestination>> QueryAsync<TDestination>(
string databaseName,
string query,
Dictionary<string, string> queryParameter)
{
try
{
var clientRequestProperties = new ClientRequestProperties();
if (queryParameter != null)
{
clientRequestProperties = new ClientRequestProperties(
options: null,
parameters: queryParameter);
}
clientRequestProperties.ClientRequestId = Guid.NewGuid().ToString();
var result = await this.client.ExecuteQueryAsync(databaseName, query, clientRequestProperties);
var resultList = result.ToJObjects().ToList();
var queryResults = new List<TDestination>();
foreach (var data in resultList)
{
queryResults.Add(data.ToObject<TDestination>());
}
return queryResults;
}
catch (Exception e)
{
throw new ApplicationException($"Error reading from kusto database {databaseName}", e);
}
}
public IDataReader ExecuteQuery(String dbName, string query, TelemetryClient telemetry)
{
if (_isUsable)
{
ClientRequestProperties clientRequestProperties = CreateRequestProperties();
try
{
var resultTask = Task.Run(() => adx.ExecuteQuery(dbName, query, clientRequestProperties));
if (resultTask.Wait(30000))
{
return(resultTask.Result);
}
}
catch (Exception ex)
{
telemetry.TrackException(ex);
}
_isUsable = false;
}
return(null);
}
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);
});
}
static void MainImpl(Arguments arguments)
{
// 1. Create a connection string to a cluster/database with AAD user authentication
var cluster = "https://help.kusto.windows.net/";
var database = "Samples";
var kcsb = new KustoConnectionStringBuilder(cluster, database)
{
FederatedSecurity = true
};
// 2. Connect to the Kusto query endpoint and create a query provider
using (var queryProvider = KustoClientFactory.CreateCslQueryProvider(kcsb))
{
// 3. Send a query using the V2 API
var query = "print Welcome='Hello, World!'; print PI=pi()";
var properties = new ClientRequestProperties()
{
ClientRequestId = "kusto_samples_query_v2;" + Guid.NewGuid().ToString()
};
if (arguments.ProgressiveMode)
{
properties.SetOption(ClientRequestProperties.OptionResultsProgressiveEnabled, true);
}
var queryTask = queryProvider.ExecuteQueryV2Async(database, query, properties);
// 4. Parse and print the results of the query
WriteResultsToConsole(queryTask);
}
}
/// <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 <IDataReader> ExecuteKustoQueryAsync(KustoQuery query)
{
var client = KustoQueryProvider;
var properties = new ClientRequestProperties();
foreach (var parameter in query.Parameters)
{
properties.SetParameter(parameter.Name, parameter.Value.ToString());
}
string text = query.Text;
if (query.Parameters?.Any() == true)
{
string parameterList = String.Join(",", query.Parameters.Select(p => $"{p.Name}:{p.Type}"));
text = $"declare query_parameters ({parameterList});{query.Text}";
}
try
{
return(await client.ExecuteQueryAsync(
DatabaseName,
text,
properties));
}
catch (SemanticException)
{
return(null);
}
}
protected Task <ObjectReader <T> > QueryKustoAsync <T>(RuleContext context, string query, string?database = null, ClientRequestProperties?requestProperties = null)
{
if (database == null)
{
database = _configuration.KustoDatabaseName;
}
if (requestProperties == null)
{
requestProperties = new ClientRequestProperties();
}
requestProperties.ClientRequestId = context.RunGuid.ToString();
return(_configuration.CslQueryProvider.QuerySingleResultSetAsync <T>(query, database, requestProperties));
}
protected Task <IReadOnlyList <T> > QueryKustoAsync <T>(RuleContext context, string query, string?database = null, ClientRequestProperties?requestProperties = null)
{
if (database == null)
{
database = _configuration.KustoDatabaseName;
}
if (requestProperties == null)
{
requestProperties = new ClientRequestProperties();
}
requestProperties.ClientRequestId = context.RunGuid.ToString();
return(_configuration.KustoClient.QueryAsync <T>(query, database, requestProperties));
}
static void Main(string[] args)
{
// The query provider is the main interface to use when querying Kusto.
// It is recommended that the provider be created once for a specific target database,
// and then be reused many times (potentially across threads) until it is disposed-of.
var kcsb = new KustoConnectionStringBuilder(Cluster, Database)
.WithAadUserPromptAuthentication();
using (var queryProvider = KustoClientFactory.CreateCslQueryProvider(kcsb))
{
// The query -- Note that for demonstration purposes, we send a query that asks for two different
// result sets (HowManyRecords and SampleRecords).
var query = "StormEvents | count | as HowManyRecords; StormEvents | limit 10 | project StartTime, EventType, State | as SampleRecords";
// It is strongly recommended that each request has its own unique
// request identifier. This is mandatory for some scenarios (such as cancelling queries)
// and will make troubleshooting easier in others.
var clientRequestProperties = new ClientRequestProperties()
{
ClientRequestId = Guid.NewGuid().ToString()
};
using (var reader = queryProvider.ExecuteQuery(query, clientRequestProperties))
{
// Read HowManyRecords
while (reader.Read())
{
var howManyRecords = reader.GetInt64(0);
Console.WriteLine($"There are {howManyRecords} records in the table");
}
// Move on to the next result set, SampleRecords
reader.NextResult();
Console.WriteLine();
while (reader.Read())
{
// Important note: For demonstration purposes we show how to read the data
// using the "bare bones" IDataReader interface. In a production environment
// one would normally use some ORM library to automatically map the data from
// IDataReader into a strongly-typed record type (e.g. Dapper.Net, AutoMapper, etc.)
DateTime time = reader.GetDateTime(0);
string type = reader.GetString(1);
string state = reader.GetString(2);
Console.WriteLine("{0}\t{1,-20}\t{2}", time, type, state);
}
}
Console.WriteLine("finisih");
}
}
public async Task <List <StormEvent> > GetStormEvents(string userId, string searchText = null)
{
List <StormEvent> stormEvents = new List <StormEvent>();
try
{
var userstates = await GetUserStates(userId);
var kcsb = new KustoConnectionStringBuilder(_options.ADXCluster, _options.ADXDatabase)
.WithAadUserPromptAuthentication();
using (var queryProvider = KustoClientFactory.CreateCslQueryProvider(kcsb))
{
var query = "StormEvents| extend i = ingestion_time() | join(StormEvents | summarize i = max(ingestion_time()) by EventId) on $left.EventId == $right.EventId and $left.i ==$right.i | sort by StartTime desc | take 100 | where isnotnull(EventId)";
if (userstates != "")
{
query += " and State in (" + userstates + ") ";
}
if (searchText != null)
{
query += " and * has '" + searchText + "'";
}
// It is strongly recommended that each request has its own unique
// request identifier. This is mandatory for some scenarios (such as cancelling queries)
// and will make troubleshooting easier in others.
var clientRequestProperties = new ClientRequestProperties()
{
ClientRequestId = Guid.NewGuid().ToString()
};
using (var reader = queryProvider.ExecuteQuery(query, clientRequestProperties))
{
while (reader.Read())
{
StormEvent se = ReflectPropertyInfo.ReflectType <StormEvent>(reader);
stormEvents.Add(se);
}
}
}
}
catch
{
}
return(stormEvents);
}
public async Task Test_KustoTestEvent_StreamsToKusto(bool useDirect)
{
_kustoQueryClient.Should().NotBeNull();
var bb = BigBrother.CreateDefault("", "");
var builder = bb.UseKusto()
.WithCluster(_kustoName, _kustoLocation, _kustoDatabase, _kustoTenantId);
if (useDirect)
{
builder.RegisterType <KustoTestEvent>().WithDirectClient().Build();
}
else
{
builder.RegisterType <KustoTestEvent>().WithQueuedClient().Build();
}
var evt = new KustoTestEvent();
bb.Publish(evt);
await Policy.Handle <Exception>()
.WaitAndRetryAsync(new[]
{
TimeSpan.FromSeconds(3),
TimeSpan.FromSeconds(5),
TimeSpan.FromSeconds(5),
TimeSpan.FromSeconds(5)
})
.ExecuteAsync(async() =>
{
var reader = await _kustoQueryClient.ExecuteQueryAsync(
_kustoDatabase,
$"{nameof(KustoTestEvent)} | where {nameof(KustoTestEvent.Id)} == \"{evt.Id}\" | summarize count()",
ClientRequestProperties.FromJsonString("{}"));
_output.WriteLine("Checking if event is in Kusto ...");
reader.Read().Should().BeTrue();
reader.GetInt64(0).Should().Be(1);
_output.WriteLine("Event verified.");
});
}
private ClientRequestProperties CreateRequestProperties()
{
var queryParameters = new Dictionary <String, String>()
{
//{ "xIntValue", "111" },
// { "xStrValue", "abc" },
// { "xDoubleValue", "11.1" }
};
var clientRequestProperties = new ClientRequestProperties(
options: null,
parameters: queryParameters)
{
ClientRequestId = _clientRequestIdPrefix + Guid.NewGuid().ToString()
};
return(clientRequestProperties);
}
protected Task <IReadOnlyList <T> > QueryKustoAsync <T>(RuleContext context, string query, string?database = null, ClientRequestProperties?requestProperties = null)
{
if (database == null)
{
database = _configuration.KustoDatabaseName;
}
if (requestProperties == null)
{
requestProperties = new ClientRequestProperties();
}
// This is used for performance monitoring of queries. Follows recommendation from here:
// https://docs.microsoft.com/en-us/azure/data-explorer/kusto/api/netfx/request-properties
requestProperties.ClientRequestId = $"Monitor.{Identifier};{context.RunGuid}-{Guid.NewGuid()}";
return(_configuration.KustoClient.QueryAsync <T>(query, database, requestProperties));
}
/// <summary>
/// Export all records from <paramref name="tableName"/> in database <paramref name="databaseName"/> to external table
/// <paramref name="externalTableName"/>. Split by intervals of <paramref name="step"/> (of ingestion time)
/// </summary>
private static void ExportTableToExternalTable(string databaseName, string tableName, string externalTableName, TimeSpan step)
{
// This authenticates based on user credentials. Can replace with AAD app if needed (see KustoConnectionStringBuilder API)
var connection = new KustoConnectionStringBuilder($"{KustoClusterUri};Fed=true");
var queryClient = KustoClientFactory.CreateCslQueryProvider(connection);
var adminClient = KustoClientFactory.CreateCslAdminProvider(connection);
var minMaxIngestionTime = queryClient.ExecuteQuery <MinMaxDateTime>(databaseName, $"{tableName} | summarize min(ingestion_time()), max(ingestion_time())").Single();
var start = minMaxIngestionTime.Min;
var end = minMaxIngestionTime.Max;
while (start < end)
{
DateTime curEnd = start + step;
var exportCommand = CslCommandGenerator.GenerateExportToExternalTableCommand(externalTableName,
query: $"{tableName} | where ingestion_time() >= {CslDateTimeLiteral.AsCslString(start)} and ingestion_time() < {CslDateTimeLiteral.AsCslString(curEnd)}",
sizeLimitInBytes: MemoryConstants._1GB,
persistDetails: true,
isAsync: true);
var crp = new ClientRequestProperties();
crp.ClientRequestId = $"ExportApp;{Guid.NewGuid().ToString()}";
ExtendedConsole.WriteLine(ConsoleColor.Cyan, $"Executing export command from {start.FastToString()} to {curEnd.FastToString()} with request id {crp.ClientRequestId}");
var operationDetails = adminClient.ExecuteAsyncControlCommand(databaseName, exportCommand, TimeSpan.FromMinutes(60), TimeSpan.FromSeconds(1), crp);
var state = operationDetails.State;
if (state == "Completed")
{
// check num records exported
var command = $"{CslCommandGenerator.GenerateOperationDetailsShowCommand(operationDetails.OperationId)} | summarize sum(NumRecords)";
long exportedRecords = adminClient.ExecuteControlCommand <long>(command).Single();
ExtendedConsole.WriteLine(ConsoleColor.Green, $"Operation {operationDetails.OperationId} completed with state {operationDetails.State} and exported {exportedRecords} records");
}
else
{
// TODO: retry same scope again (or abort and check root cause for failure). Operation may still be running on server side, so retrying
// without checking status can lead to duplicates
ExtendedConsole.WriteLine(ConsoleColor.Red, $"Operation {operationDetails.OperationId} completed with state {operationDetails.State}. Status: {operationDetails.Status}");
}
start = curEnd;
}
}
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);
}
protected async Task <ObjectReader <T> > QuerySingleResultSetAsync <T>(RuleContext context, string query, string database = null, ClientRequestProperties requestProperties = null)
{
Contract.RequiresNotNull(context);
Contract.RequiresNotNullOrEmpty(query);
if (database == null)
{
database = _configuration.KustoDatabaseName;
}
if (requestProperties == null)
{
requestProperties = new ClientRequestProperties();
}
requestProperties.ClientRequestId = context.RunGuid.ToString();
var dataReader = await _configuration.CslQueryProvider.ExecuteQueryAsync(database, query, requestProperties);
return(new ObjectReader <T>(dataReader, disposeReader: true, nameBasedColumnMapping: true));
}
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 IEnumerable <IDictionary <string, object> > ExecuteQuery(string query, Dictionary <string, string> queryParameters)
{
var clientRequestProperties = new ClientRequestProperties(
options: null,
parameters: queryParameters);
using (var client = KustoClientFactory.CreateCslQueryProvider(_kustoConnectionStringBuilder))
{
var reader = client.ExecuteQuery(DatabaseName, query, clientRequestProperties);
var names = Enumerable.Range(0, reader.FieldCount)
.Select(i => reader.GetName(i))
.ToArray();
while (reader.Read())
{
yield return(Enumerable.Range(0, reader.FieldCount)
.ToDictionary(i => names[i], i => reader.GetValue(i)));
}
}
}
public List <object[]> Query(string table, ref string query, int icm, string timestampField = "TIMESTAMP", int limit = Constants.KustoClientQueryLimit)
{
if (timestampField != null)
{
// TODO : If ICM AnalyzerStartTimeField was changed, it might be newer than the ICM creation date
DateTime startTime;
if (!DateTime.TryParse(SALsA.GetInstance(icm).ICM.GetCustomField(Constants.AnalyzerStartTimeField), out startTime))
{
startTime = SALsA.GetInstance(icm).ICM.CurrentICM.CreateDate.AddDays(-1);
}
string start = startTime.ToString("u");
//string end = SALsA.GetInstance(icm).ICM.CurrentICM.CreateDate.ToString("u");
query = String.Format("{0} | where {1} > datetime({2}) | {3} | limit {4}", table, timestampField, start, /*end,*/ query, limit);
}
else
{
query = String.Format("{0} | {1} | limit {2}", table, query, limit);
}
Log.Verbose("Sending {0} query : {1}", client.DefaultDatabaseName, query);
var clientRequestProperties = new ClientRequestProperties()
{
ClientRequestId = Guid.NewGuid().ToString()
};
using (var reader = client.ExecuteQuery(query, clientRequestProperties))
{
DataTable dt = new DataTable();
dt.Load(reader);
List <object[]> data = new List <object[]>();
data.Add(new object[dt.Columns.Count]);
dt.Columns.CopyTo(data[0], 0);
foreach (DataRow line in dt.Rows)
{
data.Add(new object[dt.Columns.Count]);
line.ItemArray.CopyTo(data[data.Count - 1], 0);
}
return(data);
}
}
static void Main()
{
// The query provider is the main interface to use when querying Kusto.
// It is recommended that the provider be created once for a specific target database,
// and then be reused many times (potentially across threads) until it is disposed-of.
var kcsb = new KustoConnectionStringBuilder(Cluster, Database)
.WithAadUserPromptAuthentication();
using (var queryProvider = KustoClientFactory.CreateCslQueryProvider(kcsb))
{
// The query -- Note that for demonstration purposes, we send a query that asks for two different
// result sets (HowManyRecords and SampleRecords).
var query = "tblProject| where LanguageId ==2|take 1";
// It is strongly recommended that each request has its own unique
// request identifier. This is mandatory for some scenarios (such as cancelling queries)
// and will make troubleshooting easier in others.
var clientRequestProperties = new ClientRequestProperties()
{
ClientRequestId = Guid.NewGuid().ToString()
};
DataSet ds = new DataSet();
using (var reader = queryProvider.ExecuteQuery(query, clientRequestProperties))
{
// Read HowManyRecords
while (reader.Read())
{
var howManyRecords = reader.GetInt32(0);
MyType mType = new MyType();
mType.ProjectId = howManyRecords;
Console.WriteLine($"There are {mType.ProjectId} records in the table");
}
////Suggested by Yoni L
var result = queryProvider.ExecuteQuery <MyType>(query, clientRequestProperties).ToList <MyType>();
foreach (var item in result)
{
//Empty result set
//item.ProjectName = item.ProjectName;
//Console.WriteLine($"The project name is {item.ProjectName} in the table");
}
// Move on to the next result set, SampleRecords
//reader.NextResult();
//Console.WriteLine();
//while (reader.Read())
//{
// // Important note: For demonstration purposes we show how to read the data
// // using the "bare bones" IDataReader interface. In a production environment
// // one would normally use some ORM library to automatically map the data from
// // IDataReader into a strongly-typed record type (e.g. Dapper.Net, AutoMapper, etc.)
// DateTime time = reader.GetDateTime(0);
// string type = reader.GetString(1);
// string state = reader.GetString(2);
// Console.WriteLine("{0}\t{1,-20}\t{2}", time, type, state);
//}
}
Console.WriteLine();
Console.ReadLine();
}
}
public Task <List <TelemetryData> > GetStormDataAsync(DateTime startDate, DateTime endDate)
{
List <TelemetryData> telemetryData = new List <TelemetryData>();
// The query provider is the main interface to use when querying Kusto.
// It is recommended that the provider be created once for a specific target database,
// and then be reused many times (potentially across threads) until it is disposed-of.
var kcsb = new KustoConnectionStringBuilder(Cluster, Database)
.WithAadUserPromptAuthentication();
using (var queryProvider = KustoClientFactory.CreateCslQueryProvider(kcsb))
{
// The query -- Note that for demonstration purposes, we send a query that asks for two different
// result sets (HowManyRecords and SampleRecords).
var query = @$ "declare query_parameters(startTime:datetime = datetime({startDate}), endTime:datetime = datetime({endDate}));
VersionedProcessedTelemetry
| where timestamp between (startTime .. endTime) and site == '52dd1dab-135b-44aa-824e-8db89f36d1d9'
| summarize arg_max(revision, *) by timestamp
| count | as HowManyRecords;
VersionedProcessedTelemetry
| where timestamp between (startTime .. endTime) and site == '52dd1dab-135b-44aa-824e-8db89f36d1d9'
| summarize arg_max(revision, *) by timestamp
| order by timestamp";
// It is strongly recommended that each request has its own unique
// request identifier. This is mandatory for some scenarios (such as cancelling queries)
// and will make troubleshooting easier in others.
var clientRequestProperties = new ClientRequestProperties()
{
ClientRequestId = Guid.NewGuid().ToString()
};
using (var reader = queryProvider.ExecuteQuery(query, clientRequestProperties))
{
// Read HowManyRecords
while (reader.Read())
{
var howManyRecords = reader.GetInt64(0);
}
// Move on to the next result set, SampleRecords
reader.NextResult();
while (reader.Read())
{
var telemetryDataItem = new TelemetryData();
// Important note: For demonstration purposes we show how to read the data
// using the "bare bones" IDataReader interface. In a production environment
// one would normally use some ORM library to automatically map the data from
// IDataReader into a strongly-typed record type (e.g. Dapper.Net, AutoMapper, etc.)
telemetryDataItem.site = reader.GetString(2);
telemetryDataItem.timestamp = reader.GetDateTime(0);
telemetryDataItem.revision = reader.GetInt32(1);
telemetryDataItem.temperature = reader.GetDouble(3);
telemetryDataItem.humidity = reader.GetDouble(4);
telemetryDataItem.methane = reader.GetDouble(5);
telemetryData.Add(telemetryDataItem);
}
}
return(Task.FromResult(telemetryData));
}
}