• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 696
  • Last Modified:

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

0
gschwiter
Asked:
gschwiter
  • 10
  • 8
  • 2
1 Solution
 
nmcdermaidCommented:
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.
0
 
Jim P.Commented:
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

0
 
gschwiterAuthor Commented:
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

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
nmcdermaidCommented:
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;"
 
0
 
nmcdermaidCommented:
Same goes for your mConnection.Execute statement.
0
 
gschwiterAuthor Commented:
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

0
 
nmcdermaidCommented:
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?
0
 
Jim P.Commented:
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

0
 
gschwiterAuthor Commented:
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

0
 
nmcdermaidCommented:
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

0
 
gschwiterAuthor Commented:
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.
0
 
gschwiterAuthor Commented:
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

0
 
nmcdermaidCommented:
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?
 
0
 
gschwiterAuthor Commented:
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
0
 
nmcdermaidCommented:
So I'll go with your second error posting (invalid object rather than invalid syntax).
Does dbo.tbl_user_log exist in the 'UserData' database? Is it in the 'dbo' schema? Although your connection string sets this for you anyway, try this SQL string:
sSQL = "INSERT INTO UserData.dbo.tbl_user_log (user_name,login_date) " & _
       "VALUES ('" & sUserName & "', GetUTCDate())"
Can you find this table in the object browser tree in Management Studio and post a picture?
 
Alternatively try running this:
INSERT INTO UserData.dbo.tbl_user_log (login_date) VALUES (GetUTCDate())
 
0
 
gschwiterAuthor Commented:
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  
0
 
gschwiterAuthor Commented:
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

0
 
gschwiterAuthor Commented:
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

0
 
nmcdermaidCommented:
Are you sure you don't want to award anyone any points? Your comment implies that we were helpful.
0
 
gschwiterAuthor Commented:
yes this was the first time placing an issue i did select points i will try again.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 10
  • 8
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now