Archive for the ‘Scripting’ Category

Visualizing data as a graph is a very effective way to “understand” the data. A graph can be thought of as a node with edges that link to related data. In this blog we’ll look at how to automate graphs for a couple of S3D scenarios:

  • Reporting plant versions

This graph relates S3D version with plants that are at the same version.


  • Reporting IFC status for plants in a site

This shows IFC status for plants serviced by an IFC Server


A .doc file is provided below with sample PowerShell script. Just download the file, rename to .zip and extract the .ps1 file within.

A Taste of Graphviz

Graphviz is an open source graph visualization tool that help you create such type of graphs using it’s DOT language to layout the structure.


So the above layout can be written in dot language as:

digraph graphname {
   node [shape=box]
   Server [label="Server-01"]
   P1 [label="Plant-01"]
   P2 [label="Plant-02"]
  Server->P1 [label="V11"]
  Server->P2 [label="V11 R1"]

In this case:

  • digraph – Specifies a directional graph (most common)
  • rankdir – Specifies the direction of layout: TopBottom or LeftRight (LR)
  • label – Specifies the label for each node
  • -> – Links a node with it’s related node

Calling Graphviz from PowerShell

For Windows, there’s a zip package that can be downloaded from Graphviz site. We’ll be using the dot.exe from this package to construct graph bitmaps. So go ahead and download the zip and extract the exe to somewhere in your path. In my case, it under the current folder where I’m running the PowerShell ISE.

To run the above command in PowerShell, use this snippet:

digraph graphname {
   node [shape=box]
   Server [label="Server-01"]
   P1 [label="Plant-01"]
   P2 [label="Plant-02"] 
  Server->P1 [label="V11"]
  Server->P2 [label="V11 R1"]
"@ | .\Graphviz\bin\dot.exe -Tpng -o .\Test.png


Highlight the lines above and right-click to choose the option to “Run Selection”. This will execute the highlighted text and you should see a Test.png in your current folder. The –Tpng –o .\Test.png clause instructs dot.exe to output a “png” file and save it as Test.png.

Using SCA CmdLets with Graphviz

Since SCA comes with a host of CmdLets that allow you to probe Site, Plant and IFC information, let’s see how we can use a couple of these in concert with Graphviz to output something useful for a S3D administrator.

Couple of scenarios one can think of are:

  • Outputting list of plants under a site with their versions
  • Outputting IFC status for a list of plant

Visualizing S3D Versions:

Let’s look at the 1st one. SCA has a cmdlet, Get-SCAPlantInfo which when invoked as:

Get-SCAPlantInfo <server> -AllPlants

will output a list of all plants on the server. Something like this:


So if we needed to output this information with version as a starting node and plant name as edges, it would look something like this in .dot language:

digraph S {
 "" ->"S3D"

To automate the creation of this grammar, we can use a PowerShell script that acts as a DSL for the DOT language as detailed in a blog by Doug Finke here (and explained in his book Windows PowerShell for Developers – a great read).

Using the PowerShell script we can wrap the calls to create a new graph and add edges within it:

New-Graph S {
  Get-SCAPlantInfo Local_SQL --AllPlants | ForEach {Add-Edge $_.Version $_.Plant}
} | .\Graphviz\bin\dot.exe -Tpng -o .\Plants.png

The resultant Plant.png shows this (below). You can already see the usefulness in visualizing this data :



Visualizing IFC Status

Taking this a step further, Get-SCAPlantInfo has an –OnlyIFCStatus option that lets you output IFC information for a plant. Something like this:


If we want to run it against all the plants in a site and create a data graph, we would need to:

  1. Gather all plants on the server using Get-SCAPlantInfo –AllPlants
  2. Filter the ones that we’re interested in maybe by site or name
  3. Call Get-SCAPlantInfo with the plant and use –OnlyIFCStatus to get ifc data
  4. Use Add-NewEdge to add the plant with IFC data


And we get the resultant graph:



That should be all. You can take these concepts and use it with other CmdLets like Get-SCADbServerInfo (db & user info), Get-SCAComputerInfo, etc. Hopefully this will encourage you to use Graphviz and SCA CmdLets to easily and efficiently visualize S3D data.

PowerShell Script


Read Full Post »

I recently had a requirement to start SCA automatically on remote machines. This was needed for our stress test where we normally have a bunch of servers taking part in the test with different roles: MSSQL Server, IIS Server, File Server, etc.

Now SCA, version 5.7.2 onwards, comes with a console app, SCARunner, that allows you to run SCA from command line. That is fine if you are starting ScA from a batch file interactively, but still doesn’t solve the issue of running it remotely. Luckily PowerShell with it’s remoting and WMI capabilities is geared for just these kind of task, and helped me solve this problem.

In this blog, I’ll show you one way of accomplishing this task.

First Step

The first step is to install SCA on a remote server and then register the server within SCA. I choose the use the default hostname as the registration name since it makes it easier to use it within the PowerShell script. In the image below, my machine name is SJOSHI


Second Step

The next step is to test the SCARunner on the remote machine. To do that, you need to open a DOS/Command window in SCA installation folder which is C:\Program Files (x86)\Intergraph\SCA

To start SCARunner you need to specify these options:

-s  – The registration name. So in my case it would be SJOSHI

-f  – The folder where you want to save the files. The scan xml and perfmon .blg file

So for example, if I needed to start SCA on my machine, I would do:

SCRunner –s SJOSHI –f D:\Temp\Low


When invoked, SCARunner first runs a scan of the registered server and saves the scan xml. Then it creates a perfmon collector with the name of the machine and starts it.

To stop this collector , I need to do:

SCARunner –stop

And this will stop and remove the collector from perfmon.

Third Step

To be able to invoke this from PowerShell remotely, I need to first enable remoting on the server. This is done by running the command, Enable-PSRemoting –Force on the server from a PowerShell admin prompt.



Fourth Step

So at this stage we have the server configured (SCA & PowerShell) and we know the options to run SCARunner. The remaining part is to work out the PowerShell script, so let’s tackle that now.

PowerShell with its WMI Cmdlets, allows you to invoke methods against WMI classes. In our case, we need to start a process, SCARunner, so the Win32_Process with it’s Create method looks like an ideal candidate.

To invoke a WMI method, we can use the Invoke-CimMethod Cmdlet which takes the class name, method name and an arguments dictionary. So let’s see how you can use it.

Say I want to open a file, test.txt in my D:\Temp\Low folder using this class. I can do this in command line by typing notepad D:\Temp\Low\Test.txt


And the file does open up. To do the same in PowerShell, you need to type:

Invoke-CimMethod –ClassName Win32_Process –MethodName Create –Arguments @{CommandLine=“notepad D:\Temp\Low\Test.txt”}

And notepad does open up:


What’s with the –Arguments field ? Well the Get-Help on Invoke-CimMethod shows it to be a dictionary (key-value type).


So it takes, a key and a value. The key name is CommandLine and value is the path to exe with arguments if reqd. The key name is obtained from the Create method description as shown here


So we can get notepad to open via WMI. Now lets try it against a remote-machine. If you do a Get-Help Invoke-CimMethod in PowerShell, you will see that it takes a –ComputerName or –CimSession parameter.


So type the same command with an additional –ComputerName option followed by a machine name that you have for this test:

Invoke-CimMethod –ComputerName XYZ ..rest of the command as before.

As you can see from the image below, the remote process is created and shown under Task Manager. Do note, the remote processes don’t show a UI because of security reasons.



So let’s now try with the SCARunner application against a remote machine on which SCA is installed (and server registered). Everything will be same as before except the CommandLine, which should now include the path to SCARunner along with the start-up options:



And you can see that it does start successfully which is pretty cool!

To stop it, you can just use the –stop option, in the $cmdLine variable:

$cmdLine = "C:\Program Files (x86)\Intergraph\SCA\SCARunner.exe -stop"

In my setup, I use a CSV file to import a list of servers using the Import-Csv cmdlet, and startup SCA on those machines.


Hope this motivates you to use PowerShell to automate remote-tasks which would otherwise be hard to accomplish in a simple and efficient way.

Read Full Post »

If you have used PowerShell V2 (and above) for sometime now, you would know that you can use the Import-Counter Cmdlet to read Perfmon data from a .blg file.

This works fine when you want to look at data “as it is” and export to a CSV file or format it for display. I recently ran into a scenario where I had parse multiple perfmon files and then “look” at the data with different time intervals for a few specific counters only.

PS >dir D:\Sunit\Blog\PerfmonFiles -Include *.blg -Recurse | Select Fullname






Also I needed to dump all the data into one CSV file so that I could use that for further analysis. So I wrote a function to allow me to do this and is included here in case it’s useful for someone else too.

The crux of the script is in this section where I calculate the averages depending on the interval:

$data = Import-Counter -Path $Path -Counter $counters
$d = $data | Where {$_.countersamples.status -eq 0}
$entries = @()
$runID = ($Path -split "\\")[-2] #Change this to your depth
Write-Debug "Key $runID"
for($i=1; $i -lt $d.Count; $i += $Interval)
$UBound = $i + ($Interval-1)
$entries += [pscustomobject] @{
Timestamp = ([datetime] $d[$i].Timestamp).ToLongTimeString()
CPUAvgProc = $d[$i..$UBound] | Select -ExpandProperty CounterSamples | `
Where {$_.Path -like $counters[0]} | measure -Property CookedValue -Average `
| select -ExpandProperty Average
CPUAvgPriv = $d[$i..$UBound] | Select -ExpandProperty CounterSamples | Where {$_.Path -like $counters[1]} `
| measure -Property CookedValue -Average | select -ExpandProperty Average
RunID = $runID

The script takes in pipeline input (from dir cmd) and lets you specify the interval and CSVFolder to dump individual files to. In my case the logging interval was 15 secs, so if I specified –Interval 4, it would average data for every 4 counters and display it in each minute interval.

It also has a –Summary option that let’s you see the logging-interval and the counter paths in the perfmon file. Something like this:


To aggregate the data for multiple files and place all data in one file, you would do:

dir D:\Sunit\Blog\ParseBLGFiles -Include *.blg –Recurse | ParseBLGFile -Interval 4 –AllInOneFile

Here’s a run with interval specified as 4 (data is avg. for each min since logging interval is 15 sec in this case) and option to save all of the data in one csv file:

dir D:\Sunit\Blog\PerfmonFiles -Include *.blg -Recurse | ParseBLGFile -Interval 4 -AllInOneFile –CSVFolder D:\Sunit\Blog\PerfmonFiles | ft -AutoSize


And the subsequent CSV file, AllRuns.csv


Here’s the entire script (rename to .zip). If it proves helpful, do please let me know.


Read Full Post »

I have updated the PowerShell script, GraphSQLIO.ps1, to include the histogram data that appears in the output file. As you know, the data is something like this:

ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 100

Now when you run the script, it will save this data too and the charts will show it like this:


The updated files are here (save & rename to .zip and extract):GraphSQLIO

Note: The script defaults to using MSChart control assembly that is delivered with .NET 4.0. In other words, it assumes that you are using PowerShell 3.0 and above. If you are using PowerShell 2.0, then make sure that you have:

  • Installed MSChart control from MSDN
  • Commented the line in the script (see below) that uses .NET 4.0 and un-commented that line that use .NET 3.5 lib for MSChart control

Some folks had trouble downloading the file so I have added the PowerShell script here in it’s entirety:

[string]$OptionsFile= “TestSQLIO.cmd”,
[string]$ResultsFile= “SQLIOResults.xml”,

Add-Type -AssemblyName System.Windows.Forms
#Use this on .NEt 4.0 otherwise comment the line below
Add-Type -AssemblyName System.Windows.Forms.DataVisualization

#Use this on .NEt 3.5
#Add-Type -AssemblyName (‘System.Windows.Forms.DataVisualization, Version=, Culture=neutral, PublicKeyToken=31bf3856ad364e35’)

#Script level variable to store Run entries
$Script:entries = @()

function ParseRunEntries
$folder = Split-Path (Resolve-Path $OptionsFile) -Parent
$lines = (Get-Content $OptionsFile) -split [Environment]::NewLine | Where {$_.Trim().Length -gt 0 -and $_.StartsWith(“sqlio”)} | foreach {$_.Trim()}

foreach($line in $lines)
$options = $line -split “-” | foreach {$_.Trim()}
$filename = [String]::Empty
if($options.Length -eq 9)
$sqlObj = New-Object psobject
Add-Member -InputObject $sqlObj -m NoteProperty -Name “Options” -Value @()
foreach($option in $options[1..$options.Length])
$sqlObj.Options += “‘-$option'” #Create parameter array of string type
$value = $option.Substring(1).Trim()
if($option -match “^k(w|W|R|r)”){
$filename += $value.ToUpper()
Add-Member -InputObject $sqlObj -m NoteProperty -Name “Operation” -Value $value
elseif($option -match “^s\d+” ) {
$filename += $option
Add-Member -InputObject $sqlObj -m NoteProperty -Name “Duration” -Value ([int]$value)
elseif($option -match “^f(random|sequential)”) {
$filename += $option
Add-Member -InputObject $sqlObj -m NoteProperty -Name “Mode” -Value $value
elseif($option -match “^o\d+”) {
Add-Member -InputObject $sqlObj -m NoteProperty -Name “Outstanding” -Value ([int]$value)
elseif($option -match “^b\d+”) {
$filename += $option
Add-Member -InputObject $sqlObj -m NoteProperty -Name “Size” -Value ([int]$value)
elseif($option -match “-F.*\.txt”)
Add-Member -InputObject $sqlObj -m NoteProperty -Name “ParamFile” -Value $value
$filename += “.txt”
Add-Member -InputObject $sqlObj -m NoteProperty -Name “OutputFile” -Value $filename
Add-Member -InputObject $sqlObj -m NoteProperty -Name “CaptureLatency” -Value “‘-LS'”
Add-Member -InputObject $sqlObj -m NoteProperty -Name “Buffering” -Value “‘-BN'”
$Script:entries += $sqlObj
$sqlObj = $null
write “Error executing script: $Error”

function RunEntries
$validRuns = @()
if($Matches) { $Matches.Clear()}
foreach($obj in $Script:entries)
$sqlioPath = Resolve-Path sqlio.exe
$results = Invoke-Expression -Command “$sqlioPath $($obj.Options)” | Out-String
$results | Set-Content $obj.OutputFile

#Add the results to each run object
Add-Member -InputObject $obj -m NoteProperty -Name “Results” -Value $results

$lines = $obj.Results -split [Environment]::NewLine | Where {$_.Trim().Length -gt 0} | foreach {$_.Trim()}
foreach($line in $lines)
$outputValue = ParseOutputValue $line
if($outputValue -is [double])
if($line -match “IOs/sec.*”)
Add-Member -InputObject $obj -MemberType NoteProperty -Name “IOsPerSec” -Value $outputValue
elseif($line -match “MBs.*”)
Add-Member -InputObject $obj -MemberType NoteProperty -Name “MBsPerSec” -Value $outputValue
elseif($line -match “Avg_Latency.*”)
Add-Member -InputObject $obj -MemberType NoteProperty -Name “AvgLatencyMs” -Value $outputValue
elseif($line -match “using (specified|current) size:”)
Add-Member -InputObject $obj -MemberType NoteProperty -Name “TestFilesize” -Value $outputValue
elseif($line -match “(?\d+)\s+thread[s]?\s+(reading|writing).*file\s+(?[a-zA-Z]?[:\\]?.*)”)
Add-Member -InputObject $obj -MemberType NoteProperty -Name “Threads” -Value ([int]$Matches.th)
Add-Member -InputObject $obj -MemberType NoteProperty -Name “TestFile” -Value $Matches.TestFile.Trim()
elseif($line -match “^ms:\s+\d+”)
#ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
$lineWithValues = $line -split {$_ -eq “m” -or $_ -eq “s” -or $_ -eq “:” -or $_ -eq “+”} | where {$_.Trim().Length -gt 0} | foreach {$_.Trim()}
$values = $lineWithValues -split “\s+” | foreach {[int]$_}
Add-Member -InputObject $obj -MemberType NoteProperty -Name “LatencyValues” -Value $values
elseif($line -match “^%:\s+\d+”)
#%: 0 0 0 0 3 4 5 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 85
$lineLatency = $line -split {$_ -eq “%” -or $_ -eq “:”} | where{$_.Trim().Length -gt 0} | foreach{$_.Trim()}
$percentValues = $lineLatency -split “\s+” | foreach {[int]$_}
Add-Member -InputObject $obj -MemberType NoteProperty -Name “LatencyPercent” -Value $percentValues
$validRuns += $obj
$validRuns | Export-Clixml -Path $ResultsFile

function ParseOutputValue($linevalue)
$value = [String]::Empty
if($linevalue -match “([^(ms:|%:\d+)].*:\s+)(?\d+)”)
$value = [double] $Matches.no

function GetChartArea($chart, $curTicks)
$chartArea = New-Object System.Windows.Forms.DataVisualization.Charting.ChartArea
$chartArea.AxisY.MajorGrid.LineColor = “Blue”
#$chartArea.Area3DStyle = New-Object System.Windows.Forms.DataVisualization.Charting.ChartArea3DStyle `
# -Property @{Enable3D=$true; IsRightAngleAxes=$false; Rotation=20; Inclination=20; PointDepth=100; PointGapDepth=200}

$chartAreaName = “CA$curTicks”
$chartArea.Name = $chartAreaName
$chartArea.BackSecondaryColor = [System.Drawing.Color]::LightSteelBlue
$chartArea.BackGradientStyle = [System.Windows.Forms.DataVisualization.Charting.GradientStyle]::DiagonalRight
$chartArea.AxisX.Title = “Block Size”
$chartArea.Area3DStyle.Enable3D = $false
$chart.ChartAreas.Add($chartArea) | Out-Null

function SetSeries($chart, $chartArea, $seriesName, $chartType=”Column”, $seriesType=”Cylinder”)
$chart.Series.Add($seriesName) | Out-Null
$chart.Series[$seriesName].ChartArea =$chartArea.Name
$chart.Series[$seriesName].BorderWidth = 2
$chart.Series[$seriesName].ChartType = $chartType
$chart.Series[$seriesName].LabelForeColor = [System.Drawing.Color]::DarkGreen
$chart.Series[$seriesName].LabelBackColor = [System.Drawing.Color]::LightGreen
$chart.Series[$seriesName][“DrawingStyle”] = $seriesType
$chart.Series[$seriesName][“PointWidth”] = “0.5”

function GenerateCharts
if(-not (Test-Path $ResultsFile))
throw “Invalid file specified”

$Script:entries = Import-Clixml $ResultsFile
$groupEntries = $Script:entries | Group-Object -Property Operation, Mode -AsHashTable -AsString

$form = New-Object System.Windows.Forms.Form
$form.Text = “SQLIO CHARTS”
$form.Width = 700
$form.Height = 700

$tabHost = New-Object System.Windows.Forms.TabControl
$tabHost.Dock = “Fill”
$runThreads = ($Script:entries | select -First 1).Threads
$runOutstanding = ($Script:entries | select -First 1).Outstanding
$runDuration = ($Script:entries | select -First 1).Duration
$runFileSize = ($Script:entries | select -First 1).TestFilesize
$runFilePath = ($Script:entries | select -First 1).TestFile
$form.Text = “SQLIO-$runDuration sec run with $runThreads threads, $runOutstanding pending IOs & $runFileSize(MB) $runFilePath file”

foreach($grpKey in $groupEntries.Keys)
#Create tab page to host group Chart
$chartName = ($grpKey -replace “,”, “-“) -replace ” ” , “”
$tabPage = New-Object System.Windows.Forms.TabPage -ArgumentList “Run-$($chartName)”

#create chart host
$chart = New-Object System.Windows.Forms.DataVisualization.Charting.Chart
$chart.BackColor = [System.Drawing.Color]::Transparent
$chart.Dock = “fill”

if($grpKey -match “random”)
$chart.Titles.Add(“Note: IOPS & Avg.Latency”) | Out-Null
$chart.Titles.Add(“Note: MBs/sec & Avg.Latency”) | Out-Null

$chart.Titles[0].Font = New-Object System.Drawing.Font(“Arial”,11, [System.Drawing.FontStyle]::Bold)

#Create chart areas
$grpEntry = $groupEntries[$grpKey]
AddChartAreas $chart $grpEntry

#Add chart to tab page

#Add tab page to tab control

$form.ShowDialog() | Out-Null

function AddChartAreas($chart, $currentGroup)
for($i=0; $i -lt 4; $i++)
$curTicks = (Get-Date).Ticks
#Get a chartarea and add it to chart
$chartArea = GetChartArea $chart $curTicks

if($i -lt 3)
#Get and set a series
$seriesName = “SE-$curTicks”

SetSeries $chart $chartArea $seriesName
$chart.Series[$seriesName].IsValueShownAsLabel = $true
$chart.Series[$seriesName].IsXValueIndexed = $true

#Add series data
if($i -eq 0)
$chartarea.AxisY.Title = “Avg. Latency(ms)”
$currentGroup | ForEach-Object{$chart.Series[$seriesName].Points.AddXY($_.Size, $_.AvgLatencyMs)} | Out-Null
elseif($i -eq 1)
$chartarea.AxisY.Title = “MBs/sec”
$currentGroup | ForEach-Object{$chart.Series[$seriesName].Points.AddXY($_.Size, $_.MBsPerSec)} | Out-Null
elseif($i -eq 2)
$chartarea.AxisY.Title = “IOPS”
$currentGroup | ForEach-Object{$chart.Series[$seriesName].Points.AddXY($_.Size, $_.IOsPerSec)}| Out-Null
$chartArea.AxisY.Title = “Percentage”
$chartArea.AxisX.Title = “Latency (ms)”
$chartArea.AxisY.IsMarginVisible = $true
#Add series data
foreach($grpEntry in $currentGroup)
$seriesName = “SE-” + (Get-Date).Ticks
SetSeries $chart $chartArea $seriesName -seriesType “Default”

for($i=0; $i -lt $grpEntry.LatencyPercent.Count; $i++)
$chart.Series[$seriesName].Points.AddY($grpEntry.LatencyPercent[$i]) | Out-Null



Read Full Post »

If you haven’t used SQLIO, it’s a tool available from Microsoft to determine the I/O capacity of a given hardware configuration before deploying SQL Server in production. I won’t go into details of using SQLIO since there are bunch of articles on the net on how to go about using/configuring it.

I’ve published the script & supporting files on GitHub:


What I want to show you here is how to automate SQLIO runs and parse the subsequent output to easily create charts that can help in analyzing the output data.

Something like this:


The “Note” is to basically reminds me that, I need to look at IOPS & Latency for random IOs and MBs/sec & Latency for sequential IOs.

My TestSQLIO.cmd file has this:

sqlio -kR -s300 -frandom -o8 -b8 -LS -BN -Fparam.txt

sqlio -kR -s300 -frandom -o8 -b64 -LS -BN -Fparam.txt

sqlio -kR -s300 -frandom -o8 -b512 -LS -BN -Fparam.txt

sqlio -kW -s300 -frandom -o8 -b8 -LS -BN -Fparam.txt

sqlio -kW -s300 -frandom -o8 -b64 -LS -BN -Fparam.txt

sqlio -kW -s300 -fsequential -o8 -b8 -LS -BN -Fparam.txt

sqlio -kW -s300 -fsequential -o8 -b64 -LS -BN -Fparam.txt

And my param.txt has this:

C:\sqltest.dat 4 0x0 32768

This runs the test using 8, 64, 512 block sizes and 8 pending IOs. These are values that I’m using and you can surely expand this for your needs.

You can see that I don’t specify the output file in the runs above since my PowerShell script concocts one for me. The PowerShell script runs the TestSQLIO.cmd (default input file) and outputs data to SQLIOResults.xml (default file).

I choose to create a PSObject in code that relates to an individual run with properties like Operation, Mode, Duration, Size, etc. so that I can serialize the object collection using Export-Clixml CmdLet. The reason I did this is, I didn’t want to worry about all the other artifacts, like the TestSQLIO.cmd and param.txt later, to query information on threads used, duration the test was run for, size of the testfile, etc. If I dump everything in an xm file, then that’s all I need later. Again this is just my preference and may or may not work for you.

With that said, this how the resultant xml looks like:

 <Obj RefId="0">
    <TN RefId="0">
      <Obj N="Options" RefId="1">
        <TN RefId="1">
      <S N="Operation">R</S>
      <I32 N="Duration">10</I32>
      <S N="Mode">random</S>
      <I32 N="Outstanding">8</I32>
      <I32 N="Size">8</I32>
      <S N="OutputFile">Rs10frandomb8.txt</S>
      <S N="CaptureLatency">'-LS'</S>
      <S N="Buffering">'-BN'</S>
      <Obj N="Results" RefId="2">
        <TNRef RefId="1" />
          <S>sqlio v1.5.SG</S>
          <S>using system counter for latency timings, 2530840 counts per second</S>
          <S>parameter file used: param.txt</S>
          <S>_x0009_file C:\sqltest.dat with 4 threads (0-3) using mask 0x0 (0)</S>
          <S>4 threads reading for 10 secs from file C:\sqltest.dat</S>
          <S>_x0009_using 8KB random IOs</S>
          <S>_x0009_enabling multiple I/Os per thread with 8 outstanding</S>
          <S>_x0009_buffering set to not use file nor disk caches (as is SQL Server)</S>
          <S>using specified size: 1024 MB for file: C:\sqltest.dat</S>
          <S>initialization done</S>
          <S>CUMULATIVE DATA:</S>
          <S>throughput metrics:</S>
          <S>IOs/sec:   235.04</S>
          <S>MBs/sec:     1.83</S>
          <S>latency metrics:</S>
          <S>Min_Latency(ms): 1</S>
          <S>Avg_Latency(ms): 134</S>
          <S>Max_Latency(ms): 972</S>
          <S>ms: 0  1  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+</S>
          <S>%:  0  0  0  0  0  0  0  3  1  0  0  1  2  0  1  1  0  1  1  1  1  1  1  0 85</S>
      <I32 N="Threads">4</I32>
      <Db N="TestFilesize">1024</Db>
      <Db N="IOsPerSec">235.04</Db>
      <Db N="MBsPerSec">1.83</Db>
      <Db N="AvgLatencyMs">134</Db>

Also once I have just this file, I can quickly “graph it” using the –OnlyCharts option and create tab based charts as shown above.

I used a a tabbed interface since I group my test runs based on Operation (R/W) and mode (sequential/random) and create a Hashtable:

Group-Object -Property Operation, Mode -AsHashTable –AsString

The Hashtable keys serve as individual tabpage that are added to the main tab hosted in the form.

To create the charts, I do this:

  • Create a form host with a tab-control that “fills” the form area  (GenerateCharts function)
  • Group the entries by Operation and Mode (GenerateCharts function)
  • Cycle through group keys by:
    • First creating a Chart object (GenerateCharts function)
    • Then creating a Chart Area and it’s Series for the current key’s values (AddChartAreas function)

That should be it!


Read Full Post »

As most Smart-3D admins know, regularly inspecting Oracle alert log and MSSQL database server log, is an important part of  daily admin tasks. Most critical errors related to failed database start-ups, initialization, failed login attempts, issues with capture/apply processes, etc. are written to these logs. Reading MSSQL logs is still simpler compared to Oracle, since in Oracle the alert log resides deep in directory structure that is hard to remember. Further, the path changes between 10g (under C:\ORACLE\PRODUCT\10.2.0\ADMIN\SP3DSMP1\BDUMP)  and 11g (under C:\ORACLE\diag\rdbms\sp3dsmp7\sp3dsmp7\trace) for each instance , making it more harder to keep track of it.

The SCA CmdLet, Get-SCADbServerInfo, tries to alleviate this issue a bit by providing the ReadLog() method, that works for both Oracle (1g/11g) and MSSQL servers. The only input it needs is the SCA server name and it figures out the path for the instance under the SCA server context. Plus, you can run the command remotely from a client machine, and as long as you have permissions to the Oracle server, the command will return the contents of the log file without you having to RDP to the server  machine and browsing to the deeply buried file, to open it, which I think is pretty powerful and useful. Also since the  output is returned by a PowerShell CmdLet (Get-SCADbServerInfo), you can additionally pipe it to other PowerShell cmds like “where”, “last”, etc. and transform the data to your liking.

So let’s go thru. the steps on how to use this command using both Oracle and MSSQL server registrations. I’ll further break the filtering in steps, so you can see how I arrive at the end-result:

Step-1: Finding your SCA Server Names:

First step is to import the SCACommands module and then run the Get-SCAServer CmdLet to see your current server registrations. I’ll be using the ones, highlighted below:


Step-2: Initializing Get-SCADbServerInfo –

Next, you would initialize the Get-SCADbServerInfo using an SCA servername as the input. So I would do the following to init the MSSQL server first:

  $db = Get-SCADbServerInfo SMP7SQL

Once you have done this, you can call the $db.ReadLog() method and pipe it to Get-Member CmdLet to see the colns. returned in the output. As you can see in the image below, “Message”, is the field I’m interested in and since it’s of type String, it can be filtered using -like or -match PS operators.

AsI’m interested in getting any “failed” messages from the first 10 lines (ReadLog() for MSSQL is sorted by DateTime in descending format), I would need to:

1. Get first 10 lines :  $db.ReadLog() | select -first 10

2. Filter for “failed” string using the where operator. The current item in PS is denoted by $_ and $_.Message gets me the Message property : $db.ReadLog() | select -first 10 | where {$_.Message -like “*failed*”}

3.  Format to fit the screen & wrap any long line. So my final cmd line is: $db.ReadLog() | select -first 10 | where {$_.Message -like “*failed*”} | ft -auto -wrap

Reading MSSQL Log

Reading MSSQL Log

Moving onto Oracle, I first init the object for Oracle using:

$db = Get-SCADbServerInfo SMP1_ORA

I can also look at the no of lines in the alert log, by piping the output to the measure command. The output from ReadLog() for Oracle is individual lines of string and Oracle keeps appending messages to the alert log, so latest messages are towards the end of the log. In this case, I want to look at the last 2500 lines in the alert log and filter the ones that start with an Oracle error.  As you know, any Oracle error has the string “ORA-“ in it, so this is what I’ll use in my filter.

So my filter for Oracle is: $db.ReadLog() | select -Last 2500 | where {$_ -like “ORA-*”} | ft -AutoSize

Note how I use PS CmdLet to help me “filter” what I need, and that’s where PS really shines:


Reading Oracle Alert Log

Sending Email Notification:

We will make this a bit more interesting and send an email of important message using the Send-EmailMessage PS CmdLet. So this is what you will need to do:

1. Setup you smtpServer in a variable – I have set mine in $smtpServer in the PS window

2. Convert the output of the previous filters to a String using Out-String PS cmdlet since the -Body parameter for Send-EmailMessage accepts that and store that in a $messages variable

3. So my final filter string would be:  $messages = $db.ReadLog() | where {$_.Message -like “starting*”} | ft -AutoSize | Out-String

Sending Email

Sending Email

Thus you can you can see that, using SCA CmdLets in tandem with PS built-in ones, can help you come up with interesting ways to ease some your admin pains. Hopefully this will pique your interest in exploring PS more on your own. Feel free to leave a comment if you have any questions.

Read Full Post »

Since a few people have asked, here’s the list of  workflows that you need to execute to make sure that PowerShell CmdLets work on all machines

  1. Make sure the SCACommands folder with all the files reside under C:\Users\<your_login_id>\Documents\WindowsPowershell\Modules path
  2. Enable execution policy by running, Set-ExecutionPolicy RemoteSigned, and then accept the prompts
  3. Enable remoting by running, Enable-PSRemoting, and accept the prompts
  4. Check to make sure the Remote Registry Service is up and running on all machines
  5. Check to make sure the Windows Managed Instrumentation service is up and running in all machines

Hopefully these steps should allow you to invoke the SCA CmdLets successfully. Feel free to post a comment, if you run into any issues.

Read Full Post »

Older Posts »