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); } }