Azure Automation – Distribution Group

I’ve been wanting to expand my skills and abilities within the Azure platform recently, and this week a great opportunity arose for this very thing.

We provide dynamic distribution groups within Office 365 for staff, based upon AD attributes populates with DirSync. The primary example here is our “SubCompany All Staff” list, which is created with this query filter:

New-DynamicDistributionGroup -alias SubCompanyAllStaff -Name "SubCompany All Staff" -RecipientFilter { ((RecipientType -eq 'UserMailbox') -and (Company -like 'SubCompanyName*')) }

Now we have executive staff who are transitioning from this SubCompany to a parent company, which is causing an issue. Their Company attributes are no longer “SubCompanyName”, but they wish to remain on the distribution list to receive communications. However, IT does not (will not) maintain a manual membership list for this type of group. We could create an “exceptions” group, add these staff in, mail-enable the group and make sure it’s part of the query filter above, but this means every time someone changes company, we have to remember to add them into the exception group.

A better way is to filter on the ProxyAddress attribute; anyone who has an email should be part of the distribution group. Those who switch companies maintain legacy addresses as aliases. For example:

Get-Recipient -Filter {EmailAddresses -like **}

However, while the Get-Recipient command works here, this type of filtering is NOT supported within a dynamic group recipient filter; it returns no results.

This leads me to the solution: use an Azure Automation runbook to run the Get-Recipient command to populate a static group on a schedule.

The Query

First, I needed a functioning PowerShell query. I initially arrived at this here:

# Add users matching criteria to list.
$users = Get-Recipient -Filter {EmailAddresses -like "**" -and RecipientTypeDetailsValue -eq 'UserMailbox'}
foreach($user in $users) { Add-DistributionGroupMember -Identity -member $user.WindowsLiveID }
# Remove those who no longer have an alias
$users = Get-DistributionGroupMember -Identity "Company TestAll" | Get-Recipient -Filter {EmailAddresses -notlike "**"} 
foreach ($user in $users) {Remove-DistributionGroupMember -Identity "Company TestAll" -member $user.WindowsLiveID -confirm:$false}

While this did perform what I was after, it was messy. I wanted this to run on a schedule, so the first ‘addition’ command would effectively error out on the majority of the group members. When I tried to run the second command as my service account in the run book, it threw a large amount of errors about the users not being found on the domain controller.

After a lot of back-and-forth testing, I had this command that works perfectly:

Update-DistributionGroupMember "Company TestAll" -Members (Get-Recipient -Filter {EmailAddresses -like "**" -and RecipientTypeDetailsValue -eq 'UserMailbox'}|select -ExpandProperty Alias) -confirm:$false



Having never looked at Azure Automation before, I started reading. This blog post was a helpful start, and I went through quite a bit of the site reading about Runbooks, PowerShell vs. PowerShell Workflow, credential access, and so on.

I created an Automation account that is intended to be specific to Office 365 scripts, along with a corresponding Resource Group. Since this isn’t going to touch any other Azure resources, I did not create a RunAs account with it.

Next I began looking at the best way to connect to Exchange Online. I quickly found this module which looked very promising. However, after importing it as an Asset in the account, the example given of “Set-ModuleCredential” wasn’t found as a valid cmdlet, and I couldn’t see it in the .psm1 file either. Since there were many other examples of connecting to Exchange, I moved on.

The next problem I found was that when I tried to do the standard RemoteSession to connect, testing the runbook would fail without any meaningful error message; just 3 attempts and then “suspended”. Based on that behavior, I found this on the feedback portal for Azure, which is exactly what was happening. The method provided by Rune Myrhaug works great:

# Pull credential from Automation assets
$credObject = Get-AutomationPSCredential -Name "svc_auto_azure"
#Connect to Exchange Online
$ExchangeOnlineSession = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri -Credential $credObject -Authentication Basic -AllowRedirection
Import-Module (Import-PSSession -Session $ExchangeOnlineSession -AllowClobber -DisableNameChecking) -Global

I created a service account in Active Directory, synchronized it to O365 with DirSync, and then used the Exchange Admin portal to ensure it had appropriate roles to manage Distribution Groups. I added this account in as a credential asset, and created a daily schedule as well.

The final step was to publish the runbook and associate it with my schedule, and now I have an automated quasi-dynamic distribution group!

I can forsee this potentially being useful for groups that are not built along attribute filters. Perhaps something like a SharePoint list that is populated by administrative staff or department heads, which is then consumed in the runbook and applied to a distribution group.




Sage 300 PayStub Self-Service PDF export

In April we deployed an add-on for our Sage 300 payroll software, which provides a self-service web interface for employees to access their pay stubs.

We recently discovered that when an employee is set to Inactive or terminated within Sage 300, their access to the portal stops working, which is a good thing. However, in our case this employee had simply transferred to the parent company, and still required access.

The self-service module is self-contained code which I can’t modify, so instead I sought out a method of extracting the PDF paystub from the database for a particular user.

Most of the information below was derived from this StackOverflow question and answer.

First, configure your SQL server to allow advanced command line commands:

EXEC sp_configure 'show advanced options', 1;  
EXEC sp_configure 'xp_cmdshell',1  

Next, you need to export a format file for the specific table we’re looking for. I used this command from my own client computer which has SQL Management Studio installed:

bcp dbo.PAYROLLCHECK format nul -S servername -T -d COMDat -n -f formatfile.fmt

This produced a file which looked like the following:

1       SQLCHAR             0       12      ""   1     EMPLOYEEID                     SQL_Latin1_General_CP1_CI_AS
2       SQLDATETIME         0       8       ""   2     PERIODENDDATE                  ""
3       SQLINT              0       4       ""   3     ENTRYSEQ                       ""
4       SQLIMAGE            4       0       ""   4     PDF                            ""
5       SQLDECIMAL          1       19      ""   5     CHECK                          ""

I trimmed this file down so that it looked exactly like this:

1       SQLIMAGE            0       0       ""   1     PDF                            ""

Some things to note here:

  1. The first row needs to match the SQL native client version that your SQL server is running; it was 12.0 when I ran it on my Windows 10 machine with SQL Server 2012 Management Studio, but my actual server is running SQL Server 2008 R2. Thus, I changed the digit to 10.0
  2. The column with “PDF” needs to match the column name that contains your file data. Some examples I found on the Internet weren’t clear here

The last step was to build the query to do the export. It is important to note that in this query the BCD command runs from the server, so any local paths where you’re going to store the format file or output PDFs must be accessible on the SQL Server itself.

This query fills a temporary table with all rows matching a specific employee ID.
Then the while loop iterates through each of those rows, and exports the PDF, naming it based on the Period End date as an increment value.

I declare the employee ID we’re looking for twice in between each ‘GO’ statement, because I’m lazy and didn’t look for a better way.

IF OBJECT_ID('dbo.tmp_for_export', 'U') IS NOT NULL
 DROP TABLE tmp_for_export
 --First Declaration of the Employee ID to use
 DECLARE @EmployeeID VARCHAR(20) = '1234';
 INTO tmp_for_export
 FROM payrollcheck p
 WHERE employeeid = @EmployeeID
DECLARE @EmployeeID VARCHAR(20) = '1234';
 DECLARE @filename VARCHAR(512)
 DECLARE @extension VARCHAR(20)
 SET @cnt = (SELECT COUNT(*) FROM Payrollcheck WHERE EmployeeID = @EmployeeID)
 SET @i = 1
 SET @extension = '.pdf'
while @i <= @cnt
 SET @filename = (SELECT PeriodEnd FROM tmp_for_export WHERE rownum = @i)
 SET @SQL = 'BCP "SELECT PDF from dbo.tmp_for_export WHERE rownum = '+str(@i)+'" QUERYOUT C:\Temp\'+@EmployeeID+'-'+@filename+@extension+' -f C:\Temp\formatfile.fmt -S servername -d COMDAT -T '
 EXEC master.dbo.xp_CmdShell @sql
 set @i = @i+1


