Friday, June 15, 2018

How do you apply the latest updates on Dynamics 365 Finance and Operations

When you deploy and environment of Dynamics 365 for Finance and Operations, you are asked to pick a version of the application along with the platform version. At the point of writing, the latest application version is 8.0 and platform version is 18. We know application version 8.0 and onwards to not allow for any overlayering of standard code. We also know that version 7.3 (the version prior to 8.0) allows overlayering.

If you choose to publish 7.3, you will get the application is it was in December 2017, and you will have to go through the process of applying a fair number of hotfixes to get your application updated.

In this post I will address this process. It is fairly well documented on docs, but I suppose it helps to read it from various sources. This post focus on the minimum effort needed to get updated. Depending on your scenario, it might be more complicated, if you for example have customizations, including extensions.

Before you leave this page, I should tell you there is a bonus part at the end of it.

This process is in two parts:
  1. Binary Updates
  2. Application Updates (or X++ Updates if you like)

Binary Updates

This part can actually be done by a non-developer. It is fairly easy to complete, and should be safe.

I start with updating a DEV environment, assuming it is aligned to the remaining environments (STAGE, PROD) when considering updates. From the environment page in LCS, I can tell there are lots of updates waiting to be installed.



I start by opening All Binary updates. Notice they are all already marked for download. You can't cherry pick these updates, you get them all. I could take only the platform updates instead, but I want everything updated, hence "All Binary updates".



When you continue, notice that you do not actually download the update, rather it is saved back to asset library.



This may take a while, as the entire thing is a couple of GB in total. Allow Asset Library analyze the package before you continue.



When the package is ready, you can go ahead and run "Maintain" and "Apply Updates" form the environment page. Pick the Binary Update package and allow for the Runbook to install it.



NOTE! This process will seed the package to your environment. Make sure you have enough space available on your Service Volume. You also want to make sure nobody is running Visual Studio on the machine while it is serviced. If your VM runs with standard disks (HDD) instead of premium storage (SSD), then the copying of files may time out. If that happens, just press the "Resume" button on the environment page. You might also notice that the machine even reboots as part of the process.

After the Binary Updates are installed, the tiles should hopefully report this.


Application Updates

The next process is a bit more technical and needs the attention of someone with a developer role.

Start by opening the Application Updates, not just the Critical Updates, but all of them. You will click "Select all" and press "Add". This will mark all of them for download.

Since I had over one thousand KBs, it took several seconds for LCS to create the download, so I simply had to wait for it to be sent to the browser for download. It is not a big file. In my example all updates were around 80MB.

The file is a Package.zip, so you will have to unblock it and unzip the file to get the actual HotfixPackageBundle.axscdppck file. That is a nice and long file extension, which I can only assume means AX Source Code Deployable Package. ;-)



Now, here comes the tricky part. While it is possible to apply this package through Visual Studio, I have found it safer to do the next part using command line util (SCDPBundleInstall.exe). Furthermore, I also ensure that there are no service or application potentially locking any files in the Package Local Directory. That means, no Visual Studio running, no IIS running and no Dynamics Ax Batch running. Have a look at the script I have shared at the end of this article.

The process is basically split in two steps:

  1. Prepare. This process analyze the content of the package and makes sure all files which will be changed in the Package Local Directory are put in source control (VSTS). That means add and edit commands, ensuring we can put them into VSTS if we mess up and need to go back to how things were before installing the updates. 
  2. Install. This process analyze the content of the package and actually change files in the Package Local Directory. Any files added or removed will also be put in the list of pending changes to source control (VSTS).

You cannot run them in one single operation, you need to run Prepare first, then commit the pending changes to VSTS. Then you run it again with the Install mode to change files.

The prepare step takes less time, but it does take time. If you want to see what it is actually doing, the closest you get is having a look under your users Temp folder. It will extract the packages and the dependency manifest under C:\Users\USERNAME\AppData\Temp\SCDPBundleInstall. You will observe the tool is extracting each package, looking at the manifest of the package, and checking what files the package will change. As part of this process, it also ensures the change is added to "pending changes". When the tool is done, it removes the folder.



When prepare is complete, you will have to open Visual Studio and commit the pending changes in order to "backup" the files to source control. Give this commit a good name, so you know it is related to preparing a hotfix bundle install. Starting Visual Studio will start IIS Express, and since you will close Visual Studio when your changes are committed, you will again have to ensure IIS is stopped before you run the install step.
Remember, when Visual Studio is closed, IIS Express is normally stopped and IIS is started. This process can take a couple of seconds, so wait a few seconds before you continue with the install step.

The install step takes the longest time, but also uses the same folder to extract and analyze the packages.

Before you go ahead and commit all the updates standard modules to VSTS from DEV, you will need to make sure it builds. Your customizations may be broken, and your overlayering may have new conflicts that needs to be resolved. All of this must be handled before the application updates are put in source control (VSTS).

From there, you initiate the BUILD, take out the final artifact containing all the updated application modules (packages) and put it up in Asset Library.

When you are ready to install in STAGE, start with the Binary Updates package in Asset Library, then install the Application Update package in Asset Library.

Some potential troubleshooting hints

