The CSV output from the following batch file looks like this:
03/04/2008, 05:34:47:00, logon, UserName,ComputerName, 192.168.100.1, 00-4C-46-21-35-2C, Finance Division, Finance Department
03/04/2008, 05:36:37:00, logoff, UserName,ComputerName, 192.168.100.1, 00-4C-46-21-35-2C, Finance Division, Finance Department
I tried to add a lot of remarks to identify what I'm trying to do in this batch file. This isn't very elegent. I found some code (primarly the FOR statements) elsewhere and modified it or added to it for our environment. It relies heavily on capturing data from other sources (IPCONFIG /all, NET command), writing to a file and then searching that file for the data I want to capture.
The reference to DATETIME.EXE is a small executable I create using WINBATCH to capture and format date and time values - otherwise tryin to use the %time% variable doesn't result in a zero filled hour (before 12:00).
This is the WINBATCH code:
a=TimeYmdHms( )
logonyear=strsub(a,1,4)
logonmonth=strsub(a,6,2)
logonday=strsub(a,9,2)
logonDate=strcat(logonMonth,"/",LogonDay,"/",LogonYear)
FileLogonDate=strcat(LogonYear,logonMonth,LogonDay)
LogonTime=strcat(strsub(a,12,11),":00")
handle = FileOpen("c:\logdata\DateTime.txt", "WRITE")
FileWrite(handle,strcat("Data Logon Date$ ",LogonDate))
Filewrite(handle,strcat("Logon Time$ ",LogonTime))
Filewrite(handle,strcat("File Logon Date$ ",FileLogonDate))
FileClose(handle)
This is what the datetime.txt file looks like:
Data Logon Date$ 03/03/2008
Logon Time$ 20:59:19:00
File Logon Date$ 20080303
Here's the logon.bat file:
@Echo off
rem Created a small executable (DATETIME.EXE) using WINBATCH to capture/format current date/time and output to a text file. FOR statement searches for the values and sets them as variables.
\\mydomain\netlogon\datetime.exe
rem Use the TYPE command to list and find logon date from c:\logdata\datetime.txt
FOR /F "TOKENS=2* DELIMS=$" %%A IN ('type c:\logdata\datetime.txt ^| FIND "Data Logon Date"') DO FOR %%B IN (%%A) DO SET logondate=%%B
rem Use the TYPE command to list and find the logon time from c:\logdata\datetime.txt
FOR /F "TOKENS=2* DELIMS=$" %%A IN ('type c:\logdata\datetime.txt ^| FIND "Logon Time"') DO FOR %%B IN (%%A) DO SET logontime=%%B
rem Use the TYPE command to list and find the logon time from c:\logdata\datetime.txt used to create the centrally stored CSV file (accessdata_yyyymmdd.txt)
FOR /F "TOKENS=2* DELIMS=$" %%A IN ('type c:\logdata\datetime.txt ^| FIND "File Logon Date"') DO FOR %%B IN (%%A) DO SET filelogondate=%%B
Rem Use IPConfig /all command to list and find the last IP address from the list
FOR /F "TOKENS=2* DELIMS=:" %%A IN ('IPCONFIG /ALL ^| FIND "IP Address"') DO FOR %%B IN (%%A) DO SET IPADDR=%%B
Rem Use IPConfig /all command to list and find the MAC ADDRESS
FOR /F "TOKENS=2* DELIMS=:" %%A IN ('IPCONFIG /ALL ^| FIND "Physical Address"') DO FOR %%B IN (%%A) DO SET macaddress=%%B
Rem create the logdata dirtory if it doesnt exist
if not exist c:\logdata md c:\logdata
Rem execute this NET statement to get group membership information for the user
net user %username% /domain>c:\logdata\logondata.txt
REM These series of statements search the file created in the last step for a matching group name. Based on that information
rem the department and division information is set
:check1
find /i "Dept_Finance" c:\logdata\logondata.txt>nul
if errorlevel 1 goto check2
set department=Finance Department
set division=Finance Division
goto EndDept
:check2
find /i "Dept_Payroll" c:\logdata\logondata.txt>nul
if errorlevel 1 goto check3
set department=Payroll Departments
set division=Finance Division
goto EndDept
:check3
find /i "Dept_Purchasing" c:\logdata\logondata.txt>nule
if errorlevel 1 goto enddept
set department=Purchasing Department
set division=Finance Division
Rem Additional department determinates inserted here adjusting the goto statements as necessary
:enddept
rem delete local copy of last logon or logoff information - only the most current session information is used.
if exist c:\logdata\AccessInfo.txt del c:\logdata\AccessInfo.txt /q
rem create the local logon or logoff data for this specific logon/logoff
echo %logondate%, %logontime%, logon, %username%, %computername%, %IPADDR%, %MACADDRESS%, %division%, %department%>>C:\LogData\AccessInfo.txt
rem create a record in the centrally stored file of logons for the day - separate file for each day. Uses variables defined through the rest of the batch file
rem except for the constant "Logon" to identify logons. A separate batch file (logoff.bat) is exactly the same except it changes the constant from "LOGON" to "LOGOFF"
echo %logondate%, %logontime%, logon, %username%, %computername%, %IPADDR%, %MACADDRESS%, %division%, %department%>>\\StorageServer\commondata$\Accessinfo_%filelogondate%.csv
rem SQL statement to update the database using the locally stored file as input so the current logon or logoff is updated.
\\MyDomain\NETLOGON\logparser.exe "select * INTO LogonLogoff from c:\LOGDATA\AccessInfo.txt" -i:CSV -o:SQL -server:LogServer\instancename -database:LogonLogoffLog -driver:"SQL Server" -username:LogUser -password:LogPassword -createtable:OFF -headerrow:off > c:\logdata\sqlresultson.txt