Login Script - Saving to database help

ASP Free Forums Sponsor:

Ajax Application Generator Generate database and reporting .NET Web apps in minutes. Quickly create visually stunning, feature-rich apps that are easy to customize and ready to deploy. Download Now!

November 16th, 2007, 01:32 PM

jskinner123

Registered User

Join Date: Nov 2007

Posts: 1

Time spent in forums: 9 m 35 sec
Reputation Power: 0

Login Script - Saving to database help

Hello everyone. I currently have a login script that I will post at the end of this post, but it basiaclly writes the following information to a text file:

User Name
Computer Name
Date / Time
IP Address

Example output
Logon , 11/12/2007 3:37:15 PM , MRB*** , go**** , 10.192.***.**

I need to figure out how to write this information into a database, so that I can make a website for my boss to run reports against the information. If someone could show me how to write a variable to a database in a login script that would be great. Or even better modify my script to write the current outputs to a database instead of the .log file.

I also need to find a way to put the month, day, year, time in their own fields to allow for better sorting. Does anyone know how to grab these specific informations?

vb Code:

Original - vb Code

Option Explicit

Dim objFSO, objLogFile, objNetwork, objShell, strText, intAns
Dim intConstants, intTimeout, strTitle, intCount, blnLog
Dim strUserName, strComputerName, strIP, strShare, strLogFile

strShare = "G:\"
strLogFile = "userlogintime.log"
intTimeout = 100

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objNetwork = CreateObject("Wscript.Network")
Set objShell = CreateObject("Wscript.Shell")

strUserName = objNetwork.UserName
strComputerName = objNetwork.ComputerName
strIP = Join(GetIPAddresses())

' Log date/time, user name, computer name, and IP address.
If objFSO.FolderExists(strShare) Then
On Error Resume Next
Set objLogFile = objFSO.OpenTextFile(strShare & "\" _
& strLogFile, 8, True, 0)
If Err.Number = 0 Then
' Make three attempts to write to log file.
intCount = 1
blnLog = False
Do Until intCount = 3
objLogFile.WriteLine "Logon , " & Month & " , " _
& strComputerName & " , " & strUserName & " , " & strIP
If Err.Number = 0 Then
intCount = 3
blnLog = True
Else
Err.Clear
intCount = intCount + 1
If Wscript.Version > 5 Then
Wscript.Sleep 200
End If
End If
Loop
On Error GoTo 0
If blnLog = False Then
strTitle = "Logon Error"
strText = "Log cannot be written."
strText = strText & vbCrlf _
& "Another process may have log file open."
intConstants = vbOKOnly + vbExclamation
intAns = objShell.Popup(strText, intTimeout, strTitle, _
intConstants)
End If
objLogFile.Close
Else
On Error GoTo 0
strTitle = "Logon Error"
strText = "Log cannot be written."
strText = strText & vbCrLf & "User may not have permissions,"
strText = strText & vbCrLf & "or log folder may not be shared."
intConstants = vbOKOnly + vbExclamation
intAns = objShell.Popup(strText, intTimeout, strTitle, intConstants)
End If
Set objLogFile = Nothing
End If

' Clean up and exit.
Set objFSO = Nothing
Set objNetwork = Nothing
Set objShell = Nothing

Wscript.Quit

Function GetIPAddresses()
Dim sh, fso, env, workfile, ts, data, index, n, arIPAddress, parts

set sh = createobject("wscript.shell")
set fso = createobject("scripting.filesystemobject")
Set Env = sh.Environment("PROCESS")
if Env("OS") = "Windows_NT" then
workfile = Env("TEMP") & "\" & fso.gettempname
sh.run "%comspec% /c ipconfig >" & Chr(34) _
& workfile & Chr(34),0,true
else
'winipcfg in batch mode sends output to
'filename winipcfg.out
workfile = "winipcfg.out"
sh.run "winipcfg /batch" ,0,true
end if
set sh = nothing
set ts = fso.opentextfile(workfile)
data = split(ts.readall,vbcrlf)
ts.close
set ts = nothing
fso.deletefile workfile
set fso = nothing
arIPAddress = array()
index = -1
for n = 0 to ubound(data)
if instr(data(n),"IP Address") then
parts = split(data(n),":")
'if trim(parts(1)) <> "0.0.0.0" then
if instr(trim(parts(1)), "0.0.0.0") = 0 then
index = index + 1
ReDim Preserve arIPAddress(index)
arIPAddress(index)= trim(cstr(parts(1)))
end if
end if
next
GetIPAddresses = arIPAddress
End Function

