Page 2 of 2 <12
Topic Options
#193169 - 2009-03-25 08:57 PM Re: User logon tracking script - Need help! [Re: Brainstormer]
Allen Administrator Offline
KiX Supporter
*****

Registered: 2003-04-19
Posts: 4545
Loc: USA
You don't have to Dim $action... Kixtart did it for you when you added it to the command line. You might also put quotes around your values - "test", just to be sure.

Edited by Allen (2009-03-25 08:58 PM)

Top
#193170 - 2009-03-25 10:26 PM Re: User logon tracking script - Need help! [Re: Allen]
Glenn Barnas Administrator Offline
KiX Supporter
*****

Registered: 2003-01-28
Posts: 4396
Loc: New Jersey
Zactly! When you DIM it, you Explicitly declare it as a local var. Arguments on the command line are implicitly declared as globals, but because the declaration is implicit, not explicit, you can override it with a Dim or Global inside your script.

This creates a point of conflict.. if you enable Explicit mode and reference $ACTION, and do not pass it on the command line, you'll get an Undeclared Variable error. If you declare it and it is passed on the command line, your declaration will destroy the data from the command line.

To be safe, you need something like:
 Code:
If Not IsDeclared($ACTION)
  Global $ACTION
  $ACTION = "default value"
EndIf

This is placed near the top of your code, and will insure that the var is declared (and optionally contains a default value). This satisfies the Explicit option fairly easily.

Glenn
_________________________
Actually I am a Rocket Scientist! \:D

Top
#193190 - 2009-03-26 06:18 PM Re: User logon tracking script - Need help! [Re: Glenn Barnas]
Brainstormer Offline
Fresh Scripter

Registered: 2007-05-16
Posts: 23
Loc: USA
That worked, thank you a lot. Now I have decent user tracking. So now, for anyone else interested here is the code, invoked using

kix32.exe scriptname.kix $action="<value>"
where $action values should be either "Logon","Logoff" ("Unknown" value set inside the script)


 Code:
;Execution Conditions 
$SO=SetOption("WrapAtEOL", "ON")
$SO=SetOption("Explicit", "ON")
$SO=SetOption("NoMacrosInStrings", "ON")
$SO=SetOption("NoVarsInStrings", "ON")

DIM $objConn, $retcode, $datastring
DIM $sqlserver, $uid, $uidpwd, $sqldb, $sqltable, $tablecolumns
DIM $userid, $logpath, $openlog, $, $defaultip, $Oc1, $Oc2, $Oc3, $Oc4

If @LOGONMODE = 1
	; Logon Mode is on
	$SO=SetConsole("HIDE")
	Break OFF
	? "Logon Mode is on"
	; Value here should never be 1.
	$openlog = 0
Else
	;Logon Mode is off
	$SO=SetConsole("SHOW")
	Break ON
	? "Logon Mode is off"
	; Value 1 opens log at end of execution, any other does not.
	$openlog = 0
EndIf

; ****** VARIABLE DEFINITIONS ***********

$sqlserver = "sqlserver"
$uid = "uid"
$uidpwd = "pass"
$sqldb = "db_name"
$sqltable = "table_name"
$userid = Join(Split(@USERID,"."),"-")
$logpath = "%Temp%\user-" + $userid + "-track.log"

; $action values should be either "Logon","Logoff" or "Unknown"
If Not IsDeclared($action)
  Global $action
  $action = "Unknown"
EndIf


; ****** VARIABLE DEFINITIONS  END ***********

;Grab the default IP
GoSub ip

$tablecolumns = "SysUptime,Username,Priv,Action,Date,IP,computer,OS,DC"

