Parse a TNS file - ARRAY?

PowerShell Help
1

Im quite new to powershell scripting so go easy. If anyone here works with oracle (or not, its just a file with addresses) , Im trying to parse a TNSnames.ora file and build a set of commands in to talk to each database. There are 2 different types of entry, 1 for single instance entries where we have ADDRESS= but another type where its a clustered environment and there could be multiple entries so here we have ADDRESS_LIST=

From looking others ideas, the goal is to strip it down for each entry to variables such as

name=entry1;address=,host=;port=;service_name=

and once we do we can run commands on those parameters.

TNSnames.ora file starts here


ENTRY1.world =
 (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = HOST1)(PORT = 1521))
  (CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = service1)
  )
)
   

ENTRY2.WORLD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (LOAD_BALANCE = OFF)
      (FAILOVER = ON)
      (ADDRESS = (PROTOCOL = TCP)(HOST = HOST3)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = HOST4)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = service2)
      (FAILOVER_MODE =
        (TYPE = SESSION)
        (METHOD = BASIC)
        (RETRIES = 2)
        (DELAY = 2)
      )
    )
  )

# tnsnames.ora ends

So for the above 2 entries we’d have
name=ENTRY1;address=,host=HOST1;port=1521;service_name=service1

But we would want multiple sets for the second entry
name=ENTRY2;address=,host=HOST3;port=1521;service_name=service2
name=ENTRY2;address=,host=HOST4;port=1522;service_name=service2

I think if found ADDRESS_LIST there has to be an array of entries for each distinct ADDRESS= line.

how to go about that?

Heres my attempt, works for first ENTRY but fails for second due to ADDRESS_LIST entry



function Get-OraTnsAdminEntries
{
    param
    (
        [System.IO.FileInfo] $File
    )
    begin {}
    process
    {
        [object[]] $tnsEntries = @()        
        if ($_)
        {
            $File = [System.IO.FileInfo] $_
        }
        [string] $data = gc $File.FullName | select-string -PAttern '#' -NotMatch
         
        $lines =  $data.Replace("`n","").Replace(" ","").Replace("`t","").Replace(")))(","))(").Replace(")))",")))`n").Replace("=(","=;").Replace("(","").Replace(")",";").Replace(";;",";").Replace(";;",";").Split("`n")  
        
        foreach ($line in $lines  | where{$_ -ne ""} )
        {
        $line
            if ($line.Trim().Length -gt 0)
            {
                $lineBreakup = ConvertFrom-StringData -StringData $line.Replace(";","`n") | where{$_ -ne ";"} | where{$_ -ne ""} 


     IF (([regex]::Matches($line, "ADDRESS" )).count -eq 1)
         {
                $entryName = $line.Split("=")[0]        #Everything to the left of the first "=" in "$Service,$Service.WORLD=;DESCRIPTION=;ADDRESS=;PROTOCOL=$Protocol;Host=$Hostname;Port=$Port;CONNECT_DATA=;SERVICE_NAME=$Service;"
                $tnsEntry = New-Object System.Object 
                $tnsEntry | Add-Member -type NoteProperty -name Name     -value $entryName.trim(";")
                $tnsEntry | Add-Member -type NoteProperty -name SimpleName -value ($entryName.Split(",")[0].Trim().Split(".")[0].Trim().trim(";"))  
                $tnsEntry | Add-Member -type NoteProperty -name Protocol -value $lineBreakup["PROTOCOL"]
                $tnsEntry | Add-Member -type NoteProperty -name Host     -value $lineBreakup["Host"]
                $tnsEntry | Add-Member -type NoteProperty -name Port     -value $lineBreakup["Port"]
                $tnsEntry | Add-Member -type NoteProperty -name Service  -value $(if ($lineBreakup["SERVICE_NAME"] -eq $null) {$lineBreakup["SID"]} else {$lineBreakup["SERVICE_NAME"]}) 
                $tnsentry
                if ( $entryName.Length -gt 1)
                {
                    $tnsEntries += $tnsEntry
                }
            }
        }
       #$tnsEntries
    }
     }
    end {}
}

I think will need an array to loop around the addresses if the word ADDRESS_LIST is found

PS P:\users\>  Get-OraTnsAdminEntries -File "tnsnames.ora"
ENTRY1.world=;DESCRIPTION=;ADDRESS=;PROTOCOL=TCP;HOST=HOST1;PORT=1521;CONNECT_DATA=;SERVER=DEDICATED;SERVICE_NAME=service1;


Name       : ENTRY1.world
SimpleName : ENTRY1
Protocol   : TCP
Host       : HOST1
Port       : 1521
Service    : service1

