Get-DbaDbMailAccount & Get-DbaDbMailProfile to return Account-Profile link details

MikeyBronowski commented 3 years ago

Summarize Functionality

In SSMS it's possible to see database mail accounts configured for the database mail profile

Similar to details in

select * from msdb.dbo.sysmail_profileaccount

I would propose adding this functionality to either Get-DbaDbMailAccount or Get-DbaDbMailProfile or even both. We could also create a separate cmdlet Get-DbaDbMailProfileAccount but it might be overkill.

Accounts have the GetAccountProfileNames method and Profiles the EnumAccounts method.

This way both commands would have the following output (new property added to the right):

Get-DbaDbMailAccount -SqlInstance $s1 -SqlCredential $credential  | ft
ComputerName InstanceName SqlInstance  ID Name         DisplayName        Description EmailAddress ReplyToAddress IsBusyAccount MailServers MailProfile                
------------ ------------ -----------  -- ----         -----------        ----------- ------------ -------------- ------------- ----------- -----------                
localhost    MSSQLSERVER  6f34ddb8eb52 12 MailAccount  MailAccountDisplay             dd@ss.com                           False {}          {MailProfile}              
localhost    MSSQLSERVER  6f34ddb8eb52 13 MailAccount2 MailAccountDisplay             dd@ss.com                           False {}          {MailProfile, MailProfile2}

Get-DbaDbMailProfile -SqlInstance $s1 -SqlCredential $credential | ft
ComputerName InstanceName SqlInstance   ID Name                 Description ForceDeleteForActiveProfiles IsBusyProfile MailAccount                
------------ ------------ -----------   -- ----                 ----------- ---------------------------- ------------- -----------                
localhost    MSSQLSERVER  6f34ddb8eb52 164 MailProfile                                              True         False {MailAccount, MailAccount2}
localhost    MSSQLSERVER  6f34ddb8eb52 165 MailProfile2                                             True         False MailAccount2

I also have a draft for Set-* function, so will open the FR later.

Is there a command that is similiar or close to what you are looking for?

No

Technical Details

I tried this way - wrap both lists in a loop to add the Profiles or Accounts.

Get-DbaDbMailAccount https://github.com/sqlcollaborative/dbatools/blob/2d4ac0da63f1845caa76aaf65a535e93d8c197b7/functions/Get-DbaDbMailAccount.ps1#L98-L101

foreach ($acct in $accounts) {
    $acct | Add-Member -Force -MemberType NoteProperty -Name ComputerName -value $mailserver.ComputerName
    $acct | Add-Member -Force -MemberType NoteProperty -Name InstanceName -value $mailserver.InstanceName
    $acct | Add-Member -Force -MemberType NoteProperty -Name SqlInstance -value $mailserver.SqlInstance
    $acct | Add-Member -Force -MemberType NoteProperty -Name MailProfile -value $acct.GetAccountProfileNames()
}
    $accounts | Select-DefaultView -Property ComputerName, InstanceName, SqlInstance, ID, Name, DisplayName, Description, EmailAddress, ReplyToAddress, IsBusyAccount, MailServers, MailProfile

Get-DbaDbMailProfile https://github.com/sqlcollaborative/dbatools/blob/2d4ac0da63f1845caa76aaf65a535e93d8c197b7/functions/Get-DbaDbMailProfile.ps1#L106-L110

foreach ($prof in $profiles) {
    $prof | Add-Member -Force -MemberType NoteProperty -Name ComputerName -Value $mailserver.ComputerName
    $prof | Add-Member -Force -MemberType NoteProperty -Name InstanceName -Value $mailserver.InstanceName
    $prof | Add-Member -Force -MemberType NoteProperty -Name SqlInstance -Value $mailserver.SqlInstance
    $prof | Add-Member -Force -MemberType NoteProperty -Name MailAccount -Value $prof.EnumAccounts().AccountName
}
    $profiles | Select-DefaultView -Property ComputerName, InstanceName, SqlInstance, ID, Name, Description, ForceDeleteForActiveProfiles, IsBusyProfile, MailAccount

PowerDBAKlaas commented 2 years ago

Hi Mikey

thanks for this: the profile-account association is indeed missing and interesting. Since in both cases the associations are fetched via a method, I assume it adds some searching. If this is minimal, I'm in favour of the way you demo. If the delay is noticeable, it's better to only provide it with a switch param, or indeed put it in a separate function. Do you have some metrics about the added functionality?

MikeyBronowski commented 2 years ago

Hey, @PowerDBAKlaas sorry no metrics for that.

dataplat / dbatools