Collect Performance DCS Pool

Set-StrictMode -Version latest
# Enter average interval in MILLIseconds
$interval = 5000
#Enter seconds for capture to run
$seconds = $(60*60*24-120)

$startTime = [DateTime]::Now
$endTime = $startTime.addSeconds($seconds)
#Run for 1 day (60sec/min * 60min/hour * 24hour/day) / interval + fudge factor for 'run time' of script
#$iterations = $($seconds/($interval + .2)) 
#Enter SDS to log into
$SDS = "xxxxxx"
#Enter Output file
#$out = "c:\Temp\perfdata.csv"
#Enter User Name
$user = 'xxxxx'
#Enter Password
$pass = 'xxxxx'
$SQLServer = "xxxxxxx" #use Server\Instance for named SQL instances! 
$SQLDBName = "xxxxxxx"
	
Function Insert-PerfData {
param(
	[String]$DatacoreId,
	[String]$DatacoreName,
	[Double]$value,
	[DateTime]$collectionTime)
	
	#Write-Host "DatacoreId = $DatacoreId"
	#Write-Host "DatacoreName = $DatacoreName"
	#Write-Host "Value=$value"
	#Write-Host "CollectionTime = $collectionTime"
	
	if($CounterIdMap.ContainsKey($DatacoreName)) {
		$CounterId = $CounterIdMap.get_item($DatacoreName)
		if($ObjectIdMap.ContainsKey($DatacoreId)) {
			$ObjectId = $ObjectIdMap.get_item($DatacoreId)
			
				$SqlQuery = "insert into perfdata (CounterId,ObjectId,Value,CollectionTime) values ($CounterId,$ObjectId,$value,@CollectionTime)"
				$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
				$SqlCmd.CommandText = $SqlQuery
				$SqlCmd.Connection = $SqlConnection
				$SqlCmd.Parameters.Add('@CollectionTime','DateTime2',8).Value = $collectionTime
				$result = $SqlCmd.ExecuteNonQuery()
	
		}
	}
}

Function Get-ObjectIdHash
{
	$ObjectIdMap =@{};
	
	$SqlQuery = 'select objectid,DatacoreId from objectlookup'

	$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
	$SqlConnection.ConnectionString = "Server = $SQLServer;Database = $SQLDBName; Integrated Security = True"
	$SqlConnection.Open()
	
	$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
	$SqlCmd.CommandText = $SqlQuery
	$SqlCmd.Connection = $SqlConnection
	$SqlReader = $SqlCmd.ExecuteReader();
	
	while($SqlReader.Read()) {
		$ObjectIdMap.Add($SqlReader["DatacoreId"],$SqlReader["objectid"])
	}
	
	$SqlConnection.Close()
	
	$ObjectIdMap
}

Function Get-CounterIdHash
{
	$CounterIdMap =@{};
	
	$SqlQuery = 'select counterid,DatacoreName from counterlookup'

	$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
	$SqlConnection.ConnectionString = "Server = $SQLServer;Database = $SQLDBName; Integrated Security = True"
	$SqlConnection.Open()
	
	$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
	$SqlCmd.CommandText = $SqlQuery
	$SqlCmd.Connection = $SqlConnection
	$SqlReader = $SqlCmd.ExecuteReader();
	
	while($SqlReader.Read()) {
		$CounterIdMap.Add($SqlReader["DatacoreName"],$SqlReader["counterid"])
	}
	
	$SqlConnection.Close()
	
	$CounterIdMap
}


#Init Connections to Database / Datacore Server
Connect-DcsServer -Server $SDS -UserName $user -Password $pass

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer;Database = $SQLDBName; Integrated Security = True"
$SqlConnection.Open()

$CounterIdMap = Get-CounterIdHash
$ObjectIdMap = Get-ObjectIdHash

$diskPools = Get-DcsPool #|? {$_.caption -match '02'}
$lastPerf = @{};

# Load up Last Perf Hash
foreach ($diskPool in $diskPools) {
	$perf = Get-DcsPerformanceCounter $diskPool
	$lastPerf.add($diskPool.id,$perf)
}

