Loop through the list of servers and check connection

wsl --shutdown
diskpart
# open window Diskpart
select vdisk file="C:\WSL-Distros\…\ext4.vhdx"
attach vdisk readonly
compact vdisk
detach vdisk
exit

#bash #powershell

WSL2 network port forwarding script v1

# WSL2 network port forwarding script v1
#   for enable script, 'Set-ExecutionPolicy -ExecutionPolicy Bypass -Scope CurrentUser' in Powershell,
#   for delete exist rules and ports use 'delete' as parameter, for show ports use 'list' as parameter.
#   written by Daehyuk Ahn, Aug-1-2020

# Display all portproxy information
If ($Args[0] -eq "list") {
    netsh interface portproxy show v4tov4;
    exit;
} 

# If elevation needed, start new process
If (-NOT ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator))
{
  # Relaunch as an elevated process:
  Start-Process powershell.exe "-File",('"{0}"' -f $MyInvocation.MyCommand.Path),"$Args runas" -Verb RunAs
  exit
}

# You should modify '$Ports' for your applications 
$Ports = (22,80,443,8080)

# Check WSL ip address
wsl hostname -I | Set-Variable -Name "WSL"
$found = $WSL -match '\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}';
if (-not $found) {
  echo "WSL2 cannot be found. Terminate script.";
  exit;
}

# Remove and Create NetFireWallRule
Remove-NetFireWallRule -DisplayName 'WSL 2 Firewall Unlock';
if ($Args[0] -ne "delete") {
  New-NetFireWallRule -DisplayName 'WSL 2 Firewall Unlock' -Direction Outbound -LocalPort $Ports -Action Allow -Protocol TCP;
  New-NetFireWallRule -DisplayName 'WSL 2 Firewall Unlock' -Direction Inbound -LocalPort $Ports -Action Allow -Protocol TCP;
}

# Add each port into portproxy
$Addr = "0.0.0.0"
Foreach ($Port in $Ports) {
    iex "netsh interface portproxy delete v4tov4 listenaddress=$Addr listenport=$Port | Out-Null";
    if ($Args[0] -ne "delete") {
        iex "netsh interface portproxy add v4tov4 listenaddress=$Addr listenport=$Port connectaddress=$WSL connectport=$Port | Out-Null";
    }
}

# Display all portproxy information
netsh interface portproxy show v4tov4;

# Give user to chance to see above list when relaunched start
If ($Args[0] -eq "runas" -Or $Args[1] -eq "runas") {
  Write-Host -NoNewLine 'Press any key to close! ';
  $null = $Host.UI.RawUI.ReadKey('NoEcho,IncludeKeyDown');
}

#powershell #ping

Loop through the list of servers and check connection

Get-Content "\\fs01\Align\Public\OLEGSQLSWITCH\Servers.txt" |
     ForEach-Object{
          if(Test-Connection $_ -Count 1 -quiet){
               "$_ Up"
          }else{
               "$_ Down"
          }
     }

#powershell #ping

ReportingServicesTools

# make sure the module is installed:
Install-Module -Name ReportingServicesTools

# add computername to trusted hosts list to be able to connect remotely
Set-Item WSMan:\localhost\Client\TrustedHosts -Value "POWERBI"

# view content of the folder
$reportServerUri = 'http://10.36.8.17/ReportServer/'
$folderName = "/AFH/Align General/Programs"
Get-RsFolderContent -ReportServerUri $reportServerUri -RsFolder $folderName | Format-Table -AutoSize


# Return existing data source information for "GrossBudget" report. If multiple datasources uses for queries - it will return all of them. 
$ReportPortalUri = "http://10.36.8.17/Reports"
Get-RsRestItemDataSource -RsItem "/AFH/GrossBudget" -ReportPortalUri $ReportPortalUri

# Get connection string info for "Construction - AY Loss Ratios" report
$ReportPortalUri = "http://10.36.8.17/Reports"
Get-RsRestItemDataSource -RsItem "/AFH/Align General/Programs/Construction - AY Loss Ratios" -ReportPortalUri $ReportPortalUri

#----------------------------------------------------------------------------------------------
#----------Change connection string username and passwork in PowerBI reports-------------------
#----------------------------------------------------------------------------------------------

# connect remotely to POWERBI server
Enter-PSSession -ComputerName POWERBI -Credential ssrsserviceaccount
# specify parameters
$ReportPortalUri = "http://10.36.8.17/Reports"
$ReportName = '/AFH/Catalytic/Underwriter Reports/Underwriter Production'
$Username = 'catalyticSSRS'
$Password = 'RND1101!'
$ServerName = '10.36.8.18'
# grab parameters that are currently exists in a report
$parameters = Get-RsRestItemDataModelParameters $ReportName -ReportPortalUri $ReportPortalUri
$parameters
# Map to a dictionary to access the parameter values.
$parameterdictionary = @{}
foreach ($parameter in $parameters) { $parameterdictionary.Add($parameter.Name, $parameter); }
# change parameter value. "ServerName" or "datasource" whatever the name of the parameter in PowerBI
$parameterdictionary[“ServerName”].Value = $ServerName
# update parameters that are currentl in a report
Set-RsRestItemDataModelParameters -RsItem $ReportName -ReportPortalUri $ReportPortalUri -DataModelParameters $parameters
# print what changed
$parameterdictionary.Values
# set authentification mode, username and password
$dataSources = Get-RsRestItemDataSource -RsItem $ReportName -ReportPortalUri $ReportPortalUri
$dataSources[0].DataModelDataSource.AuthType = 'UsernamePassword' # UsernamePassword should be used when specifying SQL or Basic Credentials
$dataSources[0].DataModelDataSource.Username = $Username
$dataSources[0].DataModelDataSource.Secret = $Password
Set-RsRestItemDataSource -RsItem $ReportName -RsItemType PowerBIReport -DataSources $datasources

#powershell #ping

SSH key generation for GitHub

# generate public key using Powershell
# email at the end is just a signature that you created it
ssh-keygen -t rsa -b 4096 -C "olegserdia@gmail.com"


# if you see an error: WARNING: UNPROTECTED PRIVATE KEY FILE!
# then cd to a folder with files and run this in PS:
icacls * /reset /t /c /q   # https://superuser.com/questions/106181/equivalent-of-chmod-to-change-file-permissions-in-windows

# start the ssh-agent in the background using GIT
eval "$(ssh-agent)"

# using GIT add private key to SSH agent: (PowerShell does not work for some reason)
# cd to .ssh folder first "cd C:/Users/oserdyuk/.ssh"
ssh-add ~/.ssh id_rsa

# add public key to a GitHub repository: Left top corner--> settings

# Clone repository using GIT
# make sure you are in GIT repository : "cd C:/Users/oserdyuk/Documents/AzureProjects/azure-resume"
git clone git@github.com:Serdia/azure-resume.git

######################################################################
						### Push changes to GIT  ###
# save what you've done
git add -A
# say what you've done
git commit -m "Deployed storage and bla bla"
# push what you've done
git push

# if a project created in vs code and then need to be pushed to github
git push --set-upstream git@github.com:Serdia/Flask_WeatherApp.git master


######################################################################
# Troubleshooting

# add Private key to ssh agent using PS
ssh-add C:/Users/oserdyuk/.ssh/id_rsa.pub
# or make sure you are in c:/oserdyuk and run
ssh-add .\.ssh\id_rsa

#if error: Error loading key "C:/Users/oserdyuk/.ssh/id_rsa.pub": invalid format
# you can either overrite key in a right format: (it changes email to oserdyuk@oserdyuk-vm - not sure why)
ssh-keygen -f C:/Users/oserdyuk/.ssh/id_rsa.pub   # click Enter to ignore password

git clone git@github.com:ACloudGuru-Resources/acg-project-azure-resume-starter.git

# to remove keys from SSH agent
ssh-add -D

# service OpenSSH Authentification Agent. Make sure it's running.
C:\WINDOWS\System32\OpenSSH\ssh-agent.exe
# start service in powershell
Start-Service -Name "OpenSSH Authentication Agent"
# check if it started
Get-Service ssh-agent

# start the ssh-agent in the background
eval "$(ssh-agent)"


echo "$(ssh-agent)"

# check whether agent is running or not (GIT)
if ps -p $SSH_AGENT_PID > /dev/null
then
   echo "ssh-agent is already running"
   # Do something knowing the pid exists, i.e. the process with $PID is running
else
eval `ssh-agent -s`
fi


# list all keys that are present in the agent PS or GIT
ssh-add -l



# overwrite the key. Just please enter (to ignore password)
 ssh-keygen -f C:/Users/oserdyuk/.ssh/id_rsa.pub

ssh-add -L # copy the key, place it into GitHub. Worked



ssh-add "C:/Users/oserdyuk/.ssh/id_rsa.pub.pub"  -- Error loading key. Invalid format

ssh-add ~/.ssh my_id_rsa

#powershell #ping

Azure PowerShell

# need to try this:
# https://learn.microsoft.com/en-us/azure/batch/quick-run-python

# connect to Azure account:
Connect-AzAccount

# view resource groups 
Get-AzResourceGroup |Format-Table

# list batch accounts
Get-AzBatchAccount

# view batch account info, first create $context parameter:
# https://learn.microsoft.com/en-us/azure/batch/batch-powershell-cmdlets-get-started
$context = Get-AzBatchAccountKeys -AccountName osbatchaccount
# now run this command:
Get-AzBatchPool -Id "osbatchpool" -BatchContext $context

# delete batch account:
Remove-AzBatchAccount -AccountName <account_name>
  
# view batch node information
# https://learn.microsoft.com/en-us/powershell/module/az.batch/?view=azps-11.0.0
Get-AzBatchComputeNode -PoolId "osbatchpool" -Id "nodeid" -BatchContext $context

# view files and URL on a node in batch pool
Get-AzBatchComputeNode "osbatchpool" -Id "nodeid" -BatchContext $context | Get-AzBatchNodeFile -BatchContext $context
#--------------------------------------------------------------------------------
# check if python is installed using powershell:
if ($pythonPath) {
    # Python is installed, get the version
    $pythonVersion = & $pythonPath --version 2>&1
    Write-Output "Python is installed. Version: $pythonVersion"
} else {
    Write-Output "Python is not installed on this node."
}
#--------------------------------------------------------------------------------
# commant to execute PS script 
powershell.exe -ExecutionPolicy Unrestricted -File CheckPython.ps1

# display all system variables unsing Powershell:
dir env:

# remove a single task in azure batch job
Remove-AzBatchTask -JobId "myjob" -Id "mytask10" -BatchContext $context
#--------------------------------------------------------------------------------
# remove all tsks in a batch job:
# Get all tasks in the specified job
$jobId = "adfv2-osbatchpool"
$tasks = Get-AzBatchTask -JobId $jobId -BatchContext $context
# Remove each task in the job
foreach ($task in $tasks) {
     Remove-AzBatchTask -JobId $jobId -Id $task.Id -BatchContext $context
 }
Write-Host "All tasks in the job have been removed."

#powershell

powershell - How to load a JSON file and convert it to an object of a specific type? - Stack Overflow

[PSCustomObject]$param = [PSCustomObject](Get-Content .\parameters.json | Out-String | ConvertFrom-Json)

#powershell #certificate

Trusting Self-Signed HTTPS Certificates

class TrustAll : System.Net.ICertificatePolicy 
{
  [bool]CheckValidationResult([System.Net.ServicePoint]$sp, [System.Security.Cryptography.X509Certificates.X509Certificate]$cert, [System.Net.WebRequest]$request, [int]$problem)
  {
    return $true
  }
}

[System.Net.ServicePointManager]::CertificatePolicy = [TrustAll]::new()

#powershell

Clean VS solution

Get-ChildItem -Path bin -Recurse | Remove-Item -Recurse -Force -Verbose
Get-ChildItem -Path obj -Recurse | Remove-Item -Recurse -Force -Verbose

#powershell

TurboBoost

Powercfg -setacvalueindex scheme_current sub_processor PERFBOOSTMODE 0
Powercfg -setactive scheme_current

#powershell

ps.WebRequest

set WEBAPIURL=http://...

powershell -Command "& {Invoke-RestMethod -UseDefaultCredential -Method Post -Uri %WEBAPIURL% }>> output.txt

#powershell

Modifying a VSIX file before publishing

[cmdletbinding()]
param(
 [string] [Parameter(Mandatory=$true)] $PathToVSIX,
 [string] [Parameter(Mandatory=$true)] $Token,
 [string] $IsPublicInput = "false",
 [string] $Version = $null,
 [string] $Publisher = $null,
 [string] $RemoveBaseUriInput = "true",
 [string] $ShareWith= $null
)
Set-StrictMode -Version 3

[bool]$IsPublic = [bool]::Parse($IsPublicInput)
[bool]$RemoveBaseUri = [bool]::Parse($RemoveBaseUriInput)

$file = Get-ChildItem $PathToVSIX -Filter *.vsix -Recurse | % { $_.FullName } | Select -First 1
Write-Verbose "Found VSIX Package $file"

try { $null = [IO.Compression.ZipFile] }
catch { [System.Reflection.Assembly]::LoadWithPartialName('System.IO.Compression.FileSystem') }

try { $fileZip = [System.IO.Compression.ZipFile]::Open( $file, 'Update' ) }
catch { throw "Another process has locked the '$file' file." }

