Parsing log files

This topic contains 11 replies, has 4 voices, and was last updated by Profile photo of Curtis Smith Curtis Smith 3 months, 3 weeks ago.

Viewing 12 posts - 1 through 12 (of 12 total)
  • Author
    Posts
  • #35713
    Profile photo of LuisC
    LuisC
    Participant

    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...

    #35719
    Profile photo of random commandline
    random commandline
    Participant

    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
    
    #35722
    Profile photo of Bob McCoy
    Bob McCoy
    Participant

    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
    
    #35751
    Profile photo of LuisC
    LuisC
    Participant

    **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

    #35752
    Profile photo of Bob McCoy
    Bob McCoy
    Participant

    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.

    #35754
    Profile photo of random commandline
    random commandline
    Participant

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

    #35764
    Profile photo of LuisC
    LuisC
    Participant

    @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.

    #35770
    Profile photo of random commandline
    random commandline
    Participant

    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

    #42318
    Profile photo of LuisC
    LuisC
    Participant

    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 3 months, 3 weeks ago by Profile photo of LuisC LuisC.
    #42320
    Profile photo of LuisC
    LuisC
    Participant

    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 3 months, 3 weeks ago by Profile photo of LuisC LuisC.
    • This reply was modified 3 months, 3 weeks ago by Profile photo of LuisC LuisC.
    #42322
    Profile photo of LuisC
    LuisC
    Participant

    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 3 months, 3 weeks ago by Profile photo of LuisC LuisC.
    • This reply was modified 3 months, 3 weeks ago by Profile photo of LuisC LuisC.
    #42329
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    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?

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic.