/// <summary> /// Generates a Kusto table for a specific <see cref="Type"/>, by mapping it's properties to columns. /// </summary> /// <param name="client">The <see cref="ICslAdminProvider"/> that we are extending.</param> /// <param name="type">The <see cref="Type"/> that we are generating a table for.</param> /// <returns>The name of the table created.</returns> public static async Task <string> GenerateTableFromType(this ICslAdminProvider client, Type type) { var tableName = type.Name; var tables = new List <string>(); var command = CslCommandGenerator.GenerateTablesShowCommand(); var reader = await client.ExecuteControlCommandAsync(client.DefaultDatabaseName, command); while (reader.Read()) { tables.Add(reader.GetString(0)); } if (tables.Contains(tableName)) { return(tableName); } var columns = type.GetProperties().Select(property => Tuple.Create(property.Name, property.PropertyType.FullName)).ToList(); command = CslCommandGenerator.GenerateTableCreateCommand(tableName, columns); await client.ExecuteControlCommandAsync(client.DefaultDatabaseName, command); return(tableName); }
/// <summary> /// Generates a Kusto table mapping for a specific <see cref="Type"/>, by mapping it's properties to column mappings. /// </summary> /// <param name="client">The <see cref="ICslAdminProvider"/> client that we are extending.</param> /// <param name="type">The <see cref="Type"/> that we are generating the JSON mapping for.</param> /// <returns>The name of the mapping created.</returns> public static async Task <string> GenerateTableJsonMappingFromType(this ICslAdminProvider client, Type type) { var tableName = type.Name; var mappingName = $"{tableName}_mapping"; var tableMappings = new List <string>(); var command = CslCommandGenerator.GenerateTableJsonMappingsShowCommand(tableName); var reader = await client.ExecuteControlCommandAsync(client.DefaultDatabaseName, command); while (reader.Read()) { tableMappings.Add(reader.GetString(0)); } if (tableMappings.Contains(mappingName)) { return(mappingName); } var mappings = type.GetProperties().Select(property => new JsonColumnMapping { ColumnName = property.Name, JsonPath = $"$.{property.Name}" }).ToList(); command = CslCommandGenerator.GenerateTableJsonMappingCreateCommand(tableName, mappingName, mappings); await client.ExecuteControlCommandAsync(client.DefaultDatabaseName, command); return(mappingName); }
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); } }
private static void PopulateTypesIndexData() { // Build list of columns and mappings to provision Kusto var kustoColumns = new List <string>(); var columnMappings = new List <JsonColumnMapping>(); foreach (var entry in KustoColumnType) { var name = entry.Key; kustoColumns.Add($"{name}:{entry.Value}"); columnMappings.Add(new JsonColumnMapping() { ColumnName = name, JsonPath = $"$.{name}" }); } // Send drop table ifexists command to Kusto var command = CslCommandGenerator.GenerateTableDropCommand(TypesIndex, true); KustoExecute(command); // Send create table command to Kusto command = $".create table {TypesIndex} ({string.Join(", ", kustoColumns)})"; Console.WriteLine(command); KustoExecute(command); // Send create table mapping command to Kusto command = CslCommandGenerator.GenerateTableJsonMappingCreateCommand( TypesIndex, TypesMapping, columnMappings, true); KustoExecute(command); command = ".append types_index <|" + "print x = true, datetime('2020-02-23T07:22:29.1990163Z'), guid(74be27de-1e4e-49d9-b579-fe0b331d3642), int(17), long(17), real(0.3), 'string type', 30m, decimal(0.3), dynamic({'a':123, 'b':'hello'})"; KustoExecute(command); }
/// <summary> /// Generates a Kusto table mapping for a specific <see cref="Type"/>, by mapping it's properties to column mappings. /// </summary> /// <param name="client">The <see cref="ICslAdminProvider"/> client that we are extending.</param> /// <param name="type">The <see cref="Type"/> that we are generating the JSON mapping for.</param> /// <returns>The name of the mapping created.</returns> public static string GenerateTableJsonMappingFromType(this ICslAdminProvider client, Type type) { var tableName = type.Name; var mappingName = $"{tableName}_mapping"; var command = CslCommandGenerator.GenerateTableJsonMappingShowCommand(tableName, mappingName); try { client.ExecuteControlCommand(command); return(mappingName); } catch (KustoBadRequestException ex) when(ex.ErrorMessage.Contains("'JsonMappingPersistent' was not found")) { // soak } var mappings = type.GetProperties().Select(property => new JsonColumnMapping { ColumnName = property.Name, JsonPath = $"$.{property.Name}" }).ToList(); command = CslCommandGenerator.GenerateTableJsonMappingCreateCommand(tableName, mappingName, mappings); client.ExecuteControlCommand(command); return(mappingName); }
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 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)"); } }
public void CreateTableIfNotExists(string table, string mappingName) { try { using (var kustoAdminClient = KustoClientFactory.CreateCslAdminProvider(_kustoConnectionStringBuilder)) { // check if already exists. var showTableCommands = CslCommandGenerator.GenerateTablesShowDetailsCommand(); var existingTables = kustoAdminClient.ExecuteControlCommand <IngestionMappingShowCommandResult>(DatabaseName, showTableCommands).Select(x => x.Name).ToList(); if (existingTables.Contains(table)) { Logger.Info($"Table {table} already exists"); return; } // Create Columns var command = CslCommandGenerator.GenerateTableCreateCommand(table, GetColumns()); kustoAdminClient.ExecuteControlCommand(databaseName: DatabaseName, command: command); // Create Mapping command = CslCommandGenerator.GenerateTableJsonMappingCreateCommand( table, mappingName, GetJsonColumnMappings()); kustoAdminClient.ExecuteControlCommand(databaseName: DatabaseName, command: command); } } catch (Exception ex) { Logger.Error($"Cannot create table due to {ex}"); } }
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 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 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); } }
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)); }
public static string GenerateTableJsonMappingCreateOrAlterCommand(KustoTableInfo kustoTable, string mappingName) { if (kustoTable == null) { throw new ArgumentNullException(nameof(kustoTable)); } var mapping = kustoTable.Columns.Select(BuildColumnMapping).ToList(); return(CslCommandGenerator.GenerateTableMappingCreateOrAlterCommand( IngestionMappingKind.Json, kustoTable.TableName, mappingName, mapping)); }
public static string GenerateTableCreateCommand(KustoTableInfo kustoTable) { if (kustoTable == null) { throw new ArgumentNullException(nameof(kustoTable)); } var columns = kustoTable.Columns .Select(column => new Tuple <string, Type>(column.Value.Name, column.Value.Type)); return(CslCommandGenerator.GenerateTableCreateCommand(kustoTable.TableName, columns)); }
public void AlterTablePolicy(string tableName, string databaseName, IEnumerable <DataUpdatePolicy> dataUpdatePolicies) { try { var command = CslCommandGenerator.GenerateTableUpdatePoliciesAlterCommand(databaseName, tableName, dataUpdatePolicies); this.client.ExecuteControlCommand(databaseName, command); } catch (Exception e) { throw e; } }
public void EnableStreamingIngestionPolicyToTable(string tableName, string databaseName) { try { var command = CslCommandGenerator.GenerateTableAlterStreamingIngestionPolicyCommand(tableName, true); this.client.ExecuteControlCommand(databaseName, command); } catch (Exception e) { throw e; } }
public void AlterTableRetentionPolicy(string tableName, string databaseName, TimeSpan?softDeletePeriod, DataRecoverability recoverability = DataRecoverability.Unknown) { try { var command = CslCommandGenerator.GenerateTableAlterRetentionPolicyCommand(databaseName, tableName, softDeletePeriod, recoverability); this.client.ExecuteControlCommand(databaseName, command); } catch (Exception e) { throw e; } }
private Task WriteFunctionsAsync( string outputFolder, IEnumerable <FunctionSchema> functions, CancellationToken cancellationToken) { return(WriteSchemaToFileAsync( Path.Combine(outputFolder, "Functions"), functions.ToArray(), f => f.Name, f => f.Folder, f => CslCommandGenerator.GenerateCreateOrAlterFunctionCommand(f, false), cancellationToken)); }
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); } }
public void CreateTable(string tableName, IEnumerable <Tuple <string, string> > rowFields, string databaseName) { try { var command = CslCommandGenerator.GenerateTableCreateCommand( tableName, rowFields); this.client.ExecuteControlCommand(databaseName, command); } catch (Exception e) { throw e; } }
/// <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 void CreateTableMapping(string tableMappingName, IEnumerable <ColumnMapping> mapping, string tableName, string databaseName) { try { var command = CslCommandGenerator.GenerateTableMappingCreateCommand( Kusto.Data.Ingestion.IngestionMappingKind.Json, tableName, tableMappingName, mapping); this.client.ExecuteControlCommand(databaseName, command); } catch (Exception e) { throw e; } }
private Task WriteTablesAsync( string outputFolder, IEnumerable <TableSchema> tables, IReadOnlyDictionary <string, IngestionMapping[]> ingestionMappingLookup, CancellationToken cancellationToken) { return(WriteSchemaToFileAsync( Path.Combine(outputFolder, "Tables"), tables.ToArray(), t => t.Name, t => t.Folder, t => { // Add new lines so we generate a file that will diff nicely in source control var createOrMergeTableCommand = CslCommandGenerator .GenerateTableCreateMergeCommand(t) .Replace("(", "(\n ") .Replace(", ", ",\n ") .Replace(")", "\n)"); var builder = new StringBuilder(); builder.AppendLine(createOrMergeTableCommand); if (ingestionMappingLookup.TryGetValue(t.Name, out var ingestionMappings)) { foreach (var ingestionMapping in ingestionMappings.OrderBy(m => m.Kind).ThenBy(m => m.Name)) { builder.AppendLine(); builder.AppendLine(); builder.AppendLine($".create-or-alter table {t.Name} ingestion {ingestionMapping.Kind.ToLower()} mapping \"{ingestionMapping.Name}\""); builder.AppendLine("'['"); for (var i = 0; i < ingestionMapping.ColumnMappings.Count; i++) { var columnMapping = ingestionMapping.ColumnMappings[i]; builder.Append($"' {JsonConvert.SerializeObject(columnMapping, new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore })}"); builder.Append(i == ingestionMapping.ColumnMappings.Count - 1 ? "" : ","); builder.AppendLine("'"); } builder.AppendLine("']'"); } } return builder.ToString(); }, cancellationToken)); }
private void CreateMergeKustoTable(ICslAdminProvider admin, IDictionary <string, object> value) { TableSchema tableSchema = new TableSchema(TableName); foreach (var pair in value) { tableSchema.AddColumnIfMissing(new ColumnSchema(pair.Key, _columnType[pair.Value != null ? pair.Value.GetType() : typeof(string)])); } string createTable = CslCommandGenerator.GenerateTableCreateMergeCommand(tableSchema); admin.ExecuteControlCommand(createTable); string enableIngestTime = CslCommandGenerator.GenerateIngestionTimePolicyAlterCommand(TableName, true); admin.ExecuteControlCommand(enableIngestTime); }
public void CreateOrAlterFunction(string functionName, IDictionary <string, string> parameters, string body, string databaseName) { try { if (parameters == null) { parameters = new Dictionary <string, string>(); } var command = CslCommandGenerator.GenerateCreateOrAlterFunctionCommand(functionName, null, null, parameters, body); this.client.ExecuteControlCommand(databaseName, command); } catch (Exception e) { throw e; } }
/// <summary> /// Wrap the call to CslCommandGenerator.GenerateTableCreateCommand and allow special formatting if the user /// has enabled the setting flag for it. Also choose between "create" and "create merge" based on setting /// </summary> /// <param name="table">The table schema to convert to a string</param> /// <param name="forceNormalizeColumnName">True to force the column names to be normalized/escaped</param> /// <returns></returns> public static string GenerateTableCreateCommand(TableSchema table, bool forceNormalizeColumnName = false) { string result = SettingsWrapper.CreateMergeEnabled == true ? CslCommandGenerator.GenerateTableCreateMergeCommandWithExtraProperties(table, forceNormalizeColumnName) : CslCommandGenerator.GenerateTableCreateCommand(table, forceNormalizeColumnName); if (SettingsWrapper.TableFieldsOnNewLine == true) { // Add a line break between each field result = result.Replace(", ['", ",\r\n ['"); // Add a line break before the first field int parameterStartIndex = result.LastIndexOf("(["); result = result.Insert(parameterStartIndex + 1, "\r\n "); } return(result); }
/// <summary> /// Write a table to the file system /// </summary> /// <param name="tableSchema">The table to write</param> /// <param name="rootFolder">The root folder for all the CSL files</param> public static void WriteToFile(this TableSchema tableSchema, string rootFolder) { string tableFolder = rootFolder; if (!string.IsNullOrEmpty(tableSchema.Folder)) { string cleanedFolder = string.Join("", tableSchema.Folder.Split(Path.GetInvalidPathChars())); tableFolder = Path.Combine(rootFolder, "Tables", cleanedFolder); } string destinationFile = Path.Combine(tableFolder, tableSchema.Name + ".csl"); if (!Directory.Exists(tableFolder)) { Directory.CreateDirectory(tableFolder); } File.WriteAllText(destinationFile, CslCommandGenerator.GenerateTableCreateCommand(tableSchema, true)); }
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)}"); } }