Option Explicit
 
Dim objFSO, objLogFile, objNetwork, objShell, strText, intAns
Dim intConstants, intTimeout, strTitle, intCount, blnLog
Dim strUserName, strComputerName, strIP, strShare, strLogFile
 
strShare = "G:\"
strLogFile = "userlogintime.log"
intTimeout = 100
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objNetwork = CreateObject("Wscript.Network")
Set objShell = CreateObject("Wscript.Shell")
 
strUserName = objNetwork.UserName
strComputerName = objNetwork.ComputerName
strIP = Join(GetIPAddresses())
 
' Log date/time, user name, computer name, and IP address.
If objFSO.FolderExists(strShare) Then
On Error Resume Next
Set objLogFile = objFSO.OpenTextFile(strShare & "\" _
& strLogFile, 8, True, 0)
If Err.Number = 0 Then
' Make three attempts to write to log file.
intCount = 1
blnLog = False
Do Until intCount = 3
objLogFile.WriteLine "Logon , " & Month & " , " _
& strComputerName & " , " & strUserName & " , " & strIP
If Err.Number = 0 Then
intCount = 3
blnLog = True
Else
Err.Clear
intCount = intCount + 1
If Wscript.Version > 5 Then
Wscript.Sleep 200
End If
End If
Loop
On Error GoTo 0
If blnLog = False Then
strTitle = "Logon Error"
strText = "Log cannot be written."
strText = strText & vbCrlf _
& "Another process may have log file open."
intConstants = vbOKOnly + vbExclamation
intAns = objShell.Popup(strText, intTimeout, strTitle, _
intConstants)
End If
objLogFile.Close
Else
On Error GoTo 0
strTitle = "Logon Error"
strText = "Log cannot be written."
strText = strText & vbCrLf & "User may not have permissions,"
strText = strText & vbCrLf & "or log folder may not be shared."
intConstants = vbOKOnly + vbExclamation
intAns = objShell.Popup(strText, intTimeout, strTitle, intConstants)
End If
Set objLogFile = Nothing
End If
 
' Clean up and exit.
Set objFSO = Nothing
Set objNetwork = Nothing
Set objShell = Nothing
 
Wscript.Quit
 
Function GetIPAddresses()
Dim sh, fso, env, workfile, ts, data, index, n, arIPAddress, parts
 
set sh = createobject("wscript.shell")
set fso = createobject("scripting.filesystemobject")
Set Env = sh.Environment("PROCESS")
if Env("OS") = "Windows_NT" then
workfile = Env("TEMP") & "\" & fso.gettempname
sh.run "%comspec% /c ipconfig >" & Chr(34) _
& workfile & Chr(34),0,true
else
'winipcfg in batch mode sends output to
'filename winipcfg.out
workfile = "winipcfg.out"
sh.run "winipcfg /batch" ,0,true
end if
set sh = nothing
set ts = fso.opentextfile(workfile)
data = split(ts.readall,vbcrlf)
ts.close
set ts = nothing
fso.deletefile workfile
set fso = nothing
arIPAddress = array()
index = -1
for n = 0 to ubound(data)
if instr(data(n),"IP Address") then
parts = split(data(n),":")
'if trim(parts(1)) <> "0.0.0.0" then
if instr(trim(parts(1)), "0.0.0.0") = 0 then
index = index + 1
ReDim Preserve arIPAddress(index)
arIPAddress(index)= trim(cstr(parts(1)))
end if
end if
next
GetIPAddresses = arIPAddress
End Function

Moderator's Note: Syntax highlighting added by Nilpo.

Last edited by Nilpo : November 19th, 2007 at 01:44 AM.

November 19th, 2007, 01:55 AM

Nilpo

Shaolin Scripter!™

Join Date: Jun 2006

Location: Salem, OH

Posts: 675

Folding Points: 156663 Folding Title: Super Ultimate Folder - Level 1

Time spent in forums: 3 Days 18 h 35 m 6 sec
Reputation Power: 308

This can certainly be done, however, before going any further there are several things in your code that need to be fixed. I'm reworking your code now and I'll post back with explanations and your solution.

__________________

Click the image if at any point you don't like my decision.

Scripting problems? Windows questions? Ask the Windows Guru!

