I'm creating a pretty comprehensive login script to allow us to colelct data on the login process so we can identify key problems and so we can improve log in times.
At the moment it times most processes (drive mappings, printer allocation) and reports any errors or long process times using "send" to the helpdesk PCs. From this we can monitor trends and identify global issues pretty quickly. This is great but it does not give us any history.
So I've set up a database to store PC name, time, error code, user, duration of process etc.
If the "record to database" variable is set to "yes" then on each error an "Insert" command is added to the "sql script" variable. At the end of the login script if the "sql script" variable is not empty the whole lot is written to the DB in one db connection (see code below).
I echo the sql command to the screen so I can see what is being created. I then run a "shell" command to connect to the db and update it. However when run from the script this call to mysql fails. I can copy the sql command echoed to the screen into an identical command prompt and it works fine.
I'm completely stumped! What does Shell do differently?
Here's the code (variable definitions removed)
Remember the code works from the command line.
The command appears as:
\\LINCOLN\netlogon\mysql --host=172.16.1.1 --database=<dbname> --user=<username> --password=<password> < C:\log.sql
(I've changed the usernames etc)
Any suggestions appreciated.
ChrisJ
Code:
;build example errors
$SQLcommand = buildsql ($SQLCommand,"My Test1", "title1",10)
$SQLcommand = buildsql ($SQLCommand,"My Test2", "title2",20)
$SQLcommand = buildsql ($SQLCommand,"My Test3", "title3",30)
?"Updating db"
updatedb ($createsql);
function buildsql ($currentSQL,$message,$title,$duration)
;log errors to db?
if LCASE(left($logging,1))="y"
$mySQLcommand = "INSERT INTO `events`
(`username` , `pcname` , `time` , `eventtype`,`eventdesc` , `duration` )
VALUES
('"+@userid+"' , '"+@wksta+"' , '"+@year+"-"+@monthno+"-"+@mdayno+" "+@time+"',
'errortype', '"+$message+"', '"+$duration+"'); "
endif
$buildsql= $currentSQL + $mySQLcommand
endfunction
function updatedb ($sql)
;open new file for writing sql script to
$openfileresult=open (1,$SQLfile,5)
if $openfileresult=0
;write sql command to file
$writefileresult=WRITELINE (1,$SQLcommand)
$null = close (1)
;if file created successfully run it against the database
if $writefileresult = 0
? "Successfully created database instruction file"
;next three lines echo to screen for testing.
$sqlcommand="%logonserver%\netlogon\mysql
--host="+$dbhost+" --database="+$dbname+" --user="+$dbuser+"
--password="+$dbpassword+" < "+$SQLfile
? $sqlcommand
?
SHELL $sqlcommand
$sqlexitcode=@error
if $sqlexitcode=0
?"Reported errors successfully recorded in
database."
else
color $errorcolour
? "An error occurred when adding the error
codes to the database"
color $textcolour
$sendmessage="At "+@date+" " + @userid +
" on " + @wksta +
" : An error occurred when adding the error codes to the database"
$ErrorNotificationPClist=split($ErrorNotificationPC," ")
for each $PC in $ErrorNotificationPClist
$null=sendmessage ($PC,$sendmessage)
next
? "The technical team have been notified of this issue. "
endif
else
? "Error creating database instruction file"
Return
endif
;remove sql instruction file if it exists.
;remove old sqlfile
; if (exist ($SQLfile))
; del $SQLfile
; if @error = 0
; ? "Deleted existing database instruction file"
; else
; ? "Failed to delete existing instruction file"
; endif
; endif
else
;if file won't open report why
select
? "When trying to create database instruction file
the following error occurred:"
case $openfileresult=-1
? "File handle already in use"
case $openfileresult=-2
? "Invalid file handle specified"
case $openfileresult=-3
? "Invalid file name specified"
endselect
endif
endfunction
(code lines now reduced in length for better readability, I accept not everyone has dual screens! Apologies)