$desiredFile = [System.IO.StreamReader]($fileZip.Entries | Where-Object { $_.FullName -match 'extension.vsixmanifest' }).Open()
$text = $desiredFile.ReadToEnd()
[xml]$xml = $text
$desiredFile.Close()
$desiredFile.Dispose()

if ($Version)
{
 Write-Verbose "Updating Version to $Version"
 $xml.PackageManifest.MetaData.Identity.Version = $Version
}

if ($Publisher)
{
 Write-Verbose "Updating Publisher to $Publisher"
 $xml.PackageManifest.MetaData.Identity.Publisher = $Publisher
}

if($IsPublic -eq $true)
{
 Write-Verbose "Setting GalleryFlag to Public"
 $xml.PackageManifest.MetaData.GalleryFlags = "Public"
}
else
{
 Write-Verbose "Setting GalleryFlag to Private"
 $xml.PackageManifest.MetaData.GalleryFlags = ""
}

$desiredFile = [System.IO.StreamWriter]($fileZip.Entries | Where-Object { $_.FullName -match 'extension.vsixmanifest' }).Open()

$desiredFile.BaseStream.SetLength(0)
$desiredFile.Write($xml.InnerXml)
$desiredFile.Flush()
$desiredFile.Close()

$desiredFile = [System.IO.StreamReader]($fileZip.Entries | Where-Object { $_.FullName -match 'extension.vsomanifest' }).Open()
$text = $desiredFile.ReadToEnd()
$desiredFile.Close()
$desiredFile.Dispose()

if ($RemoveBaseUri -eq $true)
{
 $text = (($text -split "`n") | ? {$_ -notmatch 'baseUri'}) -join "`n"
}

$desiredFile = [System.IO.StreamWriter]($fileZip.Entries | Where-Object { $_.FullName -match 'extension.vsomanifest' }).Open()

$desiredFile.BaseStream.SetLength(0)
$desiredFile.Write($text)
$desiredFile.Flush()
$desiredFile.Close()

$fileZip.Dispose()

if($ShareWith -ne $null)
{
 $ShareWith = "--share-with" + $ShareWith
}
else
{
 $ShareWith = ""
}

npm install -g tfx-cli
tfx extension publish --vsix "$File" --token $Token $ShareWith

#powershell #k8s #spn #reset #expired #expire

Reset the SPN of a K8s cluster

$RG = "di-platform-dev-rg-execution"
$NAME = "di-platform-dev-k8s-exe"

$SP_ID=$(az aks show --resource-group "$RG" --name "$NAME" \
    --query servicePrincipalProfile.clientId -o tsv)

az ad sp credential list --id "$SP_ID" --query "[].endDate" -o tsv

$SP_ID=$(az aks show --resource-group "$RG" --name "$NAME" \
    --query servicePrincipalProfile.clientId -o tsv)

$SP_SECRET=$(az ad sp credential reset --name "$SP_ID" --query password -o tsv)

az aks update-credentials \
    --resource-group "$RG" \
    --name $NAME" \
    --reset-service-principal \
    --service-principal "$SP_ID" \
    --client-secret "$SP_SECRET"

#powershell #filter

How to Use PowerShell Where-Object to Filter All the Things

Get-Service | Where-Object -FilterScript {$_.StartType -EQ 'Automatic'}

#powershell

Formatting T-SQL Scripts using ScriptDOM and PowerShell – SQLServerCentral

<#  
.SYNOPSIS  
    Format-TSQL will format tsql script supplied per options set
.DESCRIPTION  
    This script will strip supplied code of comments format per options
.NOTES  
    Author     : Mala Mahadevan (malathi.mahadevan@gmail.com)
  
.PARAMETERS
-InputScript: text file containing T-SQL
    -OutputScript: name of text file to be generated as output
.LIMITATIONS
Strips code of comments
.LINK  
    

