Add Multiple Replace

PowerShell Help
1

I am trying to find a way to incorporate multiple -replace into the statement below. When I try to add another section like {$Header -replace “<Commit ts=”,“”} it give me a missing statement block at the end. Any ideas for me? Thanks

Trying to do multiple replace like.
{$Header -replace “<Commit ts=”,“”}
{$Header -replace " >“,”"}

switch -Regex -File $strLogFile 
{
    { $_ -match ".*Commit ts=`"[0-9]+`".*" } { $Header = $_ }
       
    { $_ -match "`"[0-9]+`",.*" } { $Header + " " + $_ | Out-File -Append "${strLogFile}_filtered" }
}
2

You can simply chain multiple replaces and if you actually want to remove partial strings you can ommit the second value from the -replace operator. … like this:

$Header -replace '<Commit ts=' -replace ' >'
3

Thanks Olaf. Where would I put this in the statement below? I tried to add $Header -replace ‘<Commit ts=’ after { $Header = $_ } and it give me a missing block error at the end of the statement. Sorry for my ignorance I am relatively new at powershell.

switch -Regex -File $strLogFile 
{
    { $_ -match ".*Commit ts=`"[0-9]+`".*" } { $Header = $_ }
       
    { $_ -match "`"[0-9]+`",.*" } { $Header + " " + $_ | Out-File -Append "${strLogFile}_filtered" }
}
4

Actually I don’t know. I did not get how the two code snippets you posted are related with each other. You may explain a tlittle more detailed what you’re actually trying to do.

5 
switch -Regex -File $strLogFile 
{
    { $_ -match ".*Commit ts=`"[0-9]+`".*" } { $Header = $_ }
       
    { $_ -match "`"[0-9]+`",.*" } { $Header + " " + $_ | Out-File -Append "${strLogFile}_filtered" }
}

When I run this code above it generates one line per record in the format below. There are many codes but this is one example.

<Commit ts="20210311040006" client=""> "39",”Save Data","Process 'Save Data' was executed."

I would like to make this more manageable to load into a DB. If I replace the “<Commit ts=” with nothing. Replace the client= with a comma and replace > with a comma. Example below. Then it would simple be a comma separated file and easily loaded into a DB.

"20210311040006","","39",”Save Data","Process 'Save Data' was executed."

Thanks for the help!

6

So it’s about extracting particular strings from each line of an input file. Could you please post a few lines of the file in the original format. Please format it as code an obfuscate sensitive information if there are some.

7

I use the powershell script I outlined above to get the header and data on one line making it easier to load. Now just looking to format in comma separated to make easy to load into the database. Thanks!

<Commit ts="20210311040006" client=" automation_user">
 "144"," automation_user","122.2.2.2","User 'automation _user' successfully logged in from address '122.2.2.2'."
<Commit\>
<Commit ts="20210311040006" client="">
"39",”Save Data","Process 'Save Data' was executed."
<Commit\>
<Commit ts="20210311125155" client="Steve"> 
"144","Steve","11.111.111.111","User 'Steve' successfully logged in from address '11.111.111.111'."
<Commit\>
<Commit ts="20210311130404" client="Shelly"> 
"144","Shelly","11.111.111.111","User 'Shelly' successfully logged in from address '11.111.111.111'."
<Commit\>
<Commit ts="20210311131122" client="Daniel"> 
"144","Daniel","11.111.111.111","User 'Daniel' successfully logged in from address '11.111.111.111'."
<Commit\>
<Commit ts="20210311131122" client="Daniel"> 
"144","Daniel","11.111.111.111","User 'Daniel' successfully logged in from address '11.111.111.111'."
<Commit\>
<Commit ts="20210311131747" client="Shelly"> 
"144","Shelly","11.111.111.111","User 'Shelly' successfully logged in from address '11.111.111.111'."
<Commit\>
<Commit ts="20210311135147" client="Shelly"> 
"144","Shelly","11.111.111.111","User 'Shelly' successfully logged in from address '11.111.111.111'."
<Commit\>
<Commit ts="20210311135151" client="Tom"> 
"144","Tom","11.111.111.111","User 'Tom' successfully logged in from address '11.111.111.111'."
<Commit\>
<Commit ts="20210311135250" client=""> 
"21","Version","Dimension 'Version' was updated."
<Commit\>
<Commit ts="20210311135250" client=""> 
"27","Version:Working Forecast","Version:07_05 Vs Working Forecast","1.0","-1.0","Element 'Version:Working Forecast' in consolidation 'Version:07_05 Vs Working Forecast' was changed from weight '1.0' to weight '-1.0'."
<Commit\>
8

Half of your input data already is comma separated. :wink:

Assumed I undestood it correctly and your input file always has this structure your could try it like this:

$Content = Get-Content -Path D:\sample\input.log 

$Result = 
for ($i = 0; $i -lt $Content.count; $i = $i + 3) {
    $Content[$i] -match '(?<=ts=")(?<DateTime>\d{14}).*(?<=client=")(?<Name>.*)(?=")' | Out-Null
    $CSVData = $Content[$i + 1] | ConvertFrom-Csv -Delimiter ',' -Header 'Number', 'User', 'IP', 'Description'
    [PSCustomObject]@{
        Name        = $Matches.Name
        DateTime    = [datetime]::ParseExact($Matches.DateTime , 'yyyyMMddHHmmss', $null)
        Number      = $CSVData.Number
        User        = $CSVData.User
        IP          = $CSVData.IP
        Description = $CSVData.Description
    }
}

$Result | 
    Format-Table -AutoSize

Of course you can pipe the result contained in the variable $result to Export-Csv or whatever you need. :wink:

The ouput with Format-Table would look like this:

Name             DateTime                Number User                     IP                                Description
----             --------                ------ ----                     --                                -----------
 automation_user Do, 11.03.2021 04:00:06 144     automation_user         122.2.2.2                         User 'automation _user' successfully logged in from address '122.2.2.2'.
                 Do, 11.03.2021 04:00:06 39     Save Data                Process 'Save Data' was executed.
Steve            Do, 11.03.2021 12:51:55 144    Steve                    11.111.111.111                    User 'Steve' successfully logged in from address '11.111.111.111'.
Shelly           Do, 11.03.2021 13:04:04 144    Shelly                   11.111.111.111                    User 'Shelly' successfully logged in from address '11.111.111.111'.
Daniel           Do, 11.03.2021 13:11:22 144    Daniel                   11.111.111.111                    User 'Daniel' successfully logged in from address '11.111.111.111'.
Daniel           Do, 11.03.2021 13:11:22 144    Daniel                   11.111.111.111                    User 'Daniel' successfully logged in from address '11.111.111.111'.
Shelly           Do, 11.03.2021 13:17:47 144    Shelly                   11.111.111.111                    User 'Shelly' successfully logged in from address '11.111.111.111'.
Shelly           Do, 11.03.2021 13:51:47 144    Shelly                   11.111.111.111                    User 'Shelly' successfully logged in from address '11.111.111.111'.
Tom              Do, 11.03.2021 13:51:51 144    Tom                      11.111.111.111                    User 'Tom' successfully logged in from address '11.111.111.111'.
                 Do, 11.03.2021 13:52:50 21     Version                  Dimension 'Version' was updated.
                 Do, 11.03.2021 13:52:50 27     Version:Working Forecast Version:07_05 Vs Working Forecast 1.0

The DateTime column may look different for you as I am on a German system. :wink:

9

Olaf when I run your statement against my file I get the following error. Any ideas what I am doing wrong?

ConvertFrom-Csv : Cannot validate argument on parameter 'InputObject'. The argument is null. Provide a valid value for the 
argument, and then try running the command again.
At line:6 char:35
+ ... t[$i + 1] | ConvertFrom-Csv -Delimiter ',' -Header 'Number', 'User',  ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [ConvertFrom-Csv], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.PowerShell.Commands.ConvertFromCsvCommand
10

So your file might look different to the sample data you posted. The function depends on the structure of the file. From your sample data I assumed that there are a kind of data sets in your file organised in 3 rows each right from the beginning. If that’s not the case the code will fail. :wink:

11

Ok I will look into it some more. The output csv seem to be keeping the some of the and so I will try to figure out how to exclude those from the file as well. Thanks

"","3/1/2021 2:38:38 PM","99","Department:5200.99025-Oracle","Oracle Subsidiary","5200"
"","3/1/2021 2:38:38 PM","</Commit>",,,
"","3/1/2021 2:38:38 PM","21","Revenue Class","Dimension 'Revenue Class' was updated.",
"","3/1/2021 2:38:38 PM","26","Revenue Class:Cloud","Revenue Class:NetSuite Revenue Class","Element 'Revenue Class:Cloud' was removed from consolidation 'Revenue Class:NetSuite Revenue Class'."
"","3/1/2021 2:38:38 PM","</Details>",,,
12

I didn’t get that, sorry. Does your input file really look the way you posted? The output you just posted does not look like.

13

Yes it is audit log data so varies quite a bit. It has two types of headers in the file. I dont know if this helps and thanks for the help.

<Commit ts="20210311040006" client="">
"39",”Save Data","Process 'Save Data' was executed."
<Commit\>

and

<Commit ts="20210311040006" client="">
"39",”Save Data","Process 'Save Data' was executed."
<Details>
"144","Daniel","11.111.111.111","User 'Daniel' successfully logged in from address '11.111.111.111'."
</Details>
</Commit>
14

Usually there is no “too much” information. :wink: The most reliable solution would probably be to ask the manufacturer of the audit software to include a function to output the data in an interoperable format.

Of course there would be ways to deal with the two different data sets in your log file. But you would need to decide how. If there is a “<Details>” section what would you like to do with information one line before this “<Details>” section?

Here is a suggestion where the additional line in front of the “<Details>” section will be ignored.

$OriginalContent = Get-Content -Path D:\sample\input.log 

$Content = 
for ($i = 0; $i -lt $OriginalContent.count; $i++) {
    if ($OriginalContent[$i + 1] -notmatch '<Details>' -and $OriginalContent[$i] -notmatch '</?Details>') {
        $OriginalContent[$i]
    }
}

$Result = 
for ($i = 0; $i -lt $Content.count; $i = $i + 3) {
    $Content[$i] -match '(?<=ts=")(?<DateTime>\d{14}).*(?<=client=")(?<Name>.*)(?=")' | Out-Null
    $CSVData = $Content[$i + 1] | ConvertFrom-Csv -Delimiter ',' -Header 'Number', 'User', 'IP', 'Description'
    [PSCustomObject]@{
        Name        = $Matches.Name
        DateTime    = [datetime]::ParseExact($Matches.DateTime , 'yyyyMMddHHmmss', $null)
        Number      = $CSVData.Number
        User        = $CSVData.User
        IP          = $CSVData.IP
        Description = $CSVData.Description
    }
}

$Result | 
    Format-Table -AutoSize
15

Olaf thanks for the help this seems to be working with a small exception. The only issue is it appear to be giving an error if values are blank. I am guessing a -replace somewhere in your statement but not sure where would be the best place for this.

ConvertFrom-Csv : Cannot validate argument on parameter 'InputObject'. The argument is null. Provide a valid value for the 
argument, and then try running the command again.
At line:6 char:35
+ ... t[$i + 1] | ConvertFrom-Csv -Delimiter ',' -Header 'Number', 'User',  ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [ConvertFrom-Csv], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.PowerShell.Commands.ConvertFromCsvCommand
16

That’s the issue I meant with …

It’s pretty much impossible to help you with this without seeing your actual input data. But if you understand the code you can debug the script and adapt it to your particular environment. The code expects the logfile to have a line count dividable by 3. If that’s not the case there will be errors like this where no data are available for the cmdlet ConvertFrom-Csv.