Re: Recording AD Logons to SQL Database


"Richard Mueller [MVP]" <rlmueller-nospam@xxxxxxxxxxxxxxxxxxxx> wrote in
message news:%23H%23P9%23WAIHA.484@xxxxxxxxxxxxxxxxxxxxxxx

"pooradmin" <jskiba99@xxxxxxxxx> wrote in message
news:1190937855.550975.257960@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Sep 27, 11:01 am, "Richard Mueller [MVP]" <rlmueller-
nos...@xxxxxxxxxxxxxxxxxxxx> wrote:
"kcadmin" <kcad...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message

news:50E95CBC-E6DB-4580-BBAE-FC951F323998@xxxxxxxxxxxxxxxx





I've been trying to find a way to keep a running log of who logged on,
when
and from what pc by running the code below in my login script. The
code
works, but the csv file method is limited since it tends to lock up
the
file
if more than one is trying to write to it simultaneously. I want to
do
basically the same thing, but log it directly to SQL. Any ideas?

'*****************************************************
'** Record Logon Information
'*****************************************************

'Check for the existance of the drop log and create new file if
necessary

Const FOR_APPENDING = 8
strOutputFile = "\\servername\drop$\ad\adlog.csv"
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(strOutputFile) Then
Set objTextStream = objFSO.OpenTextFile(strOutputFile, FOR_APPENDING)
Else
Set objTextStream = objFSO.CreateTextFile(strOutputFile)
End If

'get user info

Set objSysInfo = CreateObject("ADSystemInfo")
Set objUser = GetObject("LDAP://"; & objSysInfo.UserName)
Set objComputer = GetObject("LDAP://"; & objSysInfo.ComputerName)
strMessage = objUser.CN &","& objComputer.CN &","& Now &","

'write it to the file

objTextStream.WriteLine strMessage
objTextStream.Close

First, I have a similar sample VBScript logon program, but it makes 3
attempts to append to the log file before giving up:

http://www.rlmueller.net/Logon5.htm

A program to log to an SQL Server database could be similar to below
(not
tested):
====================
Option Explicit

Dim strServer, strInstance, strDatabase, strConnect
Dim adoConnection, adoCommand, objNetwork
Dim strUserName, strComputerName, strSQL

' Specify SQL Server, Instance name (if any), and database.
strServer = "MyServer"
strInstance = "MyInstance"
strDatabase = "MyDatabase"
strServer = "Idaho"
strInstance = "PocketLunch"
strDatabase = "PocketLunch"

If (strInstance <> "") Then
strServer = strServer & "\" & strInstance
End If

' Connection string for database.
' This uses Windows Authentication.
strConnect = "DRIVER=SQL Server;" _
& "Trusted_Connection=Yes;" _
& "DATABASE=" & strDatabase & ";" _
& "SERVER=" & strServer

' Create ADO objects and connect to database.
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.ConnectionString = strConnect
adoConnection.Open

Set adoCommand = CreateObject("ADODB.Command")
adoCommand.ActiveConnection = adoConnection

' Retrieve values.
Set objNetwork = CreateObject("Wscript.Network")
strUserName = objNetwork.UserName
strComputerName = objNetwork.ComputerName

' Log date/time, user name, and computer name.
strSQL = "INSERT INTO MyTable " _
& "(LogonDate, UserName, ComputerName) " _
& "VALUES(" _
& "GETDATE(), " _
& "'" & strUserName & "', " _
& "'" & strComputerName & "'" _
& ")"
adoCommand.CommandText = strSQL
adoCommand.Execute

' Clean up.
adoConnection.Close

--
Richard Mueller
Microsoft MVP Scripting and ADSI
Hilltop Lab -http://www.rlmueller.net
--- Hide quoted text -

- Show quoted text -

I'm sure the database would be the way to go. One option that came to
mind when reading both posts.. If you have a directory to dump
multiple files for each pc then schedule a task with a script or do it
on demand to combine them into a single csv file. Noone would be
writing to their mahines file at the same time. Maybe a drawback
would be that when you do combine the files into one they won't be in
chronological order, until sorted by some means.

Set objSysInfo = CreateObject("ADSystemInfo")
Set objUser = GetObject("LDAP://"; & objSysInfo.UserName)
Set objComputer = GetObject("LDAP://"; & objSysInfo.ComputerName)
strMessage = objUser.CN &","& objComputer.CN &","& Now &","

.....

strOutputFile = "\\servername\drop$\ad\" & objComputer.CN & ".csv"

.....


-J
www.pooradmin.com


That could work. Once you combine all the csv files into one, it can be
read into a spread*** and sorted. Finding information on one PC would be
easy. To find information on one user a script could extract the lines
from all the files that pertain to that user. It would be a lot of files
if you have a lot of PC's.

Any script or task that works with the csv files should make copies, so
the files are not locked when users attempt to append. Also, if Common
Names can have commas, enclose values in quotes.

--
Richard Mueller
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net
--

We do something similar, but write all logs to a file whose name is based on
the date, i.e. one for each day: 2007-10-01, 2007-10-02, and etc. If a file
is unavailable, we append the workstation name to it and try again. At a
site with 300 users and 250 workstations this happens infrequently.

Some time after the end of the month we run another script that combines
each month's logs into a single file, and we have a batch script that does a
simple find.exe query through *all* of the log files to look for all
instances of a particular user or a particular workstation.

The main thing with basing the log file name on the date is that no
intervention is required in order to keep the log files to a reasonable size
in order to prevent contention and delays.

It should be noted that we run this for all users at a particular site in a
given OU, and the log files are located on a local server. This approach
would not scale well to a large or disperse organization, unless it were
done individually by site. Logging to a database of some kind would be an
improvement, as long as performance did not become a problem. All sessions
being logged to a single database server over a WAN could be a problem, so
perhaps local logging combined with a set of data collection jobs that would
aggregate it all into a central database might be a good way to go. But it
starts to get a bit complicated...

/Al


.