Extracting exact information from a text file using Powershell

Hi All,

I have a text file with the Contents below. I have to fetch only the – CTIA File Checks – info i.e the 3 lines and then Take out the OK or NOT OK value shown below.

OK: ‘Nortel Data Load’, Expected: 2016-08-30 00:00:00, found: 2016-08-30 02:03:00
NOT OK: ‘AVAYA Data Load’, Expected: 2016-08-30 00:00:00, found: 2016-08-30 02:14:56
OK: ‘AUX Data Load’, Expected: 2016-08-30 00:00:00, found: 2016-08-30 02:29:27

Here’s the Text file -> https://gist.github.com/anonymous/6dc2701ad4adb24a0f1ce09978502a36

Here’s a start.

gc .\text.txt | Select-String “CTIA” -Context 3

Well, that gives the output as below :

Connecting to ‘ABC-043’…
OK

> – CTIA File Checks –
OK: ‘Nortel Data Load’, Expected: 2016-08-30 00:00:00, found: 2016-08-30 02:03:00
NOT OK: ‘AVAYA Data Load’, Expected: 2016-08-30 00:00:00, found: 2016-08-30 02:14:56
OK: ‘AUX Data Load’, Expected: 2016-08-30 00:00:00, found: 2016-08-30 02:29:27

How to extract the exaxt OK and NOT OK from this?

$file = Get-Content c:\myfile.txt
$isCTIA = $False

$lines = ForEach($line in $file)
{
    If(-not $isCTIA)
    {
        If($line -match 'CTIA File Checks')
        {
            $isCTIA = $True
            Continue
        }
    }
    ElseIf (-not $line.Trim())
    {
        $isCTIA = $False
        Continue
    }
    Else
    {
        $line
    }
}

ForEach ($line in $lines)
{
    $line.Split(":")[0]
}

Something along those lines?

my bad, -context 0,3

Here’s another way.

(gc .\text.txt) -match “Nortel Data Load|AVAYA Data Load|AUX Data Load”

Use methods such as trimstart,split,replace.

Thanks Craig and Dan, that worked perfectly :slight_smile:

My co-workers have a tendency to plop my code into their scripts and never learn why. String manipulation is crucial to learning powershell. I’ve had this bookmarked for about six years.

https://technet.microsoft.com/en-us/library/ee692804.aspx

((gc .\text.txt) -match “Nortel Data Load|AVAYA Data Load|AUX Data Load”) -split 'OK: ’ |?{$_ -match ‘data’}

(gc .\text.txt | Select-String “CTIA” -Context 0,3) -split “`n” | %{$_ -split ’ OK: '} | ?{$_ -match ‘data’}

There are multiple ways to do it. I started by removing stuff I didn’t care about like whitespace and log comments:

#Get the Log and remove any line that doesn't have at least 2 semicolons
$log = Get-Content -Path C:\Users\Rob\Desktop\Archive\test.txt | Where {$_ -like "*:*:*"}

Then you can use a WHERE statement because each row you care about in this case contains “Data load” or even just data in the file you provided:

PS C:\Users\Rob> $log | Where{$_ -like "*Data*"}
OK: 'Nortel Data Load', Expected: 2016-08-30 00:00:00, found: 2016-08-30 02:03:00
OK: 'AVAYA Data Load', Expected: 2016-08-30 00:00:00, found: 2016-08-30 02:14:56
OK: 'AUX Data Load', Expected: 2016-08-30 00:00:00, found: 2016-08-30 02:29:27

or SELECT-STRING:

PS C:\Users\Rob> $log | Select-String "Data"


OK: 'Nortel Data Load', Expected: 2016-08-30 00:00:00, found: 2016-08-30 02:03:00
OK: 'AVAYA Data Load', Expected: 2016-08-30 00:00:00, found: 2016-08-30 02:14:56
OK: 'AUX Data Load', Expected: 2016-08-30 00:00:00, found: 2016-08-30 02:29:27
Or you can even go a bit further and actually do a full parse of the log.  Not sure how to define the columns, but you should get the jest:

#Get the Log and remove any line that doesn't have at least 2 semicolons
$log = Get-Content -Path C:\Users\Rob\Desktop\Archive\test.txt | Where {$_ -like "*:*:*"}

$results = foreach ($line in $log) {
    $arrLine = $line -split ","

    $props = [ordered]@{
        Status=$arrLine[0].Split(":")[0]
        Message=$arrLine[0].Split(":")[1]
        Threshold=$arrLine[1]
        Current=$arrLine[2]

    }

    New-Object -TypeName PSObject -Property $props
}

which gives you a object to work with:

PS C:\Users\Rob> $results | Select -First 10

Status Message                                          Threshold                      Current                    
------ -------                                          ---------                      -------                    
OK      VW_PARTITIONED_FCT_CALL_DETAIL_END               Expected: 2016-08-30 06:42:58  found: 2016-08-30 06:56:30
OK      VW_PARTITIONED_FCT_CALL_DETAIL_LEG               Expected: 2016-08-30 06:42:58  found: 2016-08-30 06:55:50
OK      VW_CUS_PARTITIONED_FCT_TCD_CORRELATED            Expected: 2016-08-30 05:58:11  found: 2016-08-30 06:37:25
OK      VW_CUS_PARTITIONED_FCT_TCD_CONTACT_CORRELATED    Expected: 2016-08-30 05:58:11  found: 2016-08-30 06:37:25
OK      VW_CUS_PARTITIONED_FCT_SOFTPHONE_TRANSFER_EVENT  Expected: 2016-08-30 05:58:15  found: 2016-08-30 06:55:59
OK      VW_CUS_PARTITIONED_FCT_AGENT_EVENT               Expected: 2016-08-29 23:30:00  found: 2016-08-29 23:59:59
OK      VW_CUS_PARTITIONED_SUM_AGENT_EVENT_HH            Expected: 2016-08-29 23:30:00  found: 2016-08-29 23:30:00
OK      VW_CUS_PARTITIONED_SUM_AGENT_HH                  Expected: 2016-08-29 23:30:00  found: 2016-08-29 23:30:00
OK      VW_CUS_PARTITIONED_FCT_AVAYA_AGENT_EVENT         Expected: 2016-08-29 23:30:00  found: 2016-08-29 23:59:59
OK      VW_CUS_PARTITIONED_SUM_AVAYA_AGENT_EVENT_HH      Expected: 2016-08-29 23:30:00  found: 2016-08-29 23:30:00

Then you would parse the Message column and could remove the “Status”:

PS C:\Users\Rob> $results | Where{$_.Message -like "*Data*"}  | Select Message, Threshold, Current


Message             Threshold                      Current                    
-------             ---------                      -------                    
 'Nortel Data Load'  Expected: 2016-08-30 00:00:00  found: 2016-08-30 02:03:00
 'AVAYA Data Load'   Expected: 2016-08-30 00:00:00  found: 2016-08-30 02:14:56
 'AUX Data Load'     Expected: 2016-08-30 00:00:00  found: 2016-08-30 02:29:27