OratoCSV.ps1

# ---------------------------------------------

$ORAFullPath = 'C:\Users\ab\Downloads\tnsnames.ora'
$CSVFullPath = 'C:\Users\ab\Documents\tnsnames.csv'

#Defaults/hard coded/static values - these will appear in every row of the CSV.
#------------------------------------------------------------------------------

#For those columns marked #Dynamic (which come from the ora file) the value will revert to the default below if its not provided in the ora file.

$Password_name = $null #Should this include the name of the database (from the ora file)?
$TemplateSafe = $null
$CPMUser = $null
$Port = $null #Dynamic
$Safe = $null
$Folder = 'Root'
$Password = 'P@ssw0rd'
$DeviceType = 'Database'
$PolicyID = $null
#$Address = $null  #Dynamic - this is required: no ora value = no csv row.
$UserName = $null #Dynamic
$Type = $null
$VTY = $null
$ExtraPass1Name = $null
$ExtraPass1Safe = $null
$ExtraPass1Folder = $null
$ExtraPass2Name = $null
$ExtraPass2Safe = $null
$ExtraPass2Folder = $null
$ExtraPass3Name = $null
$ExtraPass3Safe = $null
$ExtraPass3Folder = $null
$Location = $null
$OwnerName = $null
$MasterPassName = $null
$MasterPassFolder = $null
$GroupName = $null
$ServiceName = $null #Dynamic
$RestartService = $null
$CPMDisabled = $null
$ResetImmediately = $null
$DSN = $null
$ClientDN = $null
$ServerDN = $null


#Main function