I did run into some issues while doing this. All of which I had to resolve manually, and some were reported back to Microsoft Support.

You might experience delays when applying the application updates due to limitations of how many transactions you are allowed to do against VSTS. These are delays, so it should only mean things takes longer.

If you get errors while running the prepare or install, it might be something wrong with one of the packages, either due to invalid manifest or dependencies. I've only seen this a few times, and it is not expected. If that happens, contact Microsoft Support.

Also be aware that if one module fails to build for whatever reason, all modules depending on that module will most likely also throw errors. So don't fall off your chair if you get a high number of compilation errors. It might just be one error, creating a chain or other errors. Solve that one error, and the other go away.

Bonus

Using the SCDPBundleInstall tool is documented on docs, but for your convenience I will share a little PowerShell script that helps you run the prepare and install step. If you see any errors or improvements, I am grateful for all feedback.

function InstallHotfixBundle ([string] $file, [string]$vstsAccountName, [bool] $installMode = $false)
{
    $VSTSURI = 'https://{0}.visualstudio.com' -f $vstsAccountName

    $pldPath = "\AOSService\PackagesLocalDirectory"
    $packageDirectory = "{0}:$pldPath" -f ('J','K')[$(Test-Path $("K:$pldPath"))] 

    $command = ('prepare','install')[$installMode]

    if ($installMode -eq $true)
    {
        Write-Host "INSTALL MODE!" -f Yellow
        Get-Service w3svc | Stop-Service -Force
        Get-Service DynamicsAxBatch | Stop-Service -Force
    }
    else
    {
        Write-Host "PREPAREMODE ONLY!" -f Yellow
    }

    if (Test-Path -Path $file)
    {
        Unblock-File -Path $file
        $InstallUtility = '{0}\Bin\SCDPBundleInstall.exe' -f $packageDirectory
        $params = @(
            '-{0}' -f $command
            '-packagepath={0}' -f $file 
            '-metadatastorepath={0}' -f $packageDirectory
            '-tfsworkspacepath={0}' -f $packageDirectory
            '-tfsprojecturi={0}/defaultcollection' -f $VSTSURI
        )

        & $InstallUtility $params 2>&1 | Out-String

        if ($installMode -eq $true)
        {
            Write-Host "Hotfixes have been applied. Verify through build & sync, and commit the updates to VSTS!" -f Green
        }
        else
        {
            Write-Host "Touched elements ready for backup to VSTS. Commit changes before continue with install! Remember to close Visual Studio when you are done!" -f Green
        }
    }
    else
    {
        throw 'No such file {0}' -f $file
    }
}

# Remove the # to uncomment the line you want to run
#InstallHotfixBundle -file 'D:\Hotfix\HotfixPackageBundle.axscdppkg' -vstsAccountName 'YOUR_VSTS_ACCOUNT'
#InstallHotfixBundle -file 'D:\Hotfix\HotfixPackageBundle.axscdppkg' -vstsAccountName 'YOUR_VSTS_ACCOUNT' -installMode $true


Enjoy!

Sunday, February 4, 2018

Installing a Software Deployable Package (SDP) using PowerShell

Now the PowerShell involved here is miniscule, so don't expect much. But I'm going to post this either way.

You will most likely install Software Deployable Packages using LCS, as outlined on the official docs, so why would you need a PowerShell script for this? It so happens that you need to install the package manually if you for example need to upgrade from 7.2 to 7.3 of Operations.

You download the package from LCS. After unblocking the zip-file, and extract it somewhere. I typically extract it on the Temporary Drive, the D-drive. Then you simply need to run this small script to initiate the installation locally.

#Requires -RunAsAdministrator

function InstallSDP()
{
    $BinaryPackageLocation = 'D:\Update'
    $Installer = $('{0}\AXUpdateInstaller.exe' -f $BinaryPackageLocation)

    if (Test-Path -Path $Installer)
    {
        Set-Location $BinaryPackageLocation
        & $Installer 'quickinstallall' 2>&1 | Out-String
    }
    else
    {
        Write-Output $("No update found in {0}" -f $BinaryPackageLocation)
    }
}

InstallSDP

Now, this will not work unless you have local admin rights. So the yes, that means if you plan to run the 7.2 to 7.3 upgrade, you need to run it on a machine where you have local admin rights. This is pointed out in question 14 on Robert Badawys FAQ on the matter.

Notice I am using the "quickinstallall" command here, and this is only applicable for OneBox Developer VMs.

So what about "devinstall"-command? You cannot use the devinstall for the upgrade package, but you can use it in other scenarios where you install customization packages and hotfixes. It was introduced in Platform Update 12, and is intended for use without the need for local admin privileges.


Friday, February 2, 2018

PowerShell script to toggle Maintenance mode

In order to change licence configurations on Operations, you need to toggle maintenance mode on or off. This can be done using a Setup tool, but on the development machines where we do not have local admin rights, the only solution would be to hack the database, like Kurt Hatlevik shows us in this blog post.

In this post I will show how you can toggle maintenance mode on or off using PowerShell. The script is intended for OneBox environments. Just paste it into a new ps1 file for future use, or run it through PowerShell ISE.

DISCLAIMER: Don't run this unless you are prepared to take the heat from restarting the entire web application. It stops and starts the web server.

