As a result of monitoring and reporting needs, statistical performance counter daily values, to confirm the health of the database server. If open counter to fill in, more trouble, now statistics with PowerShell to read the value of the counter.
Phase I: Powershell read the counter file and count the values of one of the counters
$startDate = (get-date). AddDays (-1). Date
$endDate = (get-date). Date
$perfPath = "D:\DATAFILES\PERFMON\MSSQL_PERFMON_08240904.BLG"
#读取文件中的计数器名称
$counterList = Import-counter-path $perfPath
$countersNameList = $counterList [0].countersamples |% {$_.path}
# Filter specify counters and times to re-import ps
$counter = $countersNameList-like ' *processor time* '
$counterData = Import-counter-path $ Perfpath-counter $counter | Where-object-filterscript {($_. Timestamp-ge $startDate)-and ($_. Timestamp-lt $endDate)}
#计算日期范围内的数值统计
$counterInfo = $counterData | Foreach-object {$_. Countersamples} | Measure-object-property cookedvalue-average-maximum
#哈希表存储结果数据
$resultTable =@{}
$resultTable. " CPU utilization-average "= $counterInfo. Average
$resultTable." CPU utilization-Max "= $counterInfo. Maximum
Phase two: All the counters in the batch statistics file and exported to the file
$startDate = (get-date). AddDays (-1). Date $endDate = (get-date). Date $perfPath = "D:\360DOWNLOADS\*.BLG" #哈希表存储结果数据 $resultTable =@{} #导入指定时间的所有计数器信息 $counterData = Import-coun Ter-path $perfPath | Where-object-filterscript {($_. Timestamp-ge $startDate)-and ($_. Timestamp-lt $endDate)} #所有的计数器名字 $countersNameList = $counterData [0].countersamples | % {$_. Path} #遍历每个计数器, storing the results of the calculation in the hash table foreach ($counterName in $countersNameList) {# $counterName = ' \\hzc\system\threads $ Counterdataone = $counterData | Foreach-object {$_. Countersamples} | Where {$_. Path-like $counterName} $counterInfo = $counterDataOne | Measure-object cookedvalue-average-minimum-maximum $resultTable. $ ($counterName + ": average") = $counterInfo. Average $resu
lttable.$ ($counterName + ": Min.") = $counterInfo. Minimum $resultTable. $ ($counterName + ": max") = $counterInfo. Maximum} # $resultTable. GetEnumerator () | Sort Name | Format-table-auto #几种方法导出到文件 $resultTable. GetEnumerator () | Sort Name | FoRmat-table-auto | Out-file "D:\360Downloads\PerfmonCounter.txt" $resultTable. GetEnumerator () | Sort Name | Export-csv-path "D:\360Downloads\PerfmonCounter.txt"-encoding "Unicode"-force $resultTable. GetEnumerator () | Sort Name | Format-list | Export-csv-path "D:\360Downloads\PerfmonCounter.xlsx"-encoding "Unicode"-force