Subscribe to Nilpo's Live Article Feed

November 19th, 2007, 02:27 AM

Nilpo

Shaolin Scripter!™

Join Date: Jun 2006

Location: Salem, OH

Posts: 675

Time spent in forums: 3 Days 18 h 35 m 6 sec
Reputation Power: 308

To begin, you do not need to recreate the WshShell or FileSystemObject in your function. These have already been made available globally and the same objects should be reused. Also, in VBS there is no need to use the Set = Nothing method of unloading objects in a subroutine or method. Since these objects are only available within the current scope, they are automatically released when exiting a subroutine or function.

Second, the Join function you are using to create a string from your array elements requires a second parameter to indicate a delimiter. If you want to push them together without separating them in the string, you should pass an empty ( "" ) string as a parameter instead.

It looks to me like you're attempting to log the system's current date and time. You're using the Month function but this is only getting you part way to where you want to be. VBScript's Now function returns a string containing the current date and time. Use this instead.

Next, I see you are trying to disable internal error handling by using the On Error Resume Next and On Error Goto 0 statements. You're approach is fine, however your placement is not. You are disabling error handling before you enter and IF statement, and then re-enabling it part way through. You should enable and disable at the same block level. If you disable before an IF statement, then you re-enable after. Alternatively, keep it to within the IF or ELSE block. Since your usage only excludes the production of a message box, I see no reason not to do this before and after the entire IF statement.

On the same lines, you are using the Err object as a conditional device. While this is a great device for constructing statements that only execute upon successful completion of another task, your usage again is not correct...

...Remember that the Err object will be fired for EVERY script error, even those that are non-fatal and do not cause execution to stop. Since this value changes so frequently, you need to control it before polling for its value. To do this, make sure that it is cleared IMMEDIATELY before whatever line or lines you wish to monitor. The Err object provides a method for this.

There is also no real purpose for using the Clear method when you are not actively monitoring the contents of the Err object. I've removed an extraneous call to this method that you had in the Else block of one of your IF statements.

Finally, be careful with your use of DIM statements. These are used to create memory allocations for objects or variables. Using them inside of a function or subroutine will only make them available within their scope. Further, adding a DIM statement after an object is created will clear its contents. The use of Option Explicit further complicates the use of these statements so be careful to get them in the right place. If you're not sure, you can always put them at the very top of your script. When using Option Explicit, you also need to be sure not to miss ANY Dim statements.

vb Code:

Original - vb Code

Option Explicit

Dim objFSO, objLogFile, objNetwork, objShell, strText, intAns
Dim intConstants, intTimeout, strTitle, intCount, blnLog
Dim strUserName, strComputerName, strIP, strShare, strLogFile

strShare = "G:\"
strLogFile = "userlogintime.log"
intTimeout = 100

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objNetwork = CreateObject("Wscript.Network")
Set objShell = CreateObject("Wscript.Shell")

strUserName = objNetwork.UserName
strComputerName = objNetwork.ComputerName
strIP = Join(GetIPAddresses(), "")

' Log date/time, user name, computer name, and IP address.
If objFSO.FolderExists(strShare) Then
	On Error Resume Next
	
	Err.Clear

