[2023-July-31]: The previous limitation has been resolved. I modified the PowerShell script to update the table name in the workbook file inheriting the value passed as parameter. Make sure you use the latest updated attachment.
[2023-July-27]: To avoid workbook issues, make sure you call the the Custom table DHCPLOG_CL (using the correct case). If you prefer another name, then you have to edit the workbook code to point to the correct table.
After 5 minutes of brainstorming I got the solution in mind: I needed to ingest DHCP logs into Azure Monitor, storing them in a Log Analytics workspace and visualize the data through Azure Workbooks. Looks complicated? It is not, but let us go step by step:
#1: Ingesting logs into Azure Monitor:
This is not something difficult, you can follow the Collect text logs with Azure Monitor Agent documentation or you can read ahead to see how I tried to make life easier by using templates and scripts to manage everything in one go. Reason I created the script is just to avoid too many steps to be completed manually. I preferred to have one single script to run which does the following:
Import the table definition schema
Create the table in Log Analytics
If table creation was successful, it creates the necessary DCE and DCR using a JSON template
This approach does not require any customization since all the necessary info are asked as parameters during the script execution. However, you might need to import a different set of info so consider the following:
The table definition schema template includes only meaningful field. If you need less, more or all the fields in the original log, you need to adapt the table schema.
The transformation rule has been defined according to the table schema. If you change the table schema, make sure you adapt the transformation rule as well template prior to running the script.
With that said, let us try it:
Open a PowerShell prompt and launch the script with no parameters:
The script will ask for Azure Active Directory Tenant ID This is required to correctly scope the authentication:
Once the Azure Active Directory Tenant ID has been entered the script will move on with the authentication asking to pick an account from one of the recently used or to enter a new one with the corresponding password:
As the next step, a grid will show up, allowing you to select the subscription you want to use:
From this point on, specific information will be asked like:
The Resource Group containing the Log Analytics workspace to be used
The name of the Log Analytics workspace that will host the custom table
A name for the Custom table
A name for the DCE
A name for the DCR
The name of the Json template file containing DCE and DCR definitions
The execution will continue, letting you know about the step and the outcome. If everything goes smoothly you will get the following results
At this point you just need to associate the above created DCR with the DHCP server(s) making sure to set the endpoint to the above created DCE
Easy enough, isn’t it ?
You can find the script, the table schema template and the DCE+DCR template files attached to the post.
But you know me by now! I love to make my readers’ life easier, so I am going to add a ready-to-use workbook as part of this post. This first version includes tiles with aggregated information on events by DHCP server, events by Event Id and event by description
As well as a grid with all log entries with a search box on top.
The search box is a superb feature of the workbooks. It searches against everything showing up in the grid. Really amazing!!!
It goes without saying that this solution applies to both Azure virtual machines and Arc-Enabled servers.
The sample scripts are not supported under any Microsoft standard support program or service. The sample scripts are provided AS IS without warranty of any kind. Microsoft further disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample scripts and documentation remains with you. In no event shall Microsoft, its authors, or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation, even if Microsoft has been advised of the possibility of such damages.