asked on

Windows Logon script VBS SQL

Currently we use a VBS windows logon and logoff script into a text file. I would like to change the script to write to a SQL database with Windows Authentication. I am not a programmer and any help would be grateful.
I have been trying to test the following code with no luck.

Function WriteToDatabase(sUserName)
Dim mConnection
Set mConnection = CreateObject("ADODB.Connection")
mConnection.Open "Provider=SQLOLEDB.1;Data Source=EHR\EHR;Initial
Catalog=UserData"
mConnection.Execute "INSERT INTO tbl_user_log (user_name,
login_date) VALUES ('" & sUserName" & ", GetUTCDate())"
Set mConnection = Nothing
End Function

Const ForAppending = 8
Dim objADSysInfo : Set objADSysInfo = CreateObject("ADSystemInfo")
Dim objUser : Set objUser = GetObject("LDAP://" & objADSysInfo.UserName)
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile _
    ("\\MYServer\logfile$\Login.txt", ForAppending, True)
 
Set objNetwork = CreateObject("Wscript.Network")
strComputer = objNetwork.ComputerName
 
 
 
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2") 
 
Set colComputer = objWMIService.ExecQuery _
    ("Select * from Win32_ComputerSystem")
 
Set IPConfigSet = objWMIService.ExecQuery _
    ("Select * from Win32_NetworkAdapterConfiguration Where IPEnabled=TRUE")
 
Set colItems = objWMIService.ExecQuery("Select * from Win32_LocalTime")
 
Set colSMBIOS = objWMIService.ExecQuery ("SELECT * FROM Win32_SystemEnclosure")
 
For Each objSMBIOS in colSMBIOS
strSerialNo = objSMBIOS.SerialNumber
Next
 
objTextFile.WriteLine()
 
 For Each objComputer in colComputer
    objTextFile.Write(objUser.DisplayName & vbTab)
 Next
 
objTextFile.Write( strComputer & vbTab)
 
For Each objItem in colItems
    objTextFile.Write(objItem.Month & "/" & objItem.Day & "/" & objItem.Year & vbTab & objItem.Hour & ":" & objItem.Minute & ":" & objItem.Second & vbTab)
   Next
 
 
For Each IPConfig in IPConfigSet
    If Not IsNull(IPConfig.IPAddress) Then 
        For i=LBound(IPConfig.IPAddress) to UBound(IPConfig.IPAddress)
            objTextFile.Write(IPConfig.IPAddress(i) & vbTab)
        Next
    End If
Next
 
For Each objSMBIOS in colSMBIOS
    objTextFile.Write(objSMBIOS.SerialNumber & vbTab)
 Next
 
 
objTextFile.WriteLine()
 
objTextFile.Close

Open in new window

nmcdermaid

with no luck
Can you be more specific? Does it throw an error, or does it not write to the database?
I suggest you run SQl Profiler and see what the database server can see is happening.

Jim P.

Try this:

Trusted Connection/Windows Authent:
mConnection.Open "Provider=SQLOLEDB.1;Data Source=EHR\EHR;Initial  
Catalog=UserData;Integrated Security=SSPI;"
 
SQL Security:
mConnection.Open "Provider=SQLOLEDB.1;Data Source=EHR\EHR;Initial  
Catalog=UserData;User Id=myUsername;Password=myPassword;"

Open in new window

gschwiter

ASKER

I think we are on the right track. I am receiving an error on Line 5 Char: 68 Error: Unterminated string constant Code: 800A0409

Function WriteToDatabase(sUserName)
Dim mConnection
Set mConnection = CreateObject("ADODB.Connection")
 
mConnection.Open "Provider=SQLOLEDB.1;Data Source=EHR\EHR;Initial  
Catalog=UserData;Integrated Security=SSPI;"
 
mConnection.Execute "INSERT INTO tbl_user_log (user_name, 
login_date) VALUES ('" & sUserName" & ", GetUTCDate())"
 
Set mConnection = Nothing
 
End Function

Open in new window

nmcdermaid

This:
mConnection.Open "Provider=SQLOLEDB.1;Data Source=EHR\EHR;Initial
Catalog=UserData;Integrated Security=SSPI;"

Has to be on all one line like this:
mConnection.Open "Provider=SQLOLEDB.1;Data Source=EHR\EHR;Initial Catalog=UserData;Integrated Security=SSPI;"

nmcdermaid

Same goes for your mConnection.Execute statement.

gschwiter

ASKER

The code adjustment fixed the syntax errors however I think I have a logic problem. When I execute the file it acts like the code executed properly when I check the database there are no changes in the data. I rechecked the database name, table name and data types varchar50 and date time. Any ideas

Function WriteToDatabase(sUserName)
Dim mConnection
Set mConnection = CreateObject("ADODB.Connection")
mConnection.Open "Provider=SQLOLEDB.1; Data Source=EHR\EHR; InitialCatalog=UserData; Integrated Security=SSPI"
mConnection.Execute "INSERT INTO dbo.tbl_user_log (user_name,login_date) VALUES ('sUserName', GetUTCDate())"
Set mConnection = Nothing
End Function
ReturnType = sUserName

Open in new window

nmcdermaid

Yes, like I said in my orginal post, use SQL Profiler to see what the server is seeing from its side.
Have you run the WriteToDatabase function on its own to verify that it works on its own?

Jim P.

Try this:

Function WriteToDatabase(sUserName)
Dim sSQL as String
Dim mConnection
sSQL = "INSERT INTO dbo.tbl_user_log (user_name,login_date) " & _
       "VALUES ('" & sUserName & "', GetUTCDate())"
Msgbox sSQL
Set mConnection = CreateObject("ADODB.Connection")
mConnection.Open "Provider=SQLOLEDB.1; Data Source=EHR\EHR; InitialCatalog=UserData; Integrated Security=SSPI"
mConnection.Execute sSQL
Set mConnection = Nothing
End Function
ReturnType = sUserName

Open in new window

gschwiter

ASKER

I want to make sure I am executing the code correctly I dont want to waist anyones time. When I double click on the file it acts like the file runs correctly. When I check SQL server Profiler it does not see the connection and there are no log entry for my self. I can see other users and the code they are executing through SQL reporting and other applications. Any Ideas? i did remove the as String from the code below it was causing another error.

Function WriteToDatabase(sUserName)
Dim sSQL 
Dim mConnection
sSQL = "INSERT INTO dbo.tbl_user_log (user_name,login_date) " & _
       "VALUES ('" & sUserName & "', GetUTCDate())"
Msgbox sSQL
Set mConnection = CreateObject("ADODB.Connection")
mConnection.Open "Provider=SQLOLEDB.1; Data Source=EHR\EHR; InitialCatalog=UserData; Integrated Security=SSPI"
mConnection.Execute sSQL
Set mConnection = Nothing
End Function
ReturnType = sUserName

Open in new window

nmcdermaid

You still haven't clarified whether this function is called from your larger script or whether its on its own inside a VBS file.
Assuming that it's on its own, then it won't do anything until you put a call to your function at the start. See attached below.
You should then see a msgbox pop up and see the data written to your database.
I have removed
ReturnType = sUserName
because it doesn't do anything.

Call WriteToDatabase("TEST")
 
 
Function WriteToDatabase(sUserName)
Dim sSQL 
Dim mConnection
sSQL = "INSERT INTO dbo.tbl_user_log (user_name,login_date) " & _
       "VALUES ('" & sUserName & "', GetUTCDate())"
Msgbox sSQL
Set mConnection = CreateObject("ADODB.Connection")
mConnection.Open "Provider=SQLOLEDB.1; Data Source=EHR\EHR; InitialCatalog=UserData; Integrated Security=SSPI"
mConnection.Execute sSQL
Set mConnection = Nothing
End Function

Open in new window

gschwiter

ASKER

I am getting closer on this project I have only worked with very simple VBS scripts. I can see the database connection now in SQL server Profiler however I receive an error in line 11 at the table. Line 11 Char 1 Error: Invalid syntax near tbl_user_log Code: 80040E37. I re-check table name and case everything looks correct.

gschwiter

ASKER

I am not sure why I am getting the syntax error I tried to simplify the code to just the time. I copied this line into a SQL Query and it created a table entry.
Any ideas?

INSERT INTO tbl_user_log (login_date) VALUES (GetUTCDate())

Open in new window

nmcdermaid

You're not giving any feedback - did you run the code I posted? are you running it on its own?
What does the messagebox look like when it pops up - it should be showing the SQL query. Does a messagebox show up?

gschwiter

ASKER

Sorry I did try the code I received the MsgBox and the command string with the word Test. After I click OK I receive an error line 11 Char 1 Error: Invalid object nametbl_user_log.
thanks

ASKER CERTIFIED SOLUTION

nmcdermaid

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gschwiter

ASKER

I wanted to say thank you to everyone for assisting me with my problem. The last bit of code worked. I should be able to finish the project. I guess I need a good book on the subject.
Thanks Again

gschwiter

ASKER

final Code

Call WriteToDatabase("TEST")
 
 
Function WriteToDatabase(sUserName)
Dim sSQL 
Dim mConnection
sSQL = "INSERT INTO UserData.dbo.tbl_user_log (user_name,login_date) " & _
      "VALUES ('" & sUserName & "', GetUTCDate())"
Msgbox sSQL
Set mConnection = CreateObject("ADODB.Connection")
mConnection.Open "Provider=SQLOLEDB.1; Data Source=EHR\EHR;InitialCatalog=UserData; Integrated Security=SSPI"
mConnection.Execute sSQL
Set mConnection = Nothing
End Function

Open in new window

gschwiter

ASKER

Attach is the completed windows login to SQL script
thanks


Dim objADSysInfo : Set objADSysInfo = CreateObject("ADSystemInfo")
Dim objUser : Set objUser = GetObject("LDAP://" & objADSysInfo.UserName)
Dim IP
 
Set objNetwork = CreateObject("Wscript.Network")
strComputer = objNetwork.ComputerName
 
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2") 
 
Set colComputer = objWMIService.ExecQuery _
    ("Select * from Win32_ComputerSystem")
 
Set IPConfigSet = objWMIService.ExecQuery _
    ("Select * from Win32_NetworkAdapterConfiguration Where IPEnabled=TRUE")
 
Set colItems = objWMIService.ExecQuery("Select * from Win32_LocalTime")
 
Set colSMBIOS = objWMIService.ExecQuery ("SELECT * FROM Win32_SystemEnclosure")
 
For Each objSMBIOS in colSMBIOS
strSerialNo = objSMBIOS.SerialNumber
Next
 
 For Each objComputer in colComputer
    UserName = (objUser.DisplayName )
 Next
 
For Each IPConfig in IPConfigSet
    If Not IsNull(IPConfig.IPAddress) Then 
        
             IP = (IPConfig.IPAddress(i) )
       
    End If
Next
 
For Each objSMBIOS in colSMBIOS
    SN = (objSMBIOS.SerialNumber )
 Next
 
 
Call WriteToDatabase(UserName,strComputer,IP,SN)
 
 
Function WriteToDatabase(sUserName,Computer_Name,IP,Service_Tag)
Dim sSQL 
Dim mConnection
sSQL = "INSERT INTO UserData.dbo.tbl_user_log (user_name,Computer_Name,IP,Service_Tag,Login_Date,Login_Time) " & _
      "VALUES ('" & sUserName & "', '" & Computer_Name & "','" & IP & "','" & Service_Tag & "',GetDate(),CURRENT_TIMESTAMP)"
 
Set mConnection = CreateObject("ADODB.Connection")
mConnection.Open "Provider=SQLOLEDB.1; Data Source=EHR\EHR;InitialCatalog=UserData; Integrated Security=SSPI"
mConnection.Execute sSQL
Set mConnection = Nothing
End Function

Open in new window

nmcdermaid

Are you sure you don't want to award anyone any points? Your comment implies that we were helpful.

gschwiter

ASKER

yes this was the first time placing an issue i did select points i will try again.