Extracting Data from XML by Python

I usually executed md5deep64.exe with “-d” parameter to create the result as XML format includes both file full path and MD5 value.

C:> md5deep64.exe -r -d * > C:\%COMPUTERNAME%_%DATE%.xml

The XML file that is the result of the command above shows like this as below.

<?xml version='1.0' encoding='UTF-8'?>
<dfxml xmloutputversion='1.0'>
<metadata
xmlns='http://md5deep.sourceforge.net/md5deep/'
xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'
xmlns:dc='http://purl.org/dc/elements/1.1/'>
<dc:type>Hash List</dc:type>
</metadata>
<creator version='1.0'>
<program>MD5DEEP</program>
<version>4.3</version>
<build_environment>
<compiler>GCC 4.7</compiler>
</build_environment>
<execution_environment>
<command_line>c:\temp\md5deep64.exe -r -d *</command_line>
<start_time></start_time>
</execution_environment>
</creator>
<configuration>
<algorithms>
<algorithm name='md5' enabled='1'/>
<algorithm name='sha1' enabled='0'/>
<algorithm name='sha256' enabled='0'/>
<algorithm name='tiger' enabled='0'/>
<algorithm name='whirlpool' enabled='0'/>
</algorithms>
</configuration>
<fileobject>
<filename>C:\bootmgr</filename>
<filesize>398356</filesize>
<ctime></ctime>
<mtime></mtime>
<atime></atime>
<hashdigest type='MD5'>55272fe96ad87017755fd82f7928fda0</hashdigest>
</fileobject>
<fileobject>
<filename>C:\BOOTNXT</filename>
<filesize>1</filesize>
<ctime></ctime>
<mtime></mtime>
<atime></atime>
<hashdigest type='MD5'>93b885adfe0da089cdf634904fd59f71</hashdigest>
</fileobject>
</dfxml>

To extract md5 and filepath from the XML, we can use minidom python library.

from xml.dom import minidom
xmldoc = minidom.parse(fn)
files = xmldoc.getElementsByTagName('fileobject')
for fileobject in files:
  fn = fileobject.getElementsByTagName('filename')[0]
  md5 = fileobject.getElementsByTagName('hashdigest')[0]
  print fn.firstChild.data +", "+ md5.firstChild.data

Once we execute the python code to parsing a huge XML, however, we can easily meet Memory Error. To avoid this kind of error, I used BeautifulSoup.

from bs4 import BeautifulSoup
fp = open(fn, 'r')
soup = BeautifulSoup(fp, 'xml')
for node in soup.findAll('fileobject'):
  try:
    print "%s, %s"%(node.hashdigest.string,node.filename.string)
  except UnicodeEncodeError as e:
    continue

The whole code is uploaded at my GitHub.
https://github.com/hojinpk/CodeSnippets/blob/master/extracting_md5_from_XML.py

Huge Syslog Archive MySQL file

I or maybe you have a problem there is no disk space due to MySQL database on Security Onion Sensor Server. Upon investigating, I realized a table is huge around 34GB.

$ find / -type f -name '*.ARN' -size +1024M -ls
524481 36073464 -rw-rw---- 1 mysql mysql 36939788428 May 26 12:41 /var/lib/mysql/syslog_data/syslogs_archive_1004053.ARN

The table is related to ELSA including MySQL, Sphinx and syslog-ng.

To delete the huge file elegantly, we can use a script, /usr/bin/securityonion-elsa-reset-archive, but the target table of the script is fixed to ‘syslogs_archive_1’. So you can use the script after replacing the table name to found one above or type some commands directly as below.

$ mysql --defaults-file=/etc/mysql/debian.cnf syslog_data \
 -e "DROP TABLE syslog_data.syslogs_archive_1004053"
$ mysql --defaults-file=/etc/mysql/debian.cnf syslog_data \
 -e "DELETE FROM syslog.tables \
      WHERE table_name='syslog_data.syslogs_archive_10024053'"
$ rm /var/lib/mysql/syslog_data/syslogs_archive_1004053.ARN

If you want to remove all of tables, whose name is start with ‘syslogs_archives_1’, you can utilize the sql below.

SELECT CONCAT('DROP TABLE ', GROUP_CONCAT(table_name), ';') as statement 
  FROM information_schema.tables
 WHERE table_name LIKE 'syslogs_archive_1%'

Basically, you need to adjust a value of retention_days of ‘/etc/elsa_node.conf’ because the huge table belongs to ELSA.

BTW, the reason why the table in question became huge is still remain. I just tried delete it as above. Sorry for that.

Create Windows Task Scheduler

This page describes how to create a Windows task scheduler with proper user. It means that the user doesn’t need to have the unused permission. For example, console logon both locally and remotely.