function ToggleMaintenanceMode()
{
    $parm = @{
        ServerInstance = 'localhost'
        Database = 'AxDB'
        Query = "UPDATE SQLSYSTEMVARIABLES SET [VALUE] = IIF([VALUE]=1, 0, 1) WHERE PARM = 'CONFIGURATIONMODE'"
    }

    Get-Service "W3SVC" | Stop-Service -Force
    Invoke-Sqlcmd @parm
    Get-Service "W3SVC" | Start-Service  

    $parm.Query = "SELECT [VALUE] FROM SQLSYSTEMVARIABLES WHERE PARM = 'CONFIGURATIONMODE'"
    $result = Invoke-Sqlcmd @parm
    [int]$value = $result.Value

    Write-Output "Configuration mode $(('disabled','enabled')[$value])"
}

ToggleMaintenanceMode

The script shows you how you can easily run SQL commands, and even retrieve values back to your PowerShell script.

Enjoy!

Sunday, January 28, 2018

PowerShell script for synchronizing the database

UPDATE! Just a day after posting this article, I got some valuable feedback that made me rewrite the script. I kept the top part of the post as is, for historical reference, but the new script is below. Keep reading!

In this post I want to share a neat way to use a PowerShell script for running the database synchronization when working. You probably already know you can run the database synchronization from within Visual Studio, and that is probably where most developers and consultants will do this operation, but sometimes you want the option to just run a script. Examples of this is when you copy a database between environments, or during upgrade operations.

Let's put the script out, and I'll discuss the parts below.

#Requires -RunAsAdministrator
Import-Module "$PSScriptRoot\AOSEnvironmentUtilities.psm1" -DisableNameChecking
Import-Module "$PSScriptRoot\CommonRollbackUtilities.psm1" -DisableNameChecking

function Run-DBSync()
{
    $SyncToolExecutable = '{0}\bin\Microsoft.Dynamics.AX.Deployment.Setup.exe' -f $(Get-AosWebSitePhysicalPath)
    $params = @(
        '-bindir',       $(Get-AOSPackageDirectory)
        '-metadatadir' , $(Get-AOSPackageDirectory) 
        '-sqluser',      $(Get-DataAccessSqlUsr)
        '-sqlserver',    $(Get-DataAccessDbServer)
        '-sqldatabase',  $(Get-DataAccessDatabase)
        '-setupmode',    'sync' 
        '-syncmode',     'fullall' 
        '-isazuresql',   'false' 
        '-sqlpwd',       $(Get-DataAccessSqlPwd)
    )
    & $SyncToolExecutable $params 2>&1 | Out-String    
}

Run-DBSync

Let's look at what this script does. The very first line is just a hint to the runtime that this script must be run in elevated mode. The reason is that it must get some information from the system that requires admin rights. Typically I also stop some services, like the Management Reporter Process Service, before I run the synchronization, and obviously a non-admin will struggle to do that.

Notice that I have imported some modules, and you may be wondering where I got those. These PowerShell modules are part of the Software Deployable Packages, and either you can create one yourself, or simply download one of those made available by Microsoft in LCS. Extract the package and look under the following path, \AOSService\Scripts. Just grab the two files and make sure you save them alongside your script, like the example below:



The rest is simply building the parameters for the synchronization operation, and running the tool that does the job. The output is sent to the host, so if you want to look at the result you may want to run this script in PowerShell ISE (Admin mode).

What is also neat, is that it will pick up the database credentials used for your environment, so you don't have to put those details in the script yourself.

In any case, it is a neat study in how you can organize your script in such a way that you get the code all in one visible column. It's also a stepping stone to start building your own set of scripts to maintain your development environments.

Finally, a small disclaimer: Microsoft may very well change how their PowerShell modules work in the future, so if that happens, the script above will have to change.

Updated script - no modules and works for non-admin

So here is a way to run the database synchronization without having to rely on the PowerShell modules and without having to have local admin rights. Remember this is limited to OneBox environment.

function Run-DBSync()
{
     # Find the correct Package Local Directory (PLD)
    $pldPath = "\AOSService\PackagesLocalDirectory"
    $packageDirectory = "{0}:$pldPath" -f ('J','K')[$(Test-Path $("K:$pldPath"))]  

    $SyncToolExecutable = '{0}\bin\SyncEngine.exe' -f $packageDirectory
    
    $connectionString = "Data Source=localhost; " +
        "Integrated Security=True; " +
        "Initial Catalog=AxDb"

    $params = @(
        "-syncmode=`"fullall`""
        "-metadatabinaries=$packageDirectory"
        "-connect=`"$connectionString`""
    )

    & $SyncToolExecutable $params 2>&1 | Out-String    
}

Run-DBSync

Notice how I feed the parameters to the executable here, in comparisson to the Setup tool above. It is currently stated in the docs that you may want to use the Setup tool during upgrade scenarios.

Saturday, January 13, 2018

List hotfixes using PowerShell in D365FO (AX7)

You probably already know that you can open Visual Studio and from the "Dynamics 365" menu, under "Addins" and "Apply Hotfix", you will find a grid that lists all the hotfixes installed on your environment. The list can be copied and pasted into Excel if you need a better view and you need to filter and search the list. It works, but it could be a bit easier.

