README.md

Azure SQL BPA OpenAI integration: Configuration

Parameters/Requirements	Information	Note
Azure OpenAI Model	This solution require specific Azure OpenAI Model	Version gpt-4-1106-preview
replacewithsubid	Connection setting during deployment	Replace with your Subscription ID
replacewithRG	Connection setting during deployment	Replace with the selected RG Name for the deployment
replace with tenant id	HTTP Module: Tenant ID	Replace with your Tenant ID
OpenAI API Key	String Resource to connect to OpenAI	Replace with your Azure OpenAI Key

Important

This LogApp and the following changes are an example of integrating SQL BPA results with OpenAI, creating an HTML report and CSV file send via Email with OpenAI comment of Severity High and/or Medium results. You can customize them to your liking such as changing the query and/or question to ChatGPT as well as sending the results not only via email but also to Ondrive or Storage Account for example. Before using it you must have enabled and performed SQL Best Practices Assessment on your hybrid Machine.

Reference: Azure ARC SQL Assessment

Deploy

When you deploy, replace with your SubscriptionID and ResourceGroup Name:

Required Identity

Managed Identity

When the deployment is completed go in your Logic App and create a Managed Identity following the example below and give them LogAnalytics Reader permission:

Logic App Workflow Setting

High throughput

Set High throughput to ON:

Deployment and Result

After deployment completed, please follow the documentation:

Change Recurrence section after SQL BPA has been performed:

Change the broken module Run query and list result with a new one

Before:

After:

Compile all the information following your configuration.
For the query field past the code below:

let selectedCategories = dynamic([]);
let selectedTotSev = dynamic([]);
SqlAssessment_CL
| extend asmt = parse_csv(RawData)
//| where asmt[11] =~ "MSSQLSERVER" 
| extend
    AsmtId=tostring(asmt[1]),
    CheckId=tostring(asmt[2]),
    DisplayString=asmt[3],
    Description=tostring(asmt[4]),
    HelpLink=asmt[5],
    TargetType=case(asmt[6] == 1, "Server", asmt[6] == 2, "Database", ""),
    TargetName=tostring(asmt[7]), 
    Severity=case(asmt[8] == 30, "High", asmt[8] == 20, "Medium", asmt[8] == 10, "Low", asmt[8] == 0, "Information", asmt[8] == 1, "Warning", asmt[8] == 2, "Critical", "Passed"),
    Message=tostring(asmt[9]),
    TagsArr=split(tostring(asmt[10]), ","),
    Sev = toint(asmt[8])
| where isnotempty(AsmtId)  
    and (set_has_element(dynamic(['*']), CheckId) or "'*'" == "'*'")
    and (set_has_element(dynamic(['*']), TargetName) or "'*'" == "'*'")
    and set_has_element(dynamic([30, 20, 10, 0]), Sev)
    and (array_length(set_intersect(TagsArr, dynamic(['*']))) > 0 or "'*'" == "'*'")
    and (CheckId == '''' and Sev == 0 or "''" == "''")
| extend Category = case(
                        array_length(set_intersect(TagsArr, dynamic(["CPU", "IO", "Storage"]))) > 0,
                        '0',
                        array_length(set_intersect(TagsArr, dynamic(["TraceFlag", "Backup", "DBCC", "DBConfiguration", "SystemHealth", "Traces", "DBFileConfiguration", "Configuration", "Replication", "Agent", "Security", "DataIntegrity", "MaxDOP", "PageFile", "Memory", "Performance", "Statistics"]))) > 0,
                        '1',
                        array_length(set_intersect(TagsArr, dynamic(["UpdateIssues", "Index", "Naming", "Deprecated", "masterDB", "QueryOptimizer", "QueryStore", "Indexes"]))) > 0,
                        '2',
                        '3'
                    )
| where (Sev >= 0 and array_length(selectedTotSev) == 0 or Sev in (selectedTotSev))
    and (Category in (selectedCategories) or array_length(selectedCategories) == 0)
| project
    TargetType,
    TargetName,
    Severity,
    Message,
    Tags=strcat_array(array_slice(TagsArr, 1, -1), ', '),
    CheckId,
    Description,
    HelpLink = tostring(HelpLink),
    SeverityCode = toint(Sev)
| order by SeverityCode desc, TargetType desc, TargetName asc
| project-away SeverityCode 

Configure the Api Key with the value inside your OpenAI Service:

Configure ForeachSQLResult section with value of query result and each parameter in Question variable as you like:

And now configure the RedirectUrl variable following the example:

Configure the Question Variable following the example below:

NB. you can use the following variable below to valorize the items i greyed out:

@{item()?['Message']}
 
@{item()?['Description']}
 
@{item()?['TargetType']}

Configure the severity level as a condition filter as you prefer:

Now configure the HTTP Connector for OpenAI Connection following this configuration:

At this point, be sure that the ComposeCSV block is correctly configured. When the "Run query and list result" block is recreated some link can be resetted. Follow the ComposeCSV attribute configuration following the example below (if the new UI model is not useful for the customization, evaluate to switch in "code view" mode):

NB. you can use the following variable below to valorize the items i greyed out:

@{items('For_eachSQLResult')?['TargetName']}
 
@{items('For_eachSQLResult')?['Severity']}
 
@{items('For_eachSQLResult')?['Message']}
 
@{items('For_each_AI_Response')?['message']?['content']}

@{items('For_eachSQLResult')?['HelpLink']}
 

The last configuration is change the broken module Send an email (V2) with a new one, add the attachment and customize the file name :

Before

After

Here all the configuration related the 2 attachments:

                            {
                                "Name": "Sql-BPA-Result@{formatDateTime(convertTimeZone(utcNow(),'UTC','Romance Standard Time'), 'yyyy-MM-ddTHH.mm')}.html",
                                "ContentBytes": "@{base64(variables('HTML-custom'))}"
                            },
                            {
                                "Name": "Sql-BPA-Result@{formatDateTime(convertTimeZone(utcNow(),'UTC','Romance Standard Time'), 'yyyy-MM-ddTHH.mm')}.csv",
                                "ContentBytes": "@{base64(body('Create_CSV_table'))}"
                            }

Now save your LogicApp and Enable it.