CODEWITHSUNDEEP
arraysstringpowershellsearchtext-search
joe williams III
joe williams III

Reputation: 3

Pull specific data from text files in PowerShell with search parameters

We are trying to Pull AP name, bss, and ess values out of text files provided by a network team, in PowerShell 5.1. We have numerous files to go through and multiple AP Targets in each file.

Here's example of Text file. We need to extract the ap name, the ess, and bss values for each ess that matches "trustedwifi" and "guest" items only. The rest of the data is unneeded.

*********************************************************************************************************
 9/5/2021 7:42:16 AM    Target: AP01 (VC)    
*********************************************************************************************************
WIFI AP BSS Table
------------------
bss                ess              port  ip            band/ht-mode/bandwidth  ch/EIRP/max-EIRP  type  cur-cl  ap name   in-t(s)  tot-t            flags
---                ---              ----  --            ----------------------  ----------------  ----  ------  -------   -------  -----            -----
ab:cd:ef:gh:if:jk  trustedwifi      ?/?   A.b.c.d       2.4GHz/HT/20MHz         1/24.0/25.5       ap    0       AP01      0        43d:14h:48m:14s  K
ab:cd:ef:gh:if:jk  secure           ?/?   A.b.c.d       2.4GHz/HT/20MHz         1/24.0/25.5       ap    0       AP01      0        43d:14h:47m:53s  -
ab:cd:ef:gh:if:jk  guest            ?/?   A.b.c.d       2.4GHz/HT/20MHz         1/24.0/25.5       ap    0       AP01      0        22h:11m:43s      -
ab:cd:ef:gh:if:jk  trustedwifi      ?/?   A.b.c.d       5GHz/VHT/40MHz          100+/18.0/30.0    ap    0       AP01      0        43d:14h:48m:15s  K
ab:cd:ef:gh:if:jk  secure           ?/?   A.b.c.d       5GHz/VHT/40MHz          100+/18.0/30.0    ap    0       AP01      0        43d:14h:47m:54s  -
ab:cd:ef:gh:if:jk  guest            ?/?   A.b.c.d       5GHz/VHT/40MHz          100+/18.0/30.0    ap    0       AP01      0        22h:11m:44s      -

Channel followed by "*" indicates channel selected due to unsupported configured channel.
"Spectrum" followed by "^" indicates Local Spectrum Override in effect.

Num APs:6
Num Associations:0

Flags:       a = Airslice policy; A = Airslice app monitoring; c = MBO Cellular Data Capable BSS; d = Deferred Delete Pending; D = VLAN Discovered; E = Enhanced-open BSS without transition mode; I = Imminent VAP Down; K = 802.11K Enabled; m = Agile Multiband (MBO) BSS; M = WPA3-SAE mixed mode BSS; o = Enhanced-open transition mode open BSS; O = Enhanced-open BSS with transition mode; r = 802.11r Enabled; t = Broadcast TWT Enabled; T = Individual TWT Enabled; W = 802.11W Enabled; x = MBSSID Tx BSS; 3 = WPA3 BSS; 


*********************************************************************************************************
  9/5/2021 7:42:18 AM    Target: AP02    
*********************************************************************************************************
WIFI AP BSS Table
------------------
bss                ess              port  ip            band/ht-mode/bandwidth  ch/EIRP/max-EIRP  type  cur-cl  ap name   in-t(s)  tot-t            flags
---                ---              ----  --            ----------------------  ----------------  ----  ------  -------   -------  -----            -----
ab:cd:ef:gh:if:jk  trustedwifi      ?/?   A.b.c.d       2.4GHz/HT/20MHz         11/24.0/25.5      ap    0       AP02      0        43d:14h:47m:24s  K
ab:cd:ef:gh:if:jl  secure           ?/?   A.b.c.d       2.4GHz/HT/20MHz         11/24.0/25.5      ap    0       AP02      0        43d:14h:47m:3s   -
ab:cd:ef:gh:if:jm  guest            ?/?   A.b.c.d       2.4GHz/HT/20MHz         11/24.0/25.5      ap    0       AP02      0        22h:11m:43s      -
ab:cd:ef:gh:if:j0  rustedwifi       ?/?   A.b.c.d       5GHz/VHT/40MHz          108+/18.0/30.0    ap    0       AP02      0        43d:14h:47m:24s  K
ab:cd:ef:gh:if:jp  secure           ?/?   A.b.c.d       5GHz/VHT/40MHz          108+/18.0/30.0    ap    0       AP02      0        43d:14h:47m:4s   -
ab:cd:ef:gh:if:jq  guest            ?/?   A.b.c.d       5GHz/VHT/40MHz          108+/18.0/30.0    ap    0       AP02      0        22h:11m:43s      -

Preferred Output would be something like this repeated for each Ap name in the files with ALL records containing trustedwifi and guest. Repeated for every match that includes the two search criteria.

ap name           ess                   bss
AP01              Trustedwifi           ab:cd:ef:gh:if:jk
AP01              guest                 ab:cd:ef:gh:if:jm
AP02              Trustedwifi           ab:cd:ef:gh:if:jk
AP02              guest                 ab:cd:ef:gh:if:jm
.
.

Started with the following to try and solve it, but am stumped on how to get the data extracted and formatted properly to export to a .csv file

# Specify the directory to search
$directory = "C:\Your\Directory" 

# Specify the search terms
$searchTerms = "error", "warning", "exception"

# Get all .txt files in the directory
$files = Get-ChildItem -Path $directory -Filter "*.txt"

# Search for each term in each file
foreach ($file in $files) {
    foreach ($term in $searchTerms) {
        $matches = Select-String -Path $file.FullName -Pattern $term

        if ($matches) {
            Write-Host "File: $($file.FullName)"
            foreach ($match in $matches) {
                Write-Host "Line $($match.LineNumber): $($match.Line)"
            }
            Write-Host ""
        }
    }
}

But expect to end up with the output example from above.

Upvotes: 0

Views: 88

Answers (3)

Theo
Theo

Reputation: 61013

Assuming all columns in your files are in the same order and all fields are separated by at least 2 spaces like you show in your post, then a quick and dirty way of parsing out the values you need could be like below:

$sourcePath = 'D:\Test'
$outputFile = 'D:\Test\result.csv'

$result = Get-ChildItem -Path $sourcePath -Filter '*.txt' -File | ForEach-Object {
    switch -Regex -File $_.FullName {
        '^..:..:..:..:..:..' {
            $data = $_ -split '\s{2,}'
            # You could process only items where column 'ess' ($data[1]) 
            # is either 'trustedwifi' or 'guest' at this point:
            if ($data[1] -match 't?rustedwifi|guest') {
                # output an object with the columns needed
                [PsCustomObject]@{
                    appname = $data[8]
                    ess     = $data[1]
                    bss     = $data[0]
                }
            }
        }
    }
}

# save the result
$result | Export-Csv -Path $outputFile -NoTypeInformation

# if you didn't use the filtering for 'trustedwifi' or 'guest' in the above, you can do that here:
$result | Where-Object { $_.ess -match 't?rustedwifi|guest' } | Export-Csv -Path $outputFile -NoTypeInformation

P.S. In your example, there is a t missing in one of the rows for trustedwifi, so that is why the regex now uses t?rustedwifi. If that is just a typo in your post and does not occur in the real files, just remove the questionmark in the regex.

Upvotes: 0

iRon
iRon

Reputation: 23578

As a general solution, you might consider this (custom) ConvertFrom-SourceTable cmdlet:

The latest version (0.5.1, just uploaded) supports suppressing certain columns by supplying an empty column name:

Install-Script -Name ConvertFrom-SourceTable

Get-Content $Files | Select-String -Pattern '^\w\w:\w\w:\w\w:\w\w:\w\w:\w\w' |
  ConvertFrom-SourceTable -Header 'bss', 'ess', '', '', '', '', '', '', 'ap name'

Explanation:

  • The Select-String -Pattern '^\w\w:\w\w:\w\w:\w\w:\w\w:\w\w' command, selects all the lines (data rows) that start with a mac address
  • Each data row is passed to the ConvertFrom-SourceTable cmdlet which determines the columns based on their fixed size (and spaces in between).
  • The header names (-Header 'bss', 'ess', '', '', '', '', '', '', 'ap name') are applied to each of the corresponding column with the same index

Note that the above command will not work if the concerned tables have different column widths. In that case you might convert each table separately:

$Files | ForEach-Object { 
    Get-Content $_ | Select-String -Pattern '^\w\w:\w\w:\w\w:\w\w:\w\w:\w\w' |
      ConvertFrom-SourceTable -Header 'bss', 'ess', '', '', '', '', '', '', 'ap name'
}

For either command the order of the (captured) columns needs to same (e.g. the 'ap name' column is presumed to be the 9th column).

Upvotes: 1

jdweng
jdweng

Reputation: 34421

Try following

$filename = "c:\temp\test.txt"

$lines = Get-Content -Path $filename

$patternAP = 'Target:\s+(?<ap>[^\s]+)'
$patternBss = '^(?<bss>[a-z]{2}:[a-z]{2}:[a-z]{2}:[a-z]{2}:[a-z]{2}:[a-z0-9]{2})\s+(?<ess>[^\s]+)\s+(?<port>[^\s]+)\s+(?<ip>[^\s]+)\s+(?<band>[^\s]+)\s+(?<ch>[^\s]+)\s+(?<type>[^\s]+)\s+(?<cur>[^\s]+)\s+(?<apname>[^\s]+)\s+(?<in>[^\s]+)\s+(?<tot>[^\s]+)\s+(?<flags>[^\s]+)'
$table = [System.Collections.Generic.List[pscustomobject]]::new()
foreach($line in $lines)
{
   if($line -match $patternAP)
   {
      $line -match $patternAP | Out-Null
      $ap = $matches.ap
   } `
   else
   {

      if($line -match $patternBss)
      {
         $line -match $patternBss | Out-Null
         if($matches.ess -eq 'guest')
         {
             $newRow = [pscustomobject]@{
                ap = $ap
                bss = $matches.bss
                ess = $matches.ess
                port = $matches.port
                ip = $matches.ip
                'band/ht-mode/bandwidth' = $matches.band
                'ch/EIRP/max-EIRP' = $matches.ch
                type = $matches.type
                'curl-cl' = $matches.cur
                'ap name' = $matches.apname
                'in-t(s)' = $matches.in
                'tot-t' = $matches.tot
                'flags' = $matches.flags
             }
             $table.Add($newRow)  | Out-Null
         }
      }
   }
}

$table | format-list

Results

ap                     : AP01
bss                    : ab:cd:ef:gh:if:jk
ess                    : guest
port                   : ?/?
ip                     : A.b.c.d
band/ht-mode/bandwidth : 2.4GHz/HT/20MHz
ch/EIRP/max-EIRP       : 1/24.0/25.5
type                   : ap
curl-cl                : 0
ap name                : AP01
in-t(s)                : 0
tot-t                  : 22h:11m:43s
flags                  : -

ap                     : AP01
bss                    : ab:cd:ef:gh:if:jk
ess                    : guest
port                   : ?/?
ip                     : A.b.c.d
band/ht-mode/bandwidth : 5GHz/VHT/40MHz
ch/EIRP/max-EIRP       : 100+/18.0/30.0
type                   : ap
curl-cl                : 0
ap name                : AP01
in-t(s)                : 0
tot-t                  : 22h:11m:44s
flags                  : -

ap                     : AP02
bss                    : ab:cd:ef:gh:if:jm
ess                    : guest
port                   : ?/?
ip                     : A.b.c.d
band/ht-mode/bandwidth : 2.4GHz/HT/20MHz
ch/EIRP/max-EIRP       : 11/24.0/25.5
type                   : ap
curl-cl                : 0
ap name                : AP02
in-t(s)                : 0
tot-t                  : 22h:11m:43s
flags                  : -

ap                     : AP02
bss                    : ab:cd:ef:gh:if:jq
ess                    : guest
port                   : ?/?
ip                     : A.b.c.d
band/ht-mode/bandwidth : 5GHz/VHT/40MHz
ch/EIRP/max-EIRP       : 108+/18.0/30.0
type                   : ap
curl-cl                : 0
ap name                : AP02
in-t(s)                : 0
tot-t                  : 22h:11m:43s
flags                  : -

After reviewing code I found a simpler method. Original code was geting AP from line between the asterisks. New code is getting AP from column 'ap -name'

$filename = "c:\temp\test.txt"

$lines = Get-Content -Path $filename

$patternBss = '^(?<bss>[a-z]{2}:[a-z]{2}:[a-z]{2}:[a-z]{2}:[a-z]{2}:[a-z0-9]{2})\s+(?<ess>[^\s]+)\s+(?<port>[^\s]+)\s+(?<ip>[^\s]+)\s+(?<band>[^\s]+)\s+(?<ch>[^\s]+)\s+(?<type>[^\s]+)\s+(?<cur>[^\s]+)\s+(?<apname>[^\s]+)\s+(?<in>[^\s]+)\s+(?<tot>[^\s]+)\s+(?<flags>[^\s]+)'
$table = [System.Collections.Generic.List[pscustomobject]]::new()
foreach($line in $lines)
{
   if($line -match $patternBss)
   {
      $line -match $patternBss | Out-Null
      if($matches.ess -eq 'guest')
      {
          $newRow = [pscustomobject]@{
             bss = $matches.bss
             ess = $matches.ess
             port = $matches.port
             ip = $matches.ip
             'band/ht-mode/bandwidth' = $matches.band
             'ch/EIRP/max-EIRP' = $matches.ch
             type = $matches.type
             'curl-cl' = $matches.cur
             'ap name' = $matches.apname
             'in-t(s)' = $matches.in
             'tot-t' = $matches.tot
             'flags' = $matches.flags
         }
         $table.Add($newRow)  | Out-Null
      }
   }
}

$table | format-list

Upvotes: 0

Related Questions