sleep -Milliseconds $interval

while([DateTime]::Now -lt $endTime) {
	$iterationStart = [DateTime]::Now
	"`nInterval Start Time: $iterationStart"
	foreach ($diskPool in ($diskPools)) {
		
		$perf = Get-DcsPerformanceCounter $diskPool
		$lastperfpool = $lastperf.get_item($diskPool.id)
		
		$timediff = ($perf.CollectionTime.Ticks - $lastperfpool.CollectionTime.Ticks) / [Math]::Pow(10,7)
		$MBdiff = $timediff *[Math]::Pow(2,20)
		
		$TMT = [Math]::Round(($perf.TotalBytesTransferred - $lastperfpool.TotalBytesTransferred) / $MBdiff,3)
		$RMB = [Math]::Round(($perf.TotalBytesRead - $lastperfpool.TotalBytesRead)  / $MBdiff,3)
		$WMB = [Math]::Round(($perf.TotalBytesWritten - $lastperfpool.TotalBytesWritten)  / $MBdiff,3)
		$TIO = [Math]::Round((($perf.TotalOperations - $lastperfpool.TotalOperations) / $timediff ),3)
		$RIO = [Math]::Round((($perf.TotalReads - $lastperfpool.TotalReads) / $timediff ),3)
		$WIO = [Math]::Round((($perf.TotalWrites - $lastperfpool.TotalWrites) / $timediff ),3)
		
		If (($perf.TotalReads - $lastperfpool.TotalReads) -eq 0)	{ 
			$RLat = 0
		}
		else {
			#$($perf.TotalReadTime - $lastperfpool.TotalReadTime)
			$RLat = [Math]::Round(($perf.TotalReadTime - $lastperfpool.TotalReadTime) / (($perf.TotalReads - $lastperfpool.TotalReads)),2)  
		}
		
		if (($perf.TotalWrites - $lastperfpool.TotalWrites) -eq 0) {
			$WLat = 0
		}
		else  {
			#$($perf.TotalWriteTime - $lastperfpool.TotalWriteTime)
			$WLat = [Math]::Round(($perf.TotalWriteTime - $lastperfpool.TotalWriteTime) / (($perf.TotalWrites - $lastperfpool.TotalWrites)),2)
		}
	
		#Write-Host "$($logicalDisk.ExtendedCaption) Read:$RLat Write:$WLat"
	
		
		Insert-PerfData $diskPool.id "TotalBytesTransferred"  $TMT $perf.CollectionTime
		Insert-PerfData $diskPool.id "TotalBytesRead" $RMB $perf.CollectionTime
		Insert-PerfData $diskPool.id "TotalBytesWritten" $WMB $perf.CollectionTime
		Insert-PerfData $diskPool.id "TotalOperations"  $TIO $perf.CollectionTime
		Insert-PerfData $diskPool.id "TotalReads" $RIO $perf.CollectionTime
		Insert-PerfData $diskPool.id "TotalWrites" $WIO $perf.CollectionTime
		
		Insert-PerfData $diskPool.id "TotalReadTime" $RLat $perf.CollectionTime
		Insert-PerfData $diskPool.id "TotalWriteTime" $WLat $perf.CollectionTime
		
		
		#Insert-PerfData $logicalDisk.id "CacheReadHits" $CRH $perf.CollectionTime
		#Insert-PerfData $logicalDisk.id "CacheReadMisses" $CRM $perf.CollectionTime
		#Insert-PerfData $logicalDisk.id "CacheWriteHits" $CWH $perf.CollectionTime
		#Insert-PerfData $logicalDisk.id "CacheWriteMisses" $CWM $perf.CollectionTime
		
		$lastPerf.$($diskPool.id) = $perf
			
	}
	$iterationEnd = [DateTime]::Now
	#"Interval End Time: $iterationEnd"
	"Elapsed: $($iterationEnd - $iterationStart)`n"
	sleep -Milliseconds $($interval - $($iterationEnd - $iterationStart).TotalMilliseconds)
	
}

$SqlConnection.Close()
Disconnect-DcsServer