Im using Powershell 5 and Filebeat to resolve this issue.
Note this has not been tested more then a few hours and its a proof of concept.
#Version 0.2
#Todo:
#* Notify on fail
#Function to get Sql Server Counters
function Get-SqlServerData()
{
$Data = get-counter ($SqlServerCounterPrefix + ":Buffer Manager\Buffer cache hit ratio"),
($SqlServerCounterPrefix + ":Buffer Manager\Page life expectancy"),
($SqlServerCounterPrefix + ":Access Methods\Page splits/sec")
#$Data
$TransformedData = $Data.CounterSamples | Select-Object -Property Path, CookedValue
$object = New-Object psobject
$object | Add-Member -NotePropertyName 'Timestamp' -NotePropertyValue $Data.Timestamp
foreach ($row in $TransformedData)
{
$path = $row.Path
$name = $path.Substring($path.LastIndexOf("\") + 1)
$object | Add-Member -NotePropertyName $name -NotePropertyValue $row.CookedValue
}
$object
}
#Parameters
$SqlServerCounterPrefix = '\MSSQL$MSSQL_2008'
$Type = "SQLServerStatistics"
$Data = Get-SqlServerData
$Path = "Z:\Temp\PowershellTest\"
$AddTimeStamp = $false
$NumberOfDaysToKeepFiles = 7
$FileExtension = "csv"
#Variables (do not change)
$Date = Get-Date -format yyyy-MM-dd
$Timestamp = Get-Date
$FilenameRegex = "^" + $Type + "_(?<Date>\d{4}-\d{2}-\d{2})(?:\(\d\))?\." + $FileExtension + "$"
$Suffix = ''
$Counter = 0
$Done = $false
if ($AddTimeStamp -eq $true)
{
$Data | ForEach-Object {
$_ | Add-Member -NotePropertyName 'Timestamp' -NotePropertyValue $Timestamp
}
}
#Try to export file if it fails (the headers have changed) add a (number)
while($Done -eq $false -and $Counter -le 9)
{
Try
{
$Filename = $Type + "_" + $Date + $Suffix + "." + $FileExtension
Write-Host "Trying to write $Filename"
$FilePath = $Path + $Filename
$Data | 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)
{
#Todo: Notify that we failed
}
#Cleanup
$Files = Get-ChildItem $Path -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)
{
Remove-Item $FilePath
}
}
}
This will create a csv file in the $Path directory, if you change the counters then it will create a new file with (1-9) in the name.
A updated version can be found at: https://gist.github.com/AnderssonPeter/8261e255630d6f672ba5bc80f51b017f