.HISTORY
2021.08.08First version for sqlservercentral.com
#>
function Format-TSQL
{
   
    #Defining parameter for scriptname
    [CmdletBinding()]
    param(
           [System.IO.FileInfo[]]$InputScript,
           [String]$OutputScript
    )
    If ((Test-Path $InputScript -PathType Leaf) -eq $false)
    {
        $errormessage = "File $InputScript not found!"
        throw $errormessage
    }
    If ((Test-Path $OutputScript -IsValid) -eq $false)
    {
        $errormessage = "Path for $Outputscript not found!"
        throw $errormessage
    }
    #This may need to be modified to wherever the dll resides on your machine
    Add-Type -Path "C:\Program Files\Microsoft SQL Server\150\DAC\bin\Microsoft.SqlServer.TransactSql.ScriptDom.dll"
    $generator = [Microsoft.SqlServer.TransactSql.ScriptDom.Sql150ScriptGenerator]::New();
    #Include semi colons at end of every statement
    $generator.Options.IncludeSemicolons = $true
    #Aligns body inside of  blocks
    $generator.Options.AlignClauseBodies = $true
    #Aligns all column definitions for create view/table
    $generator.Options.AlignColumnDefinitionFields = $true
    #Aligns set statements
    $generator.Options.AlignSetClauseItem = $true
    #create or alter 'as' will be on its own line
    $generator.Options.AsKeywordOnOwnLine = $true
    #Define indentation - only spaces
    $generator.Options.IndentationSize = 10
    #Indent set clauses
    $generator.Options.IndentSetClause = $true
    #Indent body of view
    $generator.Options.IndentViewBody = $true
    #Set keyword casing
    $generator.Options.KeywordCasing =  1 #0 lower case 1 upper case 2 pascal case
    #Seperate each column on insert source statement to its own line
    $generator.Options.MultilineInsertSourcesList = $true
    #Seperate each column on insert target statement to its own line
    $generator.Options.MultilineInsertTargetsList = $true
    #Seperate each column on select statement to its own line
    $generator.Options.MultilineSelectElementsList = $true
    #Separate each item on set clause to its own line
    $generator.Options.MultilineSetClauseItems = $true
    #Separate each column on view to its own line
    $generator.Options.MultilineViewColumnsList = $true
    #Separate each line on where predicate to its own line
    $generator.Options.MultilineWherePredicatesList = $true
    #Insert a new line before ( on multi line list of columns
    $generator.Options.NewLineBeforeCloseParenthesisInMultilineList = $true
    #Insert a new line before from clause
    $generator.Options.NewLineBeforeFromClause = $true
    #Insert a new line before group by clause
    $generator.Options.NewLineBeforeGroupByClause = $true
    #Insert a new line before having clause
    $generator.Options.NewLineBeforeHavingClause = $true
    #Insert a new line before join
    $generator.Options.NewLineBeforeJoinClause = $true
    #Insert a new line before offset clause
    $generator.Options.NewLineBeforeOffsetClause = $true
    #Insert a new line before ) on multi line list of columns
    $generator.Options.NewLineBeforeOpenParenthesisInMultilineList = $true
    #Insert a new line before order by
    $generator.Options.NewLineBeforeOrderByClause = $true
    #Insert a new line before output clause
    $generator.Options.NewLineBeforeOutputClause = $true
    #Insert a new line before where clause
    $generator.Options.NewLineBeforeWhereClause = $true
    #Recognize syntax specific to engine type - to be safe use 0
    $generator.Options.SqlEngineType = 0 # 0 All 1 Engine 2 Azure
    #Version used 
    #1002
    #1103
    #1204
    #1305
    #1406 
    #1507
    #80    1
    #90    0 (default)
    $generator.Options.SqlVersion = 7
    #Read the string passed in
    $stringreader = New-Object -TypeName System.IO.StreamReader -ArgumentList $InputScript
   
    #Create generate object
    $generate =     [Microsoft.SqlServer.TransactSql.ScriptDom.Sql150ScriptGenerator]($generator)
    #Parse the string for errors and create tsqlfragment for formatting
    $parser = [Microsoft.SqlServer.TransactSql.ScriptDom.TSql150Parser]($true)::New();
    
    if($parser -eq $null){
    throw 'ScriptDOM not installed or not accessible'
    }
    $parseerrors = $null
    $fragment = $parser.Parse($stringreader,([ref]$parseerrors))
    # raise an exception if any parsing errors occur
    if($parseerrors.Count -gt 0) {
        throw "$($parseErrors.Count) parsing error(s): $(($parseErrors | ConvertTo-Json))"
    } 
    $formattedoutput = ''
    #Format the string
    $generate.GenerateScript($fragment,([ref]$formattedoutput)) 
    write-host $formattedoutput -BackgroundColor blue
    $formattedoutput.ToString() | Out-File $OutputScript
        
}

Loop through the list of servers and check connection

Save snippets that work from anywhere online with our extensions

Comments

More like this

Browse more snippets >>

Loop through the list of servers and check connection

Save snippets that work from anywhere online with our extensions

Comments

More like this

Browse more snippets >>

Embed code snippet