In this post I will share a neat function you can use to list installed hotfixes using PowerShell. It inspired by the post from Microsoft Support (Thomas Treen), and I got some help by some of my fellow MVPs to get inspired (shout out to Martin Draab and Lane Swenka).

The function is as follows:

function Get-HotfixList()
{
    # Find the correct Package Local Directory (PLD)
    $pldPath = "\AOSService\PackagesLocalDirectory"
    $packageDirectory = "{0}:$pldPath" -f ('J','K')[$(Test-Path $("K:$pldPath"))]  
    
    [array]$Updates = @()

    # Get all updates XML
    foreach ($packagefile in Get-ChildItem $packageDirectory\*\*\AxUpdate\*.xml)
    {
        [xml]$xml = Get-Content $packagefile                         
        [string]$KBs = $xml.AxUpdate.KBNumbers.string

        # One package may refer many KBs
        foreach ($KB in $KBs -split " ")
        {
            [string]$package = $xml.AxUpdate.Name
            $moduleFolder = $packagefile.Directory.Parent

            $Updates += [PSCustomObject]@{
                Module = $moduleFolder.Parent
                Model = $moduleFolder
                KB = $KB
                Package = $package
                Folder = $moduleFolder.FullName
            }
        }
    }
    return $Updates
}

With this function, you can list out the hotfixes to a resizable, sortable and searchable grid like this:

Get-HotfixList | Out-GridView


You can list out the hotfixes into a long string where each KB number is separated by a space. Then copy this string into LCS when searching for KBs you want to use in a Hotfix Bundle.

$list = Get-HotfixList | select KB | sort KB
$list = [string]::Join(" ", $list.KB)
$list


Obviously you can use the function to quickly search for a specific hotfix.

Get-HotfixList | where {$_.KB -eq "4055564"}


And one final example, when installing a hotfix bundle, one of the steps are to compile the modules patched, and while you can do a full compile of all modules in the application, you could also just compile only the ones patched. To create a distinct list of modules, run the following statement.

Get-HotfixList | select module | sort module | Get-Unique -AsString


A quick note on the Package Local Directory (PLD) path. In my script I shift between K and J drive. I have only used this script on VMs in the cloud. If you need to run this where the PLD path is on some other drive, you will need to change that in the script.

Sunday, October 22, 2017

Use Azure Automation to start and stop your VMs on a schedule

This post is long overdue, and I have been meaning to post it over a year ago.  I did present an early version of this script at the AXUG event in Stuttgart, but since then the API has changed around tags, and also it has become very easy to solve authentication using Run as Accounts. The code I am sharing here works on the latest version of the modules, and I hope it will keep working for years to come.

I few notes before I continue:
  • I base this script off from Automys own code, and it is heavily inspired by the commits done by other users out there in the community. I will refer to the project on GitHub where you will find contributors and authors. 
  • I've only tested and used the script for ARM Resources
  • I removed references to credentials and certificates and it relies on using "Run As Account". Setting up "Run As Account" in Azure is very easy and quick to do.
You will find the Feature branch here:

Setup

I recommend starting by creating a new Automation Account. Yes, you can probably reuse an existing one, but creating a new account does not incur additional costs, and you can get this up and running fairly quick and easy just by following the steps in this blog post.



Make sure you select "Yes" on the option of creating "Azure Run as Account". Let it create all the artifacts and while you wait you can read the rest of this post.

When the Automation account is up and running, the next step is to create a new Runbook of type "PowerShell" - just straight up PowerShell, and no fancy stuff.

Then you grab the script from my feature branch based off the original trunk. You can either take the script from this post, or take the latest from GitHub. I probably won't maintain this blog post on any future updates of the script, but I might maintain the one on GitHub. I'll put a copy down below.

So with the script added as a PowerShell Runbook, and saved. Now you need to Schedule it. This is where a small cost may incur, because it is necessary to set the Runbook to run every hour. Yes - every hour. Using Automation for free only allow for a limited number of runs, and with the Runbook running every hour throughout the day, I believe it will stop running after 20 days - per month. There is a 500 minute limit per month for free, but the cost incurred when you exceed this is extremely low.

With the script running every hour you are ready to schedule "downtime". And this is easy.
You basically just either TAG the VM or the Resource Group holding a collection of VMs.

By TAG I mean you type on the downtime you want for your resource in the VALUE of a specific TAG. The script looks for a tag named "AutoShutdownSchedule". Example of value would be "20:00->06:00, Saturday, Sunday", and you can probably guess when the server will be shutdown with that value... That is correct, all weekdays between 8 pm at night and 6 am in the morning. You can imagine the flexibility this gives.

Added Features

In addition, the script is inspired by other nice ideas from the community, like providing a TimeZone for your schedule, just to ensure your 8 pm is consistent to when the script interprets the value.

Another feature added is the ability to use a "NeverStart" value keyword, to enforce the resource does not start. You can use this to schedule automatic shutdown that does not trigger startup again after the schedule ends. Example is the value "20:00->21:00,NeverStart". This would stop the resource at 8 pm, and when the RunBook runs again at 9 pm, the resource will not start even though the schedule has ended.

