PHWinfo - Afficher un message

02/10/2007, 04h39

"Richard Mueller [MVP]" <rlmueller-nospam@ameritech.nospam.net> wrote in
message news:%23H%23P9%23WAIHA.484@TK2MSFTNGP06.phx.gbl...
>
> "pooradmin" <jskiba99@gmail.com> wrote in message
> news:1190937855.550975.257960@y42g2000hsy.googlegr oups.com...
>> On Sep 27, 11:01 am, "Richard Mueller [MVP]" <rlmueller-
>> nos...@ameritech.nospam.net> wrote:
>>> "kcadmin" <kcad...@discussions.microsoft.com> wrote in message
>>>
>>> news:50E95CBC-E6DB-4580-BBAE-FC951F323998@microsoft.com...
>>>
>>>
>>>
>>>
>>>
>>> > 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 spreadsheet 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

02/10/2007, 04h39	#5
Al Dunbar Messages: n/a Hébergeur:	Re: Recording AD Logons to SQL Database "Richard Mueller [MVP]" <rlmueller-nospam@ameritech.nospam.net> wrote in message news:%23H%23P9%23WAIHA.484@TK2MSFTNGP06.phx.gbl... > > "pooradmin" <jskiba99@gmail.com> wrote in message > news:1190937855.550975.257960@y42g2000hsy.googlegr oups.com... >> On Sep 27, 11:01 am, "Richard Mueller [MVP]" <rlmueller- >> nos...@ameritech.nospam.net> wrote: >>> "kcadmin" <kcad...@discussions.microsoft.com> wrote in message >>> >>> news:50E95CBC-E6DB-4580-BBAE-FC951F323998@microsoft.com... >>> >>> >>> >>> >>> >>> > 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 spreadsheet 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