powershell merge

Sara 401 Reputation points
2023-05-04T15:38:46.7666667+00:00

I have 3 separate scripts where it collects and saves the data into a SQL Table, I am new to PowerShell and I am looking for help to merge all this into a single script as pretty much all the lines are same except the "table names and the sql select query".script1.txt script2.txt script3.txt

Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,427 questions
PowerShell
PowerShell
A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
2,216 questions
{count} votes

Accepted answer
  1. Rich Matheisen 45,416 Reputation points
    2023-05-05T22:50:04.66+00:00

    I think this is what you asked for. For each set of parameters, it will collect the information for all servers and write each servers data to a database table.

    Keep in mind that I don't have the software to verify it does what I think it should! Make sure to test!

    [CmdletBinding()]
    param(
        [Parameter(Mandatory=$false)]
        [string]$Email = "test.com"
    )
    Begin {
        $modules = @(
            "secretserver"
            "automation"
        )
        # Load  modules if not present
        if (-not (get-module $modules)) {
            Import-AHModule $modules -ErrorAction Stop
        }
        Import-Module "SqlServer","dbatools","NetScaler" -Verbose:$false -ErrorAction Stop
    
        # Name of SQL Instance where database is located
        $SqlInstance = "dbserv"
    
        # Name of the database
        $DbName = "test"
    
        # Name of the schema in which the above database is in
        $Schema = "dbo"
    
        # connect to NetScalers using the login nsexsremetrics
        if ($env:Winusername) {
            Write-verbose "$(Get-date): Creating Secret Server Login" -Verbose
            $SecurePassword = ConvertTo-SecureString $env:WinPassword -AsPlainText -Force -ErrorAction Stop
            $SSCred = New-Object System.Management.Automation.PSCredential ($env:WinUserName, $SecurePassword) -ErrorAction Stop
        }
        $NScred = Get-SecretServerCredential -SamAccountName "nsexs" -OperatorCredentials $SSCred -ErrorAction Stop -Verbose:$false
    
        # Get Primary NetScalers
        $Netscalers = Get-Primary | select-object -ExpandProperty DNSName
        
        $props =    [PSCustomObject]@{Table = "NetScaler_Servicegroup";NSStatType = "servicegroup";NSStatProps = ("name,servicegroupname,effectivestate,servicetype,state" -split ',')},
                    [PSCustomObject]@{Table = "NetScaler_service";     NSStatType = "service";     NSStatProps = ("name,primaryipaddress,servicetype,state" -split ',')},
                    [PSCustomObject]@{Table = "NetScaler_stat";        NSStatType = "lbvserver";   NSStatProps = ("name,primaryipaddress,type,state" -split ',')}
    }
    Process {
        # Collect information from three sets of parameters, for all servers
        ForEach ($p in $props){
            # Connect to each netscaler and insert its stats into the database table
            foreach ($NetScaler in $NetScalers) {
                Connect-NetScaler -Hostname $NetScaler -Credential $NScred
                Get-NSStat -Type $p.NSStatType | 
                    Where-object { $_.name -notlike "*test*" } |
                        Select-Object $p.NSStatProps |
                            Write-SqlTableData -ServerInstance $SqlInstance -DatabaseName $DbName -SchemaName $Schema -TableName $p.Table -Force
            }
        }
    }
    
    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Sedat SALMAN 13,190 Reputation points
    2023-05-04T21:17:52.5+00:00

    To merge the three scripts into a single script, you can create a function for the common code and pass the table name, SQL select query, and other required parameters as arguments to the function.

    function SaveToSQLTable {
        param(
            $TableName,
            $SqlSelectQuery,
            $NetscalerType,
            $EmailDefault = "email@example.com",
            $Account = "account"
        )
    
        # Common code here, for example:
    
        # Connect to the SQL Server
    
        # Execute the SQL select query
        $query = $SqlSelectQuery
    
        # Save the data to the specified SQL table
        $table = $TableName
    
        # Other common code here
    }
    
    # Call the SaveToSQLTable function for each set of parameters
    SaveToSQLTable -TableName "Table1" -SqlSelectQuery "SELECT * FROM Table1" -NetscalerType "service"
    SaveToSQLTable -TableName "Table2" -SqlSelectQuery "SELECT * FROM Table2" -NetscalerType "servicegroup"
    SaveToSQLTable -TableName "Table3" -SqlSelectQuery "SELECT * FROM Table3" -NetscalerType "other_type" -EmailDefault "another_email@example.com" -Account "another_account"
    
    
    0 comments No comments