Announcement

Collapse
No announcement yet.

Login Script - Write to database

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Login Script - Write to database

    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?




    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

  • #2
    Re: Login Script - Write to database

    To break the Day, Month and Year to different variables, you can do something like this;
    dt = date() '<-- just one call to the date function is enough
    sYear = Year(dt)
    sMonth = Right(100+Month(dt),2)
    sDay = Right(100+Day(dt),2)

    OR in "military" date format it would be;
    dt = date() '<-- just one call to the date function is enough
    sDay = Right(100+Day(dt),2)
    sMonth = MonthName(Month(dt), True)
    sYear = Right(Year(dt),2)


    In the sample below I created just one yyyymmdd variable ("EntryDate"), this is also good sortable.

    Code:
    '=======================================================================
    ' name  : InsertVariablesInToDB.vbs
    ' author: Remco Simons [NL] 2007
    '
    ' ( http://forums.petri.com/showthread.php?t=19689 )
    '=======================================================================
    
    '(This is ADO, DAO would be similar)
    
    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const Jet10 = 1
    Const Jet11 = 2
    Const Jet20 = 3
    Const Jet3x = 4
    Const Jet4x = 5
    
    Dim adoConnection, adoRecordset
    
    strDBFile = "\\server\share\filename.mdb"
    strTable = "UserLogons"
    
    set fs = WScript.CreateObject("Scripting.FileSystemObject")
    Set objNetwork = CreateObject("Wscript.Network")
    
    ' Variables
    dt = Now()  '<-- just one call to the datetime function is enough
    tm = FormatDateTime(dt,vblongtime)
    EntryDate = Year(dt)*1e4 + Month(dt)*1e2 + Day(dt)
    strUserName = objNetwork.UserName
    strComputerName = objNetwork.ComputerName
    strIP = GetIPAddress(strComputerName)
    
    '// Initial DB setup (is processed only when not already exist)
    ' Fields going to be created (and can be used)
    strFields = "RecordNumber COUNTER ,"  _
              & "onTime DATETIME ,"       _
              & "EntryDate TEXT(8) ,"     _
              & "UserName TEXT(50) ,"     _
              & "ComputerName TEXT(50) ," _
              & "IPaddress TEXT(50) ,"    _
              & "Notes MEMO"
    Call CreateDataTable(strDBFile, Jet4x, strTable, strFields)
    
    '// start Log logon event ---------------------------------------------
    If fs.FileExists( strDBFile ) then
    
      ' Compose the INSERT statement.
      On Error Resume Next
      statement = "INSERT INTO " & strTable _
        & "(onTime,EntryDate,UserName,ComputerName,IPaddress)"
      statement = statement & " VALUES " _
        & "('" & tm & "','" & EntryDate & "','" & strUserName & "','" & strComputerName & "','" & strIP & "')"
    
      ' Initialize the database connection.
      Set adoConnection = CreateObject("ADODB.Connection")
    
      ' Open the database, use Microsoft Jet OLEDB data provider
      adoConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBFile
    
      ' Execute the statement.
      adoConnection.Execute statement, , adCmdText
      adoConnection.Close
      On Error GoTo 0
    End If
    
    wscript.quit 0
    
    
    Function GetIPAddress(strComputer)
        Set TmpFolder = FS.GetSpecialFolder(2)
            TmpFile   = TmpFolder & "\" & FS.GetTempName
        With createobject("wscript.shell")
          Onreturn = .run("%comspec% /c ping.exe/a -n 1 -w 1 " _
            & strComputer & ">" & Chr(34) & TmpFile & Chr(34),0 ,True)
        End With
        Set ts = FS.OpenTextFile(TmpFile, 1, True, 0)
        Do Until ts.AtEndOfStream
          strLine = ts.ReadLine
          If CBool(InStr(UCase(strLine),UCase(strComputer))) Then
            GetIPAddress = split(split(strLine,"[")(1),"]")(0)
            Exit Do
          End If
        Loop
        ts.Close
        FS.deletefile TmpFile
        GetIPAddress = GetIPAddress
    End Function
    
    Sub CreateDataTable(FileName, Format, sTable, sFields)
      On Error Resume Next
      'Create Access2000 database
      If Not fs.FileExists( FileName ) then
        Set Catalog = CreateObject("ADOX.Catalog")
        Catalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Jet OLEDB:Engine Type=" & Format & _
         ";Data Source=" & FileName
      End If
      If fs.FileExists( FileName ) then
        'create table
        Set adoConnection = CreateObject("ADODB.Connection")
        'check if table already exist (will not raise error)
        Set adoRecordSet = CreateObject("ADODB.Recordset")
        adoRecordset.Open "SELECT * FROM " & sTable , _
          adoConnection, adOpenStatic, adLockOptimistic
        If not err.number = 0 then
          Err.clear
          If adoRecordset.RecordCount = 0 Then
            adoConnection.Open _
              "Provider= Microsoft.Jet.OLEDB.4.0; " & _
              "Data Source=" & FileName
            adoConnection.Execute "CREATE TABLE " & _
            sTable & "(" & sFields & ")"
          End If
        End If
        adoRecordset.close
        adoConnection.Close
      End If
    End Sub
    User Logons are written directly to the database.
    You can import the data to HTML by using a script.

    \Rems

    Note;
    At least MDAC2.0 installed is required to run the ADOX code



    EDIT

    When date/Time registration is important, you must be sure that users cannot manupilate time on their computers.
    And make sure that the time on all the computers are set the correct timezone and the time is insync with the dcs on the network and their timezone is correct.

    The 'onTime' column in the example datebase just shows the time, the date is in and other format in an other column. This is not realy nesseray for sorting since this is a database, you can configure a column to DateTime format, so the database can sort on date. Just put the dt (Date+Time) value to the column "onTime"

    If you want to use the logged time to calculate with other time values, then it is required to save also the 'time offset from GMT' (BIAS).
    The 'Current TimeZone' is the offset time included the daylightsavings correction. The offtime value is in plus or minus the amount of minutes.
    You can determine the value using WMI or read it from the registry. The currenttime format for the column "EntryDate" will then be: yyyymmddhhmmss.000000+60

    example how to read the 'current GMT offset in minutes' from the registry
    Code:
    dt = Now()
    dtmCurrentDate = Year(dt)*1e4 + Month(dt)*1e2 + Day(dt)
    dtmCurrentTime = Right(1e6 + _
                (Hour(dt)*1e4 + Minute(dt)*1e2 + Second(dt)), 6)
    
    With CreateObject("WScript.Shell") 
      atb = "HKEY_LOCAL_MACHINE\System\CurrentControlSet\" &_ 
           "Control\TimeZoneInformation\ActiveTimeBias" 
      offsetMin = .RegRead(atb)
      If Int(instr(Cstr(offsetMin),"-"))=1 Then a="+" Else a="-"
      GetBIAS = Cstr(a & Abs(offsetMin))
    End With
    
    EntryDate = dtmCurrentDate & dtmCurrentTime _ 
       & ".000000" & Cstr(GetBIAS)
    
    wscript.echo EntryDate
    Last edited by Rems; 19th November 2007, 10:13.

    This posting is provided "AS IS" with no warranties, and confers no rights.

    __________________

    ** Remember to give credit where credit's due **
    and leave Reputation Points for meaningful posts

    Comment

    Working...
    X