Back to the main data prep VBA page
This is a sample script that I used in Excel VBA to extract data from IOSTAT output trapped at a regular interval (typically 15 seconds) to create a single row time series table for use in plotting. For those of you not familiar with IOSTAT output, it’s a text based ASCI table format that is human readable, but very difficult to use when conducting overlays with multiple data sources. The IOSTAT -x option provides great detail on service times that can be quite useful for debugging application performance, and a visualization of IOSTAT data is often critical when debugging IO issues.
Here’s a sample of IOSTAT output:
<<>>>
Here’s the script I use within Excel VBA for connecting to a source IOSTAT output file and extracting the data I need for creating time series plots:
Sub OSiostatClean()
'set variables
''''''''''''''
Dim rawLOG As String, rawLOGpath As String, splitArray() As String
Dim IOarray(100000, 18) As Variant
Dim rowCT As Double
pathFull = Sheets("input").Range("C13")
Set objrawLOG = CreateObject("Scripting.FileSystemObject")
rawLOGpath = objrawLOG.GetFile(pathFull).ParentFolder.path
srcFILE = Dir(objrawLOG.GetFile(pathFull).ParentFolder.path & "\")
'build array of log names up front, use those as source
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim logNameArr(100, 1) As Variant
rawLOG = Dir(rawLOGpath & "\")
logCT = 0: logSO = 0
Do While rawLOG <> ""
If rawLOG Like "OSiostat*.txt" Then
Set delFILE = CreateObject("Scripting.FileSystemObject")
delFILE.Deletefile rawLOGpath & "\" & rawLOG & "", True
End If
If rawLOG Like "RAWiostat*" Then
logCT = logCT + 1
logNameArr(logCT, 1) = rawLOG
End If
NextLogName:
rawLOG = Dir
Loop
'loop through directory, clean relevant files
'''''''''''''''''''''''''''''''''''''''''''''
For logProc = 1 To logCT
srcFILE = logNameArr(logProc, 1)
trimR = 10: rowCT = 0
hostName = Right(srcFILE, Len(srcFILE) - trimR)
hostName = Left(hostName, Len(hostName) - 4)
If hostName = "rage" Or hostName = "ragweed" Then devID = "fio*": driveCT = 2
If hostName = "zigzag" Or hostName = "dosxx" Then devID = "sd*": driveCT = 2
Erase IOarray()
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objrawLOG = objFSO.OpenTextFile(rawLOGpath & "\" & srcFILE, 1)
Do Until objrawLOG.AtEndOfStream
txnstring = objrawLOG.ReadLine
If txnstring Like "avg-cpu*" Then
rowCT = rowCT + 1
stampYear = Year(prevString)
stampMonth = Month(prevString)
If stampMonth < 10 Then stampMonth = "0" & stampMonth
stampDay = Day(prevString)
If stampDay < 10 Then stampDay = "0" & stampDay
stampHour = Hour(prevString)
If stampHour < 10 Then stampHour = "0" & stampHour
stampMin = Minute(prevString)
If stampMin < 10 Then stampMin = "0" & stampMin
stampSec = Second(prevString)
If stampSec < 10 Then stampSec = "0" & stampSec
IOarray(rowCT, 1) = stampYear & "/" & stampMonth & "/" & stampDay & " " & stampHour & ":" & stampMin & ":" & stampSec
End If
If prevString Like "avg-cpu*" Then
Do While InStr(txnstring, " ")
txnstring = Replace(txnstring, " ", " ")
Loop
splitArray() = Split(txnstring, " ")
IOarray(rowCT, 2) = splitArray(Val(1)) '%user
IOarray(rowCT, 3) = splitArray(Val(2)) '%nice
IOarray(rowCT, 4) = splitArray(Val(3)) '%system
IOarray(rowCT, 5) = splitArray(Val(4)) '%iowait
IOarray(rowCT, 6) = splitArray(Val(5)) '%steal
IOarray(rowCT, 7) = splitArray(Val(6)) '%idle
End If
If txnstring Like devID Then
Do While InStr(txnstring, " ")
txnstring = Replace(txnstring, " ", " ")
Loop
splitArray() = Split(txnstring, " ")
IOarray(rowCT, 8) = IOarray(rowCT, 8) + Val(splitArray(Val(1))) 'rrqm/s
IOarray(rowCT, 9) = IOarray(rowCT, 9) + Val(splitArray(Val(2))) 'wrqm/s
IOarray(rowCT, 10) = IOarray(rowCT, 10) + Val(splitArray(Val(3))) 'r/s
IOarray(rowCT, 11) = IOarray(rowCT, 11) + Val(splitArray(Val(4))) 'w/s
IOarray(rowCT, 12) = IOarray(rowCT, 12) + Val(splitArray(Val(5))) 'rsec/s
IOarray(rowCT, 13) = IOarray(rowCT, 13) + Val(splitArray(Val(6))) 'wsec/s
IOarray(rowCT, 14) = IOarray(rowCT, 14) + Val(splitArray(Val(7))) 'avgrq-sz
IOarray(rowCT, 15) = IOarray(rowCT, 15) + Val(splitArray(Val(8))) 'avgqu-sz
IOarray(rowCT, 16) = IOarray(rowCT, 16) + Val(splitArray(Val(9))) 'await
IOarray(rowCT, 17) = IOarray(rowCT, 17) + Val(splitArray(Val(10))) 'svctm
IOarray(rowCT, 18) = IOarray(rowCT, 18) + Val(splitArray(Val(11))) '%util
End If
prevString = txnstring
If rowCT = 100000 Then
tgtFile = "OSiostat_" & hostName & ".txt"
Set objFSOc = CreateObject("Scripting.FileSystemObject")
Set objFSOw = CreateObject("Scripting.FileSystemObject")
If Dir(rawLOGpath & "\" & tgtFile) <> "" Then
Set objWrite = objFSOw.OpenTextFile(rawLOGpath & "\" & tgtFile, 8)
ElseIf Dir(rawLOGpath & "\" & tgtFile) = "" Then
Set objWrite = objFSOc.CreateTextFile(rawLOGpath & "\" & tgtFile, 1)
objWrite.WriteLine "time,%user,'%nice,%system,%iowait,%steal,%idle,rrqm/s,wrqm/s,r/s,w/s,rsec/s,wsec/s,avgrq-sz,avgqu-sz,await,svctm,%util"
End If
For i = 1 To rowCT
For j = 1 To 18
If j < 14 Then
textOut = textOut & IOarray(i, j) & ","
Else
textOut = textOut & Val(IOarray(i, j)) / driveCT & ","
End If
Next j
objWrite.WriteLine textOut
textOut = ""
Next i
Erase IOarray()
rowCT = 0
End If
Loop
'final write
''''''''''''
tgtFile = "OSiostat_" & hostName & ".txt"
Set objFSOc = CreateObject("Scripting.FileSystemObject")
Set objFSOw = CreateObject("Scripting.FileSystemObject")
If Dir(rawLOGpath & "\" & tgtFile) <> "" Then
Set objWrite = objFSOw.OpenTextFile(rawLOGpath & "\" & tgtFile, 8)
ElseIf Dir(rawLOGpath & "\" & tgtFile) = "" Then
Set objWrite = objFSOc.CreateTextFile(rawLOGpath & "\" & tgtFile, 1)
objWrite.WriteLine "time,%user,'%nice,%system,%iowait,%steal,%idle,rrqm/s,wrqm/s,r/s,w/s,rsec/s,wsec/s,avgrq-sz,avgqu-sz,await,svctm,%util"
End If
For i = 1 To rowCT
For j = 1 To 18
If j < 14 Then
textOut = textOut & IOarray(i, j) & ","
Else
textOut = textOut & Val(IOarray(i, j)) / driveCT & ","
End If
Next j
objWrite.WriteLine textOut
textOut = ""
Next i
Next logProc
End Sub