Finally, I want to comment the added feature of disabling the schedule without removing the schedule. If you provide an additional tag with the name "AutoShutdownDisabled" with a value of Yes/1/True. This means you can keep the schedule and temporarily disable the shutdown schedule altogether.

The script

<#
        .SYNOPSIS
        This Azure Automation runbook automates the scheduled shutdown and startup of resources in an Azure subscription. 

        .DESCRIPTION
        The runbook implements a solution for scheduled power management of Azure resources in combination with tags
        on resources or resource groups which define a shutdown schedule. Each time it runs, the runbook looks for all
        supported resources or resource groups with a tag named "AutoShutdownSchedule" having a value defining the schedule, 
        e.g. "10PM -> 6AM". It then checks the current time against each schedule entry, ensuring that resourcess with tags or in tagged groups 
        are deallocated/shut down or started to conform to the defined schedule.

        This is a PowerShell runbook, as opposed to a PowerShell Workflow runbook.

        This script requires the "AzureRM.Resources" modules which are present by default in Azure Automation accounts.
        For detailed documentation and instructions, see: 
        
        CREDITS: Initial version credits goes to automys from which this script started :
        https://automys.com/library/asset/scheduled-virtual-machine-shutdown-startup-microsoft-azure

        .PARAMETER Simulate
        If $true, the runbook will not perform any power actions and will only simulate evaluating the tagged schedules. Use this
        to test your runbook to see what it will do when run normally (Simulate = $false).

        .PARAMETER DefaultScheduleIfNotPresent
        If provided, will set the default schedule to apply on all resources that don't have any scheduled tag value defined or inherited.

        Description | Tag value
        Shut down from 10PM to 6 AM UTC every day | 10pm -> 6am
        Shut down from 10PM to 6 AM UTC every day (different format, same result as above) | 22:00 -> 06:00
        Shut down from 8PM to 12AM and from 2AM to 7AM UTC every day (bringing online from 12-2AM for maintenance in between) | 8PM -> 12AM, 2AM -> 7AM
        Shut down all day Saturday and Sunday (midnight to midnight) | Saturday, Sunday
        Shut down from 2AM to 7AM UTC every day and all day on weekends | 2:00 -> 7:00, Saturday, Sunday
        Shut down on Christmas Day and New Year?s Day | December 25, January 1
        Shut down from 2AM to 7AM UTC every day, and all day on weekends, and on Christmas Day | 2:00 -> 7:00, Saturday, Sunday, December 25
        Shut down always ? I don?t want this VM online, ever | 0:00 -> 23:59:59
        
    
        .PARAMETER TimeZone
        Defines the Timezone used when running the runbook. "GMT Standard Time" by default.
        Microsoft Time Zone Index Values:
        https://msdn.microsoft.com/en-us/library/ms912391(v=winembedded.11).aspx

        .EXAMPLE
        For testing examples, see the documentation at:

        https://automys.com/library/asset/scheduled-virtual-machine-shutdown-startup-microsoft-azure
    
        .INPUTS
        None.

        .OUTPUTS
        Human-readable informational and error messages produced during the job. Not intended to be consumed by another runbook.
#>
[CmdletBinding()]
param(
    [parameter(Mandatory=$false)]
    [bool]$Simulate = $false,
    [parameter(Mandatory=$false)]
    [string]$DefaultScheduleIfNotPresent,
    [parameter(Mandatory=$false)]
    [String] $Timezone = "W. Europe Standard Time"
)

$VERSION = '3.3.0'
$autoShutdownTagName = 'AutoShutdownSchedule'
$autoShutdownOrderTagName = 'ProcessingOrder'
$autoShutdownDisabledTagName = 'AutoShutdownDisabled'
$defaultOrder = 1000

$ResourceProcessors = @(
    @{
        ResourceType = 'Microsoft.ClassicCompute/virtualMachines'
        PowerStateAction = { param([object]$Resource, [string]$DesiredState) (Get-AzureRmResource -ResourceId $Resource.ResourceId).Properties.InstanceView.PowerState }
        StartAction = { param([string]$ResourceId) Invoke-AzureRmResourceAction -ResourceId $ResourceId -Action 'start' -Force } 
        DeallocateAction = { param([string]$ResourceId) Invoke-AzureRmResourceAction -ResourceId $ResourceId -Action 'shutdown' -Force } 
    },
    @{
        ResourceType = 'Microsoft.Compute/virtualMachines'
        PowerStateAction = { 
            param([object]$Resource, [string]$DesiredState)
      
            $vm = Get-AzureRmVM -ResourceGroupName $Resource.ResourceGroupName -Name $Resource.Name -Status
            $currentStatus = $vm.Statuses | Where-Object Code -like 'PowerState*' 
            $currentStatus.Code -replace 'PowerState/',''
        }
        StartAction = { param([string]$ResourceId) Invoke-AzureRmResourceAction -ResourceId $ResourceId -Action 'start' -Force } 
        DeallocateAction = { param([string]$ResourceId) Invoke-AzureRmResourceAction -ResourceId $ResourceId -Action 'deallocate' -Force } 
    },
    @{
        ResourceType = 'Microsoft.Compute/virtualMachineScaleSets'
        #since there is no way to get the status of a VMSS, we assume it is in the inverse state to force the action on the whole VMSS
        PowerStateAction = { param([object]$Resource, [string]$DesiredState) if($DesiredState -eq 'StoppedDeallocated') { 'Started' } else { 'StoppedDeallocated' } }
        StartAction = { param([string]$ResourceId) Invoke-AzureRmResourceAction -ResourceId $ResourceId -Action 'start' -Parameters @{ instanceIds = @('*') } -Force } 
        DeallocateAction = { param([string]$ResourceId) Invoke-AzureRmResourceAction -ResourceId $ResourceId -Action 'deallocate' -Parameters @{ instanceIds = @('*') } -Force } 
    }
)

