locked
Scripts to capture logon/logoff works fine-does not work if run as a group policy logon/logoff script RRS feed

  • Question

  • User1690724930 posted

    I created a process to capture specific logon/logoff information - including date, time, user name, ip address, mac address, computer name, and some other site specific information. This all runs as part of a batch file that collects the data and ultimately echos all captured data to a csv file.

    I want to import this data at the time it is captured to a SQL database, so I modified my script to run logparser and read the csv file in order to import it directly to the SQL DB. All data is initially stored on the local machine of the user logging on. There do not appear to be any issues with creating the text files in that directory. If I manually run the batch file, everything works as planned - CSV is created, read and imported into SQL.  Works Great!

    But....if I set the batch files to run as the logon and logoff scripts in group policy (user), the CSV file is created, but the data is not imported into SQL. I tried to pipe the output of the SQL command to a text file for debugging and along with the statistics, it reports "task aborted". That's it. I couldn't find anything else anywhere.

    This is the statement I'm trying to execute:

    \\mydomain\NETLOGON\logparser.exe "select * INTO LogonLogoff from c:\logdata\DataToImport.txt" -i:CSV -o:SQL -server:MySQLServer\ThisInstance -database:LogonLogoffDB -driver:"SQL Server" -username:loguser -password:logpassword -createtable:OFF -headerrow:off

    I'd certainly appreciate any suggestions anyone may have.

    Monday, March 3, 2008 11:12 PM

Answers

  • User989702501 posted

    Agreed. could be permission related when the logon scripts being execute.. hence if you can capture the output of each steps, that might helps.

    • Marked as answer by Anonymous Tuesday, September 28, 2021 12:00 AM
    Wednesday, March 5, 2008 12:40 AM

All replies

  • User-2104394151 posted

    Hi,

     Can you share your script that generates the CSV with us ?

     Djoh

     

    Tuesday, March 4, 2008 3:42 AM
  • User1690724930 posted

    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

    Tuesday, March 4, 2008 9:27 AM
  • User989702501 posted

    Interesting, if you pipe the output of each step....is there more information? was it stuck at LP or sql portion?

    Tuesday, March 4, 2008 10:57 PM
  • User1690724930 posted

     

    I haven't piped the output at each step yet. I've made the assumption that everything up to that point works properly because the contents of the file that the SQL step reads as input appears to be properly formatted.

    Since I wasn't having luck with the SQL statement within the logon/logoff script, I've temporarily set up a scheduled task to read the centrally located file that all output is written to (in addition to the local file) and using the same SQL statement from the batch file and it works fine. It just doesn't seem to like being executed from within the login script.

    It seems like it can't be a file permission issue with the file being read - that was created by the same process. Some other permission/rights problem?

    If I get a chance tomorrow, I'll pipe everything, just to be sure.

     Thanks.

     

    Tuesday, March 4, 2008 11:29 PM
  • User989702501 posted

    Agreed. could be permission related when the logon scripts being execute.. hence if you can capture the output of each steps, that might helps.

    • Marked as answer by Anonymous Tuesday, September 28, 2021 12:00 AM
    Wednesday, March 5, 2008 12:40 AM
  • User-327366319 posted

    Hi,

    My name is Henry Ng.

    Is any one can helps how to write or create a VB Script / bat file to capturing and query the information into Access Database when users login earlt morning and logoff end of the day.

    Best Regards,

    Henry Ng 

      

     

    Tuesday, June 16, 2009 11:27 PM
  • User-327366319 posted

    Hi,

    Is that any one can help or encounter the windows server 2003 unable to boot. The system prompt with blue-screen error "unmountable Boot Volumn". I am not able to boot to recovery console to use the command to use the command of "FixBoot" or "FixMBR" to fix the problem.

    Please kindly help.

    Urgent!

    Regards,

    Henry 

     

     

    Thursday, June 18, 2009 4:30 AM
  • User989702501 posted

    If you DB already capturing the login/logoff data, just query the table?

    and for your blue screen issue, try general OS support fourms/newsgroups.

    Wednesday, June 24, 2009 9:14 AM
  • User-1426710237 posted

    Hi Bob, Is it possible to share login and logout time script please.

     Thanks,

    Bala.

    Wednesday, July 29, 2009 2:59 PM
  • User1109686383 posted

    Hello Friend;

    I am Aniruddha and wanted to thank you for sharing above winbatch file. I tested it but i came across error as below

    'a'  is not recognized as an inetrnal or external commnd, operable program or batch file.

    'logonyear, logonmonth etc' not recognized as an inetrnal or external commnd, operable program or batch file.

    Can you help me to get rid off such error or any help is there?

     

    Wednesday, August 22, 2012 8:53 AM
  • User989702501 posted

    the entire command is enclose with "  " ? and all in a single command ?
    most likely syntax error - look at the error msgs again and see if you find it.

    Thursday, August 23, 2012 10:55 PM