I think this gets it for me. Thanks for your help.
call C:\"Program Files\Log Parser 2.2"\logparser -i:CSV -o:CSV -stats:OFF -e:-1 -q:ON "SELECT DISTINCT EXTRACT_TOKEN(Strings, 0, '|') AS User, EXTRACT_TOKEN(Strings, 5, '|') AS Workstation, EXTRACT_TOKEN(Strings, 11, '|') AS IP_Address FROM Sec530.csv.%date6% where eventid = '530' and User <> 'User' and User <> '-' Order by User, Workstation" > Sec530.email.%date6% FOR /F "tokens=1,2,3 delims=," %%i IN (Sec530.email.%date6%) DO p:\in01\oper\blat240\full\blat begin.txt -to [EMAIL PROTECTED] -bcc [EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED] .indianafcu.com -f "Daily Network Security Audit" -s "After hours access has been reported to auditors." -body "The Workstation (%%j) on IP (%%k) was not signed off and was still logged on with (%%i) after network logon hours had expired. Please log off and shut down the workstation everynight. This helps prevent damage to the workstation and assures the Credit Union that no one is trying to access the network after hours using your network logon." -priority 1 ________________________________ From: Jake Gardner [mailto:[EMAIL PROTECTED] Sent: Monday, December 01, 2008 3:36 PM To: NT System Admin Issues Subject: RE: Run a batch file to send emails I use elsave (nightly) to create daily CSV of my security event logs and then it clears the security log. I then run log parser to read the CSV and drop the columns I don't want and give myself cleaned up csv file. Watch the position identifier in the EXTRACT_TOKEN() function as this will grab the column(s) you want. Using DISTINCT in the select will make sure you only grab one instance of the username. Here's the batch file I use. @ECHO OFF REM Get current date for stamping the filename for /F "tokens=2,3,4 delims=/ " %%i in ('date/t') do set d=%%k%%i%%j REM Grab the full security event log and archive it cd \ logparser -i:EVT -o:CSV -stats:OFF "SELECT * FROM Security where eventid = '560'" >> H:\logs\evtvwr_sec_%d%.csv REM Clear the SECURITY log cd c:\scripts elsave -l security -C REM Parse the full file and create an easier to read log file. logparser -i:CSV -o:CSV -stats:OFF -e:-1 -q:ON "SELECT DISTINCT EXTRACT_TOKEN(Strings, 10, '|') AS User, EXTRACT_TOKEN(Strings, 2, '|') AS FilePath FROM H:\logs\evtvwr_sec_%d%.csv where eventid = '560' and User <> 'Administrator' and user <> '-' ORDER BY FilePath, User" > H:\logs\parsed\evtvwr_sec_parsed_%d%.csv Thanks, Jake Gardner TTC Network Administrator Ext. 246 ________________________________ From: David McSpadden [mailto:[EMAIL PROTECTED] Sent: Monday, December 01, 2008 3:10 PM To: NT System Admin Issues Subject: RE: Run a batch file to send emails Ok So I have this to create my blat input file but I am still getting to many results. C:\Program Files\Log Parser 2.2>logparser "select extract_token(strings, 0, '|') as Account, TimeGenerated into file.csv from \\10.0.50.205\security where eventid in (530) " I only want events from today-1. ________________________________ From: Jake Gardner [mailto:[EMAIL PROTECTED] Sent: Monday, December 01, 2008 2:53 PM To: NT System Admin Issues Subject: RE: Run a batch file to send emails Extract the date field the same way you extract the account field, then add it to your where clause. Thanks, Jake Gardner TTC Network Administrator Ext. 246 ________________________________ From: David McSpadden [mailto:[EMAIL PROTECTED] Sent: Monday, December 01, 2008 2:32 PM To: NT System Admin Issues Subject: RE: Run a batch file to send emails select distinct extract_token(strings, 0, '|') as Account into file.csv from \\10.0.50.205\security where eventid in (530) How do I get just yesterday's 530 events? ________________________________ From: Jake Gardner [mailto:[EMAIL PROTECTED] Sent: Monday, December 01, 2008 1:40 PM To: NT System Admin Issues Subject: RE: Run a batch file to send emails Logparser http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4 c25-91b2-f8d975cf8c07 Thanks, Jake Gardner TTC Network Administrator Ext. 246 ________________________________ From: David McSpadden [mailto:[EMAIL PROTECTED] Sent: Monday, December 01, 2008 1:33 PM To: NT System Admin Issues Subject: Run a batch file to send emails I want to read an input file. It would comma delimited. It will have those users that have not logged off and are getting after hours logon events with their names. I only want to send 1 email per user so I would have to sort and then group then blat out the email. My format for the file is like this: 866265068,Security,Security,AUDIT FAILURE,031004LD5Z3K55,12/1/2008 12:49:14 AM,530,SYSTEM\NT AUTHORITY,Logon Failure: Reason: Account logon time restriction violation User Name: DawnH Domain: IM_DOM1 Logon Type: 3 Logon Process: NtLmSsp Authentication Package: NTLM Workstation Name: XP31090634014W Caller User Name: - Caller Domain: - Caller Logon ID: - Caller Process ID: - Transited Services: - Source Network Address: 10.0.31.40 Source Port: 1087 866265066,Security,Security,AUDIT FAILURE,031004LD5Z3K55,12/1/2008 12:49:11 AM,530,SYSTEM\NT AUTHORITY,Logon Failure: Reason: Account logon time restriction violation User Name: DawnH Domain: IM_DOM1 Logon Type: 3 Logon Process: NtLmSsp Authentication Package: NTLM Workstation Name: XP31090634014W Caller User Name: - Caller Domain: - Caller Logon ID: - Caller Process ID: - Transited Services: - Source Network Address: 10.0.31.40 Source Port: 1044 866265063,Security,Security,AUDIT FAILURE,031004LD5Z3K55,12/1/2008 12:49:07 AM,530,SYSTEM\NT AUTHORITY,Logon Failure: Reason: Account logon time restriction violation User Name: DawnH Domain: IM_DOM1 Logon Type: 3 Logon Process: NtLmSsp Authentication Package: NTLM Workstation Name: XP31090634014W Caller User Name: - Caller Domain: - Caller Logon ID: - Caller Process ID: - Transited Services: - Source Network Address: 10.0.31.40 Source Port: 4976 866264132,Security,Security,AUDIT FAILURE,031004LD5Z3K55,12/1/2008 12:34:33 AM,530,SYSTEM\NT AUTHORITY,Logon Failure: Reason: Account logon time restriction violation User Name: DavidE Domain: IM_DOM1 Logon Type: 3 Logon Process: NtLmSsp Authentication Package: NTLM Workstation Name: XP250308200506 Caller User Name: - Caller Domain: - Caller Logon ID: - Caller Process ID: - Transited Services: - Source Network Address: 10.0.50.48 Source Port: 4656 866264130,Security,Security,AUDIT FAILURE,031004LD5Z3K55,12/1/2008 12:34:33 AM,530,SYSTEM\NT AUTHORITY,Logon Failure: Reason: Account logon time restriction violation User Name: DavidE Domain: IM_DOM1 Logon Type: 3 Logon Process: NtLmSsp Authentication Package: NTLM Workstation Name: XP250308200506 Caller User Name: - Caller Domain: - Caller Logon ID: - Caller Process ID: - Transited Services: - Source Network Address: 10.0.50.48 Source Port: 4614 866264128,Security,Security,AUDIT FAILURE,031004LD5Z3K55,12/1/2008 12:34:33 AM,530,SYSTEM\NT AUTHORITY,Logon Failure: Reason: Account logon time restriction violation User Name: DavidE Domain: IM_DOM1 Logon Type: 3 Logon Process: NtLmSsp Authentication Package: NTLM Workstation Name: XP250308200506 Caller User Name: - Caller Domain: - Caller Logon ID: - Caller Process ID: - Transited Services: - Source Network Address: 10.0.50.48 Source Port: 4572 866264126,Security,Security,AUDIT FAILURE,031004LD5Z3K55,12/1/2008 12:34:33 AM,530,SYSTEM\NT AUTHORITY,Logon Failure: Reason: Account logon time restriction violation User Name: DavidE Domain: IM_DOM1 Logon Type: 3 Logon Process: NtLmSsp Authentication Package: NTLM Workstation Name: XP250308200506 Caller User Name: - Caller Domain: - Caller Logon ID: - Caller Process ID: - Transited Services: - Source Network Address: 10.0.50.48 Source Port: 4529 So basically I want to skip everything except the User Name: and Workstation Name: I generate this list every morning for the prior day using psloglist and now I want to parse it and blat an email to the offenders telling them to shut down every night. Any ideas??? Data Security is everyone's responsibility. ***Teletronics Technology Corporation*** This e-mail is confidential and may also be privileged. If you are not the addressee or authorized by the addressee to receive this e-mail, you may not disclose, copy, distribute, or use this e-mail. If you have received this e-mail in error, please notify the sender immediately by reply e-mail or by telephone at 267-352-2020 and destroy this message and any copies. Thank you. ******************************************************************* ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ <http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/> ~
