Pages

Wednesday, August 23, 2023

Using Azure Resource Graph Explorer to determine what resources are sending diagnostic data to a Log Analytics Workspace

One of the questions I am frequently asked is how we can effectively determine what resources are sending data to a particular Log Analytics Workspace. Those who are administrators of Azure will know that most subscriptions will eventually contain Log Analytics Workspaces as shown in the list and screenshot below:

  • DefaultWorkspace-d3f0e229-2fcd-45df-a791-614ba183e648-canadaea
  • DefaultWorkspace-d3f0e229-2fcd-45df-a791-614ba183e648-CCAN
  • DefaultWorkspace-d3f0e229-2fcd-45df-a791-614ba183e648-EUS
image

This isn’t the fault of poor management as many resources such as Insights would automatically default to these types of workspaces when they are enabled.

Attempting to browse the blades in these Log Analytics Workspaces will not allow us to easily determine what resources in Azure are sending data to the Log Analytics Workspace:

image

While it is possible to review the type of tables created and if the schema and data stored is known, then we could possibly query the data for the resources but this can be prone to errors causing resources to be missed:

image

Trying to search for how to achieve this lead me to the PowerShell cmdlet: Get-AzOperationalInsightsDataSource (https://learn.microsoft.com/en-us/powershell/module/az.operationalinsights/get-azoperationalinsightsdatasource?view=azps-10.2.0) but this did not allow me to obtain the information I needed.

What I ended up thinking of was whether it was possible to use Resource Graph Explorer to retrieve this information and after viewing the properties of a resource that I need was sending logs to a Logs Analytics Workspace, I was able to confirm that it could be done.

The following the is properties of a Function App:

image

If we scroll down the properties of the resource, we will find the following name/value pair:

Name: "WorkspaceResourceId"
Value: "/subscriptions/dxxxxxx9-2fcd-xxxx-a791-xxxxxxxxe648/resourceGroups/DefaultResourceGroup-CCAN/providers/Microsoft.OperationalInsights/workspaces/DefaultWorkspace-d3f0e229-2fcd-45df-a791-614ba183e648-CCAN",

image

Validating that a resource would have the Log Analytics Workspace defined in its properties, we can use the following query to list all resources that contain this property:

resources
| where properties.WorkspaceResourceId == "/subscriptions/d3xxxxx-2fcd-xxxx-xxxx-6xxxxxe648/resourceGroups/DefaultResourceGroup-CCAN/providers/Microsoft.OperationalInsights/workspaces/DefaultWorkspace-d3f0e229-2fcd-45df-a791-614ba183e648-CCAN"
| project name

image

Note that if you do not know of at least one resource that uses the Log Analytics Workspace, we can retrieve the WorkspaceResourceId of the workspace by navigating to the Log Analytics Workspace in portal.azure.com and copying the string from the URL:

image

I hope this helps anyone who may be looking for this information as I did but unable to find an easy way to achieve this.

Sunday, August 20, 2023

PowerShell script to bulk convert Azure Firewall logs in JSON Line format stored on a Storage Container to CSV format

This post serves as a follow up to my:

Converting Azure Firewall logs in JSON format created from Archive to a storage account diagnostic setting to CSV format
http://terenceluk.blogspot.com/2023/07/converting-azure-firewall-logs-in-json.html

… where I provided a script to convert a single JSON file that stored Azure Firewall Logs in a Storage Account container.

As noted in the previous post, I want to follow up with a script that would traverse through a folder reading JSON files in the sub directories and converting them to CSVs to avoid manually generating each CSV for every hour of the day.

Additional reasons for using this script are:

  1. Allow the retrieval of archived Azure Firewall Logs that are no longer stored in Log Analytics
  2. Bulk converting JSON Line files to CSVs with a specified start and end date
  3. A method for working around the 30,000 records return limit when using Log Analytics Workspaces to query data

The entries for Azure Firewall Log activities can get fairly large so this script will read through each JSON file that are broken up in each hour of the day and convert them to CSV files. I originally thought about combining the files but working out the math for days of longs meant file sizes can get into the GBs and attempting to work with CSV files that large won’t be pleasant.

The JSON script can be found at GitHub repository here: https://github.com/terenceluk/Azure/blob/main/Azure%20Firewall/Bulk-Convert-Az-Firewall-Logs-JSON-to-CSV.ps1

The output file list would look as such:

image

Hope this helps anyone who may be looking for such a script that will save them the time required to manually convert the logs.

Saturday, August 12, 2023

Creating Azure Firewall Policy Rule Collections in Network Collection Group with PowerShell and Excel reference files

Those who have configured Rule Collections for a Azure Firewall Policy whether via GUI or scripting will know how tedious the task can be due to the amount of time for any type of change to be applied and the non-parallel stream of updates you can push to the firewall. I’ve also noticed that attempting to use multiple browser windows to copy and apply changes can potentially overwrite changes to the configuration. Case in point, I had a negative experience where I had window #1 to copy similar rule collections to window #2, and everything went as planned as long as I only saved to window #2. However, if I were to make a change in window #1 where it had not been refreshed with the changes applied to window #2, the save operation would overwrite the changes I made in window #2. I lost quite a bit of configuration due to this scenario.

To minimize the mistakes and amount of time I spent staring at the Azure Firewall Policy window and slowly applying configuration updates one at a time, I decide to spend a bit of time to create PowerShell scripts to reference an Excel file with configuration parameters. The first script I created was one that read an Excel spreadsheet to create the list of Rule Collections that are placed under a predefined Rule Collection Group.

The PowerShell script can be found here in my GitHub repository: https://github.com/terenceluk/Azure/blob/main/Azure%20Firewall/Create-NetworkRuleCollection.ps1

The following is a sample spreadsheet for the PowerShell script to read from:

image

Here is a sample screenshot of the Rule Collections in the Azure management portal:

image

Hope this helps anyone who may be looking for such a script as the creation of Rule Collections can only be created one at a time.

Thursday, August 10, 2023

Attempting to create a folder on an Azure Data Lake Storage Account with Private Endpoint fails with: "Failed to add directory 'Test'. Error: AuthorizationFailure: This request is not authorized to perform this operation."

Problem

A colleague of mine recently asked me to help troubleshoot an issue with an Azure Storage Account that has Hierarchical Namespace enabled, which is essentially an Azure Data Lake, where any attempts to create a folder would fail:

image

The error message presented was generic and appears to suggest that it is caused by a permissions issue:

Failed to add directory

Failed to add directory 'Test'. Error: AuthorizationFailure: This request is not authorized to perform this operation. RequestId:da720a90-c01f-0053-5d3f-c61ef5000000 Time:2023-08-03T19:22:01.2257950Z

image

Creating containers or uploading blobs (files) to the storage account did not have any issues as those operations were successful as shown in the following screenshot:

image

This error has been one that I’ve come across frequently in the past and it is usually because the storage account is locked down with only a private endpoint for the blob service and not for the data lake service created. The following Microsoft documentation explains the reason:

Use private endpoints for Azure Storage

https://learn.microsoft.com/en-us/azure/storage/common/storage-private-endpoints#creating-a-private-endpoint

If you create a private endpoint for the Data Lake Storage Gen2 storage resource, then you should also create one for the Blob Storage resource. That's because operations that target the Data Lake Storage Gen2 endpoint might be redirected to the Blob endpoint. Similarly, if you add a private endpoint for Blob Storage only, and not for Data Lake Storage Gen2, some operations (such as Manage ACL, Create Directory, Delete Directory, etc.) will fail since the Gen2 APIs require a DFS private endpoint. By creating a private endpoint for both resources, you ensure that all operations can complete successfully.

image

The following are screenshots confirming the missing configuration.

Note that Hierarchical Namespace is enabled:

image

Note that Public network access is set to Disabled:

image

Note that there is only 1 private endpoint configured for the storage account:

image

… and the Target sub-resource of the private endpoint is blob:

image

Solution

To correct the issue, we’ll need to create an additional private endpoint that has the Target sub-resource configured as DFS (Data Lake Storage Gen2). Begin by navigating to the Networking blade for the storage account and create a new Private Endpoint:

image

Proceed to fill in the details for the private endpoint:

image

Select dfs as the Target sub-resource:

image

Complete the creation of the private endpoint:

image

Folder creation should now succeed:

image

Hope this provides anyone who might have ran into this issue and is looking for a solution. I’ve found that searching for the error message does not always return results to this solution.

Tuesday, July 25, 2023

Creating Azure Route Tables, UDRs, and IPGroups with PowerShell and Excel reference files

I recently worked with a colleague to complete a deployment and one of the laborious activities we had to complete were:

  1. Create Route Tables with UDRs (user defined routes)
  2. IP Groups

There are a significant amount of entries for both resources and while it was possible to create these manually in the portal, I felt that it was better to create a PowerShell script to accelerate the creation and minimize human typo and copy and paste errors. The 2 scripts I created for this are as follows.

Creating Route Tables and UDRs

The PowerShell script I created, which can be found here in my Github repo: https://github.com/terenceluk/Azure/blob/main/PowerShell/Create-Route-Tables-and-UDRs.ps1, will read an Excel file and create the route tables and the corresponding UDRs (all route tables should have the same UDRs). One of the conditions I’ve added in is an IF statement that checks to see if the UDR to be added is the same subnet as where the route table will be attached. If it is the same, then the script will skip the creation of the UDR additional so we don’t end up routing traffic from the same subnet up to the firewall. The naming convention designed allows me to compare the Route Table and UDR name to determine if it is a match but if your environment is different then you’ll need to adjust the check. Here are screenshots of the sample spreadsheet that is read:

imageimage

Create IPGroups

There were many IP Groups that needed to be created as well because the environment had an IP Group for each subnet. The script that will read an Excel file and create the list of IP Groups can be found here at my GitHub repo: https://github.com/terenceluk/Azure/blob/main/PowerShell/Create-IP-Groups.ps1

Here are sample screenshots of the Excel file:

image

Tuesday, July 11, 2023

Sysprep fails due to Notepad++ when preparing virtual machine for image capture to deploy Azure Virtual Desktop

One of the common issues I’ve continuously come across while preparing Windows 10 and Windows 2016 above operating systems for virtual desktops or remote desktop services is when sysprep fails due to an installed application linked to a user account. I ran encountered this issue again last month with the application Notepad++ when preparing a Windows 11 Enterprise Multi-Session virtual machine for an Azure Virtual Desktop deployment. There are plenty of different PowerShell cmdlets that can be run in an attempt to fix the issue, but I find some of them result with rendering the virtual machine in a state that I would no longer be confident in deploying so I wanted to some of the steps I use for personal reference and to help anyone who may encounter a similar issue.

Problem

You attempt to run sysprep after finishing the preparation of a master image:

image

Sysprep immediately fails with the error:

Sysprep was not able to validate your Windows installation.
Review the log file at
%WINDIR%\System32\Sysprep\Panther\setupact.log for
details. After resolving the issue, use Sysprep to validate your installation again.

image

Opening the setupact.log will reveal the following line:

Error                 SYSPRP Package NotepadPlusPlus_1.0.0.0_neutral__7njy0v32s6xk6 was installed for a user, but not provisioned for all users. This package will not function properly in the sysprep image.

image

Opening the setuperr.log will reveal the following lines:

2023-05-05 07:20:10, Error                 SYSPRP BCD: BiUpdateEfiEntry failed c000000d

2023-05-05 07:20:10, Error                 SYSPRP BCD: BiExportBcdObjects failed c000000d

2023-05-05 07:20:10, Error                 SYSPRP BCD: BiExportStoreAlterationsToEfi failed c000000d

2023-05-05 07:20:10, Error                 SYSPRP BCD: Failed to export alterations to firmware. Status: c000000d

2023-07-07 19:51:46, Error                 SYSPRP Package NotepadPlusPlus_1.0.0.0_neutral__7njy0v32s6xk6 was installed for a user, but not provisioned for all users. This package will not function properly in the sysprep image.

2023-07-07 19:51:46, Error                 SYSPRP Failed to remove apps for the current user: 0x80073cf2.

2023-07-07 19:51:46, Error                 SYSPRP Exit code of RemoveAllApps thread was 0x3cf2.

2023-07-07 19:51:46, Error                 SYSPRP ActionPlatform::LaunchModule: Failure occurred while executing 'SysprepGeneralizeValidate' from C:\Windows\System32\AppxSysprep.dll; dwRet = 0x3cf2

2023-07-07 19:51:46, Error                 SYSPRP SysprepSession::Validate: Error in validating actions from C:\Windows\System32\Sysprep\ActionFiles\Generalize.xml; dwRet = 0x3cf2

2023-07-07 19:51:46, Error                 SYSPRP RunPlatformActions:Failed while validating Sysprep session actions; dwRet = 0x3cf2

2023-07-07 19:51:46, Error      [0x0f0070] SYSPRP RunDlls:An error occurred while running registry sysprep DLLs, halting sysprep execution. dwRet = 0x3cf2

2023-07-07 19:51:46, Error      [0x0f00d8] SYSPRP WinMain:Hit failure while pre-validate sysprep generalize internal providers; hr = 0x80073cf2

image

Proceeding to uninstall Notepad++ from the image will allow sysprep to run and complete successfully but this means the deployed virtual desktops would need the application installed manually.

Solution

The first step to take for resolving this issue is to restore the virtual machine from a snapshot that had not failed on a sysprep because the sysprep process removes packages from the operating system and there will be times when:

  1. After fixing the Notepad++ application, sysprep would fail and error out on other native Microsoft applications
  2. You would notice that Notepad is no longer available on the virtual machine
  3. Other odd errors would occur

It is better to troubleshoot and perform sysprep on a machine that has no experienced a half executed but failed sysprep.

Once a fresh snapshot is restored, we can now work on determining which accounts Notepad++ is linked to. This can reviewed by starting PowerShell and executing the following cmdlet:

Get-AppxPackage -AllUser | Format-List -Property PackageFullName,PackageUserInformation

The cmdlet above will list all packages installed and this example coincidentally places the Notepad++ package at the end of the output:

image

If the package in question starts with a letter earlier than M (for Microsoft) and results in being nested within the long output, we can use the following cmdlet to filter the PackageFullName to what is being searched for:

Get-AppxPackage -AllUser | Where-Object {$_.PackageFullName -like "NotepadPlusPlus*"} | Format-List -Property PackageFullName,PackageUserInformation

With the package located identify which accounts are listed to have the application installed. The screenshot above only lists one account but if there are more, the easiest approach is to delete all the accounts and their profiles. If there is only one account listed and it is the built-in administrator account, you won’t be able to delete it because the following error will be displayed when you try to do so:

The following error occurred while attempting to delete the user admin:

Cannot perform this operation on built-in accounts.

image

To get around this, log in as the account with Notepad++ install linked to, launch PowerShell and execute the following cmdlet:

Remove-AppxPackage -Package <packagefullname>

The following is the cmdlet that is used to remove Notepad++ from the account:

Remove-AppxPackage -Package NotepadPlusPlus_1.0.0.0_neutral__7njy0v32s6xk6

image

You should no longer find the Notepad++ when executing the following cmdlet:

Get-AppxPackage -AllUser | Where-Object {$_.PackageFullName -like "NotepadPlusPlus*"} | Format-List -Property PackageFullName,PackageUserInformation

image

Running sysprep should now complete so the virtual machine can be captured to an image for session host deployments.

Thursday, July 6, 2023

Converting Azure Firewall logs in JSON format created from Archive to a storage account diagnostic setting to CSV format

One of the clients I recently worked with had a requirement that all traffic traversing through the Azure Firewall need to be stored for at least 6 months due to auditing requirements. Accomplishing this wasn’t difficult because it was a matter of either increasing the retention for the Log Analytics Workspace or sending the log files to a storage account for archiving. Given the long period of 6 months, I opted to set the Log Analytics workspace retention to 3 months and provide the remaining retention by sending the logs to a storage account:

image image

The Firewall logs that are sent to the storage account will be stored in a container named insights-logs-azurefirewall:

image

Navigating into this container will show that it a folder tree consisting of multiple subfolders containing the subscription, the name of the resource group containing the firewall, which also contains the VNet because it is a requirement to store the firewall resource in the same RG as the VNet:

insights-logs-azurefirewall / resourceId= / SUBSCRIPTIONS / CCE9BD62-xxxx-xxxx-xxxx-xxxx51CE27DA / RESOURCEGROUPS / RG-CA-C-VNET-PROD / PROVIDERS / MICROSOFT.NETWORK / AZUREFIREWALLS / AFW-CA-C-PROD

It then splits to the logs into subfolders with:

  • Year
  • Month
  • Day
  • Hour
  • Minute (only 1 folder labeled as 00)
image

Drilling all the way down to the minute folder will contain a PT1H.json file that is the Append blob type. This is the file that will contain the firewall traffic log entries:

image

While browsing through the content of the PT1H.json file, I immediately noticed that the format of the entries did not appear to conform to any of the JSON Specifications (RFC 4627, 7159, 8259) because while I’m not very familiar with JSON format, I can see that:

  1. The beginning entries for the whole JSON file is missing an open square bracket and the end of the file is missing a close square bracket <- Line 1
  2. The nested properties values do not have an open square bracket before the brace and a close square bracket at the end of the close brace <- Line 5 and Line 22
  3. The close brace for each entry does not have a comma that separates each log <- Line 24
image

Trying to paste this into a JSON validator (https://jsonformatter.curiousconcept.com/) would show it does not conform to any RFC format:

image

Reviewing the Microsoft confirms that the format of blobs in a Storage Account is in JSON lines, where each record is delimited by a new line, with no outer records array and no commas between JSON records: https://learn.microsoft.com/en-us/azure/azure-monitor/logs/logs-data-export?tabs=portal#storage-account

Further reading shows that this was put in place since November 1st, 2018:

Prepare for format change to Azure Monitor platform logs archived to a storage account
https://learn.microsoft.com/en-us/previous-versions/azure/azure-monitor/essentials/resource-logs-blob-format

More reading about the JSON Lines format can be found here: https://jsonlines.org/

My objective was to simply use a PowerShell script to convert a JSON file into a CSV so it can be sent to the client for review but my script would not work with the JSON Line format. Fixing this manually by hand if there were 2 records wouldn’t be difficult, but these firewall logs have thousands of entries and I needed a way to automate the conversion. The whole process of getting this to work too quite a bit of my time so I wanted to write this blob post to help anyone who may come across the same challenge.

Step #1 – Fixing the poorly formatted JSON file

The first step was to fix the JSON Line formatted JSON file so it conforms to an RFC 8259 format. What this basically meant is addressing these 3 items:

  1. The beginning entries for the whole JSON file is missing an open square bracket and the end of the file is missing a close square bracket <- Line 1
  2. The nested properties values does not have an open square bracket before the brace and a close square bracket at the end of the close brace <- Line 5 and Line 22
  3. The close brace for each entry does not have a comma that separates each log <- Line 24

I’ve reduced the JSON file to only 2 log entries to show all the changes required:

  1. Add an open [ bracket after properties":
  2. Add a close ] bracket at the end of properties }
  3. Add a comma after close } brace for each log entry but exclude last entry
  4. Add a bracket at the beginning of the JSON
  5. Add a bracket at the end of the JSON
