Iis – TO_TIMESTAMP(date, time) missing seconds

Okay.

So let's break it up into it's smaller parts, based upon what data we want and that we're looking at.

That gives us the following as a base:

logparser "select cs-uri-stem, cs-method, c-ip from \\192.168.1.104\wwwroot\ex*.log"

Based on our conditions, we have the following then:

logparser "select cs-uri-stem, cs-method, c-ip, sc-status from \\192.168.1.104\wwwroot\ex*.log where cs-method like '%USER'"

logparser "select cs-uri-stem, cs-method, c-ip, sc-status from \\192.168.1.104\wwwroot\ex*.log where sc-status = '230'"

Now if you do this you can see the problem; cs-method like '%USER' returns a sc-status of 331, so if we attempt to combine them, that just isn't going to work.

Status code 230 is 'user has logged in, proceed' - http://support.microsoft.com/kb/318380 - while 331 is just saying the username is okay.

So let's step back and actually look at a log for when a user logs in. The cs-username, since they're authenticated, will be populated with the name of the account they're using.

logparser "select cs-username, cs-method, c-ip, sc-status from \\192.168.1.104\wwwroot\ex*.log where sc-status = '230'"

Which means this should be what we want:

logparser "select cs-username, c-ip, count(*) from \\192.168.1.104\wwwroot\ex*.log where sc-status = '230' group by cs-username, c-ip order by count(*), cs-username, c-ip"

Based on a scan of a file - http://media.jamesrskemp.com/articles/IIS6FTPSample.log.txt - that looks to be correct.

Does that help?

And looking at my post again, the key part that was missed was that the first helps "determine what user names were used to login, or attempt to login, to an FTP site." If you search for FTPUser3 in my sample log above, you'll see a 331 is returned by IIS, even though I didn't create the FTPUser3 account. Seemingly for security purposes, it seems IIS 6 FTP returns 331 either way.

Have you checked the ' -iCheckPoint' switch? It stores a timestamp of the last run and only accesses subsequent records.