1. Press + R, type “taskschd.msc” and press Enter
2. Create the task in the Task Scheduler as planned.
3. Select the “Run whether user is logged on or not” radio button.
4. Check the “Do not store password” checkbox.
5. Check the “Run with highest privileges” checkbox.
6. Assign the task to run under the new user account.
   1. Refer to Create a local account without console logon.

 

Update

1. Once “Do not store password…” ticked, you could meet an error #2147943711.

Moving MySQL Database Folder

By default, MySQL database is stored on ubuntu on “/var/lib/mysql/” folder according to my.cnf. If you want to move the stored folder to another(e.g., /nsm/mysql/), you can refer the script below.

$ service mysql stop
$ cp -avrp /var/lib/mysql /nsm/
$ chown -R mysql:mysql /nsm/mysql/
$ vi /etc/mysql/my.cnf
– datadir=/var/lib/mysql
+ datadir=/nsm/mysql
$ service mysql start

If there is anything for restricting the MySQL database file or folder, you need to re-configure it such as SELinux or AppArmor(“Application Armor”).

$ vi /etc/apparmor.d/usr.sbin.mysqld
– /var/lib/mysql/ r,
– /var/lib/mysql/** rwk,
+ /nsm/mysql/ r,
+ /nsm/mysql/** rwk,
$ /etc/init.d/apparmor restart

Windows Event Log Setting

As you might know, Windows Event Log is not properly logged by default setting. Once you meet a computer incident, if you want to get valuable information from Windows, you need to adjust configuration. Here is a script I used.

auditpol /set /category:"Account Logon" /success:enable /failure:enable
auditpol /set /category:"Account Management" /success:enable /failure:enable
auditpol /set /category:"Detailed Tracking" /success:enable /failure:enable
auditpol /set /category:"DS Access" /success:enable /failure:enable
auditpol /set /category:"Logon/Logoff" /success:enable /failure:enable
auditpol /set /category:"Object Access" /success:enable /failure:disable
auditpol /set /category:"Policy Change" /success:enable /failure:enable
auditpol /set /category:"Privilege Use" /success:enable /failure:enable
auditpol /set /category:"System" /success:enable /failure:enable
auditpol /set /subcategory:"Filtering Platform Connection" /success:disable /failure:disable
auditpol /set /subcategory:"Filtering Platform Packet Drop" /success:disable /failure:disable

I disabled the failure of Object Access and regarding filtering platform, because these generates a lot of logs unnecessarily. Of course, if you afford to store these kind of logs, you can enable them.

 

Load Balancing on Syslog-ng

When you want to send logs to specific logging servers at least two more to be load balancing, you can use Syslog-ng with the configuration as below. Deciding server where you send a log is depended on the last digit number of the system uptime. Here is a sample Syslog-ng configuration file for aforementioned the load balance.

filter f_0 {
   match("[0-9]*[02468]$" value("SYSUPTIME"));
};

filter f_1 {
      match("[0-9]*[13579]$" value("SYSUPTIME"));
};

log { source(s_fw); filter(f_0); destination(d_redis0); };
log { source(s_fw); filter(f_1); destination(d_redis1); };

 

Forensically Sound Windows

I called “Forensically Sound Environment” as an operating system is good at logging or tracing for analysis to a specific case in terms of forensic or incident response. Here are some configurations to make forensically sound environment.

Prerequisite

1. Sync date and time to localtime or UTC

Windows

Most of configurations are Windows artifacts. Of course, it’s included that application such as IIS, Windows Advanced Firewall ans so on, however we can’t get any evidence from them unless you set those configurations properly like forensically sound environment.

1. EnablePrefetcher
2. NtfsDisableLastAccessUpdate
3. $LogFile
4. Windows Recovery
5. Minidump
6. Windows Event Log
7. WinRM service
8. Event Tracing for Windows
9. C:\> openfiles /local on
10. Windows Advanced Firewall
11. Application Log
    1. IIS
    2. Anti-Virus
    3. Adobe Reader

Linux

1. Auditd
2. iptables logging

Post Task

1. Build MD5 white-list
2. Logging remotely

I will describe about each title as above in more detail. And I’ll put the link on the title on this page. I hope that I want to make the list in question with you.

Create a local account without console logon

The goal of this manual is to assign a user to necessary privilege(s) to do the purpose. For instance, you can refer to this page when create an account for file copy, local script execution as well as ftp user.

1. Create a user as local account

• GUI: lusrmgr.msc
• CLI:
  • net user /ADD [username] [password]  /PASSWORDCHG:no
  • WMIC USERACCOUNT WHERE “Name='[username]'” SET PasswordExpires=FALSE

2. The user is only included Users group

The user created just before is automatically a member of Users group. So we need to exclude the user in Users group. After that, add the user into a new group, which will be authorized to execute as a bactjob only.

• GUI: lusrmgr.msc
• CLI:
  1. net localgroup Users [username] /delete
  2. net localgroup CLIUsers /add
  3. net localgroup CLIUsers [username] /add

3. The user is not allowed to logon both locally and remotely

The new created group, CLIUsers, should not be allowed to log on both locally and remotely. Here is a way to check out.

3.1 Locally

Check if the ‘CLIUsers’ group is allowed to log on locally. The ‘CLIUsers’ group should not be included in “Allow log on locally” policy.

• GUI: secpol.msc / gpedit.msc
  [Computer Configuration –> Windows Settings –> Security Settings –> Local Policies –> User Rights Assignment –> Allow log on locally(로컬 로그온 허용)]

3.2 Remotely

Check if the ‘CLIUsers’ group is allowed on to log on through remote desktop services.

• GUI: gpedit.msc
  The CLIUsers group should not be included in the remote logon right at [Computer Configuration –> Windows Settings –> Security Settings –> Local Policies –> User Rights Assignment –> Allow log on through Remote Desktop Services(원격 테스크톱 서비스를 통한 로그온 허용)]

When you’re trying to logon on local console after the configuration as above, you can see the error message as below.

3.3 Allow “Log on as a batch job”

• GUI: gpedit.msc
  Add “CLIUsers” group into “Log on as a batch job(일괄 작업으로 로그온)” at [Computer Configuration –> Windows Settings –> Security Settings –> Local Policies –> User Rights Assignment]

Now you can use the new account for a local service, such as task scheduler, in accordance with Principle of least privilege.

Case Study: Security Onion Server Disk Full

잘 동작하던 Security Onion(SO)가 갑지가 동작하지 않을 때 수 많은 이유가 있겠지만, HDD DISK가 가득차 동작하지 않는 경우라면 이 글이 도움될 수 있겠네요.

이 글을 읽기 전에 아래와 같은 명령어로 디스크 사이즈를 줄여보셨나요?! 적어도 필자는 별 효과가 없었습니다. 왜냐하면 일반적인 파일 문제가 아니라 sguil이 사용하는 MySQL의 securityonion_db에 엄청난 데이터가 쌓여있기 때문 입니다.

$ du -h –max-depth=1 /  (하위 폴더별 디스크 사용량)
$ truncate -s 0 /var/log/*log (모든 log 파일 내용 지우기)
$ find /var/log -type f -name ‘*.gz’ -exec rm {} \; (모든 gz 파일 삭제하기)
$ find / -size +2048M -ls (2GB 보다 큰 파일 찾기)

Sguil Database

SO 운영자가 아직 최적화(Optimizing)를 하지 않고 설치 시 기본 값으로만 SO를 운영할 경우 금방 DISK가 차버릴 수 있습니다. 로그 저장 능력과 관련해 기본값은 아래와 같이 90일로 설정되어 있었는데, 필자의 경우 설치 후 운영한지 이틀만에 DISK FULL 되었습니다. 그래서 /etc/nsm/securityonion.conf 에서 DYASTOKEEP 과 DAYSTOREPAIR를 각각 7일과 3일로 변경 했습니다. (우선은 운영을 해야 하겠기에..ㅠㅠ)

-DAYSTOKEEP=90
+DAYSTOKEEP=7
-DAYSTOREPAIR=7
+DAYSTOREPAIR=3

그런데 아직 위 옵션들이 적용되지 않았습니다. 그 이유는 이런 옵션들은 sguil-db-purge 에 의해서 참조되기 때문에, sguil-db-purge를 별도로 실행시켜줘야 합니다.

Network Packet Capture Files

캡쳐된 Raw 패킷은 분석할 때 유용하게 사용되지만, 네트워크 볼륨에 비래한 저장공간을 필요로 하기 때문에 관련된 옵션을 잘 파악하여 운영의 묘를 살려야 하겠습니다. 기본적으로 저장되는 곳은 /nsm/sensor_data/eth0/dailylogs/YYYY-MM-DD/snort.log.*  와 같은 패턴으로 tcpdump 파일이 기록됩니다. 아래 명령어들은 이와 같은 파일들이 기록되지 않도록 합니다.

$ sed -i ‘s|PCAP_ENABLED=”yes“|PCAP_ENABLED=”no“|g’ /etc/nsm/*/sensor.conf
$ sed -i ‘s|PCAP_AGENT_ENABLED=”yes“|PCAP_AGENT_ENABLED=”no“|g’ /etc/nsm/*/sensor.conf
$ nsm_sensor_ps-stop –only-http-agent
$ chmod 0 /usr/bin/netsniff-ng