powershell merge

Question

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

Answer 1

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

Answer 2

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"