|
|
|||||||
I have this script that is driving me crazy! It basically pulls user login data and stores it in an existing SQL server database. The script works perfectly if I use Kixtarter 4.10 IDE with KIX 4.60 (F5, Normal Run) or if I run from command line , but if I use drag/drop to kix32.exe or wkix32.exe it throws an error for a specific line . Maybe this is a bug? The script is uses standard UDFs from this site Code below: Code: If @LOGONMODE = 1 ; Logon Mode is on $SO=SetConsole("HIDE") Break OFF Else ;Logon Mode is off $SO=SetConsole("SHOW") Break ON EndIf $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 ;Load necessary UDFs Call UDF_DBConnOpen.kix ;open DB Call UDF_DBExecuteSQL.kix ;Execute SQL in DB Call UDF_DBConnClose.kix ;close DB ; ****** VARIABLE DEFINITIONS *********** ; "Username,Date Time,IP,Computer Name, OS, Type,Domain Controller" $datastring = "'" + @TICKS + "','" + @USERID + "','" + @DATE + ' ' + @TIME + "','" + @IPADDRESS0 + "','" + @WKSTA + "','" + @PRODUCTTYPE + "','" +Split(@LDRIVE,'\')[2]+"'" $sqlserver = "server" $uid = "uid" $uidpwd = "password" $sqldb = "database" ; ****** VARIABLE DEFINITIONS END *********** $objConn = DBConnOpen('DRIVER={SQL Server};SERVER='+$sqlserver+';UID='+$uid+';PWD='+$uidpwd+';DATABASE='+$sqldb) If @ERROR=0 $retcode = DBExecuteSQL($objConn,"INSERT INTO UserLogs VALUES (" + $datastring + ")") If @ERROR <> 0 ? 'Error = '+@ERROR+' - '+@SERROR EndIf EndIf $retcode = DBConnClose($objConn) Sleep 10 Exit The error is : ERROR: expected ')'! Script: script.kix Line: 46 Where line 46 is Code: $retcode = DBExecuteSQL($objConn,"INSERT INTO UserLogs VALUES (" + $datastring + ")") |
||||||||
|
|
|||||||
so you have some lines before that you didn't paste cause exit is on line 46 in the pasted... just to be sure, check the error after call-lines to be certain the functions load correctly. |
||||||||
|
|
|||||||
Could just be missing quotes in your sql Code: $retcode = DBExecuteSQL($objConn,"INSERT INTO UserLogs VALUES ('" + $datastring + "')") |
||||||||
|
|
|||||||
What does your $DATASTRING look like? |
||||||||
|
|
|||||||
allen, I don't see any missing quotes on that line |
||||||||
|
|
|||||||
Look at his, and then look at mine... or better yet... see notes in UDF: Code: $retcode = DBExecuteSQL($objConn,"INSERT INTO Demo(Field1,Field2) VALUES('Value1','Value2')") |
||||||||
|
|
|||||||
well, I could be blind or something, but must say I can't see it. |
||||||||
|
|
|||||||
LOL... old fella can't see To bad this poor vision never shows up on the golf course. I'm pretty sure the Values in the sql need to be enclosed with single quotes... his is missing them... |
||||||||
|
|
|||||||
Am I missing something here?? Code: $ = SetOption('WrapAtEOL', 'On') $datastring = "'" + @TICKS + "','" + @USERID + "','" + @DATE + ' ' + @TIME + "','" + @IPADDRESS0 + "','" + @WKSTA + "','" + @PRODUCTTYPE + "','" +Split(@LDRIVE,'\')[2]+"'" $datastring ? The $datastring definition is from the original code, and everything is quoted. Since Brainstormer says it works from a command prompt or from the editor's debug function, I'd think that the code itself isn't 100% at fault. What is different when you "drag and drop"? Maybe I'm too "old school" but I never run scripts of any kind that way. Probably why I use .KXF for Kix GUI scripts, too. Anyway, if a script works when you are at a command prompt and type "kix32 myscript.kix", but it doesn't work when you drag the script onto the executable, what's different? When you "drag & drop", where are the following files: Kix32.exe? your script? the UDFs? If you say "desktop" to any of these, verify if the object on the desktop is a link or an actual file. Glenn |
||||||||
|
|
|||||||
I missed the top part of the code... only saw the error line in his first post. |
||||||||
|
|
|||||||
blind leading blind after reading glenn's post and revisiting the original I'm 100% sure it's the call's that don't work. using the dbconnOpen() doesn't error as it only has 1 argument, so it is treated as string. but when the dbExecuteSQL has comma in it, this error is thrown. in addition to Glenn's questions, try adding this on top of your script and see how it differs when using commandline versus drag/drop: Code: @scriptdir ? @curdir ? get $ |
||||||||
|
|
|||||||
Exactly.. My first though when reading this is that the UDFs were missing, but was stumped when it ran properly from the command line. Once I did some Drag & Drop testing, I realized that was indeed the problem. Let's let Brainstormer live up to his name and see if he can't solve the problem now with the clues presented... Glenn |
||||||||
|
|
|||||||
The mistery was solved, thanks to Code: @SCRIPTDIR ? @CURDIR ? Get $ It appears the @SCRIPTDIR shows the script path (V:), but @CURDIR shows my home path (Z:\) using drag & drop. Running it from command line they show (V:) and (V:\) respectively. Thank you all for the help, here is the final script if anyone is interested: Code: DIM $objConn, $retcode, $datastring DIM $sqlserver, $uid, $uidpwd, $sqldb, $sqltable DIM $userid, $logpath, $openlog, $ 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 $SO=SetOption("WrapAtEOL", "ON") $SO=SetOption("Explicit", "ON") $SO=SetOption("NoMacrosInStrings", "ON") $SO=SetOption("NoVarsInStrings", "ON") ; ****** VARIABLE DEFINITIONS *********** ; "Username,Date Time,IP,Computer Name, OS, Type,Domain Controller" $datastring = "'" + @TICKS + "','" + @USERID + "','" + @DATE + ' ' + @TIME + "','" + @IPADDRESS0 + "','" + @WKSTA + "','" + @PRODUCTTYPE + "','" +Split(@LDRIVE,'\')[2]+"'" $sqlserver = "server" $uid = "uid" $uidpwd = "pass" $sqldb = "db" $sqltable = "table" $userid = Join(Split(@USERID,"."),"-") $logpath = "%Temp%\log-" + $userid + "-track.log" ; ****** VARIABLE DEFINITIONS END *********** $ = RedirectOutput ($logpath,1) ;Load necessary UDFs Select Case Exist ("UDF_DBConnOpen.kix") AND Exist ("UDF_DBExecuteSQL.kix") AND Exist ("UDF_DBConnClose.kix") ? "SQL UDFs were found in same path as script" Call "UDF_DBConnOpen.kix" ;open DB Call "UDF_DBExecuteSQL.kix" ;Execute SQL in DB Call "UDF_DBConnClose.kix" ;close DB GoSub sqlinsert Case 1 ? "SQL UDFs not found, exiting script" EndSelect ; Close the log file $ = RedirectOutput ("") If $openlog = 1 Run "notepad.exe " + $logpath EndIf Exit :sqlinsert ; 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 + " VALUES (" + $datastring + ")") If @ERROR <> 0 ? 'Error = '+@ERROR+' - '+@SERROR EndIf EndIf $retcode = DBConnClose($objConn) Return |
||||||||
|
|
|||||||
My final question is, without creating 2 copies of the script with different file names, can Kix detect when the user logs off or on? I know you can name one copy login.kix and the other logoff.kix and insert the action by parsing the filename, but I am looking for a action perspective. |
||||||||
|
|
|||||||
You would have to pass the argument, parse it, and determine what to do. Back to the original problem - Why would you not simply embed your UDFs in the script, or specify a UNC path to them so they are always available? The point I was making in my earlier post is that your script was not finding the external UDFs.. the answers to those questions would have shown them to be in two different places. One method to resolve this is to use a "kixlib" share to host your UDFs. This folder is readable by all so any script can load UDFs. I would NOT hard-code this, but create a system environment var (via GPO) called KIXLIBPATH that would be referenced by your scripts. Thus, it could be easily relocated with no code changes. ie: Call "%KIXLIBPATH%\somefunc.udf" Personally, I never use external UDFs. If I were to update a UDF in a public library, I would have to locate and test every app that depended on that UDF. I use KGen to resolve dependencies and embed the UDFs as needed. This way, there's no dependency on external UDFs, and no potential for problems when updating UDFs. I can re-generate the scripts as time permits and test the effect and compatability of the new UDF without affecting production. KGen is part of the KixDev package on my web site. It also produces logs that can be scanned to identify which scripts rely on updated UDFs, and performs a sanity check as scripts are generated. Glenn |
||||||||
|
|
|||||||
Hi Glenn, thank you for your input on embedding the UDFs. I was trying to keep the script short while testing, and have already embedded them in the production version. Back to my second question on detecting logon/logoff events. Can you give me a specific example of what you mean? I am looking for some Windows event (without combing the event log) that defines a login status or a logout status, so I can pass this to the script. |
||||||||
|
|
|||||||
I assume you're running this as (part of) a logon and logoff script. Since you've defined two different script processes, just add $ACTION="logon" or "logoff" to the command line. Glenn |
||||||||
|
|
|||||||
Help me understand, in Group Policy Management I edit the GPO Browse to User Config->Windows Settings->Scripts (Logon/Logoff) Edit the Logon Properties Name: kix32.exe Parameters:script.kix $action="logon" Is that correct? |
||||||||
|
|
|||||||
Looks correct. "Same but different" for the logoff script. Glenn |
||||||||
|
|
|||||||
It is not workin, something to do with the Kix var conditions. 2 scenarios on execution a sample file test.kix: Code: KIX32.EXE test.kix $action=test File 1 Code: ? $action Get $ Output is "test" File 2 Code: ;Execution Conditions $SO=SetOption("WrapAtEOL", "ON") $SO=SetOption("Explicit", "ON") $SO=SetOption("NoMacrosInStrings", "ON") $SO=SetOption("NoVarsInStrings", "ON") DIM $action, $ ? $action Get $ Output is nothing, blank, so it can not be used on my script. |
||||||||
|
|
|||||||
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. |
||||||||
|
|
|||||||
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 |
||||||||
|
|
|||||||
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 |