CODEWITHSUNDEEP
jsonpowershell
NottyHead
NottyHead

Reputation: 187

Convert Date in TextFile using Powershell using a predefined format

In the Code Sample below, how do I convert the time stored in [10/Mar/2021:03:27:29 +0000] to MM-dd-yyyyTHH:mm:ssZ format. The reason I ask is we need this data to be used in a downstream system where the data is stored as 03-10-2021T03:27:29Z. I was assisted by @712648 (Mathias Jensen) and he suggested to look at parsedate however, I am receiving issues using the same in the below code. The same parse date when run indivdually executes without any issues

Code

cls
$date = Get-Date -UFormat "%m-%d-%Y %H-%M-%S"
$BasePath = "C:\Temp\AWS_S3\S3Logs\"
$JsonOutFile = "$Basepath"+"JsonFile - $date.json"

#Combine TextFiles Into A Single Text File
$ConcatenatedFile = "C:\Temp\AWS_S3\S3Logs\CombinedFile.txt"

#Get-ChildItem $BasePath -File -Filter *.txt | gc | out-file -FilePath $ConcatenatedFile

#Build Columns for JSON file Key:Pair combination
$columns = 'Bucket Owner', 'Bucket', 'Time', 'Remote IP', 'Requester', 'Request ID', 'Operation', 'Key', 'Request-URI', 'HTTP status', 'Error Code', 'Bytes Sent', 'Object Size', 'Total Time', 'Turn-Around Time', 'Referer', 'User-Agen', 'Version Id', 'Host Id', 'Signature Version', 'Cipher Suite', 'Authentication Type', 'Host Header', 'TLS version'
$data = Get-Content -Path $ConcatenatedFile

$parsedLog = $data -replace '\[([^\]]+)\]','"$1"' |ConvertFrom-Csv -Delimiter ' ' -Header $columns
$date_format         = "yyyy-MM-ddTHH:mm:ssZ"
    $parsedLog | Select Bucket Owner, Bucket, 
([datetime]::ParseExact($_.'Time',"dd/MMM/yyyy:HH:mm:ss +0000",$null).ToSTring($date_format)), 
Remote IP, Requester, Request ID, Operation, Key, Request-URI, HTTP status, Error Code, Bytes Sent, Object Size, Total Time,Turn-Around Time, Referer, User-Agen, Version Id, Host Id, Signature Version, Cipher Suite, Authentication Type, Host Header,TLS version | ConvertTo-JSON -Compress
#$jsonFile #| Out-File -FilePath $JsonOutFile

#Remove The Concatenated File after JSON File is created
#Remove-Item -Path $ConcatenatedFile

Contents of CombinedFile.txt

fd89d80d676948bd913040b667965ef6a50a9c80a12f38c504f497953aedc341 SampleS3Bucket [12/Mar/2021:14:11:57 +0000] 117.222.211.65 arn:aws:iam::486031527132:user/jdoe H3KK9B3EERPBZZN1 REST.GET.BUCKET - "GET /SampleS3Bucket?list-type=2&encoding-type=url&max-keys=1000&fetch-owner=true&delimiter=%2F&prefix=Test1%2F HTTP/1.1" 200 - 986 - 126 125 "-" "S3Console/0.4, aws-internal/3 aws-sdk-java/1.11.964 Linux/4.9.230-0.1.ac.224.84.332.metal1.x86_64 OpenJDK_64-Bit_Server_VM/25.282-b08 java/1.8.0_282 vendor/Oracle_Corporation" - nH4rr0OG7OPS9WYZKyi4F0VcjffvyHpVhZgJGrzB99OtGBv8EqfdbKifFaREa1sFdxLMjU1GjoU= SigV4 ECDHE-RSA-AES128-GCM-SHA256 AuthHeader s3-us-west-2.amazonaws.com TLSv1.2
fd89d80d676948bd913040b667965ef6a50a9c80a12f38c504f497953aedc341 SampleS3Bucket [12/Mar/2021:14:13:32 +0000] 117.222.211.65 arn:aws:iam::486031527132:user/jdoe 7F9VJTFGE993PZ0B BATCH.DELETE.OBJECT Test1/Debt+Dictionary_BASE.xlsx - 204 - - 71222 - - - - - Ya9Yoe23lAFZdY7grWdyD/RggdJq14T2E02enlt4qAlebHfsbeiG2JQ+qZP8CF/iNvCUuTpv7gY= SigV4 ECDHE-RSA-AES128-GCM-SHA256 AuthHeader s3-us-west-2.amazonaws.com TLSv1.2
fd89d80d676948bd913040b667965ef6a50a9c80a12f38c504f497953aedc341 SampleS3Bucket [12/Mar/2021:14:13:32 +0000] 117.222.211.65 arn:aws:iam::486031527132:user/jdoe 7F9VJTFGE993PZ0B REST.POST.MULTI_OBJECT_DELETE - "POST /SampleS3Bucket?delete= HTTP/1.1" 200 - 177 - 63 - "-" "S3Console/0.4, aws-internal/3 aws-sdk-java/1.11.964 Linux/4.9.230-0.1.ac.224.84.332.metal1.x86_64 OpenJDK_64-Bit_Server_VM/25.282-b08 java/1.8.0_282 vendor/Oracle_Corporation" - Ya9Yoe23lAFZdY7grWdyD/RggdJq14T2E02enlt4qAlebHfsbeiG2JQ+qZP8CF/iNvCUuTpv7gY= SigV4 ECDHE-RSA-AES128-GCM-SHA256 AuthHeader s3-us-west-2.amazonaws.com TLSv1.2
fd89d80d676948bd913040b667965ef6a50a9c80a12f38c504f497953aedc341 SampleS3Bucket [12/Mar/2021:14:17:40 +0000] 117.222.211.65 arn:aws:iam::486031527132:user/jdoe GQ80J3H2EJVH13RS REST.GET.VERSIONING - "GET /SampleS3Bucket?versioning= HTTP/1.1" 200 - 113 - 17 - "-" "S3Console/0.4, aws-internal/3 aws-sdk-java/1.11.964 Linux/4.9.230-0.1.ac.224.84.332.metal1.x86_64 OpenJDK_64-Bit_Server_VM/25.282-b08 java/1.8.0_282 vendor/Oracle_Corporation" - 96OcC7CN8VmIeJLY96PfWBNivqn6quBIZAYslV1kF2wZbKjYBdkn1N5aE0xbzZxyWUTDav0CT7A= SigV4 ECDHE-RSA-AES128-GCM-SHA256 AuthHeader s3-us-west-2.amazonaws.com TLSv1.2
fd89d80d676948bd913040b667965ef6a50a9c80a12f38c504f497953aedc341 SampleS3Bucket [12/Mar/2021:14:26:57 +0000] 117.222.211.65 arn:aws:iam::486031527132:user/jdoe 7G6TJ2NEKZYDR36M REST.GET.POLICY_STATUS - "GET /?policyStatus HTTP/1.1" 404 NoSuchBucketPolicy 307 - 9 - "-" "S3Console/0.4, aws-internal/3 aws-sdk-java/1.11.964 Linux/4.9.230-0.1.ac.224.84.332.metal1.x86_64 OpenJDK_64-Bit_Server_VM/25.282-b08 java/1.8.0_282 vendor/Oracle_Corporation" - 53JowWaGjViTyT0CCtzmzhoxh4WGKe8UcOkwv7Q/HlGVfStvPeDzIzlaFJsJpUujg2j5apQ9VBc= SigV4 ECDHE-RSA-AES128-GCM-SHA256 AuthHeader SampleS3Bucket.s3.us-west-2.amazonaws.com TLSv1.2