image

The best way to approach this is to use Regex expressions to match the desired block or blocks of lines and add the desired brackets and/or comma. My days of using Regex goes back to when I worked on voice deployments for OCS 2007, Lync Server, Skype for Business Server, and Teams Direct Routing. My role over the past few years does not include this product so if you (the reader) see a better way of writing these expressions, please feel free to provide suggestions in the comments.

Add an open [ bracket after properties": and add a close ] bracket at the end of properties }

The Regex expression to match all the contents in the nested properties block is:

(?<=”properties”: )([\s\S]*?})

This can be validated on a Regex validator such as: https://regexr.com/

image

We can use the following PowerShell regex replace function to add the missing open and close square brackets:

$fixedJson = [regex]::Replace($badJson, $regexPattern, { param($match) "[{0}]" -f $match.Value })

Add a comma after close } brace for each log entry but exclude last entry

With the missing open and square brackets added, we can use the output and the following regex expression to match all of the log entries to add a comma for separation AND NOT include the last log at the end of the entries:

(?="category": )([\s\S]*?}]\s}\W)

image

Note that the last block for the log entry is excluded:

image

--------------------------------- Update August 21-2023 ---------------------------------------

I realized that the previous RegEx expression I used would fail to match scenarios where there are spaces or line breaks between the square and curly brackets so I’ve updated the expression for the script on GitHub and adding the changes here.

(?="category": )([\s\S]*?}[\s\S]*?][\s\S]*?}\W)

The following is a break down of each section of the RegEx expression:

(?="category": ) <-- Match the first block before spanning the text after this

([\s\S]*?}[\s\S]*?][\s\S]*?}\W) <-- This is to match everything from the category and to the end

([\s\S]*?} <-- Match everything that is a whitespace and not whitespace, words, digits and end at the curly bracket }

[\s\S]*?] <-- Match everything that is a whitespace and not whitespace, words, digits and end at the square bracket ]

[\s\S]*?} <-- Continue matching everything that is a whitespace and not whitespace, words, digits and end at the next curly bracket }

\W) <-- This excludes the last block

----------------------------------------------------------------------------------------------------------------

We can use the following PowerShell regex replace function add the missing comma between entries:

$fixedJson = [regex]::Replace($badJson, $regexPattern, { param($match) "{0}," -f $match.Value })

Add a bracket at the beginning of the JSON and add a bracket at the end of the JSON

With the comma added between each log, we can now proceed to add the open and close square bracket to the beginning and end of the file with the following regex expression:

^([^$]+)

image

We can use the following PowerShell regex replace function add the missing open and close square bracket to the beginning and end:

$fixedJson = [regex]::Replace($badJson, $regexPattern, { param($match) "[{0}]" -f $match.Value })

With the missing formatting added, we should now be able to validate the JSON file:

image

Step #2 – Create a PowerShell script that will read the Azure Firewall Storage Account JSON and convert to CSV

With the Regex expressions defined and missing brackets and braces defined, the next step is to write a PowerShell script that will read the native JSON file, format the JSON so it is RFC 8259 compliant, parse through each entry and place the log entry details into the rows and columns of the CSV file.

The script can be found in my following GitHub: https://github.com/terenceluk/Azure/blob/main/Azure%20Firewall/Convert-JSON-Logs-to-CSV.ps1

The components of the script are as follows:

1. The first portion where we use Regex to fix the JSON formatting

image

2. Begin parsing the formatted JSON file:

**Update the following 2 variables:

  1. $pathToJsonFile = "PT1H2.json"
  2. $pathToOutputFile = "PT1H2.csv"
image

When writing the portion of the code used for parsing the JSON file, I noticed that there wasn’t an easy way for me to automatically read through the column headings to avoid defining them directly because there are different type of records in the JSON file with varying headings. This meant in order to transfer all the records into a CSV, I would need to define all of the headings upfront. Since not all the headings will be used for every record, any entries that does not have the heading will have that cell blank.

The end result of the export will look something like the following CSV:

imageimageimage

The diagnostics settings I selected for this example included the Legacy Azure Diagnostics category so the logs will have some redundant records where the legacy entries have the details in the Msg column, while the newer category will have the record details split into their own columns.

I hope this blog post helps anyone who may be looking for a way to parse and create a CSV file from the Azure Firewall log JSON files. I’ll be writing a follow up post in the future to demonstrate using a script to read the folders in the storage account so this doesn’t have to be done manually with every JSON file at every hour of the day.