Collect and Filter Logon Events with Log Parser

Hello, dear community!

IT infrastructure is always in dynamics. Thousands of changes occur every minute. Many of them are required to register. System audit is an integral part of the information security of organizations. Change control helps prevent serious accidents later on.

In the article, I want to talk about my experience in tracking the events of users' logon (and logout) on the organization’s servers, describe in detail the details that arose in the course of performing the task of analyzing audit logs, and also bring the solution of this task step by step.

The goals we pursue are:

• Control of daily connections of users to the organization’s servers, including terminal ones.
• Registration of an event, both from domain users, and from local.
• Monitoring user activity (arrival / departure).
• Control of connections of IT departments to IB servers.

First you need to enable auditing and record logging events in Windows. As a domain controller, we are provided with Windows Server 2008 R2. By enabling auditing, you will need to extract, filter, and analyze events defined by the audit policy. In addition, it is assumed to be sent to the third system for analysis, for example, to the DLP. Alternatively, we will consider the possibility of generating a report in Excel.
')
Log analysis is a routine operation of the system administrator. In this case, the volume of recorded events in the domain is such that it is difficult in itself. Audit is included in Group Policy.

Login events are generated by:

1. domain controllers in the process of checking domain accounts;
2. local computers when working with local accounts.

If both categories of policies (user accounts and auditing) are enabled, then logins using the domain account will generate login or logout events on the workstation or server and a logon event on the domain controller. Thus, the audit on domain machines will need to be configured through the GPO snap-in on the controller, the local audit - through the local security policy using the MMC snap-in.

Audit setup and infrastructure preparation:

Consider this stage in detail. To reduce the amount of information we see only successful events of entry and exit. It makes sense to increase the size of the Windows security log. The default is 128 megabytes.

To configure local policy:



Open the editor politician - Start, in the search bar, write gpedit.msc and press Enter.
Open the following path: Local Computer Policy → Computer Configuration → Windows Settings → Security Settings → Local Policies → Audit Policy.

Double click on the group policy setting Audit logon events (login audit) and Audit account logon events (audit login events). In the properties window, set the Success-checkbox to write to the log of successful logins. I do not recommend setting the Failure checkbox to avoid overflow. To apply the policy you need to type in the console gpupdate / force

To configure group policy:

Create a new GPO (group policy) with the name "Audit AD". Go to the edit section and expand the Computer Configuration → Policies → Windows Settings → Security Settings → Advanced Audit Configuration branch. This group policy branch contains advanced audit policies that can be activated on a Windows-based OS to track various events. In Windows 7 and Windows Server 2008 R2, the number of events that can be audited is increased to 53. These 53 audit policies (so-called granular audit policies) are in the Security Settings \ Advanced Audit Policy branch

Configuration and grouped into ten categories:

Include:

• Account Logon - auditing credential verification, Kerberos authentication services, Kerberos ticket operations, and other login events;
• Logon / Logoff - auditing interactive and network login attempts on computers and domain servers, as well as account lockouts.

After the changes, run gpupdate / force.

Immediately specify the types of events that our future script will analyze:

1. Remote access - remote access via RDP session.
2. Interactive - local user login from the console.
3. Computer unlocked - unlocking a locked station.
4. Logoff - logout

In Windows 2008, the successful login event has the ID of Event ID 4624, and the logoff is the Event ID 4672. You need to select a tool that allows you to analyze a huge number of records. It would seem that everything can be written using standard tools. However, requests for Powershell view

get-eventlog security | where {$_.EventId -eq 4624 -and ($_.TimeGenerated.TimeOfDay -gt '08:00:00' )} 

show themselves well only on a stand with a two-user domain controller. In production environments, collecting logs from the server took 20 minutes each. The search for the solution led to the LOG PARSER utility, previously reviewed on Habré.

