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:

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:

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:

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:

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:

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

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: