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