public async Task <SqlResourceDto> GetSqlVirtualMachineResourceAsync(ResourceDto _sqlResource) { if (_sqlResource == null || _sqlResource == default(ResourceDto)) { return(null); } DefaultAzureCredential credential; if (_sqlResourceInfoInventoryConfiguration.UseSystemAssignedManagedIdentity) { credential = new DefaultAzureCredential(); } else { credential = new DefaultAzureCredential(new DefaultAzureCredentialOptions { ManagedIdentityClientId = _sqlResourceInfoInventoryConfiguration.UserAssignedManagedIdentityClientId }); } TokenRequestContext tokenRequestContext = new TokenRequestContext(new[] { Constants.AzureResourceManagerAPIDefaultScope }); AccessToken tokenRequestResult = await credential.GetTokenAsync(tokenRequestContext); ServiceClientCredentials serviceClientCreds = new TokenCredentials(tokenRequestResult.Token); Microsoft.Azure.Management.SqlVirtualMachine.SqlVirtualMachineManagementClient sqlClient = new Microsoft.Azure.Management.SqlVirtualMachine.SqlVirtualMachineManagementClient(serviceClientCreds); sqlClient.SubscriptionId = _sqlResource.subscriptionId; var response = await sqlClient.SqlVirtualMachines.GetWithHttpMessagesAsync(_sqlResource.resourceGroup, _sqlResource.name); SqlVirtualMachineModel server = response.Body; if (server != null) { SqlResourceDto newServer = new SqlResourceDto() { ServerNameId = server.Id, ServerName = server.Name, SubscriptionId = _sqlResource.subscriptionId, Type = _sqlResource.type }; return(newServer); } return(null); }
public async Task <SqlResourceDto> GetSqlServerResourceAsync(ResourceDto _sqlResource) { if (_sqlResource == null || _sqlResource == default(ResourceDto)) { return(null); } DefaultAzureCredential credential; if (_sqlResourceInfoInventoryConfiguration.UseSystemAssignedManagedIdentity) { credential = new DefaultAzureCredential(); } else { credential = new DefaultAzureCredential(new DefaultAzureCredentialOptions { ManagedIdentityClientId = _sqlResourceInfoInventoryConfiguration.UserAssignedManagedIdentityClientId }); } TokenRequestContext tokenRequestContextGraph = new TokenRequestContext(new[] { Constants.AzureResourceGraphAPIDefaultScope }); AccessToken tokenRequestResultGraph = await credential.GetTokenAsync(tokenRequestContextGraph); TokenRequestContext tokenRequestContextARM = new TokenRequestContext(new[] { Constants.AzureResourceManagerAPIDefaultScope }); AccessToken tokenRequestResultARM = await credential.GetTokenAsync(tokenRequestContextARM); // Credentials used for authenticating a fluent management client to Azure. AzureCredentials credentials = new AzureCredentials( new TokenCredentials(tokenRequestResultARM.Token), new TokenCredentials(tokenRequestResultGraph.Token), _sqlResource.tenantId, AzureEnvironment.AzureGlobalCloud); // Top level abstraction of Azure. https://docs.microsoft.com/en-us/dotnet/api/microsoft.azure.management.fluent.iazure?view=azure-dotnet // .WithSubscription is optional if you wish to return resource beyond the scope of a single subscription. IAzure azure = Microsoft.Azure.Management.Fluent.Azure .Configure() .WithLogLevel(HttpLoggingDelegatingHandler.Level.Basic) .Authenticate(credentials) .WithSubscription(_sqlResource.subscriptionId); // Iterate through Microsoft.Sql top level resources (servers) and a list of databases (sub resources) // for data collection define IList<T> outside of these nested loops and add resources and sub resources // of interest to collections. ISqlServer server = await azure.SqlServers.GetByIdAsync(_sqlResource.id); if (server != null) { SqlResourceDto newServer = new SqlResourceDto() { ServerNameId = server.Id, ServerName = server.Name, SubscriptionId = _sqlResource.subscriptionId, AdminLogin = server.AdministratorLogin, Type = _sqlResource.type }; IReadOnlyList <ISqlDatabase> databasess = await server.Databases.ListAsync(); foreach (ISqlDatabase database in databasess) { SqlServerDatabaseDto db = new SqlServerDatabaseDto() { ServerNameId = server.Id, DatabaseName = database.Name, ServerName = server.Name, SubscriptionId = _sqlResource.subscriptionId, CreatedOn = database.CreationDate }; newServer.Databases.Add(db); } return(newServer); } return(null); }
public async Task Run ([QueueTrigger("outsqlqueue", Connection = "StorageConnectionAppSetting")] ResourceDto input, [Table("InventorySqlResource", Connection = "StorageConnectionAppSetting")] CloudTable sqlInfoTable, [Table("InventorySqlResourceDatabases", Connection = "StorageConnectionAppSetting")] CloudTable sqlDatabasesTable, ILogger log) { log.LogInformation($"C# Sql Resource Info Collector queue trigger function executed at: {DateTime.UtcNow}."); SqlResourceDto sqlresource = null; if (input.type == "microsoft.sql/servers") { sqlresource = await _sqlResourceInfoInventoryService.GetSqlServerResourceAsync(input); } else if (input.type == "microsoft.sqlvirtualmachine/sqlvirtualmachines") { sqlresource = await _sqlResourceInfoInventoryService.GetSqlVirtualMachineResourceAsync(input); } //TO DO: managed instance if (sqlresource != null) { // add in sql resource into table await sqlInfoTable.CreateIfNotExistsAsync(); DateTime init = DateTime.Now; SqlResourceEntity sqlEntity = new SqlResourceEntity() { PartitionKey = sqlresource.SubscriptionId, RowKey = sqlresource.ServerName, ServerNameId = sqlresource.ServerNameId, AdminLogin = sqlresource.AdminLogin, Type = sqlresource.Type, CreatedOn = init, LastSeenOn = init }; TableOperation retrieveOperation = TableOperation.Retrieve <SqlResourceEntity>(sqlresource.SubscriptionId, sqlresource.ServerName); TableResult retrievedEntity = await sqlInfoTable.ExecuteAsync(retrieveOperation); if (retrievedEntity.Result == null) { TableOperation insertOperation = TableOperation.Insert(sqlEntity); await sqlInfoTable.ExecuteAsync(insertOperation); } else { sqlEntity.CreatedOn = ((SqlResourceEntity)retrievedEntity.Result).CreatedOn; TableOperation mergeOperation = TableOperation.InsertOrMerge(sqlEntity); await sqlInfoTable.ExecuteAsync(mergeOperation); } // cycle through sql resource databases and insert them into a table if (sqlresource.Databases != null) { // add in sql resource into table await sqlDatabasesTable.CreateIfNotExistsAsync(); foreach (SqlServerDatabaseDto db in sqlresource.Databases) { SqlServerDatabaseEntity subEntity = new SqlServerDatabaseEntity() { PartitionKey = db.ServerName, RowKey = db.DatabaseName, SubscriptionId = db.SubscriptionId, ServerNameId = db.ServerNameId, CreatedOn = init, LastSeenOn = init }; TableOperation retrieveOperationDb = TableOperation.Retrieve <SqlServerDatabaseEntity>(db.ServerName, db.DatabaseName); TableResult retrievedEntityDb = await sqlDatabasesTable.ExecuteAsync(retrieveOperationDb); if (retrievedEntityDb.Result == null) { TableOperation insertOperation = TableOperation.Insert(subEntity); await sqlDatabasesTable.ExecuteAsync(insertOperation); } else { subEntity.CreatedOn = ((SqlServerDatabaseEntity)retrievedEntityDb.Result).CreatedOn; TableOperation mergeOperation = TableOperation.InsertOrMerge(subEntity); await sqlDatabasesTable.ExecuteAsync(mergeOperation); } } } } }