private static string CreateADXTableFromDefinition(string databaseName, string table, KustoConnectionStringBuilder kustoConnectionStringBuilder, IDictionary <string, string> tableDefinition) { var command = ""; var tuple = new Tuple <string, string> [tableDefinition.Count()]; int cnt = 0; foreach (var keyvaluepair in tableDefinition) { tuple[cnt] = new Tuple <string, string>(keyvaluepair.Key, keyvaluepair.Value); cnt++; } using (var kustoClient = KustoClientFactory.CreateCslAdminProvider(kustoConnectionStringBuilder)) { command = CslCommandGenerator.GenerateTableCreateCommand( table, tuple); var tablePolicyAlterCommand = CslCommandGenerator.GenerateTableAlterStreamingIngestionPolicyCommand(table, isEnabled: true); kustoClient.ExecuteControlCommand(databaseName, command); kustoClient.ExecuteControlCommand(databaseName, tablePolicyAlterCommand); } return(command); }
private ICslAdminProvider GetKustoAdminClient() { 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.config.Global.AzureActiveDirectory.AppId, applicationKey: this.config.Global.AzureActiveDirectory.AppSecret, authority: this.config.Global.AzureActiveDirectory.TenantId); this.client = KustoClientFactory.CreateCslAdminProvider(kustoConnectionStringBuilder); } catch (Exception e) { var msg = "Unable to retrieve kusto with Active Directory properties"; 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); }
/// <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 string CreateADXTable(string databaseName, string table, KustoConnectionStringBuilder kustoConnectionStringBuilder) { var command = ""; using (var kustoClient = KustoClientFactory.CreateCslAdminProvider(kustoConnectionStringBuilder)) { command = CslCommandGenerator.GenerateTableCreateCommand( table, new[] { Tuple.Create("id", "System.Int32"), Tuple.Create("date", "System.DateTime"), Tuple.Create("time", "System.DateTime"), Tuple.Create("sym", "System.String"), Tuple.Create("qty", "System.Double"), Tuple.Create("px", "System.Double") }); kustoClient.ExecuteControlCommand(databaseName, command); //if (!isBatch) //{ // var tablePolicyAlterCommand = // CslCommandGenerator.GenerateTableAlterStreamingIngestionPolicyCommand(table, isEnabled: true); // kustoClient.ExecuteControlCommand(databaseName, tablePolicyAlterCommand); //} return(command); // kustoClient.ExecuteControlCommand(databaseName, ".create table StreamingDataTable (['id']:int)"); } }
/// <inheritdoc /> public IBigBrother UseKusto(string kustoEngineName, string kustoEngineLocation, string kustoDb, string tenantId) { KustoDbName = kustoDb; var kustoUri = $"https://{kustoEngineName}.{kustoEngineLocation}.kusto.windows.net"; var kustoIngestUri = $"https://ingest-{kustoEngineName}.{kustoEngineLocation}.kusto.windows.net"; var token = new AzureServiceTokenProvider().GetAccessTokenAsync(kustoUri, string.Empty).Result; KustoAdminClient = KustoClientFactory.CreateCslAdminProvider( new KustoConnectionStringBuilder(kustoUri) { FederatedSecurity = true, InitialCatalog = KustoDbName, AuthorityId = tenantId, ApplicationToken = token }); KustoIngestClient = KustoIngestFactory.CreateQueuedIngestClient( new KustoConnectionStringBuilder(kustoIngestUri) { FederatedSecurity = true, InitialCatalog = KustoDbName, AuthorityId = tenantId, ApplicationToken = token }); SetupKustoSubscription(); return(this); }
public static async Task <string> AdxExportStatusCheck( [ActivityTrigger] string operationId, ILogger log) { using (var client = KustoClientFactory.CreateCslAdminProvider(await GetKustoConnectionStringBuilder())) { var operationQuery = CslCommandGenerator.GenerateOperationsShowCommand(Guid.Parse(operationId)); var resultReader = new ObjectReader <OperationsShowCommandResult>(client.ExecuteControlCommand(adxDatabaseName, operationQuery)); var res = resultReader?.FirstOrDefault(); var state = res?.State; if (state == "Completed") { // When the state is completed, we can query the export details which contains the path to the file on blob storage var operationDetailsQuery = CslCommandGenerator.GenerateOperationDetailsShowCommand(Guid.Parse(operationId)); var resultReader2 = new ObjectReader <DataExportToBlobCommandResult>(client.ExecuteControlCommand(adxDatabaseName, operationDetailsQuery)); var res2 = resultReader2?.FirstOrDefault(); var path = res2?.Path; return(path); } else if (state == "Cancelled") { return("Error"); } else { return(null); } } }
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); } }
static void Main(string[] args) { var builder = new KustoConnectionStringBuilder("https://masvaas.kusto.windows.net/") .WithAadUserPromptAuthentication(); using (var adminProvider = KustoClientFactory.CreateCslAdminProvider(builder)) { var command = CslCommandGenerator.GenerateDatabasesShowCommand(); var objectReader = new ObjectReader <DatabasesShowCommandResult>(adminProvider.ExecuteControlCommand(command)); foreach (var temp in objectReader) { var db = temp.DatabaseName; var databaseJournalCommand = CslCommandGenerator.GenerateDatabaseJournalShowCommand(db); databaseJournalCommand += " | where Event == 'ADD-DATABASE' | project EventTimestamp"; using (var journalCmdResult = adminProvider.ExecuteControlCommand(db, databaseJournalCommand)) { if (journalCmdResult.Read() && DateTime.TryParse(journalCmdResult["EventTimestamp"].ToString(), out var createdTime)) { //ValhallaLogger.LogInformationalMessage(operationId, nameof(DatabaseInfoProvider), $"Database {database} Created time {createdTime.ToUniversalTime():o}"); Console.WriteLine($"Database: {db}, CreationTime: {createdTime}"); } } } } }
public static void CreateTableMappingFromDefinition(string databaseName, string table, string tableMappingName, KustoConnectionStringBuilder kustoConnectionStringBuilder, IDictionary <string, string> tableDefinition) { using (var kustoClient = KustoClientFactory.CreateCslAdminProvider(kustoConnectionStringBuilder)) { var columnMappings = new List <ColumnMapping>(); int cnt = 0; foreach (var keyvaluepair in tableDefinition) { columnMappings.Add(new ColumnMapping() { ColumnName = keyvaluepair.Key, Properties = new Dictionary <string, string>() { { MappingConsts.Ordinal, cnt.ToString() } } }); cnt++; } var command = CslCommandGenerator.GenerateTableMappingCreateCommand( Kusto.Data.Ingestion.IngestionMappingKind.Csv, table, tableMappingName, columnMappings); kustoClient.ExecuteControlCommand(databaseName, command); } }
public static void CreateTableMapping(string databaseName, string table, string tableMappingName, KustoConnectionStringBuilder kustoConnectionStringBuilder) { using (var kustoClient = KustoClientFactory.CreateCslAdminProvider(kustoConnectionStringBuilder)) { var command = CslCommandGenerator.GenerateTableMappingCreateCommand( Kusto.Data.Ingestion.IngestionMappingKind.Csv, table, tableMappingName, new[] { new ColumnMapping() { ColumnName = "id", Properties = new Dictionary <string, string>() { { MappingConsts.Ordinal, "0" } } }, new ColumnMapping() { ColumnName = "date", Properties = new Dictionary <string, string>() { { MappingConsts.Ordinal, "1" } } }, new ColumnMapping() { ColumnName = "time", Properties = new Dictionary <string, string>() { { MappingConsts.Ordinal, "2" } } }, new ColumnMapping() { ColumnName = "sym", Properties = new Dictionary <string, string>() { { MappingConsts.Ordinal, "3" } } }, new ColumnMapping() { ColumnName = "qty", Properties = new Dictionary <string, string>() { { MappingConsts.Ordinal, "4" } } }, new ColumnMapping() { ColumnName = "px", Properties = new Dictionary <string, string>() { { MappingConsts.Ordinal, "5" } } } }); kustoClient.ExecuteControlCommand(databaseName, command); } }
private void CreateOrResetTable(IDictionary <string, object> value) { using (var admin = KustoClientFactory.CreateCslAdminProvider(kscbAdmin)) { string dropTable = CslCommandGenerator.GenerateTableDropCommand(_table, true); admin.ExecuteControlCommand(dropTable); CreateMergeKustoTable(admin, value); } }
private List <string> Command(string command) { Log.Info($"command:{command}", ConsoleColor.Blue); using (ICslAdminProvider kustoAdminClient = KustoClientFactory.CreateCslAdminProvider(ManagementConnection)) { return(EnumerateResults(kustoAdminClient.ExecuteControlCommand(command))); } }
public static void CreateClient() { using (kustoAdminClient = KustoClientFactory.CreateCslAdminProvider(Kusto())) { PopulateTypesIndexData(); } typesIndexFullName = $"{KustoDatabase()}:{TypesIndex}"; }
public static void CreateClient() { using (kustoAdminClient = KustoClientFactory.CreateCslAdminProvider(Kusto())) { PopulateFunctionsData(); } functionFullName = $"{KustoDatabase()}:fn_countries_and_airports"; }
public static void IngestCsvFile(string connectionString, string tableName, string path, IEnumerable <string> tags) { using (var adminProvider = KustoClientFactory.CreateCslAdminProvider(connectionString)) { var csvData = File.ReadAllText(path); var command = CslCommandGenerator.GenerateTableIngestPushCommand( tableName, /* compressed: */ true, csvData, tags); adminProvider.ExecuteControlCommand(command); } }
/// <summary> /// Populate the Kusto backend with test data. /// </summary> /// <param name="kusto"></param> /// <param name="db"></param> /// <param name="table"></param> /// <param name="mapping"></param> /// <param name="structure">JSON file containing the Elasticsearch index structure. Elasticsearch types will be converted to Kusto types. Note that the method only supported a small set of Elasticsearch types.</param> /// <param name="dataFile">Gzipped JSON file containing the data to be loaded.</param> /// <returns>Bulk Insert operation result.</returns> public static async Task <IKustoIngestionResult> Populate(KustoConnectionStringBuilder kusto, string db, string table, string mapping, string structure, string dataFile) { var struc = JObject.Parse(structure); var properties = struc["mappings"]["_doc"]["properties"] as JObject; // Build list of columns and mappings to provision Kusto var kustoColumns = new List <string>(); var columnMappings = new List <ColumnMapping>(); foreach (var prop in properties) { string name = prop.Key; JObject value = prop.Value as JObject; string type = (string)value["type"]; if (ES2KUSTOTYPE.ContainsKey(type)) { type = ES2KUSTOTYPE[type]; } kustoColumns.Add($"{name}:{type}"); columnMappings.Add(new ColumnMapping() { ColumnName = name, Properties = new Dictionary <string, string> { ["Path"] = $"$.{name}", }, }); } using (var kustoAdminClient = KustoClientFactory.CreateCslAdminProvider(kusto)) { // Send drop table ifexists command to Kusto var command = CslCommandGenerator.GenerateTableDropCommand(table, true); kustoAdminClient.ExecuteControlCommand(command); // Send create table command to Kusto command = $".create table {table} ({string.Join(", ", kustoColumns)})"; Console.WriteLine(command); kustoAdminClient.ExecuteControlCommand(command); // Send create table mapping command to Kusto command = CslCommandGenerator.GenerateTableMappingCreateCommand(IngestionMappingKind.Json, table, mapping, columnMappings); kustoAdminClient.ExecuteControlCommand(command); } // Log information to console. // Can't use Console.WriteLine here: https://github.com/nunit/nunit3-vs-adapter/issues/266 TestContext.Progress.WriteLine($"Ingesting {dataFile} as compressed data into Kusto"); // Populate Kusto using Stream fs = File.OpenRead(dataFile); return(await KustoIngest(kusto, db, table, mapping, fs)); }
private List <string> Command(string command) { Log.Info($"command:{command}", ConsoleColor.Blue); if (_kustoAdminClient == null) { _kustoAdminClient = KustoClientFactory.CreateCslAdminProvider(ManagementConnection); adminTimer = new Timer(DisposeAdminClient, null, maxKustoClientTimeMs, maxKustoClientTimeMs); } adminTimer.Change(maxKustoClientTimeMs, maxKustoClientTimeMs); return(EnumerateResults(_kustoAdminClient.ExecuteControlCommand(command))); }
static void ResetTable(KustoConnectionStringBuilder kscb, string tableName, Type type) { using (var admin = KustoClientFactory.CreateCslAdminProvider(kscb)) { string dropTable = CslCommandGenerator.GenerateTableDropCommand(tableName, true); admin.ExecuteControlCommand(dropTable); string createTable = CslCommandGenerator.GenerateTableCreateCommand(tableName, type); admin.ExecuteControlCommand(createTable); string enableIngestTime = CslCommandGenerator.GenerateIngestionTimePolicyAlterCommand(tableName, true); admin.ExecuteControlCommand(enableIngestTime); } }
internal static void InvokeKustoCommand(string endpoint, string authority, string command) { var db = "trident"; //".create table MyLogs1 ( Level:string, Timestamp:datetime, UserId:string, TraceId:string, Message:string, ProcessId:int32 ) "; var kcsbEngine = new KustoConnectionStringBuilder(endpoint) .WithAadApplicationTokenAuthentication(AuthenticateInteractiveUser(authority)); using (var kustoAdminClient = KustoClientFactory.CreateCslAdminProvider(kcsbEngine)) { kustoAdminClient.ExecuteControlCommand(databaseName: db, command: command); } }
public static void Run([TimerTrigger("0 15,45 * * * *")] TimerInfo myTimer, ILogger log) { log.LogInformation($"C# Timer trigger function executed at: {DateTime.Now}"); var tagMinute = DateTime.Now.Minute / 30 * 30; var timeTagTemplate = @"MM/dd/yyyy HH:{0}:00"; var execTime = DateTime.Now.AddDays(-1).ToString(string.Format(timeTagTemplate, tagMinute)); 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)); var adx = KustoClientFactory.CreateCslAdminProvider(connection); string query = @" .set-or-append Metrics with (ingestIfNotExists = '[""{0}""]', tags='[""ingest-by:{0}""]') <| let PeriodStart = bin(now(-1d), 30m) - 30m; let PeriodEnd = bin(now(-1d), 30m); let Step = 1m; RawData | where Datetime between ( PeriodStart .. PeriodEnd ) | summarize Timestamp=bin(min(Timestamp), 60000), // start of minute Name=any(Name), Instance=any(Instance), Job=any(Job), Labels=any(Labels), Value=avg(Value) by bin(Datetime, Step), LabelsHash | summarize StartDatetime=min(Datetime), EndDatetime=max(Datetime), Name=any(Name), Instance=any(Instance), Job=any(Job), Labels=any(Labels), Samples=make_list( pack( 'Timestamp', Timestamp, 'Value', Value ) ) by LabelsHash "; var kql = string.Format(query, execTime); log.LogInformation($"KQL: {kql}"); adx.ExecuteControlCommand( Environment.GetEnvironmentVariable("kustoDatabase"), kql); }
internal IBigBrother UseKustoInternal(string kustoEngineName, string kustoEngineLocation, string kustoDb, string tenantId) { try { KustoDbName = kustoDb; var kustoUri = $"https://{kustoEngineName}.{kustoEngineLocation}.kusto.windows.net"; var token = new AzureServiceTokenProvider().GetAccessTokenAsync(kustoUri, string.Empty).Result; KustoAdminClient = KustoClientFactory.CreateCslAdminProvider( new KustoConnectionStringBuilder(kustoUri) { FederatedSecurity = true, InitialCatalog = KustoDbName, Authority = tenantId, ApplicationToken = token }); var kustoQueuedIngestUri = $"https://ingest-{kustoEngineName}.{kustoEngineLocation}.kusto.windows.net"; var kustoIngestUri = $"https://{kustoEngineName}.{kustoEngineLocation}.kusto.windows.net"; KustoQueuedIngestClient = KustoIngestFactory.CreateQueuedIngestClient( new KustoConnectionStringBuilder(kustoQueuedIngestUri) { FederatedSecurity = true, InitialCatalog = KustoDbName, Authority = tenantId, ApplicationToken = token }); KustoDirectIngestClient = KustoIngestFactory.CreateDirectIngestClient( new KustoConnectionStringBuilder(kustoIngestUri) { FederatedSecurity = true, InitialCatalog = KustoDbName, Authority = tenantId, ApplicationToken = token }); SetupKustoSubscription(); KustoIngestionTimeMetric = InternalClient.GetMetric(new MetricIdentifier("", "KustoIngestionTime")); } catch (Exception e) { InternalStream.OnNext(e.ToExceptionEvent()); } return(this); }
public ICslAdminProvider GetClient(string database) { var builder = new KustoConnectionStringBuilder(_hostAddress, database); if (!string.IsNullOrEmpty(_applicationKey)) { builder = builder.WithAadApplicationKeyAuthentication(_applicationClientId, _applicationKey, _authority); } else if (!string.IsNullOrEmpty(_applicationCertificateThumbprint)) { builder = builder.WithAadApplicationThumbprintAuthentication(_applicationClientId, _applicationCertificateThumbprint, _authority); } return(KustoClientFactory.CreateCslAdminProvider(builder)); }
/// <summary> /// Check table for existense of JSON mapping and create one if necessary /// </summary> /// <param name="kcsb">KustoConnectionStringBuilder object configured to connect to the cluster</param> /// <param name="databaseName">Name of the database</param> /// <param name="tableName">Name of the table</param> static void CreateJsonMappingIfNotExists(KustoConnectionStringBuilder kcsb, string databaseName, string tableName) { using (var adminClient = KustoClientFactory.CreateCslAdminProvider(kcsb)) { var showMappingsCommand = CslCommandGenerator.GenerateTableJsonMappingsShowCommand(tableName); var existingMappings = adminClient.ExecuteControlCommand <IngestionMappingShowCommandResult>(databaseName, showMappingsCommand); if (existingMappings.FirstOrDefault(m => String.Equals(m.Name, s_jsonMappingName, StringComparison.Ordinal)) != null) { return; } var createMappingCommand = CslCommandGenerator.GenerateTableJsonMappingCreateCommand(tableName, s_jsonMappingName, s_jsonMapping); adminClient.ExecuteControlCommand(databaseName, createMappingCommand); } }
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>())); }
/// <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; } }
static void Main(string[] args) { int currentCount = 0; List <DBReader> DBData = new List <DBReader>(); var builder = new KustoConnectionStringBuilder("https://masvaas.kusto.windows.net/").WithAadUserPromptAuthentication(); //int count = 0; using (var adminProvider = KustoClientFactory.CreateCslAdminProvider(builder)) { var command = CslCommandGenerator.GenerateDatabasesShowCommand(); var objectReader = new ObjectReader <DatabasesShowCommandResult>(adminProvider.ExecuteControlCommand(command)); foreach (var temp in objectReader) { currentCount++; var db = temp.DatabaseName; var databaseJournalCommand = CslCommandGenerator.GenerateDatabaseJournalShowCommand(db); databaseJournalCommand += " | where Event == 'ADD-DATABASE' | project EventTimestamp"; //hardcoded queries to get the created time for db using journal command using (var journalCmdResult = adminProvider.ExecuteControlCommand(db, databaseJournalCommand)) { ///List<DateTime> dates= new List<string>() journalCmdResult["EventTimestamp"]; if (journalCmdResult.Read() && DateTime.TryParse(journalCmdResult["EventTimestamp"].ToString(), out var createdTime)) { DBReader DBRead = new DBReader(); DBRead.databasename = db; DBRead.Timestamp = createdTime.Ticks; DBData.Add(DBRead); } } } } var sorted = from d in DBData orderby d.Timestamp descending select d; foreach (var c in sorted) { Console.WriteLine($"Database: {c.databasename} Timestamp: {new DateTime(c.Timestamp)}"); } }
public string InvokeKustoCommand([FromBody] InvokeInputObj invokeInputObj)//string endpoint, string authority, string command,string clientId,string clientSecret) { var db = "trident"; //".create table MyLogs1 ( Level:string, Timestamp:datetime, UserId:string, TraceId:string, Message:string, ProcessId:int32 ) "; var kcsbEngine = new KustoConnectionStringBuilder(invokeInputObj.endpoint) .WithAadApplicationTokenAuthentication(AuthenticateInteractiveUser(invokeInputObj.authority, invokeInputObj.clientId, invokeInputObj.clientSecret)); using (var kustoAdminClient = KustoClientFactory.CreateCslAdminProvider(kcsbEngine)) { var commands = Regex.Split(invokeInputObj.commands, ".create", RegexOptions.IgnoreCase); for (int i = 1; i < commands.Length; i++) { var command = ".create " + commands[i]; kustoAdminClient.ExecuteControlCommand(db, command); } } return("the command is finished"); }
/// <summary> /// Populate the Kusto backend with test data. /// </summary> /// <param name="kusto">Kusto connection string builder.</param> /// <param name="db">Kusto database.</param> /// <param name="table">Kusto table within database.</param> /// <returns>Bulk Insert operation result.</returns> public static async Task <IDataReader> PopulateTypesIndex(KustoConnectionStringBuilder kusto, string db, string table) { // Build list of columns and mappings to provision Kusto var kustoColumns = new List <string>(); foreach (var entry in KustoColumnType) { kustoColumns.Add($"{entry.Key}:{entry.Value}"); } using var kustoAdminClient = KustoClientFactory.CreateCslAdminProvider(kusto); // Send drop table ifexists command to Kusto var command = CslCommandGenerator.GenerateTableDropCommand(table, true); await kustoAdminClient.ExecuteControlCommandAsync(db, command); // Send create table command to Kusto command = $".create table {table} ({string.Join(", ", kustoColumns)})"; Console.WriteLine(command); return(await kustoAdminClient.ExecuteControlCommandAsync(db, command)); }
public static async Task <IActionResult> AdxExportFunctionHttpTriggered( [HttpTrigger(AuthorizationLevel.Function, "post")] HttpRequest req, ILogger log, [DurableClient] IDurableOrchestrationClient starter) { log.LogInformation("AdxExportFunction processed a request."); var requestBody = await new StreamReader(req.Body).ReadToEndAsync(); var exportRequest = JsonConvert.DeserializeObject <AdxExportRequest>(requestBody); if (exportRequest == null || string.IsNullOrEmpty(exportRequest.AdxQuery) || string.IsNullOrEmpty(exportRequest.UserEmailAddress)) { return(new BadRequestObjectResult("Invalid ADX export request. Check your parameters")); } using (var client = KustoClientFactory.CreateCslAdminProvider(await GetKustoConnectionStringBuilder())) { // TODO: Write actual query from user input var exportQuery = CslCommandGenerator.GenerateExportCommand(new[] { storageConnectionString }, "datatable1 | take 100", true, true); var resultReader = new ObjectReader <DataExportToBlobCommandResult>(client.ExecuteControlCommand(adxDatabaseName, exportQuery)); var res = resultReader?.FirstOrDefault(); var adxExportOperationId = res?.Path; if (!string.IsNullOrEmpty(adxExportOperationId)) { // Start durable orchestrator for the status checking var durableInstanceId = await starter.StartNewAsync(nameof(AdxExportOrchestrator), new Tuple <string, string>(adxExportOperationId, exportRequest.UserEmailAddress)); return(new OkObjectResult("Request Accepted. Durable Instance=" + durableInstanceId)); } else { log.LogError("Path is empty in result"); log.LogError(JsonConvert.SerializeObject(res)); } } return(new BadRequestObjectResult("Error on starting ADX export")); }
/// <summary> /// Constructor which creates a connection to the workspace cluster and uses the workspace database to temporarily load the schema /// </summary> public QueryEngine() { if (string.IsNullOrEmpty(SettingsWrapper.KustoClusterForTempDatabases)) { throw new ArgumentNullException(nameof(SettingsWrapper.KustoClusterForTempDatabases)); } _databaseName = SettingsWrapper.TemporaryKustoDatabase; var connString = new KustoConnectionStringBuilder(SettingsWrapper.KustoClusterForTempDatabases) { FederatedSecurity = true, InitialCatalog = _databaseName, Authority = SettingsWrapper.AADAuthority }; _adminClient = KustoClientFactory.CreateCslAdminProvider(connString); _queryClient = KustoClientFactory.CreateCslQueryProvider(connString); _tempDatabaseUsed = true; _cluster = connString.DataSource; CleanDatabase(); }