Powershell - Extract timestamp-CodePudding

i am completely new to Powershell and I am trying to create a script to extract the timestamp for the string found in a log file. There are log files everyday and it should select the latest file. It then looks for a string in that file. Now, where it finds the string it should also return a timestamp where the string is located. I am only able to complete the first part and struggling with the 2nd bit. My script is in no way optimal but it is a start. Any help is appreciated.

Set-Location -Path "O:\xyz\0502251\logs"
$latest = (Get-ChildItem -Path $dir -Filter 'async_SPARKLE_ONITE_*.log' | Sort-Object CreationDate -Descending | Select-Object -last 1).fullname
$pattern = "Successfulyl finished running psp_dba_maint_4"
$search = (Get-Content $latest | Select-String -Pattern "psp_dba_maint_4" | Select-Object -last 1)
    if($search)
    {
   
    "The overnight completed at:" 
    }
    else
    {
        "Do something"
    }

This looks for the latest file and extracts the string. Now Just want to know at what time this log was generated.

The log is:

Connection 1
Server message: 2022-10-15 23:56:07:020
Message number: 0, Severity 10, State 1, Line 126
Procedure 'psp_dba_locklogin'
Message String: -----> login SPRK_SVC_IRISS logged in at 23:55:45 running SRI.exe:AWAITING COMMAND on rptSPARKLE from host da-pvrep02 is still active


Connection 1
Server message: 2022-10-15 23:56:07:020
Message number: 0, Severity 10, State 1, Line 126
Procedure 'psp_dba_locklogin'
Message String: -----> login SPRK_SVC_IRISS logged in at 23:55:45 running SRI.exe:AWAITING COMMAND on rptSPARKLE from host da-pvrep05 is still active


Connection 1
Server message: 2022-10-15 23:56:07:020
Message number: 0, Severity 10, State 1, Line 126
Procedure 'psp_dba_locklogin'
Message String: -----> login SPRK_SVC_IRISS logged in at 23:55:39 running SRI.exe:SELECT on rptSPARKLE from host da-pvrep02 is still active


Connection 1
Server message: 2022-10-15 23:56:07:020
Message number: 0, Severity 10, State 1, Line 126
Procedure 'psp_dba_locklogin'
Message String: -----> login SPARKLE_CAI logged in at 23:55:37 running SPARKLE ESB (pooled):AWAITING COMMAND on A_uatSPK from host DA-UVESB01 is still active


Connection 1
Server message: 2022-10-15 23:56:07:020
Message number: 0, Severity 10, State 1, Line 355
Procedure 'psp_dba_dbrefresh'
Message String: Setting PrivShield on SPRKSZCBS OUT OF maintenance mode


Connection 1
Server message: 2022-10-15 23:56:07:022
Message number: 0, Severity 10, State 1, Line 362
Procedure 'psp_dba_dbrefresh'
Message String:  


Connection 1
Server message: 2022-10-15 23:56:07:022
Message number: 0, Severity 10, State 1, Line 364
Procedure 'psp_dba_dbrefresh'
Message String:    - Refresh 2 complete


Connection 1
Server message: 2022-10-15 23:56:07:028
Message number: 0, Severity 10, State 1, Line 42
Procedure 'psp_dba_maint_4'
Message String: Successfulyl finished running psp_dba_maint_4

This has different values in files generated everyday. So it should look for the latest file and return the result as The overnight completed at: 23:56.

CodePudding user response：

Here is a variant using the switch statement to efficiently process the file line-by-line and test each line to match given RegEx patterns:

$found = switch -File $latest -RegEx {
    '\s(\d\d:\d\d)' { $timestamp = $matches[1] }
    'Successfulyl finished running psp_dba_maint_4' { $true; break }
}

if( $found ) {
    "The overnight completed at: $timestamp"
}
else {
    "Not found"
}

$found = switch ... assigns the output of the switch statement to the variable. The output will be $true if the pattern has been found.
\s(\d\d:\d\d) searches for the time which is delimited from the date by a space character. To extract time without the space character, use a group () and the automatic variable $matches[1] then gives the value matched by this group.
Although there is no $false output of the switch statement, an empty output evaluates to $false in a boolean context (in this case the if statement), so if the pattern isn't found, the else branch will be entered.
There is a typo in Successfulyl which I just copied from your example ;)

CodePudding user response：

As you need to get the Time information only related to the procedure psp_dba_maint_4 you can do:

#Remove empty lines, join strings and split string at "connection 1" = you keep the related information together. Next parse the array of strings for the string which matches "psp_dba_maint_4" and after that extract dateTime

$null = (((Get-Content $latest | ?{$_}) -join $null) -split "connection 1" | ?{$_ -match "psp_dba_maint_4"}) -match '\d{2}:\d{2}:\d{2}'
$Search = $matches[0]
if($search){
    "The overnight completed at: $search" 
}
else{
    "Do something"
}

Alternatively you could parse the log an create objects, e.g.:

$obj = @(
    Get-Content $latest | ?{$_} | %{
        #If string is connection 1 initialize hashtable/create object
        If ($_ -match 'connection 1'){
            If ($attrsHT){
                new-object -typename psobject -Property $attrsht
            }
            $attrsHt = @{}
        }
        Else {
            #replace Procedure with Procedure, replace ": " with | and split at |
            $split = (($_ -replace "Procedure","Procedure:") -replace ": ","|") -split "\|"
            #add key and value to hashtable
            $attrsht.add($split[0],$split[1])
        }
    }
)

gives you the array $obj containing the loginformation:

$obj[0] | fl *

Procedure      : 'psp_dba_locklogin'
Message String : -----> login SPRK_SVC_IRISS logged in at 23:55:45 running SRI.exe:AWAITING COMMAND on
                 rptSPARKLE from host da-pvrep02 is still active
Server message : 2022-10-15 23:56:07:020
Message number : 0, Severity 10, State 1, Line 126

CodePudding user response：

To complement zett42's helpful switch-based answer:

A Select-String solution, as you attempted, is possible, via the -Context parameter, which allows you to capture the lines surrounding a matching line as well:

$time = 
  Select-String -LiteralPath $latest -Pattern 'psp_dba_maint_4' -Context 3 | 
  Select-Object -Last 1 |
  ForEach-Object {
    # Extract and output the 'HH:mm' part of the timestamp from 
    # the 3rd line above the match (the first element in the pre-context array)
    $_.Context.PreContext[0] -replace '^.  (..:..). $', '$1'
  }

if ($time) {
  "The overnight completed at: $time" 
} else {
  'Do something'
}

Note:

It is much more efficient to pass the file path of the target file to Select-String, via -LiteralPath, than it is to pass the file's content, line by line, via Get-Content.
- If you had assigned the whole file-info object (System.IO.FileInfo), as emitted by Get-ChildItem, to $latest, you could alternatively have provided it via the pipeline: $latest | Select-String ...
The type of the match-information objects that Select-String emits is Microsoft.PowerShell.Commands.MatchInfo.
The regex-based -replace operator is used to extract (part of) the time-of-day string from the context line.
- For an explanation of the above regex (and substitution, where $1 refers to the first and only capture group, (...)), see this regex101.com page.