# Define function to get current date using the TimeZone Paremeter
function GetCurrentDate
{
    return [system.timezoneinfo]::ConvertTime($(Get-Date),$([system.timezoneinfo]::GetSystemTimeZones() | ? id -eq $Timezone))
}

# Define function to check current time against specified range
function Test-ScheduleEntry ([string]$TimeRange)
{ 
    # Initialize variables
    $rangeStart, $rangeEnd, $parsedDay = $null
    $currentTime = GetCurrentDate
    $midnight = $currentTime.AddDays(1).Date         

    try
    {
        # Parse as range if contains '->'
        if($TimeRange -like '*->*')
        {
            $timeRangeComponents = $TimeRange -split '->' | foreach {$_.Trim()}
            if($timeRangeComponents.Count -eq 2)
            {
                $rangeStart = Get-Date $timeRangeComponents[0]
                $rangeEnd = Get-Date $timeRangeComponents[1]
 
                # Check for crossing midnight
                if($rangeStart -gt $rangeEnd)
                {
                    # If current time is between the start of range and midnight tonight, interpret start time as earlier today and end time as tomorrow
                    if($currentTime -ge $rangeStart -and $currentTime -lt $midnight)
                    {
                        $rangeEnd = $rangeEnd.AddDays(1)
                    }
                    # Otherwise interpret start time as yesterday and end time as today   
                    else
                    {
                        $rangeStart = $rangeStart.AddDays(-1)
                    }
                }
            }
            else
            {
                Write-Output "`tWARNING: Invalid time range format. Expects valid .Net DateTime-formatted start time and end time separated by '->'" 
            }
        }
        # Otherwise attempt to parse as a full day entry, e.g. 'Monday' or 'December 25' 
        else
        {
            # If specified as day of week, check if today
            if([System.DayOfWeek].GetEnumValues() -contains $TimeRange)
            {
                if($TimeRange -eq (Get-Date).DayOfWeek)
                {
                    $parsedDay = Get-Date '00:00'
                }
                else
                {
                    # Skip detected day of week that isn't today
                }
            }
            # Otherwise attempt to parse as a date, e.g. 'December 25'
            else
            {
                $parsedDay = Get-Date $TimeRange
            }
     
            if($parsedDay -ne $null)
            {
                $rangeStart = $parsedDay # Defaults to midnight
                $rangeEnd = $parsedDay.AddHours(23).AddMinutes(59).AddSeconds(59) # End of the same day
            }
        }
    }
    catch
    {
        # Record any errors and return false by default
        Write-Output "`tWARNING: Exception encountered while parsing time range. Details: $($_.Exception.Message). Check the syntax of entry, e.g. ' -> ', or days/dates like 'Sunday' and 'December 25'"
        return $false
    }
 
    # Check if current time falls within range
    if($currentTime -ge $rangeStart -and $currentTime -le $rangeEnd)
    {
        return $true
    }
    else
    {
        return $false
    }
 
} # End function Test-ScheduleEntry


# Function to handle power state assertion for resources
function Assert-ResourcePowerState
{
    param(
        [Parameter(Mandatory=$true)]
        [object]$Resource,
        [Parameter(Mandatory=$true)]
        [string]$DesiredState,
        [bool]$Simulate
    )

    $processor = $ResourceProcessors | Where-Object ResourceType -eq $Resource.ResourceType
    if(-not $processor) {
        throw ('Unable to find a resource processor for type ''{0}''. Resource: {1}' -f $Resource.ResourceType, ($Resource | ConvertTo-Json -Depth 5000))
    }
    # If should be started and isn't, start resource
    $currentPowerState = & $processor.PowerStateAction -Resource $Resource -DesiredState $DesiredState
    if($DesiredState -eq 'Started' -and $currentPowerState -notmatch 'Started|Starting|running')
    {
        if($Simulate)
        {
            Write-Output "`tSIMULATION -- Would have started resource. (No action taken)"
        }
        else
        {
            Write-Output "`tStarting resource"
            & $processor.StartAction -ResourceId $Resource.ResourceId
        }
    }
  
    # If should be stopped and isn't, stop resource
    elseif($DesiredState -eq 'StoppedDeallocated' -and $currentPowerState -notmatch 'Stopped|deallocated')
    {
        if($Simulate)
        {
            Write-Output "`tSIMULATION -- Would have stopped resource. (No action taken)"
        }
        else
        {
            Write-Output "`tStopping resource"
            & $processor.DeallocateAction -ResourceId $Resource.ResourceId
        }
    }

    # Otherwise, current power state is correct
    else
    {
        Write-Output "`tCurrent power state [$($currentPowerState)] is correct."
    }
}

# Main runbook content
try
{
    $currentTime = GetCurrentDate
    Write-Output "Runbook started. Version: $VERSION"
    if($Simulate)
    {
        Write-Output '*** Running in SIMULATE mode. No power actions will be taken. ***'
    }
    else
    {
        Write-Output '*** Running in LIVE mode. Schedules will be enforced. ***'
    }
    Write-Output "Current UTC/GMT time [$($currentTime.ToString('dddd, yyyy MMM dd HH:mm:ss'))] will be checked against schedules"
        
    
    $Conn = Get-AutomationConnection -Name AzureRunAsConnection
    $resourceManagerContext = Add-AzureRMAccount -ServicePrincipal -Tenant $Conn.TenantID -ApplicationId $Conn.ApplicationID -CertificateThumbprint $Conn.CertificateThumbprint
    
    $resourceList = @()
    # Get a list of all supported resources in subscription
    $ResourceProcessors | % {
        Write-Output ('Looking for resources of type {0}' -f $_.ResourceType)
        $resourceList += @(Find-AzureRmResource -ResourceType $_.ResourceType)
    }

    $ResourceList | % {     
        if($_.Tags -and $_.Tags.ContainsKey($autoShutdownOrderTagName) ) {
            $order = $_.Tags | % { if($_.ContainsKey($autoShutdownOrderTagName)) { $_.Item($autoShutdownOrderTagName) } }
        } else {
            $order = $defaultOrder
        }
        Add-Member -InputObject $_ -Name ProcessingOrder -MemberType NoteProperty -TypeName Integer -Value $order
    }

    $ResourceList | % {     
        if($_.Tags -and $_.Tags.ContainsKey($autoShutdownDisabledTagName) ) {
            $disabled = $_.Tags | % { if($_.ContainsKey($autoShutdownDisabledTagName)) { $_.Item($autoShutdownDisabledTagName) } }
        } else {
            $disabled = '0'
        }
        Add-Member -InputObject $_ -Name ScheduleDisabled -MemberType NoteProperty -TypeName String -Value $disabled
    }

    # Get resource groups that are tagged for automatic shutdown of resources
    $taggedResourceGroups = Find-AzureRmResourceGroup -Tag @{ "AutoShutdownSchedule" = $null }
    $taggedResourceGroupNames = @($taggedResourceGroups | select Name)
    
    Write-Output "Found [$($taggedResourceGroupNames.Count)] schedule-tagged resource groups in subscription" 
    
    if($DefaultScheduleIfNotPresent) {
        Write-Output "Default schedule was specified, all non tagged resources will inherit this schedule: $DefaultScheduleIfNotPresent"
    }

    # For each resource, determine
    #  - Is it directly tagged for shutdown or member of a tagged resource group
    #  - Is the current time within the tagged schedule 
    # Then assert its correct power state based on the assigned schedule (if present)
    Write-Output "Processing [$($resourceList.Count)] resources found in subscription"
    foreach($resource in $resourceList)
    {
        $schedule = $null

        if ($resource.ScheduleDisabled)
        {
            $disabledValue = $resource.ScheduleDisabled
            if ($disabledValue -eq "1" -or $disabledValue -eq "Yes"-or $disabledValue -eq "True")
            {
                Write-Output "[$($resource.Name)]: `r`n`tIGNORED -- Found direct resource schedule with $autoShutdownDisabledTagName value: $disabledValue."
                continue
            }
        }

        # Check for direct tag or group-inherited tag
        if($resource.Tags.Count -gt 0 -and $resource.Tags.ContainsKey($autoShutdownTagName) -eq $true)
        {
            # Resource has direct tag (possible for resource manager deployment model resources). Prefer this tag schedule.
            $schedule = $resource.Tags.Item($autoShutdownTagName)
            Write-Output "[$($resource.Name)]: `r`n`tADDING -- Found direct resource schedule tag with value: $schedule"
        }
        elseif($taggedResourceGroupNames -contains $resource.ResourceGroupName)
        {
            # resource belongs to a tagged resource group. Use the group tag
            $parentGroup = $resourceGroups | Where-Object Name -eq $resource.ResourceGroupName
            $schedule = $parentGroup.Tags.Item($AUTOSHUTDOWNSCHEDULE_KEYWORD)
            Write-Output "[$($resource.Name)]: `r`n`tADDING -- Found parent resource group schedule tag with value: $schedule"
        }
        elseif($DefaultScheduleIfNotPresent)
        {
            $schedule = $DefaultScheduleIfNotPresent
            Write-Output "[$($resource.Name)]: `r`n`tADDING -- Using default schedule: $schedule"
        }
        else
        {
            # No direct or inherited tag. Skip this resource.
            Write-Output "[$($resource.Name)]: `r`n`tIGNORED -- Not tagged for shutdown directly or via membership in a tagged resource group. Skipping this resource."
            continue
        }

        # Check that tag value was succesfully obtained
        if($schedule -eq $null)
        {
            Write-Output "[$($resource.Name) `- $($resource.ProcessingOrder)]: `r`n`tIGNORED -- Failed to get tagged schedule for resource. Skipping this resource."
            continue
        }

        # Parse the ranges in the Tag value. Expects a string of comma-separated time ranges, or a single time range
        $timeRangeList = @($schedule -split ',' | foreach {$_.Trim()})
    
        # Check each range against the current time to see if any schedule is matched
        $scheduleMatched = $false
        $matchedSchedule = $null
        $neverStart = $false #if NeverStart is specified in range, do not wake-up machine
        foreach($entry in $timeRangeList)
        {
            if((Test-ScheduleEntry -TimeRange $entry) -eq $true)
            {
                $scheduleMatched = $true
                $matchedSchedule = $entry
                break
            }
            
            if ($entry -eq "NeverStart")
            {
                $neverStart = $true
            }
        }
        Add-Member -InputObject $resource -Name ScheduleMatched -MemberType NoteProperty -TypeName Boolean -Value $scheduleMatched
        Add-Member -InputObject $resource -Name MatchedSchedule -MemberType NoteProperty -TypeName Boolean -Value $matchedSchedule
        Add-Member -InputObject $resource -Name NeverStart -MemberType NoteProperty -TypeName Boolean -Value $neverStart
    }
    
    foreach($resource in $resourceList | Group-Object ScheduleMatched) {
        if($resource.Name -eq '') {continue}
        $sortedResourceList = @()
        if($resource.Name -eq $false) {
            # meaning we start resources, lower to higher
            $sortedResourceList += @($resource.Group | Sort ProcessingOrder)
        } else { 
            $sortedResourceList += @($resource.Group | Sort ProcessingOrder -Descending)
        }

        foreach($resource in $sortedResourceList)
        {  
            # Enforce desired state for group resources based on result. 
            if($resource.ScheduleMatched)
            {
                # Schedule is matched. Shut down the resource if it is running. 
                Write-Output "[$($resource.Name) `- P$($resource.ProcessingOrder)]: `r`n`tASSERT -- Current time [$currentTime] falls within the scheduled shutdown range [$($resource.MatchedSchedule)]"
                Add-Member -InputObject $resource -Name DesiredState -MemberType NoteProperty -TypeName String -Value 'StoppedDeallocated'

            }
            else
            {
                if ($resource.NeverStart)
                {
                    Write-Output "[$($resource.Name)]: `tIGNORED -- Resource marked with NeverStart. Keeping the resources stopped."
                    Add-Member -InputObject $resource -Name DesiredState -MemberType NoteProperty -TypeName String -Value 'StoppedDeallocated'
                }
                else
                {
                    # Schedule not matched. Start resource if stopped.
                    Write-Output "[$($resource.Name) `- P$($resource.ProcessingOrder)]: `r`n`tASSERT -- Current time falls outside of all scheduled shutdown ranges. Start resource."
                    Add-Member -InputObject $resource -Name DesiredState -MemberType NoteProperty -TypeName Boolean -Value 'Started'
                }                
            }     
            Assert-ResourcePowerState -Resource $resource -DesiredState $resource.DesiredState -Simulate $Simulate
        }
    }

    Write-Output 'Finished processing resource schedules'
}
catch
{
    $errorMessage = $_.Exception.Message
    throw "Unexpected exception: $errorMessage"
}
finally
{
    Write-Output "Runbook finished (Duration: $(('{0:hh\:mm\:ss}' -f ((GetCurrentDate) - $currentTime))))"
}