function Get-OraTnsAdminEntries
{
    param
    (
        [System.IO.FileInfo] $File
    )
    
    begin {}
    
    process
    {
        #Create an array to later output to csv.
        [object[]] $tnsEntries = @()        

        if ($_)
        {
            $File = [System.IO.FileInfo] $_
        }
        if (!$File)
        {
            Write-Error "Parameter -File  is required."
            break
        }
        if (!$File.Exists)
        {
            Write-Error "'$File.FullName' does not exist."
            break
        }
        
        [string] $data = gc $File.FullName | ? {!$_.StartsWith('#')}

        
        
        #Here is what we have to do to identify individual TNS entries..
        #----------------------------------------------------------------
        #1) Replace all "`n" with ""...so that we flatten out the file
        #2) Replace all " " with ""...so that there are no spaces
        #3) Replace all "`t" with ""...so that there are no tabs
        #4) Replace all ")))(" with "))("...remove false positive ))) as in the case of 8iQA01 for example which has ))) in the middle. for ADDRESS_LIST vs ADDERESS
        #5) Replace all ")))" with ")))`n"...so that we separate out individual entries into their own line
        #6) Replace all "=(" with "=;"...to separate by and identify individual entries
        #7) Replace all "(" with ""...to remove open brackets
        #8) Replace all ")" with ";"...to replace close brackets with ";"
        #9) Replace all ";;" with ";"...to not have multiple ;
        #10) Replace all ";;" with ";" again...to not have multiple ;
        
        
        #So this effectively flattens the ora file - specifically #1 above that removes the new line characters.
        #5 above ensures each database is back in its own line (by adding the new line character).
                
        $lines = $data.Replace("`n","").Replace(" ","").Replace("`t","").Replace(")))(","))(").Replace(")))",")))`n").Replace("=(","=;").Replace("(","").Replace(")",";").Replace(";;",";").Replace(";;",";").Split("`n")


        #At this point each line should look like this
        #----------------------------------------------------------------
        #$Service,$Service.WORLD=;DESCRIPTION=;ADDRESS=;PROTOCOL=$Protocol;Host=$Hostname;Port=$Port;CONNECT_DATA=;SERVICE_NAME=$Service;

        foreach ($line in $lines)
        {
            if ($line.Trim().Length -gt 0)
            {


                #Systems3000 Addition : Allow for a maximum of 10 addresses (per database)
                #-------------------------------------------------------------------------
                #This is really just a quick method of ensuring the ADDRESS item descriptors
                #are names differently. i.e. ADDRESS10, ADDRESS9, ADDRESS8 etc... so that the
                #values can be extracted individually.
                #(They appear in the file as ADDRESS= ADDRESS= ADDRESS= which we want to change
                #to make them different.)


                For([int]$nTo = 2; $nTo -le 10; $nTo++)
                {
                    
                    #So we rename the ADDRESS tags

                    [string]$sTo = $nTo.ToString()

                    [int]$nFrom = $nTo - 1

                    If($nFrom -eq 1)
                    {
                        #We look for ADDRESS first (no number)
                        [string]$sFrom = ''
                    }
                    else
                    {
                        [string]$sFrom = $nFrom.ToString()
                    }

                    #And replace a decreasing number of occurences until the first is numbered 10 and the remainder in descending order

                    [int]$nNum = 11 - $nTo

                    $re = [regex]";ADDRESS$sFrom="
                    $line = $re.Replace($line, ";ADDRESS$sTo=", $nNum)                

                    $re = [regex]";PROTOCOL$sFrom="
                    $line = $re.Replace($line, ";PROTOCOL$sTo=", $nNum)

                    $re = [regex]";HOST$sFrom="
                    $line = $re.Replace($line, ";HOST$sTo=", $nNum)

                    $re = [regex]";PORT$sFrom="
                    $line = $re.Replace($line, ";PORT$sTo=", $nNum)


                }

                #Replace ";" with "`n" so that each can become a name=value pair in a hash-table
                $lineBreakup = ConvertFrom-StringData -StringData $line.Replace(";","`n")

                #Added by Systems3000 to process a maximum of 10 addresses (per database)
                #------------------------------------------------------------------------
                #Having renamed the ADDRESS tags ADDRESS10, ADDRESS9, ADDRESS8 etc...
                #We loop in descending order to obtain each address value individually.

                for([int]$nAddress = 10; $nAddress -ge 1; $nAddress--)
                {
                    
                    if($nAddress -eq 1)
                    {
                        [string]$sAddressNum = ''
                    }
                    else
                    {
                        [string]$sAddressNum = $nAddress.ToString()
                    }

                    #If the corresponding ADDRESSn tag has a value then we create a new object
                    #(which will form its own csv row at the end)
                    if ($lineBreakup["HOST$sAddressNum"])
                    {
                        
                        #Create a new object which equals 1 csv row.
                        #Most of the columns are set statically using the values at the top.
                        #The Add-Member calls are in column order.

                        $tnsEntry = New-Object System.Object


                        #These lines are from the original code and might be required at some point.
                        #e.g. for the Password_name tag perhaps?

                        #$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 | Add-Member -type NoteProperty -name Name     -value $entryName
                        #$tnsEntry | Add-Member -type NoteProperty -name SimpleName -value ($entryName.Split(",")[0].Trim().Split(".")[0].Trim())  #Pick "MyDB" from "MyDB, MyDB.World" or "MyDB.World, MyDB"
                            

                        $tnsEntry | Add-Member -type NoteProperty -name Password_name -value $Password_name
                        $tnsEntry | Add-Member -type NoteProperty -name TemplateSafe -value $TemplateSafe
                        $tnsEntry | Add-Member -type NoteProperty -name CPMUser -value $CPMUser
                        
                        #Port is a 'dynamic' value but revert to the static value set at the top if not provided in the ora file.

                        if ($lineBreakup["PORT$sAddressNum"])
                        {
                            $tnsEntry | Add-Member -type NoteProperty -name Port -value $lineBreakup["PORT$sAddressNum"]
                        }
                        else
                        {
                            $tnsEntry | Add-Member -type NoteProperty -name Port -value $Port
                        }

                        $tnsEntry | Add-Member -type NoteProperty -name Safe -value $Safe
                        $tnsEntry | Add-Member -type NoteProperty -name Folder -value $Folder
                        $tnsEntry | Add-Member -type NoteProperty -name Password -value $Password
                        $tnsEntry | Add-Member -type NoteProperty -name DeviceType -value $DeviceType
                        $tnsEntry | Add-Member -type NoteProperty -name PolicyID -value $PolicyID
                                               
                        
                        $tnsEntry | Add-Member -type NoteProperty -name Address -value $lineBreakup["HOST$sAddressNum"]
                        
                        
                        #We place SID in the UserName column

                        #UserName is a 'dynamic' value but revert to the static value set at the top if not provided in the ora file.

                        if ($lineBreakup["SID"])
                        {
                            $tnsEntry | Add-Member -type NoteProperty -name UserName -value $lineBreakup["SID"]
                        }
                        else
                        {
                            $tnsEntry | Add-Member -type NoteProperty -name UserName -value $UserName
                        }


                        $tnsEntry | Add-Member -type NoteProperty -name Type -value $Type
                        $tnsEntry | Add-Member -type NoteProperty -name VTY -value $VTY
                        $tnsEntry | Add-Member -type NoteProperty -name ExtraPass1Name -value $ExtraPass1Name
                        $tnsEntry | Add-Member -type NoteProperty -name ExtraPass1Safe -value $ExtraPass1Safe
                        $tnsEntry | Add-Member -type NoteProperty -name ExtraPass1Folder -value $ExtraPass1Folder
                        $tnsEntry | Add-Member -type NoteProperty -name ExtraPass2Name -value $ExtraPass2Name
                        $tnsEntry | Add-Member -type NoteProperty -name ExtraPass2Safe -value $ExtraPass2Safe
                        $tnsEntry | Add-Member -type NoteProperty -name ExtraPass2Folder -value $ExtraPass2Folder
                        $tnsEntry | Add-Member -type NoteProperty -name ExtraPass3Name -value $ExtraPass3Name
                        $tnsEntry | Add-Member -type NoteProperty -name ExtraPass3Safe -value $ExtraPass3Safe
                        $tnsEntry | Add-Member -type NoteProperty -name ExtraPass3Folder -value $ExtraPass3Folder
                        $tnsEntry | Add-Member -type NoteProperty -name Location -value $Location
                        $tnsEntry | Add-Member -type NoteProperty -name OwnerName -value $OwnerName
                        $tnsEntry | Add-Member -type NoteProperty -name MasterPassName -value $MasterPassName
                        $tnsEntry | Add-Member -type NoteProperty -name MasterPassFolder -value $MasterPassFolder
                        $tnsEntry | Add-Member -type NoteProperty -name GroupName -value $GroupName

                        #ServiceName is a 'dynamic' value but revert to the static value set at the top if not provided in the ora file.
                        
                        If($lineBreakup["SERVICE_NAME"])
                        {
                            $tnsEntry | Add-Member -type NoteProperty -name ServiceName -value $lineBreakup["SERVICE_NAME"]
                        }
                        else
                        {
                            $tnsEntry | Add-Member -type NoteProperty -name ServiceName -value $ServiceName
                        }

                        #This line placed SID in the ServiceName column which we don't want.
                        #$tnsEntry | Add-Member -type NoteProperty -name ServiceName  -value $(if ($lineBreakup["SERVICE_NAME"] -eq $null) {$lineBreakup["SID"]} else {$lineBreakup["SERVICE_NAME"]})  #One of the two will have the value. Pick the one that does!

                        $tnsEntry | Add-Member -type NoteProperty -name RestartService -value $RestartService
                        $tnsEntry | Add-Member -type NoteProperty -name CPMDisabled -value $CPMDisabled
                        $tnsEntry | Add-Member -type NoteProperty -name ResetImmediately -value $ResetImmediately
                        $tnsEntry | Add-Member -type NoteProperty -name DSN -value $DSN
                        $tnsEntry | Add-Member -type NoteProperty -name ClientDN -value $ClientDN
                        $tnsEntry | Add-Member -type NoteProperty -name ServerDN -value $ServerDN

                        #Add the new object to the array which we will later pipe to csv.
                        $tnsEntries += $tnsEntry


                    } #If HOST is populated
                    
                } # ADDRESS 10->1 loop

            } #IF $line length > 0

        } #For each $line

        #Pipe the array of objects to the csv path given at the top (after deleting it)

        remove-item -Path $CSVFullPath -ErrorAction SilentlyContinue

        $tnsEntries | Export-csv -Path $CSVFullPath -NoTypeInformation

    }
    
    end {}
}



Get-OraTnsAdminEntries $FilePath