Set objLogFile = objFSO.OpenTextFile(strShare & "\" & strLogFile, 8, True, 0)

If Err.Number = 0 Then
		' Make three attempts to write to log file.
		intCount = 1
		blnLog = False
		Do Until intCount = 3
			Err.Clear
			objLogFile.WriteLine "Logon , " & Now & " , " & strComputerName & " , " & strUserName & " , " & strIP
			If Err.Number = 0 Then
				intCount = 3
				blnLog = True
			Else
				intCount = intCount + 1
				If WScript.Version > 5 Then
					WScript.Sleep 200
				End If
			End If
		Loop
		If blnLog = False Then
			strTitle = "Logon Error"
			strText = "Log cannot be written."
			strText = strText & VbCrLf & "Another process may have log file open."
			intConstants = vbOKOnly + vbExclamation
			intAns = objShell.Popup(strText, intTimeout, strTitle, intConstants)
		End If
		objLogFile.Close
	Else
		strTitle = "Logon Error"
		strText = "Log cannot be written."
		strText = strText & VbCrLf & "User may not have permissions,"
		strText = strText & VbCrLf & "or log folder may not be shared."
		intConstants = vbOKOnly + vbExclamation
		intAns = objShell.Popup(strText, intTimeout, strTitle, intConstants)
	End If
	On Error Goto 0
End If

' Clean up and exit.
Set objLogFile = Nothing
Set objFSO = Nothing
Set objNetwork = Nothing
Set objShell = Nothing

WScript.Quit

Function GetIPAddresses()
	Dim env, workfile, ts, data, arIPAddress, parts, index, n
	
	Set env = objShell.Environment("PROCESS")
	
	If env("OS") = "Windows_NT" Then
		workfile = env("TEMP") & "\" & objFSO.gettempname
		objShell.run "%comspec% /c ipconfig >" & Chr(34) & workfile & Chr(34),0,True
	Else
		'winipcfg in batch mode sends output to
		'filename winipcfg.out
		workfile = "winipcfg.out"
		objShell.run "winipcfg /batch" ,0,True
	End If
	
	Set ts = objFSO.opentextfile(workfile)
	
	data = Split(ts.readall,VbCrLf)
	ts.close
	
	objFSO.deletefile workfile
	
	arIPAddress = Array()
	index = -1
	For n = 0 To UBound(data)
		If InStr(data(n),"IP Address") Then
			parts = Split(data(n),":")
			'if trim(parts(1)) <> "0.0.0.0" then
			If InStr(Trim(parts(1)), "0.0.0.0") = 0 Then
				index = index + 1
				ReDim Preserve arIPAddress(index)
				arIPAddress(index)= Trim(CStr(parts(1)))
			End If
		End If
	Next
	
	GetIPAddresses = arIPAddress
End Function

Option Explicit
 
Dim objFSO, objLogFile, objNetwork, objShell, strText, intAns
Dim intConstants, intTimeout, strTitle, intCount, blnLog
Dim strUserName, strComputerName, strIP, strShare, strLogFile
 
strShare = "G:\"
strLogFile = "userlogintime.log"
intTimeout = 100
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objNetwork = CreateObject("Wscript.Network")
Set objShell = CreateObject("Wscript.Shell")
 
strUserName = objNetwork.UserName
strComputerName = objNetwork.ComputerName
strIP = Join(GetIPAddresses(), "")
 
' Log date/time, user name, computer name, and IP address.
If objFSO.FolderExists(strShare) Then
    On Error Resume Next
    
    Err.Clear
 
    Set objLogFile = objFSO.OpenTextFile(strShare & "\" & strLogFile, 8, True, 0)
 
    If Err.Number = 0 Then
        ' Make three attempts to write to log file.
        intCount = 1
        blnLog = False
        Do Until intCount = 3
            Err.Clear
            objLogFile.WriteLine "Logon , " & Now & " , " & strComputerName & " , " & strUserName & " , " & strIP
            If Err.Number = 0 Then
                intCount = 3
                blnLog = True
            Else
                intCount = intCount + 1
                If WScript.Version > 5 Then
                    WScript.Sleep 200
                End If
            End If
        Loop
        If blnLog = False Then
            strTitle = "Logon Error"
            strText = "Log cannot be written."
            strText = strText & VbCrLf & "Another process may have log file open."
            intConstants = vbOKOnly + vbExclamation
            intAns = objShell.Popup(strText, intTimeout, strTitle, intConstants)
        End If
        objLogFile.Close
    Else
        strTitle = "Logon Error"
        strText = "Log cannot be written."
        strText = strText & VbCrLf & "User may not have permissions,"
        strText = strText & VbCrLf & "or log folder may not be shared."
        intConstants = vbOKOnly + vbExclamation
        intAns = objShell.Popup(strText, intTimeout, strTitle, intConstants)
    End If
    On Error Goto 0
End If
 
' Clean up and exit.
Set objLogFile = Nothing
Set objFSO = Nothing
Set objNetwork = Nothing
Set objShell = Nothing
 
WScript.Quit
 
Function GetIPAddresses()
    Dim env, workfile, ts, data, arIPAddress, parts, index, n
    
    Set env = objShell.Environment("PROCESS")
    
    If env("OS") = "Windows_NT" Then
        workfile = env("TEMP") & "\" & objFSO.gettempname
        objShell.run "%comspec% /c ipconfig >" & Chr(34) & workfile & Chr(34),0,True
    Else
        'winipcfg in batch mode sends output to
        'filename winipcfg.out
        workfile = "winipcfg.out"
        objShell.run "winipcfg /batch" ,0,True
    End If