Extracting exact information from a text file using Powershell

This topic contains 7 replies, has 4 voices, and was last updated by Profile photo of Rob Simmers Rob Simmers 3 weeks, 4 days ago.

Viewing 8 posts - 1 through 8 (of 8 total)
  • Author
    Posts
  • #52998
    Profile photo of Amar Helloween
    Amar Helloween
    Participant

    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 ->

    #53000
    Profile photo of Dan Potter
    Dan Potter
    Participant

    Here's a start.

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

    #53004
    Profile photo of Amar Helloween
    Amar Helloween
    Participant

    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?

    #53011
    Profile photo of Craig Duff
    Craig Duff
    Participant
    $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?

    #53013
    Profile photo of Dan Potter
    Dan Potter
    Participant

    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.

    #53015
    Profile photo of Amar Helloween
    Amar Helloween
    Participant

    Thanks Craig and Dan, that worked perfectly 🙂

    #53027
    Profile photo of Dan Potter
    Dan Potter
    Participant

    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'}

    #53033
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    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
    
Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic.