; Declare global Variables
Dim $AudFile
Dim $Server
Dim $Database
Dim $OraclePN
Dim $UnixPID
Dim $DateTime
Dim $Action
Dim $DBUser
Dim $Priv
Dim $CliUser
Dim $Terminal
Dim $Status
Dim $LineStr
Dim $FileLines
Dim $CurrLine
Dim $RecordTime

; Open connection to the SQL database
$objConn = DBConnOpen("Driver={SQL Server};Server=Kaos;Database=OraAudit;Trusted_Connection=yes")

; Gather a list of all *.aud files
$FileList = DirPlus("c:\oraaudit\current\","/S /A-D /F aud")
$TotFiles = 0
For Each $FileElem in $FileList
	$TotFiles = $TotFiles+1
Next
; Just leave if there's nothing to do
If $TotFiles = 0
	Goto LEAVE
EndIf
$FileCount = 0
$TotFiles = $TotFiles-1

:NEXTFILE
$AudFile = $FileList[$FileCount]

; Open the current Oracle Audit file being processed
Open(1, $AudFile, 0)

:NEXTREC
; Read current line into string variable
$LineStr = ReadLine(1)
If @ERROR <> 0
	Goto CLOSEFILE
EndIf

; Decide whether to change session or action variables
	Select
		Case Left ("$LineStr", 10) = "Audit File"
			Gosub SETSESS
		Case Left ("$LineStr", 3) = "Mon" Or
				Left ("$LineStr", 3) = "Tue" Or
				Left ("$LineStr", 3) = "Wed" Or
				Left ("$LineStr", 3) = "Thu" Or
				Left ("$LineStr", 3) = "Fri" Or
				Left ("$LineStr", 3) = "Sat" Or
				Left ("$LineStr", 3) = "Sun"
			Gosub SETACTION
	EndSelect

:CLOSEFILE
; Closes the current Oracle Audit file being processed
Close(1)

; Move on to process next file in the list
If $FileCount < $TotFiles
	$FileCount = $FileCount+1
	Goto NEXTFILE
EndIf

:LEAVE
; Close connection to sql database
DBConnClose($objConn)

Quit

:SETSESS
; Subroutine to parse session-level lines and assign to variables
	Select
		Case Left ("$LineStr", 10) = "Audit File"
			$AudFile = Right ("$LineStr", -11)
		Case Left ("$LineStr", 10) = "Node name:"
			$Server = Right ("$LineStr", -11)
		Case Left ("$LineStr", 14) = "Instance name:"
			$Database = Right ("$LineStr", -15)
		Case Left ("$LineStr", 14) = "Oracle process"
			$OraclePN = Right ("$LineStr", -23)
		Case Left ("$LineStr", 12) = "Unix process"
			$UTempPID = Right ("$LineStr", -18)
				Gosub UNIXPID
					$LineStr = ReadLine(1)
					$LineStr = ReadLine(1)
				Goto SETACTION
	EndSelect
$LineStr = ReadLine(1)
	If @ERROR <> 0
		Goto CLOSEFILE
	EndIf
Goto SETSESS
Return

:UNIXPID
For $i = 1 to 7
	If SubStr("$UTempPID", $i,1) = ","
   	$UnixPID = Left("$UTempPID", $i-1)
	EndIf
Next
Return

:SETACTION
; Subroutine to parse action-level lines and assign to variables
	Select
		Case Left ("$LineStr", 3) = "Mon" Or
				Left ("$LineStr", 3) = "Tue" Or
				Left ("$LineStr", 3) = "Wed" Or
				Left ("$LineStr", 3) = "Thu" Or
				Left ("$LineStr", 3) = "Fri" Or
				Left ("$LineStr", 3) = "Sat" Or
				Left ("$LineStr", 3) = "Sun"
			$DateTime = SubStr("$LineStr", 5, 20)
		Case Left ("$LineStr", 8) = "ACTION :"
			Gosub ACTION	
		Case Left ("$LineStr", 14) = "DATABASE USER:"
			$DBUser = Right ("$LineStr", -15)
		Case Left ("$LineStr", 11) = "PRIVILEGE :"
			$Priv = Right ("$LineStr", -12)
		Case Left ("$LineStr", 12) = "CLIENT USER:"
			$CliUser = Right ("$LineStr", -13)
		Case Left ("$LineStr", 16) = "CLIENT TERMINAL:"
			$Terminal = Right ("$LineStr", -17)
		Case Left ("$LineStr", 7) = "STATUS:"
			$Status = Right ("$LineStr", -8)
			Goto WRITE
	EndSelect
	$LineStr = ReadLine(1)
	If @ERROR <> 0
		Goto CLOSEFILE
	EndIf
Goto SETACTION
Return

:ACTION
; Combines ACTION lines if it spans multiple lines
$LineStr = Right ("$LineStr", -9)
			$J = Len($LineStr)-2
			$LineStr = SubStr("$LineStr",2,$J)
				If InStr($LineStr, "'")
					Gosub QUOTES
				Else $Action = $LineStr
				EndIf
			$LineStr = ReadLine(1)
				If @ERROR<> 0
					Goto CLOSEFILE
				EndIf
				If Left ("$LineStr", 14) = "DATABASE USER:"
					$DBUser = Right ("$LineStr", -15)
					$LineStr = ReadLine(1)
						If @ERROR<> 0
							Goto CLOSEFILE
						EndIf
					Goto SETACTION
				EndIf
				While Left ("$LineStr", 14) <> "DATABASE USER:"
					If InStr("$LineStr", "'")
						Gosub QUOTES
					Else
						$Action = $Action + $LineStr
					EndIf
					$LineStr = ReadLine(1)
						If @ERROR<> 0
							Goto CLOSEFILE
						EndIf
				Loop
				Goto SETACTION

:QUOTES
; Change single'quotes to two single-quotes so SQL statement handles correctly
For $k = 1 to (Len($LineStr))
      If SubStr($LineStr, $k, 1) <> Chr(39)
         $Action=$Action+SubStr($LineStr, $k, 1)
      Else
         $Action=$Action+Chr(39)+Chr(39)
      EndIf
Next
Return

:WRITE
; Timestamp each record for ordering, and because nothing else is unique
			$RecMon = @MONTHNO
			$RecMDay = @MDAYNO
			$RecTime = @TIME
			$RecMsec = @MSECS
			$RecYr = @YEAR
	$RecordTime = Chr(39)+'$RecYr'+Chr(45)+'$RecMon'+Chr(45)+'$RecMDay'+Chr(32)+'$RecTime'+Chr(46)+'$RecMsec'+Chr(39)
			
;Write record to SQL database
$sql = "INSERT DBAAudit VALUES ("+Chr(39)+$AudFile+Chr(39)+","+Chr(39)+$Server+Chr(39)
       +","+Chr(39)+$Database+Chr(39)+","+$OraclePN+","+$UnixPID+","+Chr(39)+$DateTime+Chr(39)
       +","+Chr(39)+$Action+Chr(39)+","+$DBUser+","+Chr(39)+$Priv+Chr(39)+","+Chr(39)+$CliUser+Chr(39)
       +","+Chr(39)+$Terminal+Chr(39)+","+$Status+","+$RecordTime+")"
$retcode = DBExecuteSQL($objConn, $sql)

; skip the blank line after STATUS and then cleanup action variable
$LineStr = ReadLine(1)
If @ERROR <> 0
	Goto CLOSEFILE
EndIf
$action = ''

Goto NEXTREC

Function DBConnOpen($ConnDSN, optional $ConnTimeout, optional $CmdTimeout)
    Dim $objConn, $adStateOpen
    
    $adStateOpen=Val('&00000001')
    
    If VarType($ConnTimeout)
        $ConnTimeout=Val($ConnTimeout)
    Else
        $ConnTimeout=15
    EndIf
    
    If VarType($CmdTimeout)
        $CmdTimeout=Val($CmdTimeout)
    Else
        $CmdTimeout=30
    EndIf
    
    $ConnDSN=Trim($ConnDSN)
    If Not $ConnDSN
        Exit 87
    EndIf
    
    $objConn = CreateObject("ADODB.Connection")
    If @ERROR
        Exit @ERROR
    EndIf
    
    $objConn.ConnectionTimeout = $ConnTimeout
    If @ERROR
        Exit @ERROR
    EndIf
    
    $objConn.CommandTimeout = $CmdTimeout
    If @ERROR
        Exit @ERROR
    EndIf
    
    $objConn.Open($ConnDSN)
    If @ERROR
        Exit @ERROR
    EndIf
    
    If Not $objConn.State=$adStateOpen
        $objComm=''
        $DBConnOpen=''
        Exit @ERROR
    EndIf
    $DBConnOpen=$objConn
    
EndFunction

Function DirPlus($path,optional $Options, optional $f, optional $sfflag)
	If Not VarType($f)	Dim $f		EndIf
	If Not VarType($sfflag)	Dim $sfflag	EndIf

	Dim $file, $i, $temp, $item, $ex1, $mask,$mask1,$maskArray,$maskarray1,
	$ex2, $code, $CodeWeight, $targetWeight, $weight, $masktrue
	Dim $tarray[0]

	$ex1 = SetOption(Explicit,on)
	$ex2 = SetOption(NoVarsInStrings,on)
	$codeWeight = 0

	If Not Exist($path) 
		$temp = SetOption(Explicit,$ex1)
		$temp = SetOption(NoVarsInStrings,$ex2)
		Exit @ERROR
	EndIf

	If Not VarType($f)
		$f = CreateObject("Scripting.FileSystemObject").getfolder($path)
	EndIf
	If @ERROR 
		$temp = SetOption(Explicit,$ex1)
		$temp = SetOption(NoVarsInStrings,$ex2)
		Exit @ERROR
	EndIf

 	For Each $temp in Split($options,"/")
		$temp=Trim($temp)
		Select
		   Case Left($temp,1) = "s"
			If Not VarType($sfflag)
				If Val(Right($temp,-1)) = 0
					$sfflag = -1
				Else
					$sfflag = Val(Right($temp,-1))
				EndIf	
			EndIf
		   Case Left($temp,1) = "a"
			Select
			   Case Right($temp,-1)="d"
				$codeWeight = $codeWeight + 1
				$temp = "if $file.attributes & 16 " ;"if $file.type = 'File Folder' "
			   Case Right($temp,-1)="-d"
				$codeWeight = $codeWeight + 1
				$temp = "if ($file.attributes & 16)=0 " ;"if $file.type <> 'File Folder' "
			   Case Right($temp,-1)="s"
				$codeWeight = $codeWeight + 1
				$temp = "if $file.attributes & 4 "
			   Case Right($temp,-1)="-s"
				$codeWeight = $codeWeight + 1
				$temp = "if ($file.attributes & 4)=0 "
			   Case Right($temp,-1)="h"
				$codeWeight = $codeWeight + 1
				$temp = "if $file.attributes & 2 "
			   Case Right($temp,-1)="-h"
				$codeWeight = $codeWeight + 1
				$temp = "if ($file.attributes & 2)=0 "
			   Case Right($temp,-1)="r"
				$codeWeight = $codeWeight + 1
				$temp = "if $file.attributes & 1 "
			   Case Right($temp,-1)="-r"
				$codeWeight = $codeWeight + 1
				$temp = "if ($file.attributes & 1)=0 "
			   Case Right($temp,-1)="a"
				$codeWeight = $codeWeight + 1
				$temp = "if $file.attributes & 32 "
			   Case Right($temp,-1)="-a"
				$codeWeight = $codeWeight + 1
				$temp = "if ($file.attributes & 32)=0 "
			EndSelect
			$code = $temp + "$weight=$weight+1 endif" +@CRLF + $code

		   Case Left($temp,1) = "m"
			$maskarray = Split(Right($temp,-2),"|")
			$codeweight = $codeweight + 1
			$code = "$masktrue=0 for Each $mask in $maskarray if instr($file.name,$mask) $masktrue=1 " +
			"EndIf Next If $masktrue $weight=$weight+1 endif" + @CRLF +$code
		   Case Left($temp,1) = "f"
			$maskarray1 = Split(Right($temp,-2)," ")
			$codeweight = $codeweight + 1
			$code = "$masktrue=0 for Each $mask1 in $maskarray1 if substr($file.name,Instrrev($file.name,'.')+1)" +
			"=$mask1 $masktrue=1 EndIf Next If $masktrue $weight=$weight+1 endif" + @CRLF +$code

		EndSelect
	Next
	$code = "$weight = 0 $targetWeight = " + $codeweight + @CRLF + $code
	$code = $code + "if $weight = $targetweight Exit 1 endif"

	For Each $file in $f.subfolders
		If Execute($code)
			$tarray[$i] = $file
			$i = $i + 1
			ReDim preserve $tarray[$i]
		EndIf
		If $sfflag
			$temp = dirplus($file,$options,$file,$sfflag-1)
			For Each $item in $temp
				$tarray[$i] = $item
				$i = $i + 1
				ReDim preserve $tarray[$i]
			Next
		EndIf
	Next
	For Each $file in $f.files
		If Execute($code)
			$tarray[$i] = $file
			$i = $i + 1

			ReDim preserve $tarray[$i]
		EndIf
	Next

	If $i
		ReDim preserve $tarray[$i-1]
		$i=0
	Else
		$tarray = 0
	EndIf

	$dirplus = $tarray
	$temp = SetOption(Explicit,$ex1)
	$temp = SetOption(NoVarsInStrings,$ex2)
	Exit @ERROR
EndFunction

Function ArrayDim($array, optional $dims)
  Dim $a

  If VarType($array) & 8192
    $a=0
    Do
      $a=$a+1
    Until UBound($array,$a)=-1
    $arraydim=$a-1
    If Val($dims)
      Dim $b[$arraydim-1]
      For $a=1 to $arraydim
        $b[$a-1]=UBound($array,$a)
      Next
      $arraydim=$b
    EndIf
  Else
    $arraydim=-1
  EndIf
EndFunction

Function PadStr($Input, $Pad, $Length, optional $PadSide)
   Dim $i, $x
   $PadStr = ""
   $Input = "" + $Input
   $Pad = "" + $Pad
   $Length = 0 + $Length
   If $PadSide="" Or Len($PadSide)>1 Or InStr("LR",$PadSide)= 0
      $PadSide = "L"
   EndIf

   $x = Len($Input)

   For $i=$x to $Length - 1 Step Len($Pad)
      If $PadSide = "L"
         $Input = $Pad + $Input
      Else
         $Input = $Input + $Pad
      EndIf
   Next
   If $PadSide = "L"
      $Input = Right($Input, $Length)
   Else
      $Input = Left($Input, $Length)
   EndIf
   $PadStr = $Input
   Exit 0
EndFunction

Function DBExecuteSQL($objConn, $sql, optional $cmdType)
  Dim $cmdCommand, $rsRecordset
  Dim $adCmdUnspecified, $adCmdText, $adCmdTable, $adCmdStoredProc, $adCmdUnknown, $adCmdFile, $adCmdTableDirect

  $adCmdUnspecified = -1
  $adCmdText        = 1
  $adCmdTable       = 2
  $adCmdStoredProc  = 4
  $adCmdUnknown     = 8
  $adCmdFile        = 256
  $adCmdTableDirect = 512

  $cmdType=IIf(VarType($cmdType),Val($cmdType),$adCmdText)

  If VarType($objConn)<>9 Or $sql=''
    Exit 87
  EndIf

  $cmdCommand = CreateObject('ADODB.Command')
  If @ERROR
    Exit @ERROR
  EndIf

  $cmdCommand.ActiveConnection = $objConn
  If @ERROR
    Exit @ERROR
  EndIf

  $cmdCommand.CommandType = $cmdType
  If @ERROR
    Exit @ERROR
  EndIf

  $cmdCommand.CommandText = $sql
  If @ERROR
    Exit @ERROR
  EndIf

  $rsRecordset=$cmdCommand.Execute()
  $rsRecordset=''
  $cmdCommand=''
  $DBExecuteSQL=@ERROR
  Exit @ERROR
EndFunction

Function DBConnClose($objConn)
  Dim $adStateOpen

  $adStateOpen = 1

  If VarType($objConn)=9
    If $objConn.State = $adStateOpen
      $objConn.Close()
      If @ERROR
        Exit @ERROR
      EndIf
    EndIf
    $objConn=''
  Else
    Exit 87
  EndIf

  $DBConnClose=0
  Exit 0
EndFunction
 

SQL command here
sleep 2
next SQL command

sleep .01

Quote:

---

Two things:

1) The sleep command I described above should have said "sleep 0.01" not "sleep .01". The latter is not correct syntax and won't work.

2) I noticed that if I run the script nice and slow the records are written to the sql table in the exact order they are sent. As I gradually speed up the script (by decreasing the sleep time) the record writes to the table become more and more disordered, and at some point I just start losing records. I'm thinking now it's a SQL issue--at full speed the script writes about 150 records/sec, which is nothing. I'll have our DBAs look into it. If they find anything I'll post it for other's benefit. Thanks to all for your input.

---