This resulted in a bunch of PDFs in C:\Temp on my SQL Server, which I could then go and grab, and give to the payroll department.


Replace Network Printers

I recently went through a Print Server replacement, and wanted to simplify the process for my staff. Our printer connections are quite variable, so we do not push them out through Group Policy but instead let staff connect to printers they have permission for on an as-needed basis.

I wanted a method of re-assigning connections from the old print server to the new one, and I found such a method here:

Chris (above) in turn had sourced his script from Boe here and added a line for retaining the default printer.


However, I ran into a problem with Chris’ script where it would keep reporting and error that the SetDefaultPrinter vbscript couldn’t find a printer with the specified name.

A few quick tests showed that the result produced in the $default variable can’t be referenced by ShareName, but must rather use the “Name” of the printer to set the default.

Thus, I modified the last line to look like this:

(New-Object -ComObject WScript.Network).SetDefaultPrinter("\\$($newPrintServer)\" + $($default.ShareName))

I see now that the second comment on Chris’ post by Andy produces this same result.

Azure Application Proxy permission requirement

My current role has recently expanded to include interaction with Azure, and in this process I came across a permission issue in being able to fully manage Azure AD Application Proxy components.

My administrative account was granted “Co-Administrator” permissions across the Azure subscription by our global team, which was thought to be all that is required to fully managed the components I needed access to.

However, in attempting to set up a new Azure AD Application Proxy, I found that some things were out of place.

Using the Classic portal, I navigated to the Azure AD application list, and clicked Add. Based on documentation and other’s experience, I expected to see a window posing the “What do you want to do?” question. Instead, I was only give the very next screen for a standard application, “Tell us about your application”:


In troubleshooting this issue, I eventually found that I could not use the new portal ( to view Enterprise Applications, with an error of “You don’t have access to this data. Please contact your global administrator to get access.”

A bit further research eventually led me to this Azure article regarding user roles within AD:

This seemed to be the root of the problem, as when I viewed my administrative user account within Azure AD, it’s Directory Role was listed as a Limited Administrator, with “User Account Administrator” as the granted role:


My global team Changed this to “Global Administrator”, which has opened up the Enterprise Applications blade to me and allowed me to configure Application Proxy. I’m not sure which individual role would also grant the same privileges; perhaps none of them.

Commvault Network Throttle

I’ve been struggling for a while trying to get my configuration set properly to avoid WAN saturation with Commvault. There are two key areas I’ve been focusing on:

  1. Aux Copies between media agents
  2. Client to Media Agent backups

I wasn’t fully thinking about the conflict between these two goals which was the root of my problem.

MediaAgent_1 and MediaAgent_2 are geographically separate across the WAN, with a 30Mbps effective connectivity.

Client_1 and Client_2 are geographically separate, and back up to MediaAgent_1. Each has a 6Mbps link.

In my previous configuration, I applied absolute Send/Receive throttling on the Media Agents to accomodate the Aux Copy, at 20Mbps limit, and similar throttling on the Client configuration at 3Mbps.

As much as it doesn’t make sense, I believe what was causing the problem is that the MediaAgent was set to receive at 20Mbps which overrode the “send” setting on the Client.

So here’s what I have successfully configured now:

MediaAgent_1: Select MediaAgent_2 as “remote client”, with absolute SEND throttling of 20Mbps

MediaAgent_2: Select MediaAgent_1 as “remote client”, with absolute SEND throttling of 20Mbps

Client_1: Select MediaAgent_1 as “remote client”, with absolute SEND throttling of 3Mbps

Client_2: Select MediaAgent_1 as “remote client”, with absolute SEND throttling of 3Mbps

Commvault Network Throttle settings

Now running backups during the day isn’t causing extreme saturation of my WAN links!