Analysing Windows Event Logs collected in CSV Format

Recently I wanted to analyze Microsoft Windows server logs collected as CSV files. Logs in default evtx format were not available for the required period and they were extracted from the SIEM as cvs files.

I had to face a challenge of analyzing 20gb logs span across six days. I didn't have enough computation resources to open them through a text editor or import to a Excel/Calc due to the file sizes. Therefore I decided to evaluate them with the use of Ubuntu 18.04 PC.

As the very first step I extracted useful contents based of Event IDs using following commands into individual text files.

$ grep -i "EventID=4625" <log_cvs_file_names> | awk -F"","" '{print $5 "\n"}' > Failed_logins_4625.txt
$ grep -i "EventID=4624" <log_cvs_file_names> | awk -F"","" '{print $5 "\n"}' > Logon_4624.txt

Some of the useful event IDs to check when evaluating logs for suspicious activities are listed below with a star mark.

Event ID    What it means
---------------------------------------
*4624    Successful account log on
*4625    Failed account log on
*4634    An account logged off
*4648    A logon attempt was made with explicit credentials
*4647    User initiated logoff
*4778    RDP Session Reconnect (A session was reconnected to a Window Station)
*4779    RDP Session Disconnect (A session was disconnected from a Window Station)
*1102    Audit log was cleared. This can relate to a potential attack
*4720    A user account was created
*4722    A user account was enabled
*4723    An attempt was made to change the password of an account
*4725    A user account was disabled
*4738    A user account was changed
*4740    A user account was locked out
*4767    A user account was unlocked
*4782    Password hash an account was accessed
*4697    A service was installed in the systems
*4688    A new process has been created
*4689    A process has exited
*5140    A network share object was accessed

*4672: Special privileges assigned to new logon

4800     Locked (The workstation was locked)
4801    Unlocked (The workstation was unlocked)
4670    Permissions on an object were changed
4719    System audit policy was changed.
4964    A special group has been assigned to a new log on
4728    A user was added to a privileged global group
4732    A user was added to a privileged local group
4756    A user was added to a privileged universal group
4735    A privileged local group was modified
4737    A privileged global group was modified
4755    A privileged universal group was modified
4772    A Kerberos authentication ticket request failed
4777    The domain controller failed to validate the credentials of an account.
4616    System time was changed
4657    A registry value was changed
4697    An attempt was made to install a service
4698, 4699, 4700, 4701, 4702    Events related to Windows scheduled tasks being created, modified, deleted, enabled or disabled
4946    A rule was added to the Windows Firewall exception list
4947    A rule was modified in the Windows Firewall exception list
4950    A setting was changed in Windows Firewall
4954    Group Policy settings for Windows Firewall has changed
5025    The Windows Firewall service has been stopped
5031    Windows Firewall blocked an application from accepting incoming traffic
5152, 5153    A network packet was blocked by Windows Filtering Platform
5155    Windows Filtering Platform blocked an application or service from listening on a port
5157    Windows Filtering Platform blocked a connection
5447    A Windows Filtering Platform filter was changed

Once the logs pertaining to selected event IDs are extracted the process was simple as I was left with set of logs files with the size of few MBs.

While going through them when a suspicious event is detected, I analyzed the original logs around the identified timestamps to further dig down in to the incident.

Some of the useful tips I used to trace suspicious activities are also listed below.

Successful RDP sessions the login type would be Type 10. However for failed RDP login attempts the type would be 3. Useful Queries when searching for failed attempts on Windows logs CSV.

$ grep "192.168.64.189" <log_file_name> |grep -i "EventID=4625" | awk -F"","" '{OFS="  |  "}{print $5 "\n\n"}' > Failed_logins_192.168.64.189.txt

To check successful logoffs from IP Address 192.168.13.90.
$ grep "192.168.13.90" <log_file_name> | grep -i "EventID=4647/4634" | awk -F"","" '{print $5}'

Check for account session parameters. Trace account activity.

$ grep "Logon ID:  0x653$F7CF" <log_file_name> | awk -F"","" '{print $5 "\n"}'