Thursday, July 26, 2018

View who has logged into Dynamics 365 - across many environments

A licencing requirement was raised at work this week where we needed to know who out of our Dynamics 365 CRM users were actually using the application.  These licences are really expensive and having many of them unused would cost the company lots of money over the year.

We did think of looking at audit tables in CRM, but unfortunately this wasn't an option due to the fact we have many Dynamics 365 environments.  A single licence will allow you access (if you have permission) to access as many sandbox environments as you wish.  This would mean collating data from many environments into one dataset.

We needed something more overarching.

So I reviewed Azure AD to see if I could view signins for a particular application and low and behold I could see all logins to Dynamics CRM Online.  All we would is visit this page, add the filters and download a CSV file.  The resultant CSV file would then need filtering to remove duplicate users, because we don't care about when and how many times they have logged in, just the fact they have at least once in the time period.



This was a great resource and gave us the information we needed.  Naturally, we wanted to take it further and to provide this programmatically with the least amount of user interaction as possible.

Enter Microsoft Azure AD Graph API

https://docs.microsoft.com/en-us/azure/active-directory/develop/active-directory-graph-api

This API allows developers to get access to all sorts of data within Azure AD including application signins.

The first step is to setup an application in Azure AD.

https://docs.microsoft.com/en-us/azure/active-directory/develop/active-directory-graph-api

Once this is complete, you should have an application ID, Access Key and your tenant name.

Next you can write a script to interact with the API.  There are samples around and you can even use the Graph Explorer to play around against a dummy tenant

https://developer.microsoft.com/en-us/graph/graph-explorer


I found the following script which seemed like a good place to start.

https://gallery.technet.microsoft.com/scriptcenter/Pull-Azure-AD-Sign-In-3fead683

I then needed to filter it by application too.  I did this by editing the following line


This then produces a CSV with all the signins to Dynamics CRM Online for the last 30 days, but seeing that we only care if someone as logged on at all, we only want to see one entry per user. 

We added the following line at the bottom of the script which will reimport the CSV selecting only the UPN and then removing any lines which are not unique.  It will then export this to a final csv output.


Here is the full script

Thursday, July 05, 2018

Batch Printing Tool in PowerShell

As part of my company's recent ERP implementation of Dynamics 365, we found that there was no native solution for mass printing.  This is a crucial for our business as we send a lot of letters out to our customers.  We are trying to shift more towards digital, but there are some communications that have to be sent by post and many of our customers are either not online or prefer post.

So, we had to think of how to achieve this.  We had three options


  1. Buy an Commercial off the shelf (COTS) product.  It might be limited, but would be supported and be easier to implement.
  2. Develop an application in house.  Could be costly and difficult to support but could match all requirements.
  3. Have our System Integrator develop an application.  As above, but a lot more expensive.

I had the task of bringing these options to life for our Design Group to make a decision on.

There were some constraints which made it more difficult e.g. our Printing and Enveloper wasn't able to distinguish between different page totals in jobs.  This meant that our post room needed to print all 2 page letters, then 3 page letters and so on.  This seemed bonkers to me, but the cost to replace this beast of a machine was too much to challenge this point.

I looked at various commercial products, some of the really good ones could print automatically, but given the above constraint it wasn't a viable solution.

I decided to try and build an application using PowerShell just to show what is possible if we had in-house developers.

UI
My knowledge of creating UI's in PowerShell is limited to some horrible looking WinForms windows.  I wanted to make something which looked delicious, or at least palatable to the average user and not look like Windows 98.  I found Stephen Owen's website and this blog post series.  Quite simply it is excellent.

https://foxdeploy.com/2015/04/10/part-i-creating-powershell-guis-in-minutes-using-visual-studio-a-new-hope/

With this knowledge in hand, I built the following GUI


The UI top box shows the output folders from our ERP system and provides some detail about each folder.  Clicking expand will then fill the bottom section with all of the documents which are located within.  This is gathered from a CSV file which is output from our ERP system when documents are generated for printing.  If the user clicks on a document and clicks print, it will then add an entry in the CSV saying it has been printed and when.  This will avoid other users printing the same document.

