Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I've been using the Striving Life website for his IIS FTP LogParser 2.2 queries. My only issue is that I can't seem to combine the two queries that bring back both successful login AND their account name. Can anyone help?

  • This brings back the users that have logged in

    logparser "select cs-uri-stem, count(cs-method) from ex*.log where cs-method like '%USER' group by cs-uri-stem order by count(cs-method),cs-uri-stem"

  • This brings back all of the successful logins by IP

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

As I said, I would like to have the best of both worlds. Thanks ahead of time!

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
I don't actually know if you could have been MORE helpful. Thank you. –  breadly Aug 5 '10 at 1:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.