Add or delete tables and columns in Azure Monitor Logs

Grein
09/11/2024

Data collection rules let you filter and transform log data before sending the data to an Azure table or a custom table. This article explains how to create custom tables and add custom columns to tables in your Log Analytics workspace.

Important

Whenever you update a table schema, be sure to update any data collection rules that send data to the table. The table schema you define in your data collection rule determines how Azure Monitor streams data to the destination table. Azure Monitor does not update data collection rules automatically when you make table schema changes.

Prerequisites

To create a custom table, you need:

A Log Analytics workspace where you have at least contributor rights.
A data collection endpoint (DCE).
A JSON file with at least one record of sample for your custom table. This will look similar to the following:
```
[
  {
    "TimeGenerated": "supported_datetime_format",
    "<column_name_1>": "<column_name_1_value>",
    "<column_name_2>": "<column_name_2_value>"
  },
  {
    "TimeGenerated": "supported_datetime_format",
    "<column_name_1>": "<column_name_1_value>",
    "<column_name_2>": "<column_name_2_value>"
  },
  {
    "TimeGenerated": "supported_datetime_format",
    "<column_name_1>": "<column_name_1_value>",
    "<column_name_2>": "<column_name_2_value>"
  }
]
```
All tables in a Log Analytics workspace must have a column named TimeGenerated. If your sample data has a column named TimeGenerated, then this value will be used to identify the ingestion time of the record. If not, a TimeGenerated column will be added to the transformation in your DCR for the table. For information about the TimeGenerated format, see supported datetime formats.

Create a custom table

Azure tables have predefined schemas. To store log data in a different schema, use data collection rules to define how to collect, transform, and send the data to a custom table in your Log Analytics workspace. To create a custom table with the Auxiliary plan, see Set up a table with the Auxiliary plan (Preview).

Important

Custom tables have a suffix of _CL; for example, tablename_CL. The Azure portal adds the _CL suffix to the table name automatically. When you create a custom table using a different method, you need to add the _CL suffix yourself. The tablename_CL in the DataFlows Streams properties in your data collection rules must match the tablename_CL name in the Log Analytics workspace.

Warning

Table names are used for billing purposes so they should not contain sensitive information.

To create a custom table using the Azure portal:

From the Log Analytics workspaces menu, select Tables.
Select Create and then New custom log (DCR-based).
Specify a name and, optionally, a description for the table. You don't need to add the _CL suffix to the custom table's name - this is added automatically to the name you specify in the portal.
Select an existing data collection rule from the Data collection rule dropdown, or select Create a new data collection rule and specify the Subscription, Resource group, and Name for the new data collection rule.
Select a data collection endpoint and select Next.
Select Browse for files and locate the JSON file with the sample data for your new table.

If your sample data doesn't include a TimeGenerated column, then you will receive a message that a transformation is being created with this column.
If you want to transform log data before ingestion into your table:
1. Select Transformation editor.
  
  The transformation editor lets you create a transformation for the incoming data stream. This is a KQL query that runs against each incoming record. Azure Monitor Logs stores the results of the query in the destination table.
2. Select Run to view the results.
Select Apply to save the transformation and view the schema of the table that's about to be created. Select Next to proceed.
Verify the final details and select Create to save the custom log.

Use the Tables - Update PATCH API to create a custom table with the PowerShell code below. This code creates a table called MyTable_CL with two columns. Modify this schema to collect a different table.

Select the Cloud Shell button in the Azure portal and ensure the environment is set to PowerShell.

Copy the following PowerShell code and replace the Path parameter with the appropriate values for your workspace in the Invoke-AzRestMethod command. Paste it into the Cloud Shell prompt to run it.

$tableParams = @'
{
    "properties": {
        "schema": {
            "name": "MyTable_CL",
            "columns": [
                {
                    "name": "TimeGenerated",
                    "type": "DateTime"
                }, 
                {
                    "name": "RawData",
                    "type": "String"
                }
            ]
        }
    }
}
'@

Invoke-AzRestMethod -Path "/subscriptions/{subscription}/resourcegroups/{resourcegroup}/providers/microsoft.operationalinsights/workspaces/{workspace}/tables/MyTable_CL?api-version=2021-12-01-preview" -Method PUT -payload $tableParams

Delete a table

