MS Log Parser 2.2 Query Error

Question

I am trying to determine if a user downloaded a file from FTP using MS Log Parser 2.2

I have not been able to get parser SQL query going, although I have used several samples queries.

Water Down Parser Query does not work:

strSQL = "SELECT date,COUNT(*) AS downloads,c-ip "
strSQL = strSQL & "FROM C:\temp\Log\*.log "
strSQL = strSQL & "WHERE cs-method='RETR' "
strSQL = strSQL & "GROUP BY date,c-ip "

Error:

RecordSet cannot be used at this time [Unknown Error]

Question:

How do I create a query:

 - SELECT Date and Time of download
 - Where user = 'xxx' 
 - WHERE RETR = is a download
 - WHERE Filename = u_ex150709.log or xxx

Answers in C# are also welcome

VB.net Code:

Dim rsLP As ILogRecordset = Nothing
Dim rowLP As ILogRecord = Nothing

Dim LogParser As LogQueryClassClass = Nothing
Dim W3Clog As COMW3CInputContextClassClass = Nothing

Dim UsedBW As Double = 0
Dim Unitsprocessed As Integer

Dim strSQL As String = Nothing

LogParser = New LogQueryClassClass()
W3Clog = New COMW3CInputContextClassClass()

Try

strSQL = "SELECT date,COUNT(*) AS downloads,c-ip "
strSQL = strSQL & "FROM C:\temp\Log\*.log "
strSQL = strSQL & "WHERE cs-method='RETR' "
strSQL = strSQL & "GROUP BY date,c-ip "

'run the query against W3C log
rsLP = LogParser.Execute(strSQL, W3Clog)

'Error occurs in the line below
rowLP = rsLP.getRecord()

Answer 1

Just like you I've written tools that leverage LogParser, eg http://eventanalyser.appointmentsbook.com/

Though back in 2004 (using .Net 1.1) I didn't have the benefit of downloading: https://visuallogparser.codeplex.com/

Check their source code, get your query working in it (VisualLogParser) and then simply reference it in your project and enjoy the open source community goodness.

As for your query regarding FTP leeching, here is the MSDN article: http://blogs.msdn.com/b/robert_mcmurray/archive/2010/09/02/detecting-ftp-leeches-with-logparser.aspx

SELECT date,COUNT(*) AS downloads,c-ip,x-session
FROM *.log
WHERE cs-method='RETR'
GROUP BY date,c-ip,x-session
HAVING COUNT(*) > 100

One thing does stand out about your query when looking at the one's I created a GUI to dynamically create, you're missing single quotes around the file path:

strSQL = strSQL & "FROM C:\temp\Log\*.log "

Try this:

strSQL = strSQL & "FROM 'C:\temp\Log\*.log' "

(and use a StringBuilder, not string concatenation... just to get in the habit of best practice)

As per:

If the quotes don't solve the problem first go, then try a single log file rather than the wildcard *.log to narrow down on the syntax error. LogParser isn't designed to be helpful at diagnosing problem queries, instead Gabriele Giuseppini designed it to be fast, very fast!