# Configuration

## Decide what to send to Azure Log

By default, SQLWATCH comes with a number of pre-defined objects and queries that will be sent to the Azure Log. To view them, execute the following:

```sql
select * from [dbo].[sqlwatch_config_report]
where report_batch_id = 'AzureLogMonitor-1'
```

SQLWATCH is using the Report engine to generate and send data to Azure. It simple terms, it means that every 10 minutes it pulls data since the last report run (i.e. since 10 minutes ago) and sends it Azure.

The report generation is invoked by an agent job `SQLWATCH-REPORT-AZMONITOR` which runs  a stored procedure:

```sql
exec dbo.usp_sqlwatch_internal_process_reports 
    @report_batch_id = 'AzureLogMonitor-1'
```

The `@report_batch_id` parameter corresponds to the the `report_batch_id` column in the `[dbo].[sqlwatch_config_report]` table.

The report generates the required output and passes it into the action queue `[dbo].[sqlwatch_meta_action_queue]`. There could be a short delay between when the report has finished and before it's sent to the Azure, depending on how often the action queue is processed by the job `SQLWATCH-INTERNAL-ACTIONS` (default 30 seconds)

## Configure the action to push selected data to Azure Log

There is an existing action `-16` in SQLWATCH that is expecting this script to be placed in \``C:\SQLWATCHPS\`. This is configurable:

```sql
select * from [dbo].[sqlwatch_config_action]
where action_id = -16
```

The `action_exec` field will have the following content. update the path to the `Upload-AzMonitorLog.ps1` if it's different to the below:

```csharp
Invoke-Sqlcmd `
    -ServerInstance localhost `
    -Database SQLWATCH `
    -Query "{BODY}" | C:\SQLWATCHPS\Upload-AzMonitorLog.ps1 
    -WorkspaceId "YOURWORKSPACEID" 
    -WorkspaceKey "WORKSPACEKEY" 
    -LogTypeName "{SUBJECT}" 
    -AddComputerName
```

To obtain the  `WorkspaceId` and the `WorkspaceKey` login to your Azure portal and go to the Log Analytics workspace where you want to push the logs. Go to Advanced Settings and both will be there. You can use either The Primary or Secondary key:

![](https://560149426-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LamHUpIpVXoBUUDZZpO%2F-MB4uEUped__u3DwKp1L%2F-MB4wUCMI9ukWeBQNCDi%2Fimage.png?alt=media\&token=6cadf6bd-6b15-47dc-be9e-6057ad990924)

Update values in the SQLWATCH table with the above values and enable action:

```sql
declare @workspaceid varchar(255),
        @workspacekey varchar(255)
        
set @workspaceid = '""' --paste your WorkspaceId here between double quotes
set @workspacekey = '""' --paste your WorkspaceKey here between double quotes

update [dbo].[sqlwatch_config_action]
    set [action_enabled] = 1, 
        [action_exec] = replace(replace([action_exec]
        ,'YOURWORKSPACEID',@workspaceid)
        ,'YOURWORKSPACEKEY',@workspacekey))
where action_id = -16
```

Data pushed to Azure will appear in the Custom Logs section with the `_CL` suffix appended to object names:

![](https://560149426-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LamHUpIpVXoBUUDZZpO%2F-MB4zWH_-gqjhksMV_qt%2F-MB51R2fiZd2HJQQfX2V%2Fimage.png?alt=media\&token=6de56412-fa37-441e-9591-56e3111649f3)
