Script to combine index usages stats across multiple AG nodes

IndexUsageAcrossAGNodes.ps1

$PrimaryInstance = 'Sql1'
$SecondaryInstance = 'Sql2'
 
$SqlInstance = $PrimaryInstance, $SecondaryInstance
$Database = 'DbName'
 
$results = Get-DbaHelpIndex -SqlInstance $SqlInstance -Database $Database
 
$export = @()
 
$export += $results | Where-Object {$_.SqlInstance -eq $PrimaryInstance} | ForEach-Object -PipelineVariable pIndex -Process { $_ } | ForEach-Object {
   
    # get the matching secondary row
    $sIndex = $results | Where-Object { $_.SqlInstance -eq $SecondaryInstance -and $_.ObjectName -eq $pIndex.ObjectName -and $_.Index -eq $pIndex.Index}
 
    [PSCustomObject]@{
        PrimaryInstance         = $pIndex.SqlInstance
        SecondaryInstance       = $sIndex.SqlInstance
        Database                = $pIndex.Database
        Object                  = $pIndex.Object
        Index                   = $pIndex.Index
        IndexType               = $pIndex.IndexType
        KeyColumns              = $pIndex.KeyColumns
        IncludeColumns          = $pIndex.IncludeColumns
        FilterDefinition        = $pIndex.FilterDefinition
        Rows                    = $pIndex.IndexRows
        SizeKB                  = $pIndex.Size
 
        PrimaryIndexReads       = [int]$pIndex.IndexReads
        SecondaryIndexReads     = [int]$sIndex.IndexReads
        TotalIndexReads         = [int]$pIndex.IndexReads + $sIndex.IndexReads
 
        PrimaryIndexUpdates     = [int]$pIndex.IndexUpdates
        SecondaryIndexUpdates   = [int]$sIndex.IndexUpdates
        TotalIndexUpdates       = [int]$pIndex.IndexUpdates + $sIndex.IndexUpdates
    }
}
 
$export | Export-Excel -Path IndexUsage.xlsx -WorksheetName IndexUsage -TableName IndexUsage -AutoSize