Monday, October 2, 2017

Importing users in D365 Operations using Excel

Let me start off by admitting I was initially thinking about naming this post "Importing users in AX7 using Excel", so there, now this post suddenly became a little bit more "searcher friendly".

In this post I will show how easy you can connect to your Dynamics 365 Operations instance using Excel. Before I begin the post, let me just remind you that importing users from Azure Active Directory is perhaps easier and quicker. So this post is just to show you it is also possible to import users using Excel with the Dynamics Office Add-in.

You may have seen the Data Entity "System User" (SystemUserEntity), and you may have tried using it to add users with it, and  furthermore you may also have seen the error "A row created in data set SystemUser was not published. Error message: Write failed for table row of type 'SystemUserEntity'. Infolog: Error: Error in getting SID."


You will get the same error through Excel if you do not provide some additional columns and information while trying to create a user through that Data Entity.

You can either start off with opening Excel, install the Dynamics Office Add-in and connect it to the target instance. Or you can open the list of users directly on the instance, and open the list in Excel from there. Either way you should start with a view where you have the System User list in your spreadsheet.

The next step is to modify the Design of the view. Click the Design link first.



Then edit the System User table.



Then add the following following columns: Enabled, AccountType and Alias (Email).



Save the design changes and ensure you update the view so the added columns are populated with data.

You will notice the Type (AccountType) and Alias (Email) carry important information for how the user authenticates, in addition to the Provider column. With these columns properly populated, you should be able to add multiple rows and hit a single "Publish" from within Excel.

Given this, you can have two open Excel instances, and connect to two different instances. And then copy over users from a source to a target using Excel. As long as all the columns are available and in the same order, of course.

This post should also give you some clue to how you can use Data Management to populate a system with users through a Data Package, if that is your preference.