You can do per-column security in SQL2000. With that in mind, you could easily give users different rights to the logoff time column (update) versus insert for the others. A properly equipped SQL box will be much more efficient in a busy environment han Access will ever be. --Brian
-----Original Message-----
From: Paul Wilkinson [mailto:[EMAIL PROTECTED]
Sent: Fri 7/2/2004 1:21 PM
To: [EMAIL PROTECTED]
Cc:
Subject: Re: [ActiveDir] Logging User Logon and Logoffs
Security is a problem for me, I want to give users the minimum rights
needed. Right now they can only insert a new row in the database. In
order to be able to update the same row the used at logon with the
logoff data, I'd be giving everyone the ability to completely change all
login/logoff times in the database, which throws out the use of the log
for security records. It's interesting that you used a mdb database.
I'll have to try that to see if it makes a speed difference.
Paul Wilkinson
865-974-0649
2422 Dunford Hall
OIT Lab Services
University of TN, Knoxville
Bruce Clingaman wrote:
> I am using an mdb database. I had the exact issue you have, adding the
> logout time to the same record as the login time. My solution was to store
> the autonum from the mdb record in the user's registry then read it at
> logout and look it up in the mdb and enter the logout time. In Access
> databases this is very easy, but in SQL Server I had to take a completely
> different approad which I had to drop because the logout script took too
> long to execute.
> The biggest issue I have with my method is security, users have modify
> access to the share and database.
> The logout script misses about 1 in 50 logouts. I don't know why.
> The mdb has the following fields: ID (Access autonumber), DC (rootDSE),
> Computername, Username, Login (time/date), Logout (time/date)
> I have login.vbs and logout.vbs:
>
> '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
> 'Login.vbs
> option explicit
>
> On Error Resume Next
>
> DIM strKeyPath, strValueName, strComputer, DC
>
> const HKEY_CURRENT_USER = &H80000001
> strComputer = "."
>
> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
> Dim UserID, ComputerName, objNetwork
> Dim objConn, objRS, AutoNum, Field
> Dim fs, a
> AutoNum = 0
>
> Set objNetwork = CreateObject("WScript.Network")
> Set DC = getObject("LDAP://rootDse";)
> DC = DC.Get("dnsHostName")
> ComputerName = objNetwork.ComputerName
>
> UserID = objNetwork.UserName
>
> Set objConn = CreateObject("ADODB.Connection")
> objConn.Mode = 3
>
> objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security
> Info=False;Data Source=\\DC1\logins$\logins.mdb"
>
> Set objRS = CreateObject("ADODB.Recordset")
> objRS.Open "Logins", objConn, 2, 2
>
> objRS.AddNew
> AutoNum = objRS("ID")
> objRS("ComputerName") = ComputerName
> objRS("UserID") = UserID
> objRS("DC") = DC
> objRS("TimeIn") = Now
>
> objRS.Update
>
> objRS.Close
> Set objRS = Nothing
> objConn.Close
> Set objConn = Nothing
>
> '''''''''''''''''''''''''''
> '''''''''''''''''''''''''''
> Dim oReg
> Set oReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" &_
> strComputer & "\root\default:StdRegProv")
>
> strKeyPath = "SOFTWARE\Microsoft\Windows\CurrentVersion\Logins"
> strValueName = "autonum"
>
> oReg.CreateKey HKEY_CURRENT_USER,strKeyPath,strValueName
> oReg.SetStringValue HKEY_CURRENT_USER,strKeyPath,strValueName,AutoNum
> '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
>
> '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
> 'Logout.vbs
> option explicit
>
> On Error Resume Next
>
> Dim UserID, ComputerName, objNetwork
> Dim objConn, objRS, AutoNum
> Dim a, fs, SQL
> Dim strComputer, oReg, strKeyPath,strValueName
>
> const HKEY_CURRENT_USER = &H80000001
> strComputer = "."
> Set oReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" &_
> strComputer & "\root\default:StdRegProv")
>
> strKeyPath = "SOFTWARE\Microsoft\Windows\CurrentVersion\Logins"
> strValueName = "autonum"
>
> oReg.GetStringValue HKEY_CURRENT_USER,strKeyPath,strValueName,AutoNum
>
> Set objConn = CreateObject("ADODB.Connection")
> objConn.Mode = 3
>
> objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security
> Info=False;Data Source=\\DC1\logins$\logins.mdb"
> Set objRS = CreateObject("ADODB.Recordset")
> objRS.Open "Logins", objConn, 2, 2
>
> SQL = "UPDATE Logins SET TimeOut='" & Now & "' WHERE ID = " & AutoNum
> objConn.Execute(SQL)
>
> Set objRS = Nothing
> objConn.Close
> Set objConn = Nothing
>
> '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
> @@@@
>
>
>
>
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Paul Wilkinson
> Sent: Friday, July 02, 2004 11:21 AM
> To: [EMAIL PROTECTED]
> Subject: [ActiveDir] Logging User Logon and Logoffs
>
>
> Is anyone logging both logion and log offs? I'm working on a method of
> recording this info for a computer lab environment, and I'd like to see if
> someone else is already doing it. I want real time utilization info and I
> want to be able to build stats off of it for latter use.
>
> On Cs, you can determine when I person logs on to a client machine, but you
> don't know when they log off because that information is only on the client
> machine.
>
> Right now I'm running a log on, and log off script to insert a row in SQ
> database in the following format:
>
> Computer name, use rid, login, log off
>
> Where either the login, or log off fields are date/time, and the other field
> is NULL.
>
>
> The problem with this approach is that you get logs like this:
>
> computer1, bob, 2004-04-29 14:36:08, NULL
> computer1, bob, NULL, 2004-04-29 14:52:34
>
> So I need a way to combine the login/off logs into one roll to be able
> to perform stats. I'm a SQ novice, so I don't really know a good way of
> doing this besides exporting all of it to a cs, write a script that
> combines them, then export that back to a new database before running stats.
>
>
>
>
>
List info : http://www.activedir.org/mail_list.htm
List FAQ : http://www.activedir.org/list_faq.htm
List archive: http://www.mail-archive.com/activedir%40mail.activedir.org/
<<winmail.dat>>
