private ICslQueryProvider GetKustoQueryClient()
{
if (this.client == null)
{
try
{
var kustoUri = $"https://{this.config.Global.DataExplorer.Name}.{this.config.Global.Location}.kusto.windows.net/";
var kustoConnectionStringBuilder = new KustoConnectionStringBuilder(kustoUri)
.WithAadApplicationKeyAuthentication(
applicationClientId: this.applicationId,
applicationKey: this.applicationSecret,
authority: this.tenant);
this.client = KustoClientFactory.CreateCslQueryProvider(kustoConnectionStringBuilder);
}
catch (Exception e)
{
var msg = "Unable to retrieve kusto with Active Directory properties" +
$"'{this.applicationId}'," +
$"'{this.applicationSecret}' and" +
$"'{this.tenant}'.";
throw new InvalidConfigurationException(msg, e);
}
if (this.client == null)
{
// this.logger.LogError(new Exception(errorMessage), errorMessage);
throw new InvalidConfigurationException("Could not connect to kusto client");
}
}
return(this.client);
}
} // - function Read
private static void InitializeKustoClient(ILogger log)
{
lock (Lock)
{
if (!_isInitialized)
{
log.LogInformation("[PrometheusRead] [InitializeKustoClient] Initialize");
var connection =
new KustoConnectionStringBuilder(
Environment.GetEnvironmentVariable("kustoUrl", EnvironmentVariableTarget.Process))
.WithAadApplicationKeyAuthentication(
applicationClientId: Environment.GetEnvironmentVariable("appClientId",
EnvironmentVariableTarget.Process),
applicationKey: Environment.GetEnvironmentVariable("appClientSecret",
EnvironmentVariableTarget.Process),
authority: Environment.GetEnvironmentVariable("tenantId",
EnvironmentVariableTarget.Process));
_adx = KustoClientFactory.CreateCslQueryProvider(connection);
_isInitialized = true;
}
log.LogInformation("[PrometheusRead] [InitializeKustoClient] Ok");
}
} // - InitializeKustoClient
/// <summary>
/// Get kusto query record into Datatable.
/// </summary>
/// <returns>Datatable</returns>
public DataTable ExecuteQueryOnKusto(KustoConnectionStringBuilder kustoConnectionStringBuilder, string query)
{
Console.WriteLine("\n[System]: In Kusto Execute Query to get DT");
DataTable dt = new DataTable();
if (kustoConnectionStringBuilder != null && !string.IsNullOrWhiteSpace(query))
{
try
{
var clients = KustoClientFactory.CreateCslQueryProvider(kustoConnectionStringBuilder);
using (var record = clients.ExecuteQuery(query))
{
if (record != null)
{
dt.Load(record);
}
}
}
catch (Exception ex)
{
Console.WriteLine("\n[System]: Exception :" + ex.Message);
}
}
return(dt);
}
public Monitor(Configuration configuration)
{
Contract.RequiresNotNull(configuration);
_configuration = configuration;
_logger = CreateLogger();
Contract.RequiresNotNullOrEmpty(_configuration.KustoIngestionClusterUrl);
Contract.RequiresNotNullOrEmpty(_configuration.ApplicationClientId);
Contract.RequiresNotNullOrEmpty(_configuration.ApplicationKey);
Contract.RequiresNotNullOrEmpty(_configuration.Authority);
var kustoIngestConnectionString = new KustoConnectionStringBuilder(_configuration.KustoIngestionClusterUrl)
.WithAadApplicationKeyAuthentication(_configuration.ApplicationClientId, _configuration.ApplicationKey, _configuration.Authority);
_kustoIngestClient = KustoIngestFactory.CreateDirectIngestClient(kustoIngestConnectionString);
var kustoConnectionString = new KustoConnectionStringBuilder(_configuration.KustoClusterUrl)
.WithAadApplicationKeyAuthentication(_configuration.ApplicationClientId, _configuration.ApplicationKey, _configuration.Authority);
_cslQueryProvider = KustoClientFactory.CreateCslQueryProvider(kustoConnectionString);
Contract.RequiresNotNull(_configuration.KustoNotifier);
_alertNotifier = new KustoWriter <Notification>(_configuration.KustoNotifier, _logger, _kustoIngestClient);
Contract.RequiresNotNull(_configuration.SchedulerKustoNotifier);
_schedulerLogWriter = new KustoWriter <Scheduler.LogEntry>(_configuration.SchedulerKustoNotifier, _logger, _kustoIngestClient);
_scheduler = new Scheduler(_configuration.Scheduler, _logger, _clock, _schedulerLogWriter);
}
/// <summary>
/// After querying, if we need alert, return true, otherwise return false
/// </summary>
/// <param name="rule">The specified MonitorRule instance.</param>
/// <returns>If need alert, return true, otherwise return false.</returns>
public override bool ExecuteRule(MonitorRule rule, out string result)
{
object value = null;
result = string.Empty;
try
{
//Then there is the code to execute the Kusto query based on rule
var client = KustoClientFactory.CreateCslQueryProvider(rule.DataSource);
IDataReader reader = client.ExecuteQuery(rule.AlertQuery);
while (reader.Read())
{
value = reader.GetValue(0);
}
}
catch (Exception ex)
{
Log.WriteErrorLog("{0} throw exception : {1}", rule.RuleUniqueIdentity, ex.ToString());
return(false);
}
result = value.ToString();
Double.TryParse(result, out double returnObj);
return(CommonHelper.CheckResult(rule.Operation, returnObj, rule.Threshold, rule.RuleUniqueIdentity));
}
/// <summary>
/// Constructor which gets ready to make queries to Kusto
/// </summary>
/// <param name="kustoConnectionStringBuilder">The connection string builder to connect to Kusto</param>
public QueryEngine(KustoConnectionStringBuilder kustoConnectionStringBuilder)
{
_adminClient = KustoClientFactory.CreateCslAdminProvider(kustoConnectionStringBuilder);
_queryClient = KustoClientFactory.CreateCslQueryProvider(kustoConnectionStringBuilder);
_databaseName = kustoConnectionStringBuilder.InitialCatalog;
_cluster = kustoConnectionStringBuilder.DataSource;
}
private static ICslQueryProvider CreateClient(AppConfigHelper appConfigHelper)
{
string adxClustorName = appConfigHelper.GetValue(ADXClusterNameKey);
string location = appConfigHelper.GetValue(LocationKey);
string applicationId = appConfigHelper.GetValue(ApplicationIdKey);
string applicationSecret = appConfigHelper.GetValue(ApplicationSecretKey);
string appTenant = appConfigHelper.GetValue(AppTenantKey);
try
{
var kustoUri = $"https://{adxClustorName}.{location}.kusto.windows.net/";
var kustoConnectionStringBuilder = new KustoConnectionStringBuilder(kustoUri)
.WithAadApplicationKeyAuthentication(
applicationClientId: applicationId,
applicationKey: applicationSecret,
authority: appTenant);
return KustoClientFactory.CreateCslQueryProvider(kustoConnectionStringBuilder);
}
catch (Exception ex)
{
throw new ApplicationException("DocumentClient creation failed in the helper class", ex);
}
}
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);
}
}
public BigBrotherUseKustoTest(ITestOutputHelper output)
{
_output = output;
_kustoName = Environment.GetEnvironmentVariable("kusto_name");
_kustoLocation = Environment.GetEnvironmentVariable("kusto_location");
_kustoDatabase = Environment.GetEnvironmentVariable("kusto_database");
_kustoTenantId = Environment.GetEnvironmentVariable("kusto_tenant_id");
if (_kustoName != null && _kustoLocation != null && _kustoDatabase != null && _kustoTenantId != null)
{
var kustoUri = $"https://{_kustoName}.{_kustoLocation}.kusto.windows.net";
var token = new AzureServiceTokenProvider().GetAccessTokenAsync(kustoUri, _kustoTenantId).Result;
var kustoConnectionString =
new KustoConnectionStringBuilder(kustoUri)
{
FederatedSecurity = true,
InitialCatalog = _kustoDatabase,
Authority = _kustoTenantId,
ApplicationToken = token
};
_kustoQueryClient = KustoClientFactory.CreateCslQueryProvider(kustoConnectionString);
_kustoAdminClient = KustoClientFactory.CreateCslAdminProvider(kustoConnectionString);
}
}
public Monitor(Configuration configuration)
{
Contract.RequiresNotNull(configuration);
_configuration = configuration;
_logger = CreateLogger();
// TODO(jubayard): use streaming ingestion instead of direct ingestion. There seems to be some assembly
// issues when attempting to do that
Contract.RequiresNotNullOrEmpty(_configuration.KustoIngestionClusterUrl);
Contract.RequiresNotNullOrEmpty(_configuration.ApplicationClientId);
Contract.RequiresNotNullOrEmpty(_configuration.ApplicationKey);
Contract.RequiresNotNullOrEmpty(_configuration.Authority);
var kustoIngestConnectionString = new KustoConnectionStringBuilder(_configuration.KustoIngestionClusterUrl)
.WithAadApplicationKeyAuthentication(_configuration.ApplicationClientId, _configuration.ApplicationKey, _configuration.Authority);
_kustoIngestClient = KustoIngestFactory.CreateDirectIngestClient(kustoIngestConnectionString);
var kustoConnectionString = new KustoConnectionStringBuilder(_configuration.KustoClusterUrl)
.WithAadApplicationKeyAuthentication(_configuration.ApplicationClientId, _configuration.ApplicationKey, _configuration.Authority);
_cslQueryProvider = KustoClientFactory.CreateCslQueryProvider(kustoConnectionString);
Contract.RequiresNotNull(_configuration.KustoNotifier);
_alertNotifier = new KustoWriter <Notification>(_configuration.KustoNotifier, _logger, _kustoIngestClient);
Contract.RequiresNotNull(_configuration.SchedulerKustoNotifier);
_schedulerLogWriter = new KustoWriter <Scheduler.LogEntry>(_configuration.SchedulerKustoNotifier, _logger, _kustoIngestClient);
_scheduler = new Scheduler(_configuration.Scheduler, _logger, _clock, _schedulerLogWriter);
}
public List <string> Query(string query)
{
Log.Info($"query:{query}", ConsoleColor.Blue);
if (_kustoQueryClient == null)
{
_kustoQueryClient = KustoClientFactory.CreateCslQueryProvider(ManagementConnection);
queryTimer = new Timer(DisposeQueryClient, null, maxKustoClientTimeMs, maxKustoClientTimeMs);
}
try
{
queryTimer.Change(maxKustoClientTimeMs, maxKustoClientTimeMs);
// unable to parse multiple tables v1 or v2 using kusto so using httpclient and rest
string requestBody = "{ \"db\": \"" + DatabaseName + "\", \"csl\": \"" + query + "\" }";
string requestId = new Guid().ToString();
Dictionary <string, string> headers = new Dictionary <string, string>();
headers.Add("accept", "application/json");
headers.Add("host", HostName);
headers.Add("x-ms-client-request-id", requestId);
Log.Info($"query:", requestBody);
_httpClient.DisplayResponse = Config.LogDebug >= LoggingLevel.Verbose;
_httpClient.SendRequest(uri: RestQueryUri, authToken: _arm.BearerToken, jsonBody: requestBody, httpMethod: HttpMethod.Post, headers: headers);
ResponseDataSet = JsonConvert.DeserializeObject <KustoRestResponseV1>(_httpClient.ResponseStreamString);
if (!ResponseDataSet.HasData())
{
Log.Info($"no tables:", ResponseDataSet);
return(new List <string>());
}
KustoRestTableOfContentsV1 toc = SetTableOfContents(ResponseDataSet);
if (toc.HasData)
{
SetExtendedProperties();
long index = toc.Rows.FirstOrDefault(x => x.Kind.Equals("QueryResult")).Ordinal;
PrimaryResultTable = new KustoRestTable(ResponseDataSet.Tables[index]);
return(PrimaryResultTable.RecordsCsv());
}
else
{
TableOfContents = new KustoRestTableOfContentsV1();
Cursor = "''";
ExtendedPropertiesTable = new KustoRestTable();
PrimaryResultTable = new KustoRestTable(ResponseDataSet.Tables[0]);
return(PrimaryResultTable.RecordsCsv());
}
}
catch (Exception e)
{
Log.Exception($"exception executing query: {query}\r\n{e}");
return(new List <string>());
}
}
public AdxClientTool()
{
connection =
new KustoConnectionStringBuilder(Properties.Connection.Default.kustoURL).WithAadApplicationKeyAuthentication(
applicationClientId: Properties.Connection.Default.appClientId,
applicationKey: Properties.Connection.Default.appClientSecret,
authority: Properties.Connection.Default.appAadTenantId);
adx = KustoClientFactory.CreateCslQueryProvider(connection);
}
public List <string> Query(string query)
{
Log.Info($"query:{query}", ConsoleColor.Blue);
List <string> list = new List <string>();
using (ICslQueryProvider kustoQueryClient = KustoClientFactory.CreateCslQueryProvider(ManagementConnection))
{
return(EnumerateResults(kustoQueryClient.ExecuteQuery(query)));
}
}
private static void ValidateIngestion(string databaseName, string table, KustoConnectionStringBuilder kustoConnectionStringBuilder)
{
using (var cslQueryProvider = KustoClientFactory.CreateCslQueryProvider(kustoConnectionStringBuilder))
{
var query = $"{table} | count";
var results = cslQueryProvider.ExecuteQuery <long>(databaseName, query);
Console.WriteLine(results.Single());
}
}
private ICslQueryProvider GetKustoConnection(KustoConnectionConfiguration conf)
{
var kustoConnectionStringBuilder = new KustoConnectionStringBuilder(conf.Cluster, conf.DefaultDB)
{
UserID = conf.User,
Authority = conf.AuthorityId,
FederatedSecurity = true
};
return(KustoClientFactory.CreateCslQueryProvider(kustoConnectionStringBuilder));
}
public static Result <IKustoClient> CreateKustoQueryClient(string kustoClusterUrl, string azureTenantId, string azureAppId, string azureAppKey)
{
Contract.RequiresNotNullOrEmpty(kustoClusterUrl);
Contract.RequiresNotNullOrEmpty(azureAppId);
Contract.RequiresNotNullOrEmpty(azureAppKey);
Contract.RequiresNotNullOrEmpty(azureTenantId);
var kustoConnectionString = new KustoConnectionStringBuilder(kustoClusterUrl)
.WithAadApplicationKeyAuthentication(azureAppId, azureAppKey, azureTenantId);
return(new Result <IKustoClient>(new KustoClient(KustoClientFactory.CreateCslQueryProvider(kustoConnectionString))));
}
public KustoClient(string clusterName, string database, int icm)
{
Log.Information("Creating Kusto connector for Cluster : {0} in database : {1}", clusterName, database);
var authority = Authentication.Instance.ServicePrincipal.tenant;
var applicationClientId = Authentication.Instance.ServicePrincipal.appId;
var applicationKey = Authentication.Instance.ServicePrincipal.password;
var kcsb = new KustoConnectionStringBuilder(String.Format("https://{0}.kusto.windows.net", clusterName), database)
.WithAadApplicationKeyAuthentication(applicationClientId, applicationKey, authority);
client = KustoClientFactory.CreateCslQueryProvider(kcsb);
Log.Information("Finished creating Kusto connector : {0}", kcsb);
}
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 ValueTask <KustoContext> CreateContextAsync()
{
var clusterUrl = _options.Value.ClusterUrl ?? throw new ArgumentException("Missing required configuration value: 'Kusto.ClusterUrl'");
var databaseName = _options.Value.DatabaseName ?? throw new ArgumentException("Missing required configuration value: 'Kusto.DatabaseName'");
var token = await _azureAuth.AcquireTokenAsync();
var kcsb = new KustoConnectionStringBuilder(clusterUrl, databaseName)
.WithAadUserTokenAuthentication(token.AccessToken);
return(new KustoContext(
KustoClientFactory.CreateCslQueryProvider(kcsb),
KustoClientFactory.CreateCslAdminProvider(kcsb),
databaseName,
_loggerFactory.CreateLogger <KustoContext>()));
}
public static ICslQueryProvider Create(string clusterName, IConfigurationRoot config, string userToken)
{
if (!string.IsNullOrWhiteSpace(userToken))
{
return(KustoClientFactory.CreateCslQueryProvider(new KustoConnectionStringBuilder("https://" + clusterName + ".kusto.windows.net")
.WithAadUserTokenAuthentication(userToken)));
}
else
{
return(KustoClientFactory.CreateCslQueryProvider(new KustoConnectionStringBuilder("https://" + clusterName + ".kusto.windows.net")
.WithAadApplicationKeyAuthentication(
applicationClientId: GetEnvVariable(config, "ClientId"),
applicationKey: GetEnvVariable(config, "ClientSecret"),
authority: GetEnvVariable(config, "Tenant"))));
}
}
static void Main(string[] args)
{
var builder = new KustoConnectionStringBuilder("https://help.kusto.windows.net/Samples")
.WithAadUserPromptAuthentication();
using (var client = KustoClientFactory.CreateCslQueryProvider(builder))
{
var command = Kusto.Data.Net.Client.KustoClientFactory.CslCommandGenerator.GenerateDatabasesShowCommand();
var databases = Kusto.Data.Net.Client.KustoClientFactory.adminProvider.ExecuteControlCommand <DatabasesShowCommandResult>(command).ToList();
foreach (var database in databases)
{
Console.WriteLine(database.DatabaseName);
}
}
}
public static void MainMethod()
{
ISecretProvider secretProvider = KeyVaultSecretProvider.Instance;
string kustoAppId = secretProvider.GetSecretAsync("datacop-prod", "KustoAppId").Result;
string kustoAppKey = secretProvider.GetSecretAsync("datacop-prod", "KustoAppKey").Result;
string kustoAuthorityId = secretProvider.GetSecretAsync("datacop-prod", "KustoAuthorityId").Result;
// The value is that "https://icmcluster.kusto.windows.net"
// It is not very necessary to save it in Azure KeyVault
string kustoConnectionString = secretProvider.GetSecretAsync("datacopdev", "KustoConnectionString").Result;
string query = @"let idx = 0;
let cutoffDate = datetime_add('month',-idx , endofmonth(now()));
Incidents
| where ModifiedDate <= cutoffDate and SourceCreatedBy == 'DataCopMonitor'
| summarize arg_max(ModifiedDate, OwningContactAlias, Status, CreateDate, ImpactStartDate, ResolveDate) by IncidentId
| where Status == 'ACTIVE'
| distinct IncidentId
| count";
string url = $@"{kustoConnectionString}/IcmDataWarehouse";
var kustoConnectionStringBuilder = new KustoConnectionStringBuilder(url)
{
FederatedSecurity = true,
ApplicationClientId = kustoAppId,
ApplicationKey = kustoAppKey,
Authority = kustoAuthorityId
};
var client = KustoClientFactory.CreateCslQueryProvider(kustoConnectionStringBuilder);
Console.WriteLine(client.DefaultDatabaseName);
client.DefaultDatabaseName = "IcmDataWarehouse";
IDataReader reader = client.ExecuteQuery(query);
IList <string[]> a = reader.ToStringColumns(false);
Console.WriteLine(a.Count);
foreach (string[] strs in a)
{
foreach (string str in strs)
{
Console.Write(str + "\t");
}
Console.WriteLine();
}
}
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 KustoTimelineTelemetryRepository(ILogger <KustoTimelineTelemetryRepository> logger, IOptionsSnapshot <KustoTimelineTelemetryOptions> options)
{
_logger = logger;
// removing the IngestConnectionString was a default setup in local debugging
if (string.IsNullOrEmpty(options.Value.IngestConnectionString))
{
_logger.LogDebug("No ingest connection string provided; will ignore ingest operations");
_ingest = new NullKustoIngestClient();
}
else
{
_ingest = KustoIngestFactory.CreateQueuedIngestClient(options.Value.IngestConnectionString);
}
_query = KustoClientFactory.CreateCslQueryProvider(options.Value.QueryConnectionString);
_database = options.Value.Database;
}
static QueryHelper()
{
queryProviderDictionary = new Dictionary <string, ICslQueryProvider>();
nationalQueryProviderDictionary = new Dictionary <string, ICslQueryProvider>();
mabProviderDictionary = new Dictionary <string, ICslQueryProvider>();
queryProviderDictionary.Add("Europe", KustoClientFactory.CreateCslQueryProvider(Constant.ConnectionStringEurope));
queryProviderDictionary.Add("US", KustoClientFactory.CreateCslQueryProvider(Constant.ConnectionStringUS));
queryProviderDictionary.Add("Asia", KustoClientFactory.CreateCslQueryProvider(Constant.ConnectionStringAsia));
queryProviderDictionary.Add("Internal", KustoClientFactory.CreateCslQueryProvider(Constant.ConnectionStringInternal));
nationalQueryProviderDictionary.Add("MoonCake", KustoClientFactory.CreateCslQueryProvider(Constant.ConnectionStringMoonCake));
nationalQueryProviderDictionary.Add("BlackForest", KustoClientFactory.CreateCslQueryProvider(Constant.ConnectionStringBlackForest));
nationalQueryProviderDictionary.Add("FairFax", KustoClientFactory.CreateCslQueryProvider(Constant.ConnectionStringFairFax));
mabProviderDictionary.Add("MabWUS", KustoClientFactory.CreateCslQueryProvider(Constant.ConnectionStringMabWUS));
mabProviderDictionary.Add("MabWEU", KustoClientFactory.CreateCslQueryProvider(Constant.ConnectionStringMabWEU));
mabProviderDictionary.Add("MabProd1", KustoClientFactory.CreateCslQueryProvider(Constant.ConnectionStringMabProd1));
//mabProviderDictionary.Add("MabTest1", KustoClientFactory.CreateCslQueryProvider(Constant.ConnectionStringMabTest1));
}
private static void Initialize()
{
lock (_lock)
{
if (!_isInitialized)
{
KustoConnectionStringBuilder connection =
new KustoConnectionStringBuilder(Environment.GetEnvironmentVariable("kustoUrl", EnvironmentVariableTarget.Process)).WithAadApplicationKeyAuthentication(
applicationClientId: Environment.GetEnvironmentVariable("appClientId", EnvironmentVariableTarget.Process),
applicationKey: Environment.GetEnvironmentVariable("appClientSecret", EnvironmentVariableTarget.Process),
authority: Environment.GetEnvironmentVariable("tenantId", EnvironmentVariableTarget.Process));
adx = KustoClientFactory.CreateCslQueryProvider(connection);
_isInitialized = true;
}
}
}
/// <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 CaseExtra Get(string id)
{
CaseExtra extra = new CaseExtra();
//Get Recommendations from SQL
string SQLQueryString = @"SELECT RuleName, OldClosedAgainst, RecommendedClosedAgainst, Recommendations.Date As RecommendationDate
FROM Incidents
JOIN Recommendations ON Recommendations.Id=Incidents.ID
JOIN Rules ON Recommendations.RuleId=Rules.RuleId
WHERE IncidentId = @IncidentId;";
using (SqlConnection connection = new SqlConnection(_caseCleansingConfiguration.ConnectionString))
{
var res = connection.Query <Recommendation>(SQLQueryString, new { IncidentId = id });
extra.Recommendations = res.ToArray();
}
//Get More Info from Kusto
string kustoQueryString = $@"SupportProductionClosedVolumeDailyVer1023
| where Incidents_IncidentId == {id}";
var queryProvider = KustoClientFactory.CreateCslQueryProvider(_caseCleansingConfiguration.KustoP360ConnectionString);
var reader = queryProvider.ExecuteQuery(kustoQueryString);
dynamic kustoResult = new ExpandoObject();
if (reader.Read())
{
var dictionary = (IDictionary <string, object>)kustoResult;
for (int i = 0; i < reader.FieldCount; ++i)
{
dictionary[reader.GetName(i)] = reader.GetValue(i);
}
}
else
{
kustoResult.Error = "Result no longer in DB";
}
extra.KustoData = kustoResult;
return(extra);
}
private static ICslQueryProvider GetKustoClient(
string aadAppId,
string aadAppSecret,
string aadTenant,
string kustoUrl,
string kustoDatabaseName)
{
var authContext = new AuthenticationContext($"https://login.windows.net/{aadTenant}");
var clientCredential = new ClientCredential(aadAppId, aadAppSecret);
var authResult = authContext.AcquireTokenAsync(kustoUrl, clientCredential)
.GetAwaiter()
.GetResult();
var connectionStringBuilder = new KustoConnectionStringBuilder(
$"{kustoUrl}/{kustoDatabaseName};fed=true;UserToken={authResult.AccessToken}"
);
return(KustoClientFactory.CreateCslQueryProvider(connectionStringBuilder));
}
static void Main()
{
using (var queryProvider = KustoClientFactory.CreateCslQueryProvider($"{Cluster}/{Database};Fed=true"))
{
// Query for the current cursor
var query = "print cursor_current()";
var currentCursorValue = queryProvider.ExecuteQuery <string>(query).Single();
Console.WriteLine("Current cursor value is: " + currentCursorValue);
// Now query for all records after current cursor, and fetch the new cursor from the @ExtendedProperties table
query = $"StormEvents | where cursor_after('{currentCursorValue}') | count";
var dataSet = queryProvider.ExecuteQuery(query).ToDataSet();
var cursor = ExtractCursorValue(dataSet);
Console.WriteLine("Cursor value in @ExtendedProperties table is: " + cursor);
}
}