Ipswitch WS_FTP ftp logs export

Hi everybody, hope you enjoy reading our blog! This time I will write about Ipswitch WS_FTP ftp server and how did we extract logs from it.

One of our customer has very old ws_ftp setup (ver 7.1) and they decided to perform migration to another FTP server solution. As I told before, it is very old setup with 200+ users, who are not documented. Ftp is extensively used, so customer decided to analyze logs for last year and maybe clean some of the users and inform others about future changes in FTP setup. There are 2 ways to get a logs:

  • From PostgreSQL database
  • From log viewer built into WS_FTP

Unfortunately, the password from PostgreSQL was unknown so the only possible way for us was to use log viewer. The only export format for log viewer is xml. It is not user-friendly and does not allow regular user to make any filtering. In addition exported xml was about 4.5Gb. So not possible to open with excel. Ms Access with some stupid errors, even when I split file to parts


So I decided to export data to MS SQL Server table and convert it to flat format from xml. Here is an example on how xml looked

<?xml version=”1.0″ encoding=”utf-8″ ?>
<description><![CDATA[Timeout session:  605,109375 secs inactivity, Login state]]></description>
<type>0</type>    <severity>1</severity>
<params><![CDATA[MLSD ]]>

Therefore, I made an empty database and a table inside

create table ftplog (

[log_time] nvarchar (17),

[description] nvarchar (100),

[user] nvarchar (30),

[host] nvarchar (20),

[lstnconnaddr] nvarchar (30),

[cliconnaddr] nvarchar (30),

[cmd] nvarchar (30))


The length of the fields can be different, especially for “description”, however I used max values to cover every possible case

I also made a query to import a data from xml file to table so it is written as table, but not xml

use FTPdatabase


DECLARE @messagebody XML


SELECT @messagebody = BulkColumn

FROM OPENROWSET(BULK ‘C:\year.xml’, SINGLE_CLOB) AS X –change to your path


INSERT INTO [ftplog]

select  a.value(N'(./log_time)[1]’, N’nvarchar (17)’) as [log_time],

a.value(N'(./description[1])’, N’nvarchar (30)’)) as [description],

a.value(N'(./user[1])’, N’nvarchar (30)’) as [user],

a.value(N'(./host[1])’, N’nvarchar (30)’) as [host],

a.value(N'(./lstnconnaddr[1])’, N’nvarchar(30)’) as [lstnconnaddr],

a.value(N'(./cliconnaddr[1])’, N’nvarchar (30)’) as [cliconnaddr],

a.value(N'(./cmd[1])’, N’nvarchar (30)’) as [command]

from @messagebody.nodes(‘/log/entry’) as r(a);

This query is designed for this specific xml structure, so for different xml it need to be changed. As you can see I skip some unnecessary fields from xml, like sessionid, host, sguid and so on.

After the first run, I got an error message that it cannot read some symbols, sure, it cannot! FTP was installed on Swedish, and I had different settings on my laptop, so I had to change language for non Unicode programs to be Swedish and restart laptop. By the way, before laptop I was trying to import data to SQL Server installed on virtual machine, which is used quite a lot by different people. Query was running for years, so I decided to use my laptop with SSD and 12gigs of RAM.


On the small files import query was running fine, but when I started it against xml with data for the last year it gave me an error message

Msg 9420, Level 16, State 1, Line 5

XML parsing: line 42131402, character 49, illegal xml character


It is not possible to view 4Gb file in notepad. First I tried to use powershell, but it took whole memory and was running for years. Therefore I made a vbs script to read a line with specific number

Set objFSO=CreateObject(“Scripting.FileSystemObject”)

‘ Put your path to xml here

filename = “C:\Desktop\FTP\year.xml”


Set fso = CreateObject(“Scripting.FileSystemObject”)

Set f = fso.OpenTextFile(filename)


for k = 1 to 42131400   ‘ In my case error happened on line 42131400 and I was not sure how vbs count the ‘strings so I did output for 2 strings prior to 42131402

strline = f.ReadLine



wscript.echo “String with number: ” & k & “has value” & “strline”

strline = f.ReadLine

wscript.echo “String with number: ” & k+1 & “has value” & “strline”

strline = f.ReadLine

wscript.echo “String with number: ” & k+2 & “has value” & “strline”



Unfortunately, even with this tool I was not able to identify the problem. I was able to read a string, but not to identify what is wrong with it. Here is what I got reading the string. Yeah, there are some Unicode symbols, but anyway it should be possible to read them. Here is example of string output from script:

<description><![CDATA[Unknown command : ŸÔŸÝ]]></description>

After googling a bit I found out that it might be a problem with unreadable Unicode table characters (1-32). So I made another vbs script to remove such characters from my source file.

Set objFSO=CreateObject(“Scripting.FileSystemObject”)

‘ How to write file

outFile=”C:\FTP\fixed1.xml”   ‘ Keep results in this file

Set objFile = objFSO.OpenTextFile(outFile,2)

filename = “C:\Desktop\FTP\year.xml” ‘ Source file


Set fso = CreateObject(“Scripting.FileSystemObject”)

Set f = fso.OpenTextFile(filename)

i = 0

Do Until f.AtEndOfStream


strline = f.ReadLine ‘ Read each string of the source file

for j=1 to 32              ‘ Change 32 to be 31, so you do not loose spaces

strline=Replace(strline,Chr(j),””) ‘Remove unreadable characters


objFile.WriteLine strLine  ‘ Write fixed string to output file

i = i+1


wscript.echo “Number of strings is: ” & i



After cleaning a file I got error message from SQL

Msg 9421, Level 16, State 1, Line 5

XML parsing: line 1, character 13, illegal name character


Using the first vbs script I red first 3 lines and they looked exactly fine




However, after bit more research I discovered that the first string is missing spaces (unreadable character with code 32 that was removed by vbs script. I was not worried about spaces anywhere so just added them to the first string

<?xml version=”1.0″ encoding=”utf-8″ ?>


I tried running SQL import batch again and it still was not working with error message

Msg 9420, Level 16, State 1, Line 5

XML parsing: line 1461998912, character 29, illegal xml character

Did not feel comfortable with reading strings with vbs script and spending so much time on that, so I did some research and it appeared that there are quite a lot of nice tools who can read huge text files.

One of them is EmEditor was fit for my needs with fully functional 30 days trial. So now I was able to open and browse huge xml file using nice GUI interface!!!

During file opening got an error message that some characters cannot be converted using the specified encoding.


Wow, this tool saved hours for me and I was able to identify problematic symbol, it appeared to be �

Using EmEditor I replaced this symbol with nothing and saved changes


Finally, I was able to run SQL import query and after 30 minutes I got a table containing 6+ million records ready for queries and sorting!

For customer I exported same xml file to MS Access database table. Compressed it consumed just 60Mb

Hope you enjoyed this long story. I do not think that someone will need to reproduce all of my steps, but some of them might be useful for different purpose. Looking forward for your feedbacks and do not forget to follow our blog. Promise, soon it will update more often and it will be a lot of interesting content!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s