CODEWITHSUNDEEP
powershell
fbe106360
fbe106360

Reputation: 71

Extracting data from a file and sort it in a new file with powershell

I'm working on a script which should give me the Time per Request of an Invoke ID. The Short lines is hold the Invoke ID. In the long Lines I recieve the message of the Server.

I need to structure the file by getting the ID and the ApplicationGatewayID and the time difference between sending and receiving.

My Plan was to get the request and the response on one line, although the file is sorted by the invokeID the pattern is not regurlar. Further there are a few IDs which only have the response and no request which makes it harder.

11:20:36:645 ra-agi Trace: Received Query Confirm message from application gateway host.  11:20:36:645 ra-agi Trace:     ApplicationGatewayID = 5001  11:20:36:645 ra-agi Trace:     InvokeID =             11359017 
11:20:36:645 ra-agi Trace: Received Query Confirm message from application gateway host.  11:20:36:645 ra-agi Trace:     ApplicationGatewayID = 5001  11:20:36:645 ra-agi Trace:     InvokeID =             11359018 
11:20:36:739 ra-agi Trace: Received Query Confirm message from application gateway host.  11:20:36:739 ra-agi Trace:     ApplicationGatewayID = 5001  11:20:36:739 ra-agi Trace:     InvokeID =             11359026 
11:20:36:723 ra-agi Trace: Received Query Confirm message from application gateway host.  11:20:36:723 ra-agi Trace:     ApplicationGatewayID = 5001  11:20:36:723 ra-agi Trace:     InvokeID =             11359027 
11:20:36:739 ra-agi Trace: Received Query Confirm message from application gateway host.  11:20:36:739 ra-agi Trace:     ApplicationGatewayID = 5001  11:20:36:739 ra-agi Trace:     InvokeID =             11359028 
11:20:36:739 ra-agi Trace: Received Query Confirm message from application gateway host.  11:20:36:739 ra-agi Trace:     ApplicationGatewayID = 5001  11:20:36:739 ra-agi Trace:     InvokeID =             11359029 
11:20:36:848 ra-agi Trace: Received Query Confirm message from application gateway host.  11:20:36:848 ra-agi Trace:     ApplicationGatewayID = 5001  11:20:36:848 ra-agi Trace:     InvokeID =             11359031 
11:20:36:645 11359032
11:20:36:645 ra-agi Trace: Received Query Confirm message from application gateway host.  11:20:36:645 ra-agi Trace:     ApplicationGatewayID = 5000  11:20:36:645 ra-agi Trace:     InvokeID =             11359032 
11:20:36:645 11359033
11:20:36:676 ra-agi Trace: Received Query Confirm message from application gateway host.  11:20:36:676 ra-agi Trace:     ApplicationGatewayID = 5000  11:20:36:676 ra-agi Trace:     InvokeID =             11359033 
11:20:36:848 ra-agi Trace: Received Query Confirm message from application gateway host.  11:20:36:848 ra-agi Trace:     ApplicationGatewayID = 5001  11:20:36:848 ra-agi Trace:     InvokeID =             11359034 
11:20:36:645 11359034
11:20:36:676 11359035
11:20:36:848 ra-agi Trace: Received Query Confirm message from application gateway host.  11:20:36:848 ra-agi Trace:     ApplicationGatewayID = 5001  11:20:36:848 ra-agi Trace:     InvokeID =             11359035 
11:20:36:739 ra-agi Trace: Received Query Confirm message from application gateway host.  11:20:36:739 ra-agi Trace:     ApplicationGatewayID = 5000  11:20:36:739 ra-agi Trace:     InvokeID =             11359036 
11:20:36:739 11359036
11:20:36:739 11359037

The end product should look like this: First column InvokeID, second Column ApplicationGatewayID, third column: time between sending and receiving

11359017    5001    127
11359018    5000    114
11359019    5001    105

Upvotes: 0

Views: 65

Answers (2)

Theo
Theo

Reputation: 61068

Seeing your last comment, and if I understand the question correctly, this should do it:

$result = (Get-Content -Path 'X:\TheInputFile.txt').Trim() | Where-Object { $_.Length -gt 8 } |
    Group-Object @{Expression = {$_.Substring($_.Length - 8)}} |  # group by InvokeID (last 8 characters of the trimmed line)
    Where-Object {$_.Count -eq 2 } |                              # select only groups with two items in it (one long and one short line)
    ForEach-Object {
        $invokeID  = $_.Name
        $millisOne = $millisTwo = 0   # two variables to store the parsed milliseconds from the time stamps
        foreach ($line in $_.Group) {
            $timeToParse = $line.Substring(0,12) -replace ':(\d{3}$)', '.$1' # replace the last colon ':' into a dot '.' for parsing
            if ($line -match 'ApplicationGatewayID\s+=\s+(\d+)') {           # if this is the long line..
                $gatewayID = $Matches[1]
                $millisOne = [TimeSpan]::Parse($timeToParse).TotalMilliSeconds
            }
            else {
                $millisTwo = [TimeSpan]::Parse($timeToParse).TotalMilliSeconds
            }
        }
        # output an object with the properties you need
        [PsCustomObject]@{
            'InvokeID'             = $invokeID
            'ApplicationGatewayID' = $gatewayID
            'ProcessTime'          = [Math]::Abs($millisOne - $millisTwo)
        }
    }

# output the result on screen
$result

# write the resukt to a new CSV file
$result | Export-Csv -Path 'X:\TheOutputFile.csv' -NoTypeInformation

Using your example input file yields this result:

InvokeID ApplicationGatewayID ProcessTime
-------- -------------------- -----------
11359032 5000                           0
11359033 5000                          31
11359034 5001                         203
11359035 5001                         172
11359036 5000                           0

Upvotes: 1

f6a4
f6a4

Reputation: 1782

This should working:

Get-Content 'D:\myfile.txt' | ? { $_ -like '*InvokeID*' } | ForEach-Object { 

    $a = @($_ -split ' +')
    $d = New-TimeSpan -Start ([System.DateTime](($a[0]).Substring(0,8))) -End ([System.DateTime](($a[11]).Substring(0,8)))
    "$($a[22]) $($a[16]) $d" 

   }

Upvotes: 0

Related Questions