Data processing speed increased several times, the full run with the formation of a report from one PC was reduced to 10 seconds. The utility uses a lot of command line options, so we will call cmd from powershell to get rid of escaping a heap of special characters. For writing requests, you can use the GUI - Log Parser Lizard. It is not free, but the trial period of 65 days is enough. Below is the request itself. In addition to the ones that interest us, we will also write out other options for entering the system, in case of further use.

 SELECT eventid, timegenerated, extract_token(Strings, 5, '|' ) as LogonName, extract_token(Strings, 18, '|' ) as LogonIP, case extract_token(Strings, 8, '|' ) WHEN '2' THEN 'interactive' WHEN '3' THEN 'network' WHEN '4' THEN 'batch' WHEN '5' THEN 'service' WHEN '7' THEN 'unlocked workstation' WHEN '8' THEN 'network logon using a cleartext password' WHEN '9' THEN 'impersonated logons' WHEN '10' THEN 'remote access' ELSE extract_token(Strings, 8, '|' ) end as LogonType, case extract_token(Strings, 1, '|' ) WHEN 'SERVER$' THEN 'logon' ELSE extract_token(Strings, 1, '|' ) end as Type INTO \\127.0.0.1\c$\AUDIT\new\report(127.0.0.1).csv FROM \\127.0.0.1\Security WHERE (EventID IN (4624) AND extract_token(Strings, 8, '|' ) LIKE '10') OR (EventID IN (4624) AND extract_token(Strings, 8, '|' ) LIKE '2') OR (EventID IN (4624) AND extract_token(Strings, 8, '|' ) LIKE '7') OR EventID IN (4647) AND TO_DATE( TimeGenerated ) = TO_LOCALTIME( SYSTEM_DATE() ) ORDER BY Timegenerated DESC 

Next, I give a general description of the logic:

1. Manually create a list of hosts with a locally configured audit, or specify the domain controller from which we are taking logs.
2. The script walks the list of host names in a loop, connects to each of them, starts the Remote registry service and, using the parser, executes an SQL audit.sql query to collect system security logs. The query is modified for each new host with a primitive regular expression at each iteration. The received data is saved in csv-files.
3. From the CSV files, a report is generated in the Excel file (for beauty and convenience of searching) and the body of the letter in HTML format.
4. A mail message is created separately for each report file and sent to the third system.

Preparing a platform for the test script:

For the script to work correctly, the following conditions must be met:
Create a directory on the server / workstation from which the script runs. We place the script files in the : \ audit \ directory. The list of hosts and the script are in the same directory.

We install additional software on the MS Log Parser 2.2 server and Windows powershell 3.0 as part of the management framework . You can check the Powershell version by typing $ Host.version in the PS console.

We fill in the list of servers for which we are interested in the list.txt for auditing in the : \ audit \ directory by workstation names. Customize the audit policy. Make sure it works.

We check whether the remote registry service is running (the script attempts to start and put the service into automatic mode if it has the appropriate permissions). On servers 2008/2012, this service is running by default.

Check for administrator rights to connect to the system and collect logs.
We check the ability to run unsigned powershell scripts on a remote machine (sign the script or bypass / disable the restriction policy).

Attention to the launch parameters of unsigned scripts - execution policy on the server:
You can bypass the ban by signing the script, or disable the policy itself at startup. For example:

 powershell.exe -executionpolicy bypass -file :\audit\new\run_v5.ps1 

Here is the entire listing of the script:

 Get-ChildItem -Filter report*|Remove-Item -Force $date= get-date -uformat %Y-%m-%d cd 'C:\Program Files (x86)\Log Parser 2.2\' $datadir="C:\AUDIT\new\" $datafile=$datadir+"audit.sql" $list=gc $datadir\"list.txt" $data=gc $datafile $command="LogParser.exe -i:EVT -o:CSV file:\\127.0.0.1\c$\audit\new\audit.sql" $MLdir= [System.IO.Path]::GetDirectoryName($datadir) function send_email { $mailmessage = New-Object system.net.mail.mailmessage $mailmessage.from = ($emailfrom) $mailmessage.To.add($emailto) $mailmessage.Subject = $emailsubject $mailmessage.Body = $emailbody $attachment = New-Object System.Net.Mail.Attachment($emailattachment, 'text/plain') $mailmessage.Attachments.Add($attachment) #$SMTPClient.EnableSsl = $true $mailmessage.IsBodyHTML = $true $SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, 25) #$SMTPClient.Credentials = New-Object System.Net.NetworkCredential("$SMTPAuthUsername", "$SMTPAuthPassword") $SMTPClient.Send($mailmessage) } foreach ($SERVER in $list) { Get-Service -Name RemoteRegistry -ComputerName $SERVER | set-service -startuptype auto Get-Service -Name RemoteRegistry -ComputerName $SERVER | Start-service $pattern="FROM"+" "+"\\$SERVER"+"\Security" $pattern2="report"+"("+$SERVER+")"+"."+"csv" $data -replace "FROM\s+\\\\.+", "$pattern" -replace "report.+", "$pattern2"|set-content $datafile <# #IF WE USE IP LIST $data -replace "FROM\s+\\\\([0-9]{1,3}[\.]){3}[0-9]{1,3}", "$pattern" -replace "report.+", "$pattern2"|set-content $datafile #> cmd /c $command } cd $datadir foreach ($file in Get-ChildItem $datadir -Filter report*) { #creating excel doc# $excel = new-object -comobject excel.application $excel.visible = $false $workbook = $excel.workbooks.add() $workbook.workSheets.item(3).delete() $workbook.WorkSheets.item(2).delete() $workbook.WorkSheets.item(1).Name = "Audit" $sheet = $workbook.WorkSheets.Item("Audit") $x = 2 $colorIndex = "microsoft.office.interop.excel.xlColorIndex" -as [type] $borderWeight = "microsoft.office.interop.excel.xlBorderWeight" -as [type] $chartType = "microsoft.office.interop.excel.xlChartType" -as [type] For($b = 1 ; $b -le 5 ; $b++) { $sheet.cells.item(1,$b).font.bold = $true $sheet.cells.item(1,$b).borders.ColorIndex = $colorIndex::xlColorIndexAutomatic $sheet.cells.item(1,$b).borders.weight = $borderWeight::xlMedium } $sheet.cells.item(1,1) = "EventID" $sheet.cells.item(1,2) = "TimeGenerated" $sheet.cells.item(1,3) = "LogonName" $sheet.cells.item(1,4) = "LogonIP" $sheet.cells.item(1,5) = "LogonType" $sheet.cells.item(1,6) = "Type" Foreach ($row in $data=Import-Csv $file -Delimiter ',' -Header EventID, TimeGenerated, LogonName, LogonIP, LogonType, Tipe) { $sheet.cells.item($x,1) = $row.EventID $sheet.cells.item($x,2) = $row.TimeGenerated $sheet.cells.item($x,3) = $row.LogonName $sheet.cells.item($x,4) = $row.LogonIP $sheet.cells.item($x,5) = $row.LogonType $sheet.cells.item($x,6) = $row.Tipe $x++ } $range = $sheet.usedRange $range.EntireColumn.AutoFit() | Out-Null $Excel.ActiveWorkbook.SaveAs($MLdir +'\'+'Audit'+ $file.basename.trim("report")+ $date +'.xlsx') if($workbook -ne $null) { $sheet = $null $range = $null $workbook.Close($false) } if($excel -ne $null) { $excel.Quit() $excel = $null [GC]::Collect() [GC]::WaitForPendingFinalizers() } $emailbody= import-csv $file|ConvertTo-Html $EmailFrom = "audit@vda.vdg.aero" $EmailTo = foreach ($a in (Import-Csv -Path $file).logonname){$a+"@"+"tst.com"} $EmailSubject = "LOGON" $SMTPServer = "10.60.34.131" #$SMTPAuthUsername = "username" #$SMTPAuthPassword = "password" $emailattachment = "$datadir"+"$file" #$$filexls send_email } 

For full-fledged reports in Excel, we install Excel on the station / server with which the script works.

We add a script to the Windows scheduler for daily execution. The optimal time is the end of the day - the search for events is conducted in the last 24 hours.

Event search is possible, starting with Windows 7, Windows server 2008.
Earlier Windows have other event codes (code value is 4096 less).

Notes and conclusion:

Once again, summarize the actions performed:

1. Configured local and domain audit policies on the servers you need and collected a list of servers.
2. We chose a machine to run the script, installed the necessary software (PS 3.0, LOG PARSER, Excel).
3. Wrote a request for LOG PARSER.
4. We wrote a script that runs this query in a loop for the list ,.
5. Wrote the rest of the script, processing the results.
6. Set up a daily scheduler.

Previously generated reports are in the directory until the next script execution. The list of users who made the connection is automatically added to the recipients of the letter. This is done for correct processing when sending a report to the analysis system. In general, thanks to LOG PARSER, it turned out quite powerful, and, perhaps, the only means of automating this task. Surprisingly, such a useful utility with extensive capabilities is not widely distributed. The disadvantages of the utility include weak documentation. Requests are performed by trial and error. I wish you successful experiments!