Change Blank Variable to "BLANK" String

The problem I am running into with the code below is that _ sometimes has a blank value in the file so for example _ or $3 might no longer have an attribute and is blank. How could I get this code to replace any $2,$3,$4 that are blank to “BLANK” or some string value so it will process correct? Thanks

    { $_ -match "`"99`",.*" } { $_.Replace("'","''") -replace "^<.*([0-9]{14}).*`"(.*)`"> `"99`",`"(.+)`",`"(.+)`",`"(.+)`",`"(.+)`"", 'INSERT INTO $(DATABASE) (LOGTIME, LOGCODE, USER, DESCRIPTION, OBJ_TYPE, OBJ_NAME) VALUES (''$1'', ''99'', '''', ''Attribute "$3" was set to "$4" for element "$2".'', ''Attribute'', ''$3'' ) -- $5 '    |
                            Out-File -Append "${strLogFile}.sql"
                            $intTransCount++
                        }

That replace portion is atrocious. Surely there is a better way to handle this prior to getting to the point you require this solution?

I am sure there is a better way but I would have no idea. I received the code from a SaaS company to dump log information into a DB. I know nothing of powershell and am just trying to get additional error codes loaded to this DB. All I am trying to do is make a blank variable into a value. My understanding is this can be done in powershell just dont know how since I have been working with powershell for a week now. Any thoughts or ideas would be appreciated.

First, I would highly recommend parsing the log into a PSObject and not straight a text replace. Then you should leverage SQL almost like a stored procedure like this:

foreach ($record in $parsedStuff) {
$sqlCmd = @"
DECLARE @LOGTIME     nvarchar(50) = '$($record.LogTime)'
DECLARE @LOGCODE     nvarchar(50) = '$($record.LogCode)'
DECLARE @USER        nvarchar(50) = '$($record.User)'
DECLARE @DESCRIPTION nvarchar(50) = '$($record.Description)'
DECLARE @OBJ_TYPE    nvarchar(50) = '$($record.obj_Type)'
DECLARE @OBJ_NAME    nvarchar(50) = '$($record.obj_Name)'

IF @OBJ_TYPE = '' SET @OBJ_TYPE = NULL
IF @OBJ_NAME = '' SET @OBJ_NAME = NULL

INSERT INTO $(DATABASE) (
    LOGTIME, 
    LOGCODE, 
    USER, 
    DESCRIPTION, 
    OBJ_TYPE, 
    OBJ_NAME
) 
VALUES (
    @LOGTIME,
    @LOGCODE,
    @USER,
    @DESCRIPTION,
    @OBJ_TYPE,
    @OBJ_Name
)
"@

    Out-File -InputObject $sqlCmd -Append "$(strLogFile).sql"
}

This allows you to have the variables at the top of the SQL script and if those variable are a null in Powershell, you can convert them to DBNULL in SQL like the example for OBJ_TYPE and OBJ_NAME. You’ll find this is a much cleaner approach and you’re just troubleshooting the variables rather than the entire SQL command.

Thanks Rob. I am working on this approach however running into an issue with the IF’s and the LOGTIME variable. I have attached a screenshot of the IF statement error and the LOGTIME error is below.

Unexpected token ‘SET’ in expression or statement.
At line:16 char:12

  • LOGTIME,
    
  •        ~
    

Missing argument in parameter list.

The color coding in ISE makes me think you didn’t close out a string on line 1. The foreach on line 3 should not be that color and there should be a ± next to foreach to collapse the block. If I’m correct (can’t see because line 1 is redacted), then line 3 and 4 are part of the string and not statements which explains the error because line 12 is parsing as PowerShell and not SQL. PowerShell requires if statements in this format if () { <script block}). Notice if is followed by ( which is what the error message states.

I changed the format of the IF statement and it doesnt appear to be in error.
IF($OBJ_TYPE = ‘’) {$OBJ_TYPE = NULL}
IF($OBJ_NAME = ‘’) {$OBJ_NAME = NULL}

This is the LOGTIME error in more detail. It says Missing argument in parameter list. Does anyone have thoughts on this error? I will try to look online to.

At line:16 char:12

  • LOGTIME,
    
  •        ~
    

Missing argument in parameter list.
At line:33 char:96

  • … sqlCmd -Append “E:\10055.sql”
  •                                                                     ~
    

The string is missing the terminator: ".
At line:33 char:44

  • … sqlCmd -Append “E:\10055.sql”
  •                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    

Unexpected token ‘E:\10055.sql"
}’ in expression or statement.
+ CategoryInfo : ParserError: (:slight_smile: , ParentContainsErrorRecordException
+ FullyQualifiedErrorId : MissingArgument

PS C:\Users\12345> ${parsedStuff} = "E:\100055.log_filtered

foreach ($record in $parsedStuff) {
$sqlCmd = @"
DECLARE @LOGTIME     nvarchar(50) = '$($record.LogTime)'
DECLARE @LOGCODE     nvarchar(50) = '$($record.LogCode)'
DECLARE @USER        nvarchar(50) = '$($record.User)'
DECLARE @DESCRIPTION nvarchar(50) = '$($record.Description)'
DECLARE @OBJ_TYPE    nvarchar(50) = '$($record.obj_Type)'
DECLARE @OBJ_NAME    nvarchar(50) = '$($record.obj_Name)'

IF($OBJ_TYPE = '') {$OBJ_TYPE = NULL}
IF($OBJ_NAME = '') {$OBJ_NAME = NULL}

INSERT INTO Logs (
    LOGTIME, 
    LOGCODE, 
    USER, 
    DESCRIPTION, 
    OBJ_TYPE, 
    OBJ_NAME
) 
VALUES (
    $LOGTIME,
    $LOGCODE,
    $USER,
    $DESCRIPTION,
    $OBJ_TYPE,
    $OBJ_Name
)
"@

    Out-File -InputObject $sqlCmd -Append "E:\10055.sql"
}

As @ralphmwr stated, it appears something isn’t closed out and Powershell ISE is trying to parse a text block as Powershell code. It is a here-string, a text blob, so it when I see the DECLARE blue then something isn’t closed. The parsedStuff variable is not correct with the curly brackets and I do not see a end qoute after log_filtered. You are just trying to fill in the variables like this:

$parsedStuff = [PSCustomObject]@{
    LogTime     = Get-Date -Format "u"
    LogCode     = '1234'
    User        = 'user123'
    Description = 'A description'
    obj_type    = 53232
    obj_name    = 'Foo'
}

foreach ($record in $parsedStuff) {
$sqlCmd = @"
DECLARE @LOGTIME     nvarchar(50) = '$($record.LogTime)'
DECLARE @LOGCODE     nvarchar(50) = '$($record.LogCode)'
DECLARE @USER        nvarchar(50) = '$($record.User)'
DECLARE @DESCRIPTION nvarchar(50) = '$($record.Description)'
DECLARE @OBJ_TYPE    nvarchar(50) = '$($record.obj_Type)'
DECLARE @OBJ_NAME    nvarchar(50) = '$($record.obj_Name)'

IF @OBJ_TYPE = '' SET @OBJ_TYPE = NULL
IF @OBJ_NAME = '' SET @OBJ_NAME = NULL

INSERT INTO  MYDATABASE (
    LOGTIME, 
    LOGCODE, 
    USER, 
    DESCRIPTION, 
    OBJ_TYPE, 
    OBJ_NAME
) 
VALUES (
    @LOGTIME,
    @LOGCODE,
    @USER,
    @DESCRIPTION,
    @OBJ_TYPE,
    @OBJ_Name
)
"@

    $sqlCmd
}

Output:

DECLARE @LOGTIME     nvarchar(50) = '2021-03-15 15:37:59Z'
DECLARE @LOGCODE     nvarchar(50) = '1234'
DECLARE @USER        nvarchar(50) = 'user123'
DECLARE @DESCRIPTION nvarchar(50) = 'A description'
DECLARE @OBJ_TYPE    nvarchar(50) = '53232'
DECLARE @OBJ_NAME    nvarchar(50) = 'Foo'

IF @OBJ_TYPE = '' SET @OBJ_TYPE = NULL
IF @OBJ_NAME = '' SET @OBJ_NAME = NULL

INSERT INTO  MYDATABASE (
    LOGTIME,
    LOGCODE,
    USER,
    DESCRIPTION,
    OBJ_TYPE,
    OBJ_NAME
)
VALUES (
    @LOGTIME,
    @LOGCODE,
    @USER,
    @DESCRIPTION,
    @OBJ_TYPE,
    @OBJ_Name
)

As expected, here’s the issue

${parsedStuff} = "E:\100055.log_filtered

Needs to be

${parsedStuff} = "E:\100055.log_filtered"

Your are missing the closing quotes on the string. When you fix it, you’ll see the color change. And don’t put ( ) after if statement in SQL it’s used in PowerShell scripting not SQL.

Thank this is very helpful and it executed without error. The part I am confused about is the .log_filtered file has 78K row records and when I run this all I get is the one sql statement below. Shouldnt this create an INSERT TO for each line? Plus the line appears to be blank and the only thing that populates is my email address.

Also when I rerun it append the same blank record to the file.

Below is what shows in the .sql file.

DECLARE @LOGTIME     nvarchar(50) = ''
DECLARE @LOGCODE     nvarchar(50) = ''
DECLARE @USER        nvarchar(50) = ''
DECLARE @DESCRIPTION nvarchar(50) = ''
DECLARE @OBJ_TYPE    nvarchar(50) = ''
DECLARE @OBJ_NAME    nvarchar(50) = ''

IF( = '') { = NULL}
IF( = '') { = NULL}

INSERT INTO Logs (
    LOGTIME, 
    LOGCODE, 
    USER, 
    DESCRIPTION, 
    OBJ_TYPE, 
    OBJ_NAME
) 
VALUES (
    ,
    ,
    myemailaddress,
    ,
    ,
    
)

Can you post a couple of log line examples and what data you are trying to extract?

No problem. I appreciate you taking a look. I changed the data but the format is true to the file. I attempted a Get-Content after the in and it returned a lot more of the same blank data.

<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 ts="20210311040006" client=""> "39",”Save Data","Process 'Save Data' was executed."
<Commit ts="20210311125155" client="Steve"> "144","Steve","11.111.111.111","User 'Steve' successfully logged in from address '11.111.111.111'."
<Commit ts="20210311130404" client="Shelly"> "144","Shelly","11.111.111.111","User 'Shelly' successfully logged in from address '11.111.111.111'."
<Commit ts="20210311131122" client="Daniel"> "144","Daniel","11.111.111.111","User 'Daniel' successfully logged in from address '11.111.111.111'."
<Commit ts="20210311131122" client="Daniel"> "144","Daniel","11.111.111.111","User 'Daniel' successfully logged in from address '11.111.111.111'."
<Commit ts="20210311131747" client="Shelly"> "144","Shelly","11.111.111.111","User 'Shelly' successfully logged in from address '11.111.111.111'."
<Commit ts="20210311135147" client="Shelly"> "144","Shelly","11.111.111.111","User 'Shelly' successfully logged in from address '11.111.111.111'."
<Commit ts="20210311135151" client="Tom"> "144","Tom","11.111.111.111","User 'Tom' successfully logged in from address '11.111.111.111'."
<Commit ts="20210311135250" client=""> "21","Version","Dimension 'Version' was updated."
<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 ts="20210311135250" client=""> "21","Version","Dimension 'Version' was updated."
<Commit ts="20210311135250" client=""> "27","Version:Working Forecast","Version:04_08 Vs Working Forecast","1.0","-1.0","Element 'Version:Working Forecast' in consolidation 'Version:04_08 Vs Working Forecast' was changed from weight '1.0' to weight '-1.0'."

and what data you are trying to extract…all rows, only the 144 rows…

All 78K rows on the file if that is possible. The plan is to dump the audit logs from this application into a DB to be reported on. I also edited my prior comment. Since I didnt click the preformated text it was removing the <Committs=… from the front. Now you can see the entire log lines.

I have made some good progress with the code below. This now created a .sql file with data from each line of the file. The first three variables are easy to parse because they are in the same format every time. ie: LOGTIME, LOGCODE, and USER. After that depending on the LOGCODE the variable columns change. The worse new is they have no distinguishing feature. Per my prior post with the line detail you can see how the lines vary in format. How would you accommodate for this in powershell? Would I need to do a -match to determine the LOGCODE and parse appropriately? Thanks for the advise in advance.

cls

${parsedStuff} = "Z:\AuditLogFiles\10055.log_filtered"

$recordparse = $record.Split("""")
        $LOGTIME = $recordparse[1]
        $LOGCODE  = $recordparse[5]
        $USER = if ($recordparse[3] -eq '') { 'NULL' } else { $recordparse[3] }

foreach ($record in (Get-Content Z:\AuditLogFiles\10055.log_filtered)) {

$sqlCmd = @"
DECLARE @LOGTIME     nvarchar(50) = '$($LOGTIME)'
DECLARE @LOGCODE     nvarchar(50) = '$($LOGCODE)'
DECLARE @USER        nvarchar(50) = '$($USER)'
DECLARE @DESCRIPTION nvarchar(50) = '$($record.Description)'
DECLARE @OBJ_TYPE    nvarchar(50) = '$($record.obj_Type)'
DECLARE @OBJ_NAME    nvarchar(50) = '$($record.obj_Name)'

INSERT INTO TM1_Logs (
    LOGTIME, 
    LOGCODE, 
    USER, 
    DESCRIPTION, 
    OBJ_TYPE, 
    OBJ_NAME
) 
VALUES (
    @LOGTIME,
    @LOGCODE,
    @USER,
    @DESCRIPTION,
    @OBJ_TYPE,
    @OBJ_Name
)
"@

    Out-File -InputObject $sqlCmd -Append "Z:\AuditLogFiles\10055.log_filtered.sql"
}

I think you’ll have to use regular expressions (-match) but you said

which makes regex difficult but maybe not impossible. Using your posted data I was successful at pulling out user, logtime, logcode, and description. I don’t know which field is obj_type and obj_code. If you can point those out to me I might be able to help you capture them as well.

$testfile = New-TemporaryFile

@'
<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 ts="20210311040006" client=""> "39",”Save Data","Process 'Save Data' was executed."
<Commit ts="20210311125155" client="Steve"> "144","Steve","11.111.111.111","User 'Steve' successfully logged in from address '11.111.111.111'."
<Commit ts="20210311130404" client="Shelly"> "144","Shelly","11.111.111.111","User 'Shelly' successfully logged in from address '11.111.111.111'."
<Commit ts="20210311131122" client="Daniel"> "144","Daniel","11.111.111.111","User 'Daniel' successfully logged in from address '11.111.111.111'."
<Commit ts="20210311131122" client="Daniel"> "144","Daniel","11.111.111.111","User 'Daniel' successfully logged in from address '11.111.111.111'."
<Commit ts="20210311131747" client="Shelly"> "144","Shelly","11.111.111.111","User 'Shelly' successfully logged in from address '11.111.111.111'."
<Commit ts="20210311135147" client="Shelly"> "144","Shelly","11.111.111.111","User 'Shelly' successfully logged in from address '11.111.111.111'."
<Commit ts="20210311135151" client="Tom"> "144","Tom","11.111.111.111","User 'Tom' successfully logged in from address '11.111.111.111'."
<Commit ts="20210311135250" client=""> "21","Version","Dimension 'Version' was updated."
<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 ts="20210311135250" client=""> "21","Version","Dimension 'Version' was updated."
<Commit ts="20210311135250" client=""> "27","Version:Working Forecast","Version:04_08 Vs Working Forecast","1.0","-1.0","Element 'Version:Working Forecast' in consolidation 'Version:04_08 Vs Working Forecast' was changed from weight '1.0' to weight '-1.0'."
'@ | Set-Content -Path $testfile.FullName

foreach ($record in Get-Content -Path $testfile.FullName) {
    $record -match '^<Commit ts="(?<LOGTIME>[0-9]{14})" client="(?<USER>[ a-zA-Z_]*)"> "(?<LOGCODE>[0-9]+)",.+"(?<DESCRIPTION>.*)"$' | Out-Null
    $sqlCmd = @"
DECLARE @LOGTIME     nvarchar(50) = '$($Matches.LOGTIME)'
DECLARE @LOGCODE     nvarchar(50) = '$($MAtches.LOGCODE)'
DECLARE @USER        nvarchar(50) = '$($Matches.USER)'
DECLARE @DESCRIPTION nvarchar(50) = '$($Matches.DESCRIPTION)'
DECLARE @OBJ_TYPE    nvarchar(50) = '$()'
DECLARE @OBJ_NAME    nvarchar(50) = '$()'

INSERT INTO TM1_Logs (
    LOGTIME, 
    LOGCODE, 
    USER, 
    DESCRIPTION, 
    OBJ_TYPE, 
    OBJ_NAME
) 
VALUES (
    @LOGTIME,
    @LOGCODE,
    @USER,
    @DESCRIPTION,
    @OBJ_TYPE,
    @OBJ_Name
)
"@

$sqlCmd
} #foreach

The biggest thing I see in this log is the structure is different after the tag header for each event type. I think it’s easier to put things into a PSObject to review first and then do the SQL part. The part after the tag is a comma-delimited content, so I just use ConvertFrom-Csv:

$log = @"
<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 ts="20210311040006" client=""> "39",”Save Data","Process 'Save Data' was executed."
<Commit ts="20210311125155" client="Steve"> "144","Steve","11.111.111.111","User 'Steve' successfully logged in from address '11.111.111.111'."
<Commit ts="20210311130404" client="Shelly"> "144","Shelly","11.111.111.111","User 'Shelly' successfully logged in from address '11.111.111.111'."
<Commit ts="20210311131122" client="Daniel"> "144","Daniel","11.111.111.111","User 'Daniel' successfully logged in from address '11.111.111.111'."
<Commit ts="20210311131122" client="Daniel"> "144","Daniel","11.111.111.111","User 'Daniel' successfully logged in from address '11.111.111.111'."
<Commit ts="20210311131747" client="Shelly"> "144","Shelly","11.111.111.111","User 'Shelly' successfully logged in from address '11.111.111.111'."
<Commit ts="20210311135147" client="Shelly"> "144","Shelly","11.111.111.111","User 'Shelly' successfully logged in from address '11.111.111.111'."
<Commit ts="20210311135151" client="Tom"> "144","Tom","11.111.111.111","User 'Tom' successfully logged in from address '11.111.111.111'."
<Commit ts="20210311135250" client=""> "21","Version","Dimension 'Version' was updated."
<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 ts="20210311135250" client=""> "21","Version","Dimension 'Version' was updated."
<Commit ts="20210311135250" client=""> "27","Version:Working Forecast","Version:04_08 Vs Working Forecast","1.0","-1.0","Element 'Version:Working Forecast' in consolidation 'Version:04_08 Vs Working Forecast' was changed from weight '1.0' to weight '-1.0'."
"@ -split [environment]::NewLine

foreach ( $row in $log ) {
    $arr = $row -split '>'
 
    #Parse the commit tag
    $null = $arr[0] -match '^<Commit ts="(?<commit_date>.*?)" client="(?<commit_client>.*?)"$'
    
    #Parse the data after the commit tag

    switch -Wildcard ($arr[1]) {
        '*144*' {
            $otherStuff = ConvertFrom-CSV -InputObject $arr[1] -Header Obj_Type,user,ip,description

            [PSCustomObject]@{
                CommitDate   = [datetime]::ParseExact($matches.commit_date, "yyyyMMddHHmmss",$null)
                CommitClient = $matches.commit_client
                ObjType      = $otherStuff.Obj_Type
                Stuff        = $otherStuff.ip
                Description  = $otherStuff.description
            }
        }
        '*39*' {

        }
        '*21*' {

        }
        '*27*' {

        }
    }

}

Output:

CommitDate   : 3/11/2021 4:00:06 AM
CommitClient : automation_user
ObjType      : 144
Stuff        : 122.2.2.2
Description  : User 'automation _user' successfully logged in from address '122.2.2.2'.

CommitDate   : 3/11/2021 12:51:55 PM
CommitClient : Steve
ObjType      : 144
Stuff        : 11.111.111.111
Description  : User 'Steve' successfully logged in from address '11.111.111.111'.

CommitDate   : 3/11/2021 1:04:04 PM
CommitClient : Shelly
ObjType      : 144
Stuff        : 11.111.111.111
Description  : User 'Shelly' successfully logged in from address '11.111.111.111'.

CommitDate   : 3/11/2021 1:11:22 PM
CommitClient : Daniel
ObjType      : 144
Stuff        : 11.111.111.111
Description  : User 'Daniel' successfully logged in from address '11.111.111.111'.

CommitDate   : 3/11/2021 1:11:22 PM
CommitClient : Daniel
ObjType      : 144
Stuff        : 11.111.111.111
Description  : User 'Daniel' successfully logged in from address '11.111.111.111'.

CommitDate   : 3/11/2021 1:17:47 PM
CommitClient : Shelly
ObjType      : 144
Stuff        : 11.111.111.111
Description  : User 'Shelly' successfully logged in from address '11.111.111.111'.

CommitDate   : 3/11/2021 1:51:47 PM
CommitClient : Shelly
ObjType      : 144
Stuff        : 11.111.111.111
Description  : User 'Shelly' successfully logged in from address '11.111.111.111'.

CommitDate   : 3/11/2021 1:51:51 PM
CommitClient : Tom
ObjType      : 144
Stuff        : 11.111.111.111
Description  : User 'Tom' successfully logged in from address '11.111.111.111'.

The challenge is mapping or concatenating the data into the SQL schema, but you just create custom objects with the switch. Then the for loop into SQL is just mapping the data rather trying to parse it directly into SQL.