Parse date from text file and extract lines where the date is greater than X

This topic contains 4 replies, has 3 voices, and was last updated by Profile photo of Bob McCoy Bob McCoy 10 months, 2 weeks ago.

  • Author
    Posts
  • #34112
    Profile photo of Liam Kemp
    Liam Kemp
    Participant

    please let me know if this needs clarification.

    I have a client that uses third-party software to store confidential medical information. We now need to find out which users have opened a particular record in the database. I have been in touch with the vendor, and the only way they log it is in a text file on each individual computer(Apparently). I need to parse this text file from each computer to pull out the information I need. Here is an anonymous sample of the information in the text file – I have added spaces between each line for readability.

    Log in.|10/03/2012|01:12:45|Dr John Smith|3|FS01|Windows 7 Domain Controller with Terminal Services Service Pack 1 (6.1 Build 7601)|3.12.1

    Progress Note – New record opened|10/03/2012|01:13:33|Dr John Smith|666241|8463|Richard Test^05/09/1956|.F.|.T.|1|FS01

    Progress Note – Discarded by user|10/03/2012|01:14:29|Dr John Smith|666241|8463|Richard Test|.F.|.T.|FS01

    I can easily pull out any line with the record name in question i.e. "Richard test", but these logs go all the way back to 2012. Does anyone have any idea how I can parse the date from each line so that I can pull anything after 01/01/2016 for example?

    import-module activedirectory
    $computers = "FS01"#get-adcomputer -filter * | Select-object -ExpandProperty Name
    
    foreach($computer in $computers){
    
    $path = "\\$computer\C$\Users\Public\Documents\HCN\MD\MDTrace.LOG"
    If(Test-Connection -ComputerName $computer -Count 1 -ErrorAction SilentlyContinue){
    If(Test-Path $Path){
    Get-Content -Path $path | foreach { if($_ -match "Thomas Hogan"){Write-Output "$computer -- $_"} }
    }
    }
    }
    
  • #34113
    Profile photo of Max Kozlov
    Max Kozlov
    Participant

    There is Idea for you....

    [...]
    Get-Content $File | Where-Object { $_ -match $NeededUser } | ConvertFrom-Csv -Delimiter '|' -header Operation, Date, User, S1, S2, S3, S4, S5, S6, ComputerName
    
    ### | Where-Object { [DateTime]$_.Date -ge $NeededStartDate -and [DateTime]$_.Date -le $NeededEndDate }
    [...]
    

    may be for more effectiveness (if your log a large) you can also pre-filter your log with { $_ -match '2016' }

  • #34151
    Profile photo of Liam Kemp
    Liam Kemp
    Participant

    Thanks Max, I didn't realise I could use ConvertFrom-CSV with the delimiter of my choice.

    I ended up solving the problem by using -split
    I'll try your solution as well. We are looking at turning this into a scheduled task now, so that way might be faster than all the IF's in my solution.
    Cheers

    $computers = "NB04TMPL" #Get-Content D:\computers.txt | Sort-Object
    $date = "21/01/2013"
    $name = "Richard Test"
    foreach ($computer in $computers)
    {
    	$path = "C:\temp\MDTrace.LOG"
    	If (Test-Connection -ComputerName $computer -Count 1 -ErrorAction SilentlyContinue)
    	{
    		If (Test-Path $Path)
    		{
    			Get-Content -Path $path | foreach {
    				$str = ($_ -split '\|')
    				if ($str[1] -gt $date)
    				{
    					if ($str[6] -match $name)
    					{
    						Write-Output $_
    					}
    				}
    			}	
    		}	
    	}
    }
    
  • #34166
    Profile photo of Max Kozlov
    Max Kozlov
    Participant

    the main diference is not speed
    main difference – you get object with my solution and string or array with yours

    object open ways to sorting, grouping, reporting, saving to db....

  • #34182
    Profile photo of Bob McCoy
    Bob McCoy
    Participant

    Depending on the size of the files, you may want to use StreamReader. This example uses StreamReader and StringBuilder for input and output. It also uses a RegEx pattern to extract the date and time for comparison.

    # https://powershell.org/forums/topic/parse-date-from-text-file-and-extract-lines-where-the-date-is-greater-than-x/
    $threshold = [datetime]"01/16/16"
    $pattern = "^.+\|(?'date'\d{2}/\d{2}/\d{4})\|(?'time'\d{2}:\d{2}:\d{2})\|(?'data'.*)"
    $name = "Richard Test"
    
    $src = "C:\Ephemeral\data.txt"
    $dst = "C:\Ephemeral\data1.txt"
    $inData = New-Object -TypeName System.IO.StreamReader -ArgumentList $src
    $outData = New-Object -TypeName System.Text.StringBuilder
    while ($line = $inData.ReadLine())
    {
        if ($line -match $pattern)
        {
            $date = [datetime]"$($Matches['date']) $($Matches['time'])"
            if ($date -ge $threshold -and $Matches['data'].Contains($name))
            {
                Write-Verbose "Writing record for $date"
                [void]$outData.AppendLine($line)
            }
        }
    }
    # All done with the input data, write out the results
    $outData.ToString() | Out-File -FilePath $dst
    

You must be logged in to reply to this topic.