Author Posts

February 25, 2016 at 3:43 pm

Hello,

I'm hoping to get some help with the script below. I have been asked to create a script to parse through 1GB FTP log files and get the last successful logon data/time for each unique user account.

I feel like on close to achieving what is need based on the output below. However, while I get the unique user accounts, I'm not getting the last successful date/time for most of them.

The script is below. Once it works like needed, I'll covert it into a function to be reused for other logs.

Thank you in advanced for your help.

Luis Carrillo

Truncated SAMPLE LOG:

2016-02-22 02:01:00 [2/3060/15fc] User "UserAccount2" authenticated successfully.
2016-02-22 02:01:01 [2/3060/15fc] COMMAND: SFTP->SSH_FXP_INIT Client Protocol=3/0x3
2016-02-22 02:01:01 [2/3060/15fc] Client supplied protocol version 3, we support version 3, negotiated version 3
2016-02-22 02:01:01 [2/3060/15fc] SFTP->SSH_FXP_INIT Negotiated Protocol=3/0x3
2016-02-22 02:01:01 [2/3060/15fc] Checking Path: "."
2016-02-22 02:01:01 [2/3060/15fc] COMMAND: SFTP->SSH_FXP_REALPATH request-id=1, szInPath="."
2016-02-22 02:01:07 [2/2692/ce8] Using "password" authentication for incoming user "UserAccount2"
2016-02-22 02:01:07 [2/2692/ce8] Checking for user existance: Username="UserAccount2"
2016-02-22 02:01:07 [2/2692/ce8] Trying to find user:UserAccount2
2016-02-22 02:01:07 [2/2692/ce8] Found user "UserAccount2"
2016-02-22 02:01:07 [2/2692/ce8] Check to see if the user's account has expired
2016-02-22 02:01:07 [2/2692/ce8] returning 331 to request a password for this user
2016-02-22 02:01:07 [2/2692/ce8] User "UserAccount3" authenticated successfully.
2016-02-22 02:01:07 [2/2692/ce8] COMMAND: SFTP->SSH_FXP_INIT Client Protocol=3/0x3
2016-02-22 02:01:07 [2/2692/ce8] Client supplied protocol version 3, we support version 3, negotiated version 3
2016-02-22 02:01:07 [2/2692/ce8] SFTP->SSH_FXP_INIT Negotiated Protocol=3/0x3
2016-02-22 02:01:07 [2/2692/ce8] Checking Path: "."
2016-02-22 02:03:53 [2/3272/edc] Using "password" authentication for incoming user "UserAccount3"
2016-02-22 02:03:53 [2/3272/edc] Checking for user existance: Username="UserAccount3"
2016-02-22 02:03:54 [2/3272/edc] Trying to find user:UserAccount3
2016-02-22 02:03:54 [2/3272/edc] Found user "UserAccount3"

SCRIPT:

$logpath = "C:\Users\lcarrillo\Desktop\20160221-181154.log"
$outputpath = "C:\Users\lcarrillo\Desktop\log2.csv"
$strpattern = 'authenticated successfully'
$logfile = gc -Path $logpath

$newlog = $logfile | Select-String -Pattern $strpattern
$newlog | ConvertFrom-String |
Select @{l='Date';e={$_.P1 + $_.P2}},
@{l='User';e={$_.P5}},
@{l='Status';e={$_.P7}} | sort Date -Descending | sort user -Unique

__________________________________________________________________
### CONSOLE OUTPUT BELOW:

Date User Status
—- —- ——
2/22/2016 6:03:24 AM "UserAccount1" successfully.
2/23/2016 10:35:54 AM "UserAccount2" successfully.
2/23/2016 1:35:54 PM "UserAccount3" successfully.
2/23/2016 1:16:35 AM "UserAccount4" successfully.
2/22/2016 4:34:03 AM "UserAccount5" successfully.
2/22/2016 5:08:16 AM "UserAccount6" successfully.
2/23/2016 1:17:26 AM "UserAccount7" successfully.
2/22/2016 2:03:45 PM "UserAccount8" successfully.
2/22/2016 2:00:03 AM "UserAccount9" successfully.
2/22/2016 3:31:36 PM "UserAccount10" successfully.
2/22/2016 8:31:01 AM "UserAccount11" successfully.
2/23/2016 12:55:46 PM "UserAccount12" successfully.
2/23/2016 6:58:16 AM "UserAccount13" successfully.
Etc...

February 25, 2016 at 6:23 pm

I recommend using -readcount or OpenText methods to read multiple log files. This will export latest info.

#Requires -Version 3.0
# Group successful logins by user
$logfile = Get-Content "C:\Users\lcarrillo\Desktop\20160221-181154.log" -ReadCount 5
$groups = $logfile | ForEach-Object {If ($_ -match 'success'){$_ | 
ConvertFrom-Csv -Header 'date','time','extra',
'user','username' -Delimiter " "}} |
Group-Object -Property username

# Export last successful login
$groups | foreach {$_.Group | Select-Object -Last 1 -Property date,time,username} |
Export-Csv "C:\Users\lcarrillo\Desktop\log2.csv" -NoTypeInformation -Append

February 25, 2016 at 9:02 pm

I'm not entirely sure I understand what the point is of collecting the status since it will always be the same as a result of the match. This was my approach ...

pushd C:\Ephemeral  # my temp directory
$src = ".\20160221-181154.log"
$dst = ".\log2.csv"
$pattern = [regex]"(?'datetime'\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2}).*User \`"(?'user'.+)\`" authenticated successfully"
$log = @{}
Get-Content -Path $src | foreach {
    if ($PSItem -match $pattern)
    {
        # create datetime object
        $timestamp = [datetime]$Matches['datetime']
        $user = $Matches['user']
        if ($log.keys -contains $user)
        {
            # found user, look for latest timestamp
            if ($timestamp -gt $log[$user].Values)
            {
                # update log with latest timestamp
                $log[$user] = $timestamp
            }
        } else {
            # user not previously found, add to the log
            $log.Add($user,$timestamp)
        }
    }
}

$results =  foreach ($item in $log.Keys) {
    [PSCustomObject]@{
        Date = $log[$item]
        User = $item
    }
}

$results | Export-Csv -Path $dst -NoTypeInformation -Encoding ASCII

February 26, 2016 at 9:41 am

**SOLVED**

Thank you both so much for your quick responses.

@random commandline

Random Commandline, I ran your script and its works, however, it outputs more properties than Date, Username and Status. I could try to modify it to grab just what I need.

Thank you for your time.

@Bob McCoy

This is exactly what I needed! Now I just have to convert your script into a function with src/dst parameters.

As for the “successful” status, it was more of a visual confirmation for the customer, but it makes sense to remove it since it's just repetitive.

Again, thank you both for your responses!

Luis Carrillo

February 26, 2016 at 9:51 am

I would be interested to know how it runs with your production data. The input may be processed faster using the StreamReader instead of Get-Content. However, it you are getting what you perceive to be reasonable performance, it may not be worth the additional complexity.

February 26, 2016 at 9:55 am

It should export date,time, and username. I tried it will your sample twice.

February 26, 2016 at 11:21 am

@Bob McCoy

I'll run it against the log files and see how long it takes.

@random commandline, The sample log was only a snippet of the log file. I removed usernames, ip addresses, etc. So this could've been the issue.

When I run your script I get the following:

date time username
2/23/2016 13:35:54 car…..(Real Username found, hidden)
2/21/2016 18:11:54 1648/0x670:
2/23/2016 13:35:54 SFTP->SSH_FXP_INIT
2/23/2016 13:35:54 supplied
2/23/2016 13:35:54 Negotiated
2/23/2016 12:14:55 230-Welcome
2/23/2016 12:14:55 PORT
2/23/2016 12:14:55 IP=########### (Valid IP Address here, hidden)
2/23/2016 12:14:55 200
2/23/2016 12:14:55 NLST
2/23/2016 13:29:00 150
2/23/2016 12:14:55 data
2/23/2016 12:14:55 to
2/23/2016 12:14:55 connected
2/23/2016 13:29:00
2/23/2016 13:29:00 returned
2/23/2016 12:37:34 226
2/23/2016 12:14:55 QUIT

This is what I was looking for:

Bob's script;

Date User
2/23/2016 13:35 car…..
2/23/2016 13:20 pan…..
2/23/2016 12:55 rs…..
2/23/2016 11:41 Jb…..
2/23/2016 10:35 al…..
2/23/2016 8:18 Wo…..
2/23/2016 6:58 WDC…..
2/23/2016 6:08 WDS…..
2/23/2016 6:06 wdh…..
2/23/2016 6:03 ala…..
2/23/2016 5:08 GK…..
2/23/2016 2:01 off…..
2/23/2016 1:18 Cr…..
2/23/2016 1:17 ip…..
2/22/2016 8:31 PG…..

Either way, thank you very much for your help and time.

February 26, 2016 at 11:39 am

Ok, I thought each line of your sample contained usernames, beginning, and end of each line. This is a sample result.
date time username
—- —- ——–
2016-02-22 02:01:00 UserAccount2
2016-02-22 02:01:07 UserAccount3

June 8, 2016 at 6:13 pm

Hello Bob, command line and everyone else.

I am looking for some guidance as to how to parse a temporary master file I created using all the output files generated from the FTP *.logs.

Originally, I needed a script to parse all ftp logs, retrieve the username/datetime for every successful login. Using Bob's original script and with some tweaks to get it to run on #requires version 2 (2003 server), I was able to convert it to a function to be run on-demand or automated (which it is now).

Below is the workflow I've been using (currently works fine):

1. Parse all FTP log files, *.LOG
2. Capture username and datetime –unique, placed into two properties
3. Output the contents above to .txt files
4. Then get-content from newly outputted *.txt files
5. Add content from txt files to one temp master file
6. *WIP Parse temp master file and select latest –unique login (Username and Datetime)

Function Parse-FTPLog:

#requires -version 2
Function Parse-FTPLog {

    [CmdletBinding()]
       
    Param
    (
        # Enter log/txt file location to parse through
        [Parameter(Mandatory=$true, 
                   ValueFromPipeline=$true,
                   ValueFromPipelineByPropertyName=$true, 
                   ValueFromRemainingArguments=$false, 
                   Position=0,
                   ParameterSetName='Parameter Set 1')]
        [ValidateNotNull()]
        [ValidateNotNullOrEmpty()]
        $SourceFile,

        # $DestFile - Output location of csv file with username information
        [Parameter(Mandatory=$true, 
                   ValueFromPipeline=$true,
                   ValueFromPipelineByPropertyName=$true, 
                   ValueFromRemainingArguments=$false, 
                   Position=1,
                   ParameterSetName='Parameter Set 1')]
        [ValidateNotNull()]
        [ValidateNotNullOrEmpty()]
        $DestFile
    )
    
    Begin
    {
        $pattern = [regex]"(?'datetime'\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2}).*User \`"(?'user'.+)\`" authenticated successfully"
    }
    Process
    {

    $log = @{}
    Get-Content -Path $SourceFile | foreach {
        if ($_ -match $pattern)
        {
            # create datetime object
            $timestamp = [datetime]$Matches['datetime']
            $user = $Matches['user']
            if ($log.keys -contains $user)
            {
                # found user, look for latest timestamp
                if ($timestamp -gt $log[$user].Values)
                {
                    # update log with latest timestamp
                    $log[$user] = $timestamp
                }
            } else {
                # user not previously found, add to the log
                $log.Add($user,$timestamp)
            }
        }
    }

    }
    End
    {
        $log | Out-File -FilePath "$DestFile\ActiveUsers-$(Get-Date -f yyyyMMdd_hhmmss).txt" -Encoding ASCII
        
    }
}

#Parse all the FTP log files and filter out last successful logins for each unique account

$logfiles = ls D:\FTP\srtFtpLogs\prddr1\* -Include *.LOG | ?{$_.LastWriteTime -gt (Get-Date).AddDays(-7)}

    foreach ($f in $logfiles){
        Parse-FTPLog -SourceFile $f -DestFile 'D:\FTP\srtFtpLogs\prddr1\Active Users\'
}

#Parse ALL txt files output by the command above.

ls "D:\FTP\srtFtpLogs\prddr1\Active Users\*" -Include *.txt -Force | ?{$_.LastWriteTime -gt (Get-Date).AddDays(-30)} | Get-Content| 
Out-File "D:\FTP\srtFtpLogs\prddr1\Active Users\LatestActiveUsers\temporary_parsed_master_file.txt" 

  • This reply was modified 2 years, 2 months ago by  LuisC.

June 8, 2016 at 6:13 pm

The second function and what I'm looking for help is, how do I apply the same approach as Bob did to capture the latest unique username and datetime for each account in the temp master file. I've added a snippet of what the output would look like including spaces ($sampleoutput)

$sampleoutput = @(
"Name                           Value                                           ",
"----                           -----                                           ",
"onemore-vendor                 5/24/2016 2:48:08 PM                            ",
"CrunchTect                     5/24/2016 1:17:36 AM                            ",
"WDHSAPOS                       5/24/2016 6:08:55 AM                            ",
"FTP_ISD_TWO                    5/24/2016 2:35:51 PM                            ",
"Oneday2016                     5/24/2016 3:02:39 PM                            ",
"offender_one_vendor            5/24/2016 2:27:11 AM                            ",
"wdECM062014                    5/24/2016 3:58:20 AM                            ",

"Name                           Value                                           ",
"----                           -----                                           ",
"offender_one_vendor            5/31/2016 2:23:11 AM                            ",
"cardinalaxway                  5/31/2016 8:59:11 AM                            ",
"FACEVALUE                      5/31/2016 6:58:23 AM                            ",
"CrunchTect                     5/31/2016 1:21:12 AM                            ",
"wdECM062014                    5/31/2016 3:58:19 AM                            ",
"onemore-vendor                 5/31/2016 8:56:02 AM                            ",
"Oneday2016                     5/31/2016 8:20:24 AM                            "
)

#[regex]$RegDate = '(\d{1,2}\/\d{1,2}\/\d{1,4})'
[regex]$RegTimeStamp = '\S+\s([\d])+.{6,9}'
[regex]$RegUser = '^[A-Z-a-z0-9_]+'
[regex]$RegPattern = '\w+.'
$log = @{}

$sampleoutput | % {
    #if PSItem matches a data range of ##/##/####
    if ($_ -match $RegPattern) {
        
        # create datetime object
        $timestamp = $_ | Select-String -Pattern $RegTimeStamp #PROBLEM AREA, it don't know how add the matches to the variable
        $user = $_ | Select-String -Pattern $RegUser #PROBLEM AREA, it don't know how add the matches to the variable
    }
        if ($log.keys -contains $user) {
            # found user, look for latest timestamp
            
            if ($timestamp -gt $log[$user].Values) {
            # update log with latest timestamp
            $log[$user] = $timestamp
            }
        } 
        else {
            # user not previously found, add to the log
            $log.Add($user,$timestamp)
        }
}
    $results =  foreach ($item in $log.Keys) {
        [PSCustomObject]@{
            Date = $log[$item]
            User = $item
        }
    }

$results

LuisC

  • This reply was modified 2 years, 2 months ago by  LuisC.
  • This reply was modified 2 years, 2 months ago by  LuisC.

June 8, 2016 at 6:14 pm

If I run

 select-string –pattern $reguser

to collect user string it will not work, yet if I do

 $sampleoutput –replace $RegUser 

regex pattern works…

Any help is appreciated and thank you for your time.

LuisC

  • This reply was modified 2 years, 2 months ago by  LuisC.
  • This reply was modified 2 years, 2 months ago by  LuisC.

June 8, 2016 at 6:41 pm

Why not just update your first function to accept multiple logfiles to parse and then output only only one log file in the end instead of parsing each log file individually and then re-parsing the parse files?