Sending Azure automation output to application insights in a table format

Question

My main problem is : I want to send my slowest running queries in Azure SQL to a centralised logging system for load test debugging.

How can I send my result set to Application insights? I would like send my slowest running queries from Azure automation to Application Insights ?

I have tried this with out luck when it is in table format

workflow Use-SqlCommandSample
{
param(
    [parameter(Mandatory=$True)]
    [string] $SqlServer,

    [parameter(Mandatory=$False)]
    [int] $SqlServerPort = 1433,

    [parameter(Mandatory=$True)]
    [string] $Database,

    [parameter(Mandatory=$True)]
    [string] $Table,

    [parameter(Mandatory=$True)]
    [PSCredential] $SqlCredential
)

# Get the username and password from the SQL Credential
$SqlUsername = $SqlCredential.UserName
$SqlPass = $SqlCredential.GetNetworkCredential().Password

inlinescript {
    # Define the connection to the SQL Database
    $Conn = New-Object System.Data.SqlClient.SqlConnection("xxxx")

    # Open the SQL connection
    $Conn.Open()
    $Cmd=new-object system.Data.SqlClient.SqlCommand("SELECT  top 10 creation_time"+ 
       ",last_execution_time"+
        ",total_physical_reads"+
        ",total_logical_reads "+
       ",total_logical_writes"+
        ", execution_count"+
        ", total_worker_time"+
       " , total_elapsed_time"+
        ", total_elapsed_time / execution_count avg_elapsed_time"+
        ",SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,"+
        " ((CASE statement_end_offset"+
        "  WHEN -1 THEN DATALENGTH(st.text)"+
        "  ELSE qs.statement_end_offset END"+
        "    - qs.statement_start_offset)/2) + 1) AS statement_text"+
        "  FROM sys.dm_exec_query_stats AS qs"+
        "  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st"+
        " ORDER BY total_elapsed_time / execution_count DESC;", $Conn)
    $Cmd.CommandTimeout=120

    # Execute the SQL command
    $Ds=New-Object system.Data.DataSet
    $Da=New-Object system.Data.SqlClient.SqlDataAdapter($Cmd)
    [void]$Da.fill($Ds)



      $assemblyPath = 
     "C:\Modules\Global\Azure\Compute\Microsoft.ApplicationInsights.dll"
     [System.Reflection.Assembly]::LoadFrom($assemblyPath)
     $TelClient = New-Object "Microsoft.ApplicationInsights.TelemetryClient"
     $TelClient.InstrumentationKey = "1234"

     # Output the result


     $TelClient.TrackEvent($Ds.Tables)
     $TelClient.Flush

    # Close the SQL connection
    $Conn.Close()
}

}

Answer 1

In Application Insights Events can take name, value property bags. So you will have to serialize the table preferably into json to submit it to AppInsights