Back to the main data prep VBA page
This is a script I use for extracting data of interest from NMON output. Often I need t0 extract data from a variety of tools a customer is comfortable using, and I often encounter NMON. This basic script helps me extract the data I care about into a single row for use in a time series plot.
Sub OSnmonClean()
'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 & "\")
rowCT = 0: fioCT = 0
'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 "OSnmon*.txt" Then
Set delFILE = CreateObject("Scripting.FileSystemObject")
delFILE.Deletefile rawLOGpath & "\" & rawLOG & "", True
End If
If rawLOG Like "RAWnmon*" Then
logCT = logCT + 1
logNameArr(logCT, 1) = rawLOG
End If
NextLogName:
rawLOG = Dir
Loop
'loop through directory, clean relevant files
'''''''''''''''''''''''''''''''''''''''''''''
On Error Resume Next
For logProc = 1 To logCT
srcFILE = logNameArr(logProc, 1)
trimR = 8
hostName = Right(srcFILE, Len(srcFILE) - trimR)
hostName = Left(hostName, Len(hostName) - 4)
Set objFSOc = CreateObject("Scripting.FileSystemObject")
Set objWrite = objFSOc.CreateTextFile(rawLOGpath & "\OSnmon_" & hostName & ".txt", 1)
objWrite.WriteLine "time,User%,Sys%,Wait%,Idle%,Busy,CPUs,memtotal,hightotal,lowtotal,swaptotal,memfree,highfree,lowfree,swapfree,memshared,cached,active,bigfree,buffers,swapcached,inactive,"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objrawLOG = objFSO.OpenTextFile(rawLOGpath & "\" & srcFILE, 1)
Do Until objrawLOG.AtEndOfStream
txnstring = objrawLOG.ReadLine
'ZZZZ,T0055,08:40:28,15-FEB-2013
If txnstring Like "ZZZZ*" Then
objWrite.WriteLine outString
startData = 0: N = 2
Do
startData = InStr(startData + 1, txnstring, ",")
N = N - 1
Loop Until startData = 0 Or N = 0
outString = Right(txnstring, Len(txnstring) - startData) & ","
splitArray() = Split(outString, ",")
dateStamp = splitArray(Val(1))
timeStamp = splitArray(Val(0))
stampYear = Year(dateStamp)
stampMonth = Month(dateStamp)
If stampMonth < 10 Then stampMonth = "0" & stampMonth
stampDay = Day(dateStamp)
If stampDay < 10 Then stampDay = "0" & stampDay
stampHour = Hour(timeStamp)
If stampHour < 10 Then stampHour = "0" & stampHour
stampMin = Minute(timeStamp)
If stampMin < 10 Then stampMin = "0" & stampMin
stampSec = Second(timeStamp)
If stampSec < 10 Then stampSec = "0" & stampSec
outString = stampYear & "/" & stampMonth & "/" & stampDay & " " & stampHour & ":" & stampMin & ":" & stampSec & ","
End If
'CPU_ALL,CPU Total zigzag,User%,Sys%,Wait%,Idle%,Busy,CPUs
If txnstring Like "CPU_ALL*" Then
startData = 0: N = 2
Do
startData = InStr(startData + 1, txnstring, ",")
N = N - 1
Loop Until startData = 0 Or N = 0
outString = outString & Right(txnstring, Len(txnstring) - startData) & ","
End If
'MEM,Memory MB zigzag,memtotal,hightotal,lowtotal,swaptotal,memfree,highfree,lowfree,swapfree,memshared,cached,active,bigfree,buffers,swapcached,inactive
If txnstring Like "MEM,*" Then
startData = 0: N = 2
Do
startData = InStr(startData + 1, txnstring, ",")
N = N - 1
Loop Until startData = 0 Or N = 0
outString = outString & Right(txnstring, Len(txnstring) - startData) & ","
End If
Loop
Next logProc
End Sub