Clicking Archive will move the folder into an archive folder ready to be deleted.  Clicking External will put the documents into a separate folder which will be sent to an external print house.  This is used for massive print jobs, which is cheaper than doing it in-house.

Actual Printing
To actually print from PowerShell is not an easy task to complete reliably.  I found a tool called 2printer which works quite well.  This is a pre-req for this tool to work.

https://www.doc2prn.com/download

I am sure that there is probably some .Net classes that I could use to do this natively, but given the time constraints, this seemed like a good option as a PoC.

GitHub Project
The full code and folders and some sample files can be found at this GitHub repo

https://github.com/shammyowens/TeamasBatchPrint

You just need to edit the folder locations at the bottom of the PowerShell script and you should be good to go.

Conclusion
We actually decided to go with a product called Print Conductor.  It was really close to fulfilling our requirements, it was quick to deploy and it was fairly inexpensive.

https://www.print-conductor.com/

The process of building a dummy application was great though and made our team think strongly about their requirements.  I learnt quite a lot technically which is always a bonus!

Code




Friday, July 21, 2017

Creating a Complex Custom Azure Role

We recently had the need to create a custom role in the Azure Portal which stopped a set of administrators from creating networks or virtual machines.

This was because we are planning to share our ExpressRoute connection with their subscription and we only allow IT to add new devices to our network or domain.

Now the standard Azure RBAC roles don't do anything like this.  These roles are typically configured with only a small set of permissions.

The role needed the following setup

Allow All
Allow start, stop, deallocate VM
Deny All Compute
Deny All Network
Deny All Permissions

The following article was pretty useful in describing the process of creating the custom role.  There are a few methods, but I opted for the creation of the JSON file.

To get the actual permissions required to build the JSON file itself, we needed to run the following commands

Get-AzureRMProviderOperation Microsoft.Compute/*
Get-AzureRMProviderOperation Microsoft.Network/*

Output in Powershell

This produced a list of all of the operations which can be allowed or denied.  I needed to export this to ensure I allowed the administrators to be able to start, stop and deallocate VMs.

The following items are the permissions we want to allow the users to have.

Operation         : Microsoft.Compute/virtualMachines/read
OperationName     : Get Virtual Machine
ProviderNamespace : Microsoft Compute
ResourceName      : Virtual Machines
Description       : Get the properties of a virtual machine

Operation         : Microsoft.Compute/virtualMachines/start/action
OperationName     : Start Virtual Machine
ProviderNamespace : Microsoft Compute
ResourceName      : Virtual Machines
Description       : Starts the virtual machine

Operation         : Microsoft.Compute/virtualMachines/powerOff/action
OperationName     : Power Off Virtual Machine
ProviderNamespace : Microsoft Compute
ResourceName      : Virtual Machines
Description       : Powers off the virtual machine. Note that the virtual machine will continue to be billed.

Operation         : Microsoft.Compute/virtualMachines/restart/action
OperationName     : Restart Virtual Machine
ProviderNamespace : Microsoft Compute
ResourceName      : Virtual Machines
Description       : Restarts the virtual machine

Operation         : Microsoft.Compute/virtualMachines/deallocate/action
OperationName     : Deallocate Virtual Machine
ProviderNamespace : Microsoft Compute
ResourceName      : Virtual Machines
Description       : Powers off the virtual machine and releases the compute resources

Operation         : Microsoft.Compute/virtualMachines/instanceView/read
OperationName     : Get Virtual Machine Instance View
ProviderNamespace : Microsoft Compute
ResourceName      : Virtual Machine Instance View
Description       : Gets the detailed runtime status of the virtual machine and its resources

Operation         : Microsoft.Compute/locations/vmSizes/read
OperationName     : List Available Virtual Machine Sizes in Location
ProviderNamespace : Microsoft Compute
ResourceName      : Virtual Machine Sizes
Description       : Lists available virtual machine sizes in a location

There were a few more than originally intended e.g. Instance view, list sizes etc.  We remove these entries from the Output, filter just on Operation and copy these items into the NotActions section of the JSON file.

The next step was to create a JSON file.  The * in Actions allows all permissions and then we use the NotActions to Deny Operation permissions.
{
  "Name": "BI administrator",
  "Id": "null",
  "IsCustom": true,
  "Description": "Can do everything other than create virtual machines and manage networking.",
  "Actions": ["*"
  ],
  "NotActions": [
"Microsoft.Compute/availabilitySets/delete",
"Microsoft.Compute/availabilitySets/read",
"Microsoft.Compute/availabilitySets/vmSizes/read",
"Microsoft.Compute/availabilitySets/write",
"Microsoft.Compute/disks/beginGetAccess/action",
"Microsoft.Compute/disks/delete",
"Microsoft.Compute/disks/endGetAccess/action",
"Microsoft.Compute/disks/read",
"Microsoft.Compute/disks/write",
"Microsoft.Compute/images/delete",
"Microsoft.Compute/images/read",
"Microsoft.Compute/images/write",
"Microsoft.Compute/locations/diskOperations/read",
"Microsoft.Compute/locations/operations/read",
"Microsoft.Compute/locations/runCommands/read",
"Microsoft.Compute/locations/usages/read",
"Microsoft.Compute/operations/read",
"Microsoft.Compute/register/action",
"Microsoft.Compute/restorePointCollections/delete",
"Microsoft.Compute/restorePointCollections/read",
"Microsoft.Compute/restorePointCollections/restorePoints/delete",
"Microsoft.Compute/restorePointCollections/restorePoints/read",
"Microsoft.Compute/restorePointCollections/restorePoints/retrieveSasUris/action",
"Microsoft.Compute/restorePointCollections/restorePoints/write",
"Microsoft.Compute/restorePointCollections/write",
"Microsoft.Compute/snapshots/beginGetAccess/action",
"Microsoft.Compute/snapshots/delete",
"Microsoft.Compute/snapshots/endGetAccess/action",
"Microsoft.Compute/snapshots/read",
"Microsoft.Compute/snapshots/write",
"Microsoft.Compute/virtualMachineScaleSets/deallocate/action",
"Microsoft.Compute/virtualMachineScaleSets/delete",
"Microsoft.Compute/virtualMachineScaleSets/delete/action",
"Microsoft.Compute/virtualMachineScaleSets/extensions/delete",
"Microsoft.Compute/virtualMachineScaleSets/extensions/read",
"Microsoft.Compute/virtualMachineScaleSets/extensions/write",
"Microsoft.Compute/virtualMachineScaleSets/instanceView/read",
"Microsoft.Compute/virtualMachineScaleSets/manualUpgrade/action",
"Microsoft.Compute/virtualMachineScaleSets/powerOff/action",
"Microsoft.Compute/virtualMachineScaleSets/read",
"Microsoft.Compute/virtualMachineScaleSets/reimage/action",
"Microsoft.Compute/virtualMachineScaleSets/restart/action",
"Microsoft.Compute/virtualMachineScaleSets/rollingUpgrades/cancel/action",
"Microsoft.Compute/virtualMachineScaleSets/rollingUpgrades/read",
"Microsoft.Compute/virtualMachineScaleSets/scale/action",
"Microsoft.Compute/virtualMachineScaleSets/skus/read",
"Microsoft.Compute/virtualMachineScaleSets/start/action",
"Microsoft.Compute/virtualMachineScaleSets/virtualMachines/deallocate/action",
"Microsoft.Compute/virtualMachineScaleSets/virtualMachines/delete",
"Microsoft.Compute/virtualMachineScaleSets/virtualMachines/instanceView/read",
"Microsoft.Compute/virtualMachineScaleSets/virtualMachines/powerOff/action",
"Microsoft.Compute/virtualMachineScaleSets/virtualMachines/read",
"Microsoft.Compute/virtualMachineScaleSets/virtualMachines/reimage/action",
"Microsoft.Compute/virtualMachineScaleSets/virtualMachines/restart/action",
"Microsoft.Compute/virtualMachineScaleSets/virtualMachines/start/action",
"Microsoft.Compute/virtualMachineScaleSets/write",
"Microsoft.Compute/virtualMachines/capture/action",
"Microsoft.Compute/virtualMachines/convertToManagedDisks/action",
"Microsoft.Compute/virtualMachines/delete",
"Microsoft.Compute/virtualMachines/extensions/delete",
"Microsoft.Compute/virtualMachines/extensions/read",
"Microsoft.Compute/virtualMachines/extensions/write",
"Microsoft.Compute/virtualMachines/generalize/action",
"Microsoft.Compute/virtualMachines/performMaintenance/action",
"Microsoft.Compute/virtualMachines/redeploy/action",
"Microsoft.Compute/virtualMachines/runCommand/action",
"Microsoft.Compute/virtualMachines/write",
"Microsoft.Network/applicationGatewayAvailableWafRuleSets/read",
"Microsoft.Network/applicationGateways/backendAddressPools/join/action",
"Microsoft.Network/applicationGateways/backendhealth/action",
"Microsoft.Network/applicationGateways/delete",
"Microsoft.Network/applicationGateways/read",
"Microsoft.Network/applicationGateways/start/action",
"Microsoft.Network/applicationGateways/stop/action",
"Microsoft.Network/applicationGateways/write",
"Microsoft.Network/bgpServiceCommunities/read",
"Microsoft.Network/checkTrafficManagerNameAvailability/action",
"Microsoft.Network/connections/delete",
"Microsoft.Network/connections/read",
"Microsoft.Network/connections/sharedKey/read",
"Microsoft.Network/connections/sharedKey/write",
"Microsoft.Network/connections/vpndeviceconfigurationscript/read",
"Microsoft.Network/connections/write",
"Microsoft.Network/dnsoperationresults/read",
"Microsoft.Network/dnsoperationstatuses/read",
"Microsoft.Network/dnszones/A/delete",
"Microsoft.Network/dnszones/A/read",
"Microsoft.Network/dnszones/A/write",
"Microsoft.Network/dnszones/AAAA/delete",
"Microsoft.Network/dnszones/AAAA/read",
"Microsoft.Network/dnszones/AAAA/write",
"Microsoft.Network/dnszones/CNAME/delete",
"Microsoft.Network/dnszones/CNAME/read",
"Microsoft.Network/dnszones/CNAME/write",
"Microsoft.Network/dnszones/delete",
"Microsoft.Network/dnszones/MX/delete",
"Microsoft.Network/dnszones/MX/read",
"Microsoft.Network/dnszones/MX/write",
"Microsoft.Network/dnszones/NS/delete",
"Microsoft.Network/dnszones/NS/read",
"Microsoft.Network/dnszones/NS/write",
"Microsoft.Network/dnszones/PTR/delete",
"Microsoft.Network/dnszones/PTR/read",
"Microsoft.Network/dnszones/PTR/write",
"Microsoft.Network/dnszones/read",
"Microsoft.Network/dnszones/recordsets/read",
"Microsoft.Network/dnszones/SOA/read",
"Microsoft.Network/dnszones/SOA/write",
"Microsoft.Network/dnszones/SRV/delete",
"Microsoft.Network/dnszones/SRV/read",
"Microsoft.Network/dnszones/SRV/write",
"Microsoft.Network/dnszones/TXT/delete",
"Microsoft.Network/dnszones/TXT/read",
"Microsoft.Network/dnszones/TXT/write",
"Microsoft.Network/dnszones/write",
"Microsoft.Network/expressRouteCircuits/authorizations/delete",
"Microsoft.Network/expressRouteCircuits/authorizations/read",
"Microsoft.Network/expressRouteCircuits/authorizations/write",
"Microsoft.Network/expressRouteCircuits/delete",
"Microsoft.Network/expressRouteCircuits/peerings/arpTables/action",
"Microsoft.Network/expressRouteCircuits/peerings/delete",
"Microsoft.Network/expressRouteCircuits/peerings/read",
"Microsoft.Network/expressRouteCircuits/peerings/routeTables/action",
"Microsoft.Network/expressRouteCircuits/peerings/routeTablesSummary/action",
"Microsoft.Network/expressRouteCircuits/peerings/stats/read",
"Microsoft.Network/expressRouteCircuits/peerings/write",
"Microsoft.Network/expressRouteCircuits/read",
"Microsoft.Network/expressRouteCircuits/stats/read",
"Microsoft.Network/expressRouteCircuits/write",
"Microsoft.Network/expressRouteServiceProviders/read",
"Microsoft.Network/loadBalancers/backendAddressPools/join/action",
"Microsoft.Network/loadBalancers/backendAddressPools/read",
"Microsoft.Network/loadBalancers/delete",
"Microsoft.Network/loadBalancers/frontendIPConfigurations/read",
"Microsoft.Network/loadBalancers/inboundNatPools/join/action",
"Microsoft.Network/loadBalancers/inboundNatPools/read",
"Microsoft.Network/loadBalancers/inboundNatRules/delete",
"Microsoft.Network/loadBalancers/inboundNatRules/join/action",
"Microsoft.Network/loadBalancers/inboundNatRules/read",
"Microsoft.Network/loadBalancers/inboundNatRules/write",
"Microsoft.Network/loadBalancers/loadBalancingRules/read",
"Microsoft.Network/loadBalancers/networkInterfaces/read",
"Microsoft.Network/loadBalancers/outboundNatRules/read",
"Microsoft.Network/loadBalancers/probes/read",
"Microsoft.Network/loadBalancers/read",
"Microsoft.Network/loadBalancers/virtualMachines/read",
"Microsoft.Network/loadBalancers/write",
"Microsoft.Network/localnetworkgateways/delete",
"Microsoft.Network/localnetworkgateways/read",
"Microsoft.Network/localnetworkgateways/write",
"Microsoft.Network/locations/checkDnsNameAvailability/read",
"Microsoft.Network/locations/operationResults/read",
"Microsoft.Network/locations/operations/read",
"Microsoft.Network/locations/privateAccessServices/read",
"Microsoft.Network/locations/usages/read",
"Microsoft.Network/networkInterfaces/delete",
"Microsoft.Network/networkInterfaces/effectiveNetworkSecurityGroups/action",
"Microsoft.Network/networkInterfaces/effectiveRouteTable/action",
"Microsoft.Network/networkInterfaces/ipconfigurations/read",
"Microsoft.Network/networkInterfaces/join/action",
"Microsoft.Network/networkInterfaces/loadBalancers/read",
"Microsoft.Network/networkInterfaces/read",
"Microsoft.Network/networkInterfaces/write",
"Microsoft.Network/networkSecurityGroups/defaultSecurityRules/read",
"Microsoft.Network/networkSecurityGroups/delete",
"Microsoft.Network/networkSecurityGroups/join/action",
"Microsoft.Network/networkSecurityGroups/read",
"Microsoft.Network/networkSecurityGroups/securityRules/delete",
"Microsoft.Network/networkSecurityGroups/securityRules/read",
"Microsoft.Network/networkSecurityGroups/securityRules/write",
"Microsoft.Network/networkSecurityGroups/write",
"Microsoft.Network/networkWatchers/configureFlowLog/action",
"Microsoft.Network/networkWatchers/delete",
"Microsoft.Network/networkWatchers/ipFlowVerify/action",
"Microsoft.Network/networkWatchers/nextHop/action",
"Microsoft.Network/networkWatchers/packetCaptures/delete",
"Microsoft.Network/networkWatchers/packetCaptures/queryStatus/action",
"Microsoft.Network/networkWatchers/packetCaptures/read",
"Microsoft.Network/networkWatchers/packetCaptures/stop/action",
"Microsoft.Network/networkWatchers/packetCaptures/write",
"Microsoft.Network/networkWatchers/queryFlowLogStatus/action",
"Microsoft.Network/networkWatchers/queryTroubleshootResult/action",
"Microsoft.Network/networkWatchers/read",
"Microsoft.Network/networkWatchers/securityGroupView/action",
"Microsoft.Network/networkWatchers/topology/action",
"Microsoft.Network/networkWatchers/troubleshoot/action",
"Microsoft.Network/networkWatchers/write",
"Microsoft.Network/operations/read",
"Microsoft.Network/publicIPAddresses/delete",
"Microsoft.Network/publicIPAddresses/join/action",
"Microsoft.Network/publicIPAddresses/read",
"Microsoft.Network/publicIPAddresses/write",
"Microsoft.Network/register/action",
"Microsoft.Network/routeFilters/delete",
"Microsoft.Network/routeFilters/join/action",
"Microsoft.Network/routeFilters/read",
"Microsoft.Network/routeFilters/rules/delete",
"Microsoft.Network/routeFilters/rules/read",
"Microsoft.Network/routeFilters/rules/write",
"Microsoft.Network/routeFilters/write",
"Microsoft.Network/routeTables/delete",
"Microsoft.Network/routeTables/join/action",
"Microsoft.Network/routeTables/read",
"Microsoft.Network/routeTables/routes/delete",
"Microsoft.Network/routeTables/routes/read",
"Microsoft.Network/routeTables/routes/write",
"Microsoft.Network/routeTables/write",
"Microsoft.Network/trafficManagerGeographicHierarchies/read",
"Microsoft.Network/trafficManagerProfiles/azureEndpoints/delete",
"Microsoft.Network/trafficManagerProfiles/azureEndpoints/read",
"Microsoft.Network/trafficManagerProfiles/azureEndpoints/write",
"Microsoft.Network/trafficManagerProfiles/delete",
"Microsoft.Network/trafficManagerProfiles/externalEndpoints/delete",
"Microsoft.Network/trafficManagerProfiles/externalEndpoints/read",
"Microsoft.Network/trafficManagerProfiles/externalEndpoints/write",
"Microsoft.Network/trafficManagerProfiles/nestedEndpoints/delete",
"Microsoft.Network/trafficManagerProfiles/nestedEndpoints/read",
"Microsoft.Network/trafficManagerProfiles/nestedEndpoints/write",
"Microsoft.Network/trafficManagerProfiles/read",
"Microsoft.Network/trafficManagerProfiles/write",
"Microsoft.Network/unregister/action",
"Microsoft.Network/virtualnetworkgateways/supportedvpndevices/read",
"Microsoft.Network/virtualNetworks/checkIpAddressAvailability/read",
"Microsoft.Network/virtualNetworks/delete",
"Microsoft.Network/virtualNetworks/peer/action",
"Microsoft.Network/virtualNetworks/read",
"Microsoft.Network/virtualNetworks/subnets/delete",
"Microsoft.Network/virtualNetworks/subnets/join/action",
"Microsoft.Network/virtualNetworks/subnets/joinPrivateAccessService/action",
"Microsoft.Network/virtualNetworks/subnets/joinViaServiceTunnel/action",
"Microsoft.Network/virtualNetworks/subnets/read",
"Microsoft.Network/virtualNetworks/subnets/virtualMachines/read",
"Microsoft.Network/virtualNetworks/subnets/write",
"Microsoft.Network/virtualNetworks/virtualMachines/read",
"Microsoft.Network/virtualNetworks/virtualNetworkPeerings/delete",
"Microsoft.Network/virtualNetworks/virtualNetworkPeerings/read",
"Microsoft.Network/virtualNetworks/virtualNetworkPeerings/write",
"Microsoft.Network/virtualNetworks/write",
"Microsoft.Authorization/*/Delete", 
"Microsoft.Authorization/*/Write",
"Microsoft.Authorization/elevateAccess/Action"
 ],
  "AssignableScopes": [
    "/subscriptions/123456789-1234-1234-1234-123456789123",
  ]
}

You will notice the last three entries

"Microsoft.Authorization/*/Delete", 
"Microsoft.Authorization/*/Write",
"Microsoft.Authorization/elevateAccess/Action"

These will stop the administrator being able to change the permissions for themselves.  Clearly if I missed this, the whole exercise would be pointless!

If you want to use this JSON file, you will just need to replace the name, description and the subscription ID.

You will then need to create a new Role from the JSON file.  This article has more detail on how to do this, but the PowerShell command you will need to run is
New-AzureRmRoleDefinition -InputFile "C:\CustomRoles\customrole1.json"

Role in Azure Portal
And there you have it!  A custom Azure Role that can do nearly everything other than VMs, network and permissions.