Using an Azure Automation Account Runbook to create and email a Duo report with SendGrid

I received a lot of great feedback for my previous Azure Function App with Logic App posts, which demonstrated how to create HTML reports that were scheduled to be mailed out at the end of the one. A common question that I received after the posts was whether it was possible to create these reports with Azure Automation Accounts and Runbooks. The answer is certainly, yes, and I would like to demonstrate this with another report I had created for providing a count of the amount of users provisioned in Cisco Duo, which is commonly used for providing 2-factor authentication.

Along with using an Azure Automation Account for reports, I would also like to use this post to demonstrate the following:

1. How to import custom PowerShell Module into an Automation Account (we’ll be using a the Duo PowerShell module that Matt Egan has created and shared via his GitHub: https://github.com/mbegan/Duo-PSModule)
2. How to use a Automation Account Runbook to generate a report
3. Storing and retrieving a secret in Azure Key Vault (we’ll be storing the API key for SendGrid in an Azure Key Vault)
4. Using SendGrid to the report via email

Step #1 – Create an Automation Account

The first step is to create an Automation Account that we’ll be using to host the Runbook to generate the report and use SendGrid to send it out. We’ll be storing the SendGrid API key in an Azure Key Vault and therefore need to grant access to those keys for the Automation Account and that’s why this needs to be created first.

We’ll need the Automation Account’s System assigned Object (principal) ID to grant it permissions to access the Key Vault secret so navigate to the Identity blade and copy the Object (principal) ID that will be used in the following steps:

Step #2 – Create an Azure Key Vault, add SendGrid API Key as Secret, grant Automation Account permissions to read secret

With the Automation Account created, proceed to create an Azure Key Vault if you do not already have one:

The preferred way for the Access configuration today is Azure role-based access control so our Automation Account will only be able to access a specific secret. The older Vault access policy requires permissions to be granted to the whole vault, which makes it difficult to secure other secrets in the vault. Proceed to select Azure role-based access control as the Permission model under the Access Policy menu:

Customize the settings as required or leave them as default and create the Azure Key Vault.

The account used to create the new vault will not have any permissions to the vault so attempting to perform any operations areas such as Secrets will display the message: You are unauthorized to view these contents.

Proceed to grant the account you’re logged in as through the Access control (IAM) blade > Add role assignment:

Search for Key Vault Administrator and grant the permission:

With the appropriate permissions to the vault assigned, proceed to create a new Secret to store the SendGrid API Key:

With the secret created, we can now grant the Automation Account the ability to retrieve the secret. Click on the SendGrid secret:

In the SendGrid secret, navigate to Access Control (IAM) blade, Add, then Add role assignment:

Select Key Vault Secrets User as the role:

Select Managed identity, then locate the Automation Account and select it:

Proceed to assign the permissions:

The Automation Account should now have permissions to retrieve the SendGrid secret value;

Step #3 – Import the custom Duo PowerShell Module

Rather than attempt to write the PowerShell code required to authenticate with the Duo Admin API (https://duo.com/docs/adminapi) with a HMAC signature, then call the API methods, we’ll be using Matt Egan’s PowerShell module he has shared with the community years ago that still works today https://github.com/mbegan/Duo-PSModule

The Duo PowerShell module Matt Egan provided does not simply upload into Azure Automation’s Modules blade as the psd1 file references to the Duo_org.ps1 file that is mean to store the information required to connect to the Duo API.

Neil Sabol has a great write up that explains this and how to workaround the issue so I’ll be using his method to demonstrate the configuration: https://blog.neilsabol.site/post/importing-duo-psmodule-mfa-powershell-module-azure-automation/

The method I’ll be using is not to upload a blank Duo_org.ps1 file but rather comment all references to it in the Duo.psd1 file. You can find the updated file here in my GitHub: https://github.com/terenceluk/Azure/blob/main/Automation%20Runbook/Duo/Duo.psd1

Proceed to download the Duo.psd1 and Duo.psm1 from my GitHub https://github.com/terenceluk/Azure/tree/main/Automation%20Runbook/Duo, zip them up into a package named Duo.zip (make sure Duo.zip is the file name) then import them into the Automation Account Modules:

I haven’t had much luck with 7.1 as the Runtime version so proceed to select 5.1 as the Runtime version:

Initiate the import:

Confirm the module has successfully imported:

One of the ways to check and see if the module imported properly is by clicking into the module and verify that the available cmdlets are displayed:

Step #4 – Create a Protected Application in Duo and add authentication information as Automation Account encrypted variables

Using the Duo Admin API requires authentication so we’ll need to create a protected application in the Duo Admin portal as described in the document here: https://duo.com/docs/adminapi

Copy the Integration key, Secret key, and API hostname as we’ll need them to create the encrypted variables in the following steps, and grant the application the required permissions:

Proceed to the Automation Account, navigate to create the following variables:

1. MyDuoDirectoryID
2. MyDuoIntegrationKey
3. MyDuoSecretKey
4. MyDuoAPIHostname

**Note that the Duo Directory ID can be located by navigating to: Users > Directory Syncs, select the configured directory then copy the key under the heading Admin API directory key:

Step #5 – Create the runbook to generate a report of the users and email via SendGrid

With all the components and permissions created and configured, the last step is to create the runbook and put the code in that will build the report and use the SendGrid API to send the email report. From within the Automation Account, navigate to the Runbooks blade:

Click on Create a runbook:

Fill in the required fields:

The following PowerShell Runbook will be displayed where we can paste the PowerShell script to be executed:

The script I will be using to generate and email the report can be found here: https://github.com/terenceluk/Azure/blob/main/Automation%20Runbook/Email-Duo-User-Count-and-List.ps1

Customize the following variables:

$VaultName = "kv-Production" # Azure Key Vault Name

$destEmailAddress = "tluk@contoso.com" # From address

$fromEmailAddress = "duoreport@contoso.com" # To address

$subject = "Duo User Report" # Email Subject

<h2>Client: Contoso Limited</h2>

Proceed to paste the code into the runbook:

Proceed to click Save:

Before publishing the Runbook, click on the Test pane button to bring up the test window, then click on the Start button to test the runbook:

Confirm that the test successfully completes:

Verify that the email sent contains the expected report as such:

Proceed to publish the report once we’ve confirmed that the report is delivered:

The last step is to schedule this runbook according to when the report should be ran so proceed to click on the Schedules blade and then Add a schedule:

Click on Link a schedule to your runbook:

Click on Add a Schedule:

Configure the desired schedule (the settings in the screenshot is configured to run on the last day of the month):

The schedules pane should now display the configured schedule and its next run time:

That’s it. Hope this helps anyone who might be looking for information on how to configure the various components demonstrated in this post.

Azure Function App using certificate authentication falls to authenticate when executing Connect-MgGraph

I was recently contacted by someone to inform me that the Function App I provided in my previous post written on July 25, 2022:

Create an automated report for Office 365 / Microsoft 365 license usage with friendly names using Azure a Function App and Logic Apps
http://terenceluk.blogspot.com/2022/07/create-automated-report-for-office-365.html

… no longer worked so I had a look at the Function App and confirmed that it would fail with the following error message:

2022-09-05T10:29:27Z [Error] ERROR: Could not load file or assembly 'Newtonsoft.Json, Version=13.0.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed'. The system cannot find the file specified.

Exception :

Type : System.IO.FileNotFoundException

Message : Could not load file or assembly 'Newtonsoft.Json, Version=13.0.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed'. The system cannot find the file specified.

FileName : Newtonsoft.Json, Version=13.0.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed

TargetSite :

Name : MoveNext

DeclaringType : Microsoft.Graph.PowerShell.Authentication.Cmdlets.ConnectMgGraph+<ProcessRecordAsync>d__56, Microsoft.Graph.Authentication, Version=1.11.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35

MemberType : Method

Module : Microsoft.Graph.Authentication.dll

StackTrace :

at Microsoft.Graph.PowerShell.Authentication.Cmdlets.ConnectMgGraph.ProcessRecordAsync()

Source : Microsoft.Graph.Authentication

HResult : -2147024894

CategoryInfo : NotSpecified: (:) [Connect-MgGraph], FileNotFoundException

FullyQualifiedErrorId : Microsoft.Graph.PowerShell.Authentication.Cmdlets.ConnectMgGraph

InvocationInfo :

MyCommand : Connect-MgGraph

ScriptLineNumber : 85

OffsetInLine : 1

HistoryId : 1

ScriptName : C:\home\site\wwwroot\HttpTrigger-Get-Licenses\run.ps1

Line : Connect-MgGraph -ClientID $appId -TenantId $tenantID -CertificateThumbprint $thumb ## Or -CertificateName "M365-License"

PositionMessage : At C:\home\site\wwwroot\HttpTrigger-Get-Licenses\run.ps1:85 char:1

+ Connect-MgGraph -ClientID $appId -TenantId $tenantID -CertificateThum …

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PSScriptRoot : C:\home\site\wwwroot\HttpTrigger-Get-Licenses

PSCommandPath : C:\home\site\wwwroot\HttpTrigger-Get-Licenses\run.ps1

InvocationName : Connect-MgGraph

CommandOrigin : Internal

ScriptStackTrace : at <ScriptBlock>, C:\home\site\wwwroot\HttpTrigger-Get-Licenses\run.ps1: line 85

PipelineIterationInfo :

2022-09-05T10:29:36Z [Error] ERROR: Authentication needed, call Connect-MgGraph.

Exception :

Type : System.Security.Authentication.AuthenticationException

TargetSite :

Name : GetGraphHttpClient

DeclaringType : Microsoft.Graph.PowerShell.Authentication.Helpers.HttpHelpers

MemberType : Method

Module : Microsoft.Graph.Authentication.dll

StackTrace :

at Microsoft.Graph.PowerShell.Authentication.Helpers.HttpHelpers.GetGraphHttpClient(InvocationInfo invocationInfo, IAuthContext authContext)

at Microsoft.Graph.PowerShell.Module.BeforeCreatePipeline(InvocationInfo invocationInfo, HttpPipeline& pipeline)

at Microsoft.Graph.PowerShell.Module.CreatePipeline(InvocationInfo invocationInfo, String parameterSetName)

at Microsoft.Graph.PowerShell.Cmdlets.GetMgSubscribedSku_List1.ProcessRecordAsync()

Message : Authentication needed, call Connect-MgGraph.

Source : Microsoft.Graph.Authentication

HResult : -2146233087

CategoryInfo : NotSpecified: (:) [Get-MgSubscribedSku_List1], AuthenticationException

FullyQualifiedErrorId : Microsoft.Graph.PowerShell.Cmdlets.GetMgSubscribedSku_List1

InvocationInfo :

MyCommand : Get-MgSubscribedSku_List1

ScriptLineNumber : 34

OffsetInLine : 1

HistoryId : 1

ScriptName : C:\home\site\wwwroot\HttpTrigger-Get-Licenses\run.ps1

Line : $licenseUsage = Get-MgSubscribedSku | Select-Object -Property SkuPartNumber,CapabilityStatus,@{Name="PrepaidUnits";expression={$_.PrepaidUnits.Enabled -join ";"}},ConsumedUnits,SkuId,AppliesTo

PositionMessage : At C:\home\site\wwwroot\HttpTrigger-Get-Licenses\run.ps1:34 char:1

+ $licenseUsage = Get-MgSubscribedSku | Select-Object -Property SkuPart …

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PSScriptRoot : C:\home\site\wwwroot\HttpTrigger-Get-Licenses

PSCommandPath : C:\home\site\wwwroot\HttpTrigger-Get-Licenses\run.ps1

InvocationName : Get-MgSubscribedSku

CommandOrigin : Internal

ScriptStackTrace : at Get-MgSubscribedSku<Process>, C:\home\data\ManagedDependencies\2209042059148689947.r\Microsoft.Graph.Identity.DirectoryManagement\1.10.0\exports\v1.0\ProxyCmdletDefinitions.ps1: line 12245

at Get-LicenseUsage, C:\home\site\wwwroot\HttpTrigger-Get-Licenses\run.ps1: line 34

at <ScriptBlock>, C:\home\site\wwwroot\HttpTrigger-Get-Licenses\run.ps1: line 88

PipelineIterationInfo :

Going through the logs revealed the two lines below which suggest that there was something wrong with using the Connect-MgGraph cmdlet to connect to Microsoft Graph:

Microsoft.Graph.Authentication, Version=1.11.1.0

2022-09-05T10:29:36Z [Error] ERROR: Authentication needed, call Connect-MgGraph.

I’ve ran into issues like this in the past which resulted in a lot of troubleshooting so I’m glad I’ve already gone through it before and immediately realized it must be because the Microsoft.Graph.Authentication module was updated and either intentionally or unintentionally no longer works with the certificate authentication I’m using. Browsing the PowerShell gallery for Microsoft.Graph (https://www.powershellgallery.com/packages/Microsoft.Graph/1.11.1) shows the latest version at the time of this writing was 1.11.1 and it was released in late August 2022, which the time I wrote the past was back in July. This lead me to believe that the latest 1.11.1 version is the reason for the error.

Reviewing the requirements.psd1 I had created for the Function App shows that any major version 1.* should be used:

'JoinModule' = '3.*'

'Microsoft.Graph.Identity.DirectoryManagement' = '1.*'

'Microsoft.Graph.Authentication' = '1.*'

}

To correct the issue, I reviewed the version of the Microsoft.Graph.Authentication module I had on my local computer (version 1.10.0), tested the script locally to confirm it worked, then updated the requirements.psd1 for the function app to specify a specific version:

# 'Az' = '6.*'

'JoinModule' = '3.*'

'Microsoft.Graph.Identity.DirectoryManagement' = '1.10.0'

'Microsoft.Graph.Authentication' = '1.10.0'

}

Then as per the Microsoft documentation that explains how to target a specific version (https://docs.microsoft.com/en-us/azure/azure-functions/functions-reference-powershell?tabs=portal#dependency-management), navigated into the profile.ps1 file in the App files:

.. added the import statement to import the modules:

Import-Module Microsoft.Graph.Identity.DirectoryManagement -RequiredVersion '1.10.0'
Import-Module Microsoft.Graph.Authentication -RequiredVersion '1.10.0'

Reference article: https://docs.microsoft.com/en-us/azure/azure-functions/functions-reference-powershell?tabs=portal#target-specific-versions

Once the above steps are completed, attempting to execute the function app may still fail to run and display the following error message:

2022-09-05T10:23:37Z [Error] ERROR: Assembly with same name is already loaded

Exception :

Type : System.IO.FileLoadException

Message : Assembly with same name is already loaded

TargetSite :

Name : LoadBinaryModule

DeclaringType : Microsoft.PowerShell.Commands.ModuleCmdletBase

MemberType : Method

Module : System.Management.Automation.dll

StackTrace :

at Microsoft.PowerShell.Commands.ModuleCmdletBase.LoadBinaryModule(PSModuleInfo parentModule, Boolean trySnapInName, String moduleName, String fileName, Assembly assemblyToLoad, String moduleBase, SessionState ss, ImportModuleOptions options, ManifestProcessingFlags manifestProcessingFlags, String prefix, Boolean loadTypes, Boolean loadFormats, Boolean& found, String shortModuleName, Boolean disableFormatUpdates)

at Microsoft.PowerShell.Commands.ModuleCmdletBase.LoadBinaryModule(Boolean trySnapInName, String moduleName, String fileName, Assembly assemblyToLoad, String moduleBase, SessionState ss, ImportModuleOptions options, ManifestProcessingFlags manifestProcessingFlags, String prefix, Boolean loadTypes, Boolean loadFormats, Boolean& found)

at Microsoft.PowerShell.Commands.ModuleCmdletBase.LoadModule(PSModuleInfo parentModule, String fileName, String moduleBase, String prefix, SessionState ss, Object privateData, ImportModuleOptions& options, ManifestProcessingFlags manifestProcessingFlags, Boolean& found, Boolean& moduleFileFound)

at Microsoft.PowerShell.Commands.ModuleCmdletBase.LoadModule(String fileName, String moduleBase, String prefix, SessionState ss, ImportModuleOptions& options, ManifestProcessingFlags manifestProcessingFlags, Boolean& found)

at Microsoft.PowerShell.Commands.ImportModuleCommand.ImportModule_LocallyViaName(ImportModuleOptions importModuleOptions, String name)

at Microsoft.PowerShell.Commands.ImportModuleCommand.ImportModule_LocallyViaName_WithTelemetry(ImportModuleOptions importModuleOptions, String name)

at Microsoft.PowerShell.Commands.ImportModuleCommand.ProcessRecord()

at System.Management.Automation.CommandProcessor.ProcessRecord()

Source : System.Management.Automation

HResult : -2146232799

CategoryInfo : NotSpecified: (:) [Import-Module], FileLoadException

FullyQualifiedErrorId : System.IO.FileLoadException,Microsoft.PowerShell.Commands.ImportModuleCommand

InvocationInfo :

MyCommand : Import-Module

ScriptLineNumber : 4

OffsetInLine : 9

HistoryId : 1

ScriptName : C:\home\data\ManagedDependencies\2209042059148689947.r\Microsoft.Graph.Authentication\1.10.0\Microsoft.Graph.Authentication.psm1

Line : $null = Import-Module -Name $ModulePath

PositionMessage : At C:\home\data\ManagedDependencies\2209042059148689947.r\Microsoft.Graph.Authentication\1.10.0\Microsoft.Graph.Authentication.psm1:4 char:9

+ $null = Import-Module -Name $ModulePath

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PSScriptRoot : C:\home\data\ManagedDependencies\2209042059148689947.r\Microsoft.Graph.Authentication\1.10.0

PSCommandPath : C:\home\data\ManagedDependencies\2209042059148689947.r\Microsoft.Graph.Authentication\1.10.0\Microsoft.Graph.Authentication.psm1

InvocationName : Import-Module

CommandOrigin : Internal

ScriptStackTrace : at <ScriptBlock>, C:\home\data\ManagedDependencies\2209042059148689947.r\Microsoft.Graph.Authentication\1.10.0\Microsoft.Graph.Authentication.psm1: line 4

at <ScriptBlock>, C:\home\site\wwwroot\profile.ps1: line 20

2022-09-05T10:23:39Z [Warning] The Function app may be missing a module containing the 'Get-ScriptCmdlet' command definition. If this command belongs to a module available on the PowerShell Gallery, add a reference to this module to requirements.psd1. Make sure this module is compatible with PowerShell 7. For more details, see https://aka.ms/functions-powershell-managed-dependency. If the module is installed but you are still getting this error, try to import the module explicitly by invoking Import-Module just before the command that produces the error: this will not fix the issue but will expose the root cause.

2022-09-05T10:23:39Z [Error] ERROR: The term 'Get-ScriptCmdlet' is not recognized as the name of a cmdlet, function, script file, or operable program.

Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

Exception :

Type : System.Management.Automation.CommandNotFoundException

ErrorRecord :

Exception :

Type : System.Management.Automation.ParentContainsErrorRecordException

Message : The term 'Get-ScriptCmdlet' is not recognized as the name of a cmdlet, function, script file, or operable program.

Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

HResult : -2146233087

TargetObject : Get-ScriptCmdlet

CategoryInfo : ObjectNotFound: (Get-ScriptCmdlet:String) [], ParentContainsErrorRecordException

FullyQualifiedErrorId : CommandNotFoundException

InvocationInfo :

ScriptLineNumber : 11

OffsetInLine : 36

HistoryId : 1

ScriptName : C:\home\data\ManagedDependencies\2209042059148689947.r\Microsoft.Graph.Authentication\1.10.0\StartupScripts\ExportCustomCommands.ps1

Line : Export-ModuleMember -Function (Get-ScriptCmdlet -ScriptFolder $CustomScriptPath) -Alias (Get-ScriptCmdlet -ScriptFolder $CustomScriptPath -AsAlias)

PositionMessage : At C:\home\data\ManagedDependencies\2209042059148689947.r\Microsoft.Graph.Authentication\1.10.0\StartupScripts\ExportCustomCommands.ps1:11 char:36

+ Export-ModuleMember -Function (Get-ScriptCmdlet -ScriptFolder $Cu …

+ ~~~~~~~~~~~~~~~~

PSScriptRoot : C:\home\data\ManagedDependencies\2209042059148689947.r\Microsoft.Graph.Authentication\1.10.0\StartupScripts

PSCommandPath : C:\home\data\ManagedDependencies\2209042059148689947.r\Microsoft.Graph.Authentication\1.10.0\StartupScripts\ExportCustomCommands.ps1

InvocationName : Get-ScriptCmdlet

CommandOrigin : Internal

ScriptStackTrace : at <ScriptBlock>, C:\home\data\ManagedDependencies\2209042059148689947.r\Microsoft.Graph.Authentication\1.10.0\StartupScripts\ExportCustomCommands.ps1: line 11

at <ScriptBlock>, C:\home\data\ManagedDependencies\2209042059148689947.r\Microsoft.Graph.Authentication\1.10.0\Microsoft.Graph.Authentication.psm1: line 12

at <ScriptBlock>, C:\home\data\ManagedDependencies\2209042059148689947.r\Microsoft.Graph.Authentication\1.10.0\Microsoft.Graph.Authentication.psm1: line 11

at <ScriptBlock>, C:\home\site\wwwroot\profile.ps1: line 20

CommandName : Get-ScriptCmdlet

TargetSite :

Name : LookupCommandInfo

DeclaringType : System.Management.Automation.CommandDiscovery, System.Management.Automation, Version=7.0.11.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35

MemberType : Method

Module : System.Management.Automation.dll

StackTrace :

at System.Management.Automation.CommandDiscovery.LookupCommandInfo(String commandName, CommandTypes commandTypes, SearchResolutionOptions searchResolutionOptions, CommandOrigin commandOrigin, ExecutionContext context)

at System.Management.Automation.CommandDiscovery.LookupCommandProcessor(String commandName, CommandOrigin commandOrigin, Nullable`1 useLocalScope)

at System.Management.Automation.ExecutionContext.CreateCommand(String command, Boolean dotSource)

at System.Management.Automation.PipelineOps.AddCommand(PipelineProcessor pipe, CommandParameterInternal[] commandElements, CommandBaseAst commandBaseAst, CommandRedirection[] redirections, ExecutionContext context)

at System.Management.Automation.PipelineOps.InvokePipeline(Object input, Boolean ignoreInput, CommandParameterInternal[][] pipeElements, CommandBaseAst[] pipeElementAsts, CommandRedirection[][] commandRedirections, FunctionContext funcContext)

at System.Management.Automation.Interpreter.ActionCallInstruction`6.Run(InterpretedFrame frame)

at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)

at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)

Message : The term 'Get-ScriptCmdlet' is not recognized as the name of a cmdlet, function, script file, or operable program.

Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

Data : System.Collections.ListDictionaryInternal

Source : System.Management.Automation

HResult : -2146233087

TargetObject : Get-ScriptCmdlet

CategoryInfo : ObjectNotFound: (Get-ScriptCmdlet:String) [], CommandNotFoundException

FullyQualifiedErrorId : CommandNotFoundException

InvocationInfo :

ScriptLineNumber : 11

OffsetInLine : 36

HistoryId : 1

ScriptName : C:\home\data\ManagedDependencies\2209042059148689947.r\Microsoft.Graph.Authentication\1.10.0\StartupScripts\ExportCustomCommands.ps1

Line : Export-ModuleMember -Function (Get-ScriptCmdlet -ScriptFolder $CustomScriptPath) -Alias (Get-ScriptCmdlet -ScriptFolder $CustomScriptPath -AsAlias)

PositionMessage : At C:\home\data\ManagedDependencies\2209042059148689947.r\Microsoft.Graph.Authentication\1.10.0\StartupScripts\ExportCustomCommands.ps1:11 char:36

+ Export-ModuleMember -Function (Get-ScriptCmdlet -ScriptFolder $Cu …

+ ~~~~~~~~~~~~~~~~

PSScriptRoot : C:\home\data\ManagedDependencies\2209042059148689947.r\Microsoft.Graph.Authentication\1.10.0\StartupScripts

PSCommandPath : C:\home\data\ManagedDependencies\2209042059148689947.r\Microsoft.Graph.Authentication\1.10.0\StartupScripts\ExportCustomCommands.ps1

InvocationName : Get-ScriptCmdlet

CommandOrigin : Internal

ScriptStackTrace : at <ScriptBlock>, C:\home\data\ManagedDependencies\2209042059148689947.r\Microsoft.Graph.Authentication\1.10.0\StartupScripts\ExportCustomCommands.ps1: line 11

at <ScriptBlock>, C:\home\data\ManagedDependencies\2209042059148689947.r\Microsoft.Graph.Authentication\1.10.0\Microsoft.Graph.Authentication.psm1: line 12

at <ScriptBlock>, C:\home\data\ManagedDependencies\2209042059148689947.r\Microsoft.Graph.Authentication\1.10.0\Microsoft.Graph.Authentication.psm1: line 11

at <ScriptBlock>, C:\home\site\wwwroot\profile.ps1: line 20

2022-09-05T10:23:39Z [Warning] The Function app may be missing a module containing the 'Get-ModuleCmdlet' command definition. If this command belongs to a module available on the PowerShell Gallery, add a reference to this module to requirements.psd1. Make sure this module is compatible with PowerShell 7. For more details, see https://aka.ms/functions-powershell-managed-dependency. If the module is installed but you are still getting this error, try to import the module explicitly by invoking Import-Module just before the command that produces the error: this will not fix the issue but will expose the root cause.

2022-09-05T10:23:39Z [Error] ERROR: The term 'Get-ModuleCmdlet' is not recognized as the name of a cmdlet, function, script file, or operable program.

Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

Exception :

Type : System.Management.Automation.CommandNotFoundException

ErrorRecord :

Exception :

Type : System.Management.Automation.ParentContainsErrorRecordException

Message : The term 'Get-ModuleCmdlet' is not recognized as the name of a cmdlet, function, script file, or operable program.

Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

HResult : -2146233087

TargetObject : Get-ModuleCmdlet

CategoryInfo : ObjectNotFound: (Get-ModuleCmdlet:String) [], ParentContainsErrorRecordException

FullyQualifiedErrorId : CommandNotFoundException

InvocationInfo :

ScriptLineNumber : 17

OffsetInLine : 30

HistoryId : 1

ScriptName : C:\home\data\ManagedDependencies\2209042059148689947.r\Microsoft.Graph.Authentication\1.10.0\Microsoft.Graph.Authentication.psm1

Line : Export-ModuleMember -Cmdlet (Get-ModuleCmdlet -ModulePath $ModulePath) -Alias (Get-ModuleCmdlet -ModulePath $ModulePath -AsAlias)

PositionMessage : At C:\home\data\ManagedDependencies\2209042059148689947.r\Microsoft.Graph.Authentication\1.10.0\Microsoft.Graph.Authentication.psm1:17 char:30

+ Export-ModuleMember -Cmdlet (Get-ModuleCmdlet -ModulePath $ModulePath …

+ ~~~~~~~~~~~~~~~~

PSScriptRoot : C:\home\data\ManagedDependencies\2209042059148689947.r\Microsoft.Graph.Authentication\1.10.0

PSCommandPath : C:\home\data\ManagedDependencies\2209042059148689947.r\Microsoft.Graph.Authentication\1.10.0\Microsoft.Graph.Authentication.psm1

InvocationName : Get-ModuleCmdlet

CommandOrigin : Internal

ScriptStackTrace : at <ScriptBlock>, C:\home\data\ManagedDependencies\2209042059148689947.r\Microsoft.Graph.Authentication\1.10.0\Microsoft.Graph.Authentication.psm1: line 17

at <ScriptBlock>, C:\home\site\wwwroot\profile.ps1: line 20

CommandName : Get-ModuleCmdlet

TargetSite :

Name : LookupCommandInfo

DeclaringType : System.Management.Automation.CommandDiscovery, System.Management.Automation, Version=7.0.11.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35

MemberType : Method

Module : System.Management.Automation.dll

StackTrace :

at System.Management.Automation.CommandDiscovery.LookupCommandInfo(String commandName, CommandTypes commandTypes, SearchResolutionOptions searchResolutionOptions, CommandOrigin commandOrigin, ExecutionContext context)

at System.Management.Automation.CommandDiscovery.LookupCommandProcessor(String commandName, CommandOrigin commandOrigin, Nullable`1 useLocalScope)

at System.Management.Automation.ExecutionContext.CreateCommand(String command, Boolean dotSource)

at System.Management.Automation.PipelineOps.AddCommand(PipelineProcessor pipe, CommandParameterInternal[] commandElements, CommandBaseAst commandBaseAst, CommandRedirection[] redirections, ExecutionContext context)

at System.Management.Automation.PipelineOps.InvokePipeline(Object input, Boolean ignoreInput, CommandParameterInternal[][] pipeElements, CommandBaseAst[] pipeElementAsts, CommandRedirection[][] commandRedirections, FunctionContext funcContext)

at System.Management.Automation.Interpreter.ActionCallInstruction`6.Run(InterpretedFrame frame)

at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)

at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)

Message : The term 'Get-ModuleCmdlet' is not recognized as the name of a cmdlet, function, script file, or operable program.

Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

Data : System.Collections.ListDictionaryInternal

Source : System.Management.Automation

HResult : -2146233087

TargetObject : Get-ModuleCmdlet

CategoryInfo : ObjectNotFound: (Get-ModuleCmdlet:String) [], CommandNotFoundException

FullyQualifiedErrorId : CommandNotFoundException

InvocationInfo :

ScriptLineNumber : 17

OffsetInLine : 30

HistoryId : 1

ScriptName : C:\home\data\ManagedDependencies\2209042059148689947.r\Microsoft.Graph.Authentication\1.10.0\Microsoft.Graph.Authentication.psm1

Line : Export-ModuleMember -Cmdlet (Get-ModuleCmdlet -ModulePath $ModulePath) -Alias (Get-ModuleCmdlet -ModulePath $ModulePath -AsAlias)

PositionMessage : At C:\home\data\ManagedDependencies\2209042059148689947.r\Microsoft.Graph.Authentication\1.10.0\Microsoft.Graph.Authentication.psm1:17 char:30

+ Export-ModuleMember -Cmdlet (Get-ModuleCmdlet -ModulePath $ModulePath …

+ ~~~~~~~~~~~~~~~~

PSScriptRoot : C:\home\data\ManagedDependencies\2209042059148689947.r\Microsoft.Graph.Authentication\1.10.0

PSCommandPath : C:\home\data\ManagedDependencies\2209042059148689947.r\Microsoft.Graph.Authentication\1.10.0\Microsoft.Graph.Authentication.psm1

InvocationName : Get-ModuleCmdlet

CommandOrigin : Internal

ScriptStackTrace : at <ScriptBlock>, C:\home\data\ManagedDependencies\2209042059148689947.r\Microsoft.Graph.Authentication\1.10.0\Microsoft.Graph.Authentication.psm1: line 17

at <ScriptBlock>, C:\home\site\wwwroot\profile.ps1: line 20

2022-09-05T10:23:40Z [Error] Errors reported while executing profile.ps1. See logs for detailed errors. Profile location: C:\home\site\wwwroot\profile.ps1.

2022-09-05T10:23:40Z [Information] INFORMATION: PowerShell HTTP trigger function processed a request.

2022-09-05T10:23:42Z [Error] ERROR: Could not load file or assembly 'Newtonsoft.Json, Version=13.0.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed'. The system cannot find the file specified.

Exception :

Type : System.IO.FileNotFoundException

Message : Could not load file or assembly 'Newtonsoft.Json, Version=13.0.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed'. The system cannot find the file specified.

FileName : Newtonsoft.Json, Version=13.0.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed

TargetSite :

Name : MoveNext

DeclaringType : Microsoft.Graph.PowerShell.Authentication.Cmdlets.ConnectMgGraph+<ProcessRecordAsync>d__56, Microsoft.Graph.Authentication, Version=1.11.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35

MemberType : Method

Module : Microsoft.Graph.Authentication.dll

StackTrace :

at Microsoft.Graph.PowerShell.Authentication.Cmdlets.ConnectMgGraph.ProcessRecordAsync()

Source : Microsoft.Graph.Authentication

HResult : -2147024894

CategoryInfo : NotSpecified: (:) [Connect-MgGraph], FileNotFoundException

FullyQualifiedErrorId : Microsoft.Graph.PowerShell.Authentication.Cmdlets.ConnectMgGraph

InvocationInfo :

MyCommand : Connect-MgGraph

ScriptLineNumber : 85

OffsetInLine : 1

HistoryId : 1

ScriptName : C:\home\site\wwwroot\HttpTrigger-Get-Licenses\run.ps1

Line : Connect-MgGraph -ClientID $appId -TenantId $tenantID -CertificateThumbprint $thumb ## Or -CertificateName "M365-License"

PositionMessage : At C:\home\site\wwwroot\HttpTrigger-Get-Licenses\run.ps1:85 char:1

+ Connect-MgGraph -ClientID $appId -TenantId $tenantID -CertificateThum …

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PSScriptRoot : C:\home\site\wwwroot\HttpTrigger-Get-Licenses

PSCommandPath : C:\home\site\wwwroot\HttpTrigger-Get-Licenses\run.ps1

InvocationName : Connect-MgGraph

CommandOrigin : Internal

ScriptStackTrace : at <ScriptBlock>, C:\home\site\wwwroot\HttpTrigger-Get-Licenses\run.ps1: line 85

PipelineIterationInfo :

2022-09-05T10:23:42Z [Error] ERROR: Authentication needed, call Connect-MgGraph.

Exception :

Type : System.Security.Authentication.AuthenticationException

TargetSite :

Name : GetGraphHttpClient

DeclaringType : Microsoft.Graph.PowerShell.Authentication.Helpers.HttpHelpers

MemberType : Method

Module : Microsoft.Graph.Authentication.dll

StackTrace :

at Microsoft.Graph.PowerShell.Authentication.Helpers.HttpHelpers.GetGraphHttpClient(InvocationInfo invocationInfo, IAuthContext authContext)

at Microsoft.Graph.PowerShell.Module.BeforeCreatePipeline(InvocationInfo invocationInfo, HttpPipeline& pipeline)

at Microsoft.Graph.PowerShell.Module.CreatePipeline(InvocationInfo invocationInfo, String parameterSetName)

at Microsoft.Graph.PowerShell.Cmdlets.GetMgSubscribedSku_List1.ProcessRecordAsync()

Message : Authentication needed, call Connect-MgGraph.

Source : Microsoft.Graph.Authentication

HResult : -2146233087

CategoryInfo : NotSpecified: (:) [Get-MgSubscribedSku_List1], AuthenticationException

FullyQualifiedErrorId : Microsoft.Graph.PowerShell.Cmdlets.GetMgSubscribedSku_List1

InvocationInfo :

MyCommand : Get-MgSubscribedSku_List1

ScriptLineNumber : 34

OffsetInLine : 1

HistoryId : 1

ScriptName : C:\home\site\wwwroot\HttpTrigger-Get-Licenses\run.ps1

Line : $licenseUsage = Get-MgSubscribedSku | Select-Object -Property SkuPartNumber,CapabilityStatus,@{Name="PrepaidUnits";expression={$_.PrepaidUnits.Enabled -join ";"}},ConsumedUnits,SkuId,AppliesTo

PositionMessage : At C:\home\site\wwwroot\HttpTrigger-Get-Licenses\run.ps1:34 char:1

+ $licenseUsage = Get-MgSubscribedSku | Select-Object -Property SkuPart …

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PSScriptRoot : C:\home\site\wwwroot\HttpTrigger-Get-Licenses

PSCommandPath : C:\home\site\wwwroot\HttpTrigger-Get-Licenses\run.ps1

InvocationName : Get-MgSubscribedSku

CommandOrigin : Internal

ScriptStackTrace : at Get-MgSubscribedSku<Process>, C:\home\data\ManagedDependencies\2209042059148689947.r\Microsoft.Graph.Identity.DirectoryManagement\1.10.0\exports\v1.0\ProxyCmdletDefinitions.ps1: line 12245

at Get-LicenseUsage, C:\home\site\wwwroot\HttpTrigger-Get-Licenses\run.ps1: line 34

at <ScriptBlock>, C:\home\site\wwwroot\HttpTrigger-Get-Licenses\run.ps1: line 88

PipelineIterationInfo :

The error message Assembly with the same name is already loaded is caused by the newer module that has already been downloaded, which is causing the older module we are trying to import to fail. One of the ways to resolve this is remove the new module with Kudu.

Every app that is created has a companion app created for it (https://docs.microsoft.com/en-us/azure/app-service/resources-kudu#access-kudu-for-your-app) and this app named Kudu can be accessed via:

Go to: https://<app-name>.scm.azurewebsites.net

You can browse the directories of the app but clicking on Debug console > PowerShell:

Navigate to: data

Then: ManagedDependencies

You may find several folders in this directory:

Proceed to browse into these folders and you’ll likely see the newer 1.11.1:

Using the edit button to review the contents of requirements.psd1 will also show the older version using 1.* for the modules:

Proceed to delete the old ManagedDependencies folder:

Then delete the newer 1.11.1 folder in the remaining ManagedDependencies folder:

With the newer module removed, we should now be able to run the function app:

Hope this helps anyone who may have used my previous post and noticed it did not work.

Using Azure Function App and Logic Apps to create an automated report that downloads a CSV, creates a HTML table, sends an email with the HTML table and attaches the CSV

In this post, I would like to demonstrate the following using an Azure Function App and Logic App.

Function App:

1. Download a CSV file from a URL
2. Count the amount of records in the table
3. Convert the CSV data into a table in HTML format
4. Return a HTML formatted email for delivery

Logic App:

1. Set up a recurring Logic App that runs at the end of the month
2. Executes the Function App to retrieve the HTML formatted email report
3. Download the CSV file from a URL
4. Send an email with the HTML formatted email report with the CSV as the attachment

I will use a device list downloaded from a Cylance tenant to demonstrate this. For those who are not familiar with Cylance, it is a cyber threat detection software for endpoints such as Windows and Mac operating systems. It is possible to retrieve reports via a URL with a unique token that belongs to an organization’s tenant. Here is what the portal with the URLs look like:

We’ll be using the Devices URL with the unique token to retrieve our report:

https://protect-sae1.cylance.com/Reports/ThreatDataReportV1/devices/4B1FFFxxxxxxxxxxx296

The downloaded report will look as such:

Step #1 – Create a Function App that will retrieve Cylance Device List and generate and return a HTML email report

Begin by creating a Function App that will retrieve Cylance Device List and return it in HTML format. This Function App collects the data that will in turn be call by a Logic App to generate an email and send the report off to an email address.

Proceed to create a Function App with the following parameters:

Publish: Code

Runtime stack: PowerShell Core

Version: 7.2

Operating System: Windows

Configure the rest of the parameters as required by the environment.

With the Function App created, proceed to create the function trigger:

Select HTTP trigger as the template and provide a meaningful name:

With the trigger created, navigate to Code + Test and paste the following code into run.ps1:

https://github.com/terenceluk/Azure/blob/main/Function%20App/Get-CylanceDeviceReport.ps1

The following are changes you’ll need to apply to the code:

The client name:

With the function app code in place, proceed to use the Test/Run feature to test it. Note that the function app expects the tenant ID to be passed to it so the Body of the test should include the following:

{

  "token": "xxxxxxxxxxxxxxxxxxxxxxxxxx"

}

Confirm the HTTP response code of 200 OK and the HTTP response content results:

Step #2 – Create a Logic App that is scheduled and will download the device list CSV file, call the Azure Function App to retrieve the device list report and then send it out with the CSV as an attachment

With the Azure Function App created and tested, proceed to create the Logic App that will be scheduled, download the device list CSV file, call the Function App for the HTML device list report and then email it out.

Navigate to the Logic app designer blade and begin to configure the steps for the Logic App. The following are the steps we’ll be configuring:

The first is the Recurrence step that will schedule this logic app to run on the last day of each month:

**Note that the GUI doesn’t provide the required controls so we’ll be using the JSON code provided in this document for the configuration: https://docs.microsoft.com/en-us/azure/logic-apps/concepts-schedule-automated-recurring-tasks-workflows#run-once-at-last-day-of-the-month

Click on Code View:

Then edit the triggers section:

                },

"recurrence": {

"frequency": "Month",

"interval": 1,

"schedule": {

"monthDays": [-1]

                    }

Create an additional step by clicking on the + button and select Add an action then type in Function:

Select the Function App that was created:

Select the trigger that was created:

Place the body containing the tenant ID into the Request Body:

{

  "token": "xxxxxxxxxxxxxxxxxxxxxxxxxx"

}

Proceed to create two additional steps:

1. Initialize variable
2. Set variable

These two steps will place the retrieved HTML report into the body of the email:

Initialize variable

Name: EmailBody
Type: String
Value: <leave blank>

Set variable

Name: EmailBody
Value: Select the Body

Continue to create the HTTP step to download the device list so we can attach the CSV file as an attachment:

Configure the following parameters:

Method: GET
URI: Place the URL with the token to download the device list

**Note that I have hardcoded the token into the URI. It is possible to declare this as a variable and pass it into here as well.

Add the last step that will email this report to the email address required:

Note that we are placing the EmailBody variable we created from the Function App output into the Body, and attaching the Body from the HTTP step as an attachment. The Attachments Name can be any name you prefer.

Proceed to use the Run Trigger feature to execute the Logic App and confirm that the report is generated and sent:

I hope this helps anyone who may be looking for instructions on how to configure automated reports.