; SQL data string
$datastring =  "'" + @TICKS + "','" + @USERID + "','" + @PRIV + "','" + $action + "','" + @DATE + " " + @TIME + "','" + $defaultip + "','" + @WKSTA + "','" + @PRODUCTTYPE + "','" +Split(@LDRIVE,'\')[2]+"'"

;Create a logfile to track issues
;$ = RedirectOutput ($logpath,1)

; SQL command execution
$objConn = DBConnOpen('DRIVER={SQL Server};SERVER='+$sqlserver+';UID='+$uid+';PWD='+$uidpwd+';DATABASE='+$sqldb)
If @ERROR=0
	$retcode = DBExecuteSQL($objConn,"INSERT INTO " + $sqltable + " (" + $tablecolumns + ") VALUES (" + $datastring + ")")
	If @ERROR <> 0
		? 'Error = '+@ERROR+' - '+@SERROR
	EndIf
EndIf
$retcode = DBConnClose($objConn)

; Close the log file
;$ = RedirectOutput ("")
;
;If $openlog = 1
;	Run "notepad.exe " + $logpath
;EndIf

; Exit the main script

Exit


:ip
;------------------------------------------------------------------------------;
;  Determine Primary NIC IP Information
;------------------------------------------------------------------------------;

; In case the IP is in company's range (10.0.0.0) and there are multiple adapters set it to the correct one, otherwise use IP0
Select
Case LTrim(Split(@IPADDRESS0,'.')[0])="10"
	$defaultip=@IPADDRESS0
Case LTrim(Split(@IPADDRESS1,'.')[0])="10"
	$defaultip=@IPADDRESS1
Case LTrim(Split(@IPADDRESS2,'.')[0])="10"
	$defaultip=@IPADDRESS2
Case LTrim(Split(@IPADDRESS3,'.')[0])="10"
	$defaultip=@IPADDRESS3	
Case 1
	; Using IP0 as default
	$defaultip=@IPADDRESS0
EndSelect

; Join the octets to complete the IP address
$Oc1=LTrim(Split($defaultip,'.')[0])
$Oc2=LTrim(Split($defaultip,'.')[1])
$Oc3=LTrim(Split($defaultip,'.')[2])
$Oc4=LTrim(Split($defaultip,'.')[3])
$defaultip=$Oc1+'.'+$Oc2+'.'+$Oc3+'.'+$Oc4

Return

; ********************* UDF Functions below this point ***************************

;FUNCTION      DBConnOpen()
;
;ACTION        Open a connection to a database using ADODB
;
;AUTHOR        Jens Meyer (sealeopard@usa.net)
;
;VERSION       1.11 (minor code changes)
;              1.1
;
;DATE CREATED  2002/09/05
;
;DATE MODIFIED 2004/03/07
;
;KIXTART       4.20+
;
;SYNTAX        RETCODE = DBCONNOPEN(DSN [,CONNTIMEOUT, CMDTIMEOUT])
;
;PARAMETERS    DSN
;              Database connection string (ODBC format)
;
;              CONNTIMEOUT
;              Optional integer denoting the time in seconds until a connection times out, defaults to 15 seconds
;
;              CMDTIMEOUT
;              Optional integer denoting the time in seconds until a command times out, defaults to 30 seconds
;
;RETURN        Connection object if successful, otherwise empty string
;
;REMARKS       See also DBConnClose(), DBRecordsetOpen(), DBRecordsetClose(), DBGetRecordset(), DBCommand(), DBExecuteSQL()
;
;              Example connection strings (requires appropriate ODBC drivers)::
;
;              Microsoft Access        : "DRIVER={Microsoft Access Driver (*.mdb)}; UID=; PWD=; DBQ=database.mdb"
;              Microsoft SQL Server    : "DRIVER={SQL Server};SERVER=servername;UID=user;PWD=password;DATABASE=mydatabase"
;              Microsoft Visual FoxPro : "DRIVER={Microsoft Visual FoxPro Driver}; UID=; PWD=; DBQ=database.dbc"
;              Oracle                  : "DSN=test;UID=username;PWD=password"
;              For other connection strings please see http://www.connectionstrings.com
;
;DEPENDENCIES  none
;
;EXAMPLE       $objConn = DBConnOpen('DRIVER={Microsoft Access Driver (*.mdb)}; UID=; PWD=; DBQ=database.mdb')
;
;KIXTART BBS   http://www.kixtart.org/ubbthreads/showflat.php?Cat=&Number=82470
;
Function DBConnOpen($ConnDSN, optional $ConnTimeout, optional $CmdTimeout)
  Dim $objConn

  $ConnTimeout=iif(vartype($ConnTimeout),val($ConnTimeout),15)
  $CmdTimeout=iif(vartype($CmdTimeout),val($CmdTimeout),30)

  $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=1
    $objConn=''
    $DBConnOpen=''
    exit @ERROR
  endif
  $DBConnOpen=$objConn
  exit 0
EndFunction


;FUNCTION      DBExecuteSQL()
;
;ACTION        Executes a SQL command on a database
;
;AUTHOR        Jens Meyer (sealeopard@usa.net)
;
;VERSION       1.11 (minor code changes)
;              1.1
;
;DATE CREATED  2002/09/05
;
;DATE MODIFIED 2004/03/07
;
;KIXTART       4.20+
;
;SYNTAX        RETCODE = DBEXECUTESQL(CONNECTION, SQL [,CMDTYPE])
;
;PARAMETERS    CONNECTION
;              open connection object to a data source from DBConnOpen()
;
;              SQL
;              SQL command to be executed
;
;              CMDTYPE
;              Optional integer defining the command type (e.g. SQL statement, stored procedure)
;
;RETURN        0 if successful, otherwise error code
;
;REMARKS       See also DBConnOpen(), DBConnClose(), DBRecordsetOpen(), DBRecordsetClose(), DBGetRecordset(), DBCommand()
;
;              This routine does not return a recordset as the result of the SQL
;              statement. Therefore, it should only be used with SQL commands
;              like INSERT INTO, UPDATE. For SELECT statements one should rather
;              use DBGetRecordset() or DBComand().
;
;DEPENDENCIES  none
;
;EXAMPLE       $objConn = DBConnOpen('DRIVER={Microsoft Access Driver (*.mdb)}; UID=; PWD=; DBQ=database.mdb')
;              $retcode = DBExecuteSQL($objConn,"INSERT INTO Demo(Field1,Field2) VALUES('Value1','Value2')")
;              $retcode = DBConnClose($objConn)
;
;KIXTART BBS   http://www.kixtart.org/ubbthreads/showflat.php?Cat=&Number=82477
;
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()
;
;ACTION        Closes a connection to a database that has previously been opened with DBConnOpen()
;
;AUTHOR        Jens Meyer (sealeopard@usa.net)
;
;VERSION       1.11 (minor code changes)
;              1.1
;
;DATE CREATED  2002/09/05
;
;DATE MODIFIED 2004/03/07
;
;KIXTART       4.20+
;
;SYNTAX        RETCODE = DBCONNCLOSE(CONNECTION)
;
;PARAMETERS    DSN
;              Database connection string (ODBC format)
;
;
;RETURN        0 if successful, otherwise error code
;
;REMARKS       See also DBConnOpen(), DBRecordsetOpen(), DBRecordsetClose(), DBGetRecordset(), DBCommand(), DBExecuteSQL()
;
;              Example connection strings (requires appropriate ODBC drivers)::
;
;              Microsoft Access        : "DRIVER={Microsoft Access Driver (*.mdb)}; UID=; PWD=; DBQ=database.mdb"
;              Microsoft SQL Server    : "DRIVER={SQL Server};SERVER=servername;UID=user;PWD=password;DATABASE=mydatabase"
;              Microsoft Visual FoxPro : "DRIVER={Microsoft Visual FoxPro Driver}; UID=; PWD=; DBQ=database.dbc"
;              Oracle                  : "DSN=test;UID=username;PWD=password"
;              For other connection strings please see http://www.connectionstrings.com
;
;DEPENDENCIES  none
;
;EXAMPLE       $retcode = DBConnClose($objConn)
;
;KIXTART BBS   http://www.kixtart.org/ubbthreads/showflat.php?Cat=&Number=82470
;
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=@ERROR
EndFunction


Top
Page 2 of 2 <12


Moderator:  Glenn Barnas, NTDOC, Arend_, Jochen, Radimus, Allen, ShaneEP, Ruud van Velsen, Mart 
Hop to:
Shout Box

Who's Online
0 registered and 346 anonymous users online.
Newest Members
gespanntleuchten, DaveatAdvanced, Paulo_Alves, UsTaaa, xxJJxx
17864 Registered Users

Generated in 0.04 seconds in which 0.017 seconds were spent on a total of 13 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org