Error

Exception calling "ParseExact" with "3" argument(s): "String was not recognized as a valid DateTime." At line:18 char:1 + $parsedLog | Select ([datetime]::ParseExact($_.'Time',"dd/MMM/yyyy:HH ... + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : FormatException –

However the same code when the date is considered individually work just fine PARSEDATE code

$date = "12/Mar/2021:14:11:57 +0000"
$date_format         = "yyyy-MM-dd HH:mm"
[datetime]::ParseExact($date,"dd/MMM/yyyy:HH:mm:ss +0000",$null).ToSTring($date_format)

Output 2021-03-12 14:11

Upvotes: -1

Views: 206

Answers (2)

Evan
Evan

Reputation: 111

$parsedLog | Select ($_.'Time')

Is returning all of the columns.

$parsedLog | Select {$_.'Time'}

Returns the time column.

Your code really doesn't like to use the curly braces where you are using them. I would suggest converting the time in $parsedLog by itself prior to retrieving all columns.

So do something like this first:

foreach ($log in $parsedLog){
    $log.time = ([datetime]::ParseExact($log.time,"dd/MMM/yyyy:HH:mm:ss +0000",$null).ToSTring($date_format))
}

Now you can select time just like the rest of the columns.

Upvotes: 0

Doug Maurer
Doug Maurer

Reputation: 8868

I see a few issues.

  • First, You aren't using a valid calculated expression for your time property.
  • Second, an extra pair of double quotes are being added in the replace statement.
  • Third, there are property names with spaces that aren't being quoted.

I believe you should change these lines

  • $parsedLog = $data -replace '\[([^\]]+)\]','"$1"' |ConvertFrom-Csv -Delimiter ' ' -Header $columns

  • $parsedLog | Select Bucket Owner, Bucket, ([datetime]::ParseExact($_.'Time',"dd/MMM/yyyy:HH:mm:ss +0000",$null).ToSTring($date_format)), Remote IP, Requester, Request ID, Operation, Key, Request-URI, HTTP status, Error Code, Bytes Sent, Object Size, Total Time,Turn-Around Time, Referer, User-Agen, Version Id, Host Id, Signature Version, Cipher Suite, Authentication Type, Host Header,TLS version

to these

  • $parsedLog = $data -replace '\[([^\]]+)\]','$1' |ConvertFrom-Csv -Delimiter ' ' -Header $columns
  • $parsedLog | Select 'Bucket Owner', Bucket, @{n='Time';e={[datetime]::ParseExact($_.'Time',"dd/MMM/yyyy:HH:mm:ss +0000",$null).ToSTring($date_format)}}, 'Remote IP', Requester, 'Request ID', Operation, Key, Request-URI, 'HTTP status', 'Error Code', 'Bytes Sent', 'Object Size', 'Total Time','Turn-Around Time', Referer, User-Agen, 'Version Id', 'Host Id', 'Signature Version', 'Cipher Suite', 'Authentication Type', 'Host Header','TLS version

This can also be written in a manner that's easier to read and maintain.

$selectProps = 'Bucket Owner',
               'Bucket',
               @{n='Time';e={[datetime]::ParseExact($_.'Time',"dd/MMM/yyyy:HH:mm:ss +0000",$null).ToSTring($date_format)}},
               'Remote IP',
               'Requester',
               'Request ID',
               'Operation',
               'Key',
               'Request-URI',
               'HTTP status',
               'Error Code',
               'Bytes Sent',
               'Object Size',
               'Total Time',
               'Turn-Around Time',
               'Referer',
               'User-Agent',
               'Version Id',
               'Host Id',
               'Signature Version',
               'Cipher Suite',
               'Authentication Type',
               'Host Header',
               'TLS version'

$parsedLog | Select-Object $selectProps

Upvotes: 1

Related Questions