ENTRY2.WORLD=;DESCRIPTION=;ADDRESS_LIST=;LOAD_BALANCE=OFF;FAILOVER=ON;ADDRESS=;PROTOCOL=TCP;HOST=HOST3;PORT=1521;ADDRESS=;PROTOCOL=TCP;HOST=HOST4;PORT=1522;CONNECT_DATA=;
SERVICE_NAME=service2;FAILOVER_MODE=;TYPE=SESSION;METHOD=BASIC;RETRIES=2;DELAY=2;
ConvertFrom-StringData : Data item 'ADDRESS' in line 'ADDRESS=' is already defined. 
At line:26 char:32
+ ... neBreakup = ConvertFrom-StringData -StringData $line.Replace(";","`n" ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [ConvertFrom-StringData], PSInvalidOperationException
    + FullyQualifiedErrorId : InvalidOperation,Microsoft.PowerShell.Commands.ConvertFromStringDataCommand
 
;



PS P:\users\>
2

c,
Welcome to the forum. :wave:t3:

Before we proceed … could you please go back, edit your question once again and fix the formatting of your sample data. Do not use blockquotes. Use code formatting instead.

3

fixed up as requested… thanks

4

There may be tns parsers out there already, but this is the kind of stuff I like to mess around with. Here is how I’d parse this in your particular example

$orafile = 'C:\temp\tnsnames.ora'

switch -Regex -File $orafile {
    '^([^\s#]+)\s' {
        $currentlist = New-Object System.Collections.Generic.List[hashtable]
        $base = [ordered]@{
            Name = $matches.1
            SimpleName = ($matches.1).split('.')[0]
        }
    }
    '^\s*\((\S+).*?=\s*(.+)\)' {
        if($matches.1 -eq 'ADDRESS'){
            $currentlist.Add($base + ($matches.2 -replace '\(|\)',"`n" | ConvertFrom-StringData))
        }
        elseif($matches.1 -eq 'SERVICE_NAME'){
            foreach($entry in $currentlist){
                [PSCustomObject]($entry + @{Service = $matches.2})
            }
        }
    }
}

First we start by reading the file with a switch statement. This is very performant compared to many other methods. The logic is

  • If the line doesn’t start with a space or a #, it’s the name.
    1. Take that and split at a period to get what you called the SimpleName as well. If there is no period, it will be the same as Name. These two are initialized into a base hash table.
    2. Create a temporary list to hold 1 or more address hashtables. (this way it’s consistent between 1 or more than 1)
  • If the line starts with a space and contains an equals sign
    1. If the first match is -eq to ADDRESS replace the extra parenthesis with newline character and process with ConvertFrom-StringData. This produces a hashtable from the resulting key/value pairs. We add this hashtable with the base hashtable and store it in the temporary list of address hashtables.
    2. If the first match is -eq to SERVICE_NAME we create another hashtable to combine with each address hashtable and cast to [PSCustomObject] which outputs implicitly.

The resulting output from your sample file is

Name       : ENTRY1.world
SimpleName : ENTRY1
PORT       : 1521
HOST       : HOST1
PROTOCOL   : TCP
Service    : service1

Name       : ENTRY2.WORLD
SimpleName : ENTRY2
PORT       : 1521
HOST       : HOST3
PROTOCOL   : TCP
Service    : service2

Name       : ENTRY2.WORLD
SimpleName : ENTRY2
PORT       : 1522
HOST       : HOST4
PROTOCOL   : TCP
Service    : service2

To capture this into a variable just put the variable assignment in front of the switch statement.

$orafile = 'C:\temp\tnsnames.ora'

$output = switch -Regex -File $orafile {
    '^([^\s#]+)\s' {
        $currentlist = New-Object System.Collections.Generic.List[hashtable]
        $base = [ordered]@{
            Name = $matches.1
            SimpleName = ($matches.1).split('.')[0]
        }
    }
    '^\s*\((\S+).*?=\s*(.+)\)' {
        if($matches.1 -eq 'ADDRESS'){
            $currentlist.Add($base + ($matches.2 -replace '\(|\)',"`n" | ConvertFrom-StringData))
        }
        elseif($matches.1 -eq 'SERVICE_NAME'){
            foreach($entry in $currentlist){
                [PSCustomObject]($entry + @{Service = $matches.2})
            }
        }
    }
}

Now that it’s stored in $output we can process it further

# Format as table
$output | Format-Table

Name         SimpleName PORT HOST  PROTOCOL Service 
----         ---------- ---- ----  -------- ------- 
ENTRY1.world ENTRY1     1521 HOST1 TCP      service1
ENTRY2.WORLD ENTRY2     1521 HOST3 TCP      service2
ENTRY2.WORLD ENTRY2     1522 HOST4 TCP      service2

# export to csv
$output | Export-Csv \path\to\csvfile.csv -NoTypeInformation

Now if you wanted to collect all the different bits of data for each address, it would be more complicated. Since properties vary you’d have to keep track of all unique properties, etc. As I said, this suggestion is strictly for your current requirements.

1 Like
5

Wow. Very nice. That’s a very elegant solution. :+1:t3: :+1:t3: :+1:t3: :love_you_gesture:t3:

1 Like
6

thats fantastic…thank you!!!

I was playing around with ai tools last night, and asked it to write me a script to parse a tnsnames expanded for cluster address_list entries.
Chatgpt – attempt was awful wouldnt work
googles bard - worse than chatgpt
Microsfot Bing- was superb, excellent effort that got very close.

Dougs offering above the best by an absolute country mile. My real TNS has hundreds of entries and this worked on all of them… with a couple of exceptions that are not the scripts fault. With a small bit of formatting on my TNS I can make it more standardized to fit the script.

example, if a TNS entry had (DESCRIPTION without a leading space, that the word "(DESCRIPTION "became the name


ENTRY1.world =
 (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = server1)(PORT = 1521))
  (CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = service1)
  )
)
   

ENTRY2.world =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1521))
  (CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = service2)
  )
)

and output looked like 

Name         SimpleName   Service  HOST    PORT PROTOCOL
----         ----------   -------  ----    ---- --------
ENTRY1.world ENTRY1       service1 server1 1521 TCP     
(DESCRIPTION (DESCRIPTION service2 server2 1521 TCP

so I just edited the TNS to add a space before all (DESC and that worked

7

Glad to hear. Yeah “AI” has a loooong way to go. I catch them making stuff up constantly. You would think they would’ve made sure the dang things didn’t straight up lie. It’s funny as you can call them out “That cmdlet doesn’t seem to exist for the module listed” … “You are correct, that cmdlet is not part of module xxx” THEN WHY THE HECK DID YOU SUGGEST IT? haha silly “AI”

8

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.