ExportPerfomanceCountersHelpers.ps1

Add-Type -TypeDefinition @"
    public enum AggregationType
    {
        Min,
        Max,
        Sum,
        Avg
    }
"@

function Get-CounterData
{
    param($CounterInformation, $CollectionTime)
    Write-Host 'Get-CounterData'
    $Counters = $CounterInformation.Keys | % ToString
    
    $StartDateTime = Get-Date
    $CounterResult = Get-Counter $Counters -MaxSamples $CollectionTime -SampleInterval 1
    $EndDateTime = Get-Date
    Aggregate-Counters -CounterInformation $CounterInformation -CounterResult $CounterResult -StartDateTime $StartDateTime -EndDateTime $EndDateTime
}

function Aggregate-Counters
{
    param($CounterInformation, $CounterResult, $StartDateTime, $EndDateTime)
    Write-Host 'Aggregate-Counters'
    $result = New-Object –TypeName PSObject
    $result | Add-Member -NotePropertyName 'Start' -NotePropertyValue $StartDateTime
    $result | Add-Member -NotePropertyName 'End' -NotePropertyValue $EndDateTime
    $rawCounterData = $CounterResult.CounterSamples | Select-Object -Property Path, CookedValue
    foreach ($counterName in $CounterInformation.Keys)
    {
        $relevantCounters = $rawCounterData | Where-Object {$_.Path -ilike '*' + $counterName }
        foreach ($aggregate in $CounterInformation[$counterName].Keys)
        {
            $outputName = $CounterInformation[$counterName][$aggregate]
            $value = $null
            switch ($aggregate)
            {
                ([AggregationType]::Min)
                {
                    $value = $relevantCounters | Measure-Object -Property CookedValue -Minimum | Select-Object -ExpandProperty Minimum;
                    continue
                }
                ([AggregationType]::Max)
                {
                    $value = $relevantCounters | Measure-Object -Property CookedValue -Maximum | Select-Object -ExpandProperty Maximum;
                    continue
                }
                ([AggregationType]::Sum)
                {
                    $value = $relevantCounters | Measure-Object -Property CookedValue -Sum | Select-Object -ExpandProperty Sum;
                    continue
                }
                ([AggregationType]::Avg)
                {
                    $value = $relevantCounters | Measure-Object -Property CookedValue -Average | Select-Object -ExpandProperty Average;
                    continue
                }
            }
            $result | Add-Member -NotePropertyName $outputName -NotePropertyValue $value
        }
    }
    $result
}

$fileExtension = "csv"
function Write-CountersToDisk
{
    param($CounterData, $Directory, $ExportType)
    Write-Host 'Write-CountersToDisk'
    $counter = 0
    $suffix = ''
    $done = $false
    $date = Get-Date -format yyyy-MM-dd
    while($Done -eq $false -and $Counter -le 9)
    {
        Try
        {
            $filename = $ExportType + "_" + $date + $suffix + "." + $fileExtension
            Write-Host "Trying to write $filename"
            $filePath = $Directory + $filename
            $CounterData | Export-Csv -Path $filePath -Delimiter ";" -NoTypeInformation -Append
            $done = $true
        }
        Catch [Exception]
        {
            Write-Host "Failed to create file " + $_.Exception.GetType().FullName, $_.Exception.Message
            $counter++
            $suffix = '(' + $counter + ')'
        }
    }
    #Notify if we failed
    if ($Done -eq $false)
    {
        Notify-WriteCountersToDiskFailed -Directory $Directory -ExportType $ExportType
    }
}

function Notify-WriteCountersToDiskFailed
{
    param($Directory, $ExportType)
    Write-Host 'Write-CountersToDisk'
    #Todo: Notify that we failed
}
function Clean-OldCounterFiles
{
    param($Directory, $ExportType, $NumberOfDaysToKeepFiles)
    Write-Host 'Clean-OldCounterFiles'
    $filenameRegex = "^" + $ExportType + "_(?<Date>\d{4}-\d{2}-\d{2})(?:\(\d\))?\." + $fileExtension + "$"
    #Cleanup
    $files = Get-ChildItem $Directory -Filter ("*." + $fileExtension)
    $files | Foreach-Object {
        $filePath = $_.FullName
        $filename = [System.IO.Path]::GetFileName($filePath)
    
        $match = [regex]::Match($filename, $filenameRegex)
    
        #Write-Host $FilePath
        #Write-Host $Filename

        if ($match.Success -eq $true)
        {
            #Write-Host $Match.Groups["Date"].Value
            $fileDate = [datetime]::ParseExact($match.Groups["Date"].Value, "yyyy-MM-dd", $null)
            $timeSince = New-TimeSpan -Start $fileDate -End (Get-Date).Date
            if ($timeSince.TotalDays -ge $NumberOfDaysToKeepFiles)
            {
                Write-Host "Deleting $filePath"
                Remove-Item $filePath
            }
        }
    }
}

ExportSQLCounters.ps1

. "$PSScriptRoot\ExportPerfomanceCountersHelpers.ps1"
$SqlServerCounterPrefix = '\MSSQL$MSSQL_2008'
$Counters = @{
            ($SqlServerCounterPrefix + ":Buffer Manager\Buffer cache hit ratio") = @{[AggregationType]::Avg = "Buffer_cache_hit_ratio_avg"};
            ($SqlServerCounterPrefix + ":Buffer Manager\Page life expectancy") = @{[AggregationType]::Min = "Page_life_expectancy_min"};
            ($SqlServerCounterPrefix + ":Buffer Manager\Checkpoint pages/sec") = @{[AggregationType]::Sum = "Checkpoint_pages_sum"};
            ($SqlServerCounterPrefix + ":General Statistics\Processes blocked") = @{[AggregationType]::Sum = "Processes_blocked_sum"};
            ($SqlServerCounterPrefix + ":General Statistics\User Connections") = @{[AggregationType]::Max = "User_connections_max"};
            ($SqlServerCounterPrefix + ":SQL Statistics\Batch Requests/sec") = @{[AggregationType]::Sum = "Batch_requests_sum"};
            ($SqlServerCounterPrefix + ":SQL Statistics\SQL Compilations/sec") = @{[AggregationType]::Sum = "Compilations_sum"};
            ($SqlServerCounterPrefix + ":SQL Statistics\SQL Re-Compilations/sec") = @{[AggregationType]::Sum = "ReCompilations_sum"};
            ($SqlServerCounterPrefix + ":Access Methods\Page splits/sec") = @{[AggregationType]::Sum = "Page_splits_sum"};
            ($SqlServerCounterPrefix + ":Locks(_Total)\Lock Waits/sec") = @{[AggregationType]::Sum = "Lock_waits_sum"};
            ($SqlServerCounterPrefix + ":Locks(_Total)\Lock Wait Time (ms)") = @{[AggregationType]::Sum = "Lock_wait_time_sum"};
            ($SqlServerCounterPrefix + ":Locks(_Total)\Number of Deadlocks/sec") = @{[AggregationType]::Sum = "Number_of_deadlocks_sum"};
            }

$DataDirectory = 'Z:\Temp\PowershellTest\'
$Type = 'SQLServerStatistics'

$CounterData = Get-CounterData -CounterInformation $Counters -CollectionTime 300
Write-CountersToDisk -CounterData $CounterData -Directory $DataDirectory -ExportType $Type
Clean-OldCounterFiles -Directory $DataDirectory -ExportType $Type -NumberOfDaysToKeepFiles 7