Page 1 of 2 12>
Topic Options
#193128 - 2009-03-24 09:09 PM User logon tracking script - Need help!
Brainstormer Offline
Fresh Scripter

Registered: 2007-05-16
Posts: 23
Loc: Boston Area, MA
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 + ")")

Top
#193129 - 2009-03-24 09:49 PM Re: User logon tracking script - Need help! [Re: Brainstormer]
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22344
Loc: OK
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.
_________________________
!

download KiXnet

Top
#193130 - 2009-03-24 09:59 PM Re: User logon tracking script - Need help! [Re: Lonkero]
Allen Administrator Online   shocked
KiX Supporter
*****

Registered: 2003-04-19
Posts: 4520
Loc: USA
Could just be missing quotes in your sql
 Code:
$retcode = DBExecuteSQL($objConn,"INSERT INTO UserLogs VALUES ('" + $datastring + "')")

Top
#193131 - 2009-03-24 10:00 PM Re: User logon tracking script - Need help! [Re: Allen]
Allen Administrator Online   shocked
KiX Supporter
*****

Registered: 2003-04-19
Posts: 4520
Loc: USA
What does your $DATASTRING look like?
Top
#193132 - 2009-03-24 10:11 PM Re: User logon tracking script - Need help! [Re: Allen]
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22344
Loc: OK
allen, I don't see any missing quotes on that line \:\)
_________________________
!

download KiXnet

Top
#193133 - 2009-03-24 10:53 PM Re: User logon tracking script - Need help! [Re: Lonkero]
Allen Administrator Online   shocked
KiX Supporter
*****

Registered: 2003-04-19
Posts: 4520
Loc: USA
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')")

Top
#193134 - 2009-03-24 11:35 PM Re: User logon tracking script - Need help! [Re: Allen]
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22344
Loc: OK
well, I could be blind or something, but must say I can't see it.
_________________________
!

download KiXnet

Top
#193135 - 2009-03-24 11:41 PM Re: User logon tracking script - Need help! [Re: Lonkero]
Allen Administrator Online   shocked
KiX Supporter
*****

Registered: 2003-04-19
Posts: 4520
Loc: USA
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...

Top
#193140 - 2009-03-25 01:33 AM Re: User logon tracking script - Need help! [Re: Allen]
Glenn Barnas Administrator Offline
KiX Supporter
*****

Registered: 2003-01-28
Posts: 4350
Loc: New Jersey
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


_________________________
Actually I am a Rocket Scientist! \:D

Top
#193141 - 2009-03-25 01:47 AM Re: User logon tracking script - Need help! [Re: Glenn Barnas]
Allen Administrator Online   shocked
KiX Supporter
*****

Registered: 2003-04-19
Posts: 4520
Loc: USA
I missed the top part of the code... only saw the error line in his first post.
Top
#193143 - 2009-03-25 07:16 AM Re: User logon tracking script - Need help! [Re: Allen]
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22344
Loc: OK
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 $
_________________________
!

download KiXnet

Top
#193153 - 2009-03-25 11:56 AM Re: User logon tracking script - Need help! [Re: Lonkero]
Glenn Barnas Administrator Offline
KiX Supporter
*****

Registered: 2003-01-28
Posts: 4350
Loc: New Jersey
Exactly.. \:D

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
_________________________
Actually I am a Rocket Scientist! \:D

Top
#193157 - 2009-03-25 03:34 PM Re: User logon tracking script - Need help! [Re: Glenn Barnas]
Brainstormer Offline
Fresh Scripter

Registered: 2007-05-16
Posts: 23
Loc: Boston Area, MA
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

Top
#193158 - 2009-03-25 03:39 PM Re: User logon tracking script - Need help! [Re: Brainstormer]
Brainstormer Offline
Fresh Scripter

Registered: 2007-05-16
Posts: 23
Loc: Boston Area, MA
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.

Top
#193159 - 2009-03-25 04:13 PM Re: User logon tracking script - Need help! [Re: Brainstormer]
Glenn Barnas Administrator Offline
KiX Supporter
*****

Registered: 2003-01-28
Posts: 4350
Loc: New Jersey
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
_________________________
Actually I am a Rocket Scientist! \:D

Top
#193160 - 2009-03-25 04:41 PM Re: User logon tracking script - Need help! [Re: Glenn Barnas]
Brainstormer Offline
Fresh Scripter

Registered: 2007-05-16
Posts: 23
Loc: Boston Area, MA
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.

Top
#193163 - 2009-03-25 05:35 PM Re: User logon tracking script - Need help! [Re: Brainstormer]
Glenn Barnas Administrator Offline
KiX Supporter
*****

Registered: 2003-01-28
Posts: 4350
Loc: New Jersey
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
_________________________
Actually I am a Rocket Scientist! \:D

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

Registered: 2007-05-16
Posts: 23
Loc: Boston Area, MA
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?

Top
#193166 - 2009-03-25 07:32 PM Re: User logon tracking script - Need help! [Re: Brainstormer]
Glenn Barnas Administrator Offline
KiX Supporter
*****

Registered: 2003-01-28
Posts: 4350
Loc: New Jersey
Looks correct. "Same but different" for the logoff script.

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

Top
#193168 - 2009-03-25 08:53 PM Re: User logon tracking script - Need help! [Re: Glenn Barnas]
Brainstormer Offline
Fresh Scripter

Registered: 2007-05-16
Posts: 23
Loc: Boston Area, MA
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.

Top
Page 1 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 240 anonymous users online.
Newest Members
Praveer, Ollero, rayyo, Foxlicht, DonChild
17742 Registered Users

Generated in 0.054 seconds in which 0.018 seconds were spent on a total of 14 queries. Zlib compression enabled.

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