There are several types of tables in Azure Monitor Logs. You can delete any table that's not an Azure table, but what happens to the data when you delete the table is different for each type of table.

For more information, see What happens to data when you delete a table in a Log Analytics workspace.

To delete a table from the Azure portal:

From the Log Analytics workspace menu, select Tables.
Search for the tables you want to delete by name, or by selecting Search results in the Type field.
Select the table you want to delete, select the ellipsis ( ... ) to the right of the table, select Delete, and confirm the deletion by typing yes.

To delete a table using PowerShell:

Select the Cloud Shell button in the Azure portal and ensure the environment is set to PowerShell.

Invoke-AzRestMethod -Path "/subscriptions/{subscription}/resourcegroups/{resourcegroup}/providers/microsoft.operationalinsights/workspaces/{workspace}/tables/NewCustom_CL?api-version=2021-12-01-preview" -Method DELETE

Add or delete a custom column

You can modify the schema of custom tables and add custom columns to, or delete columns from, a standard table.

Note

Column names must start with a letter and can consist of up to 45 alphanumeric characters and underscores (_). _ResourceId, id, _ResourceId, _SubscriptionId, TenantId, Type, UniqueId, and Title are reserved column names.

To add a custom column to a table in your Log Analytics workspace, or delete a column:

From the Log Analytics workspaces menu, select Tables.
Select the ellipsis ( ... ) to the right of the table you want to edit and select Edit schema. This opens the Schema Editor screen.
Scroll down to the Custom Columns section of the Schema Editor screen.
To add a new column:
1. Select Add a column.
2. Set the column name and description (optional), and select the expected value type from the Type dropdown.
3. Select Save to save the new column.
To delete a column, select the Delete icon to the left of the column you want to delete.

To add a new column to an Azure or custom table, run:

$tableParams = @'
{
    "properties": {
        "schema": {
            "name": "<TableName>",
            "columns": [
                {
                    "name": ""<ColumnName>",
                    "description": "First custom column",
                    "type": "string",
                    "isDefaultDisplay": true,
                    "isHidden": false
                }
            ]
        }
    }
}
'@

Invoke-AzRestMethod -Path "/subscriptions/{subscription}/resourcegroups/{resourcegroup}/providers/microsoft.operationalinsights/workspaces/{workspace}/tables/<TableName>?api-version=2021-12-01-preview" -Method PUT -payload $tableParams

The PUT call returns the updated table properties, which should include the newly added column.

Example

Run this command to add a custom column, called Custom1_CF, to the Azure Heartbeat table:

$tableParams = @'
{
    "properties": {
        "schema": {
            "name": "Heartbeat",
            "columns": [
                {
                    "name": "Custom1_CF",
                    "description": "The second custom column",
                    "type": "datetime",
                    "isDefaultDisplay": true,
                    "isHidden": false
                }
            ]
        }
    }
}
'@

Invoke-AzRestMethod -Path "/subscriptions/{subscription}/resourcegroups/{resourcegroup}/providers/microsoft.operationalinsights/workspaces/{workspace}/tables/Heartbeat?api-version=2021-12-01-preview" -Method PUT -payload $tableParams

Now, to delete the newly added column and add another one instead, run:

$tableParams = @'
{
    "properties": {
        "schema": {
            "name": "Heartbeat",
            "columns": [
                {
                    "name": "Custom2_CF",
                    "description": "The second custom column",
                    "type": "datetime",
                    "isDefaultDisplay": true,
                    "isHidden": false
                }
            ]
        }
    }
}
'@

Invoke-AzRestMethod -Path "/subscriptions/{subscription}/resourcegroups/{resourcegroup}/providers/microsoft.operationalinsights/workspaces/{workspace}/tables/Heartbeat?api-version=2021-12-01-preview" -Method PUT -payload $tableParams

To delete all custom columns in the table, run:

$tableParams = @'
{
    "properties": {
        "schema": {
            "name": "Heartbeat",
            "columns": [
            ]
        }
    }
}
'@

Invoke-AzRestMethod -Path "/subscriptions/{subscription}/resourcegroups/{resourcegroup}/providers/microsoft.operationalinsights/workspaces/{workspace}/tables/Heartbeat?api-version=2021-12-01-preview" -Method PUT -payload $tableParams

Next steps

Learn more about:

Deila með