PowerShell | SQL

ip addr | grep eth0 | grep inet | awk '{print $2}' | awk -F '/' '{print $1}' | awk '{printf "%s:3000", $0}' | clip.exe

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

function processSQLFile(fileName) {

  // Extract SQL queries from files. Assumes no ';' in the fileNames
  var queries = fs.readFileSync(fileName).toString()
    .replace(/(\r\n|\n|\r)/gm," ") // remove newlines
    .replace(/\s+/g, ' ') // excess white space
    .split(";") // split into all statements
    .map(Function.prototype.call, String.prototype.trim)
    .filter(function(el) {return el.length != 0}); // remove any empty ones

  // Execute each SQL query sequentially
  queries.forEach(function(query) {
    batch.push(function(done) {
      if (query.indexOf("COPY") === 0) { // COPY - needs special treatment
        var regexp = /COPY\ (.*)\ FROM\ (.*)\ DELIMITERS/gmi;
        var matches = regexp.exec(query);
        var table = matches[1];
        var fileName = matches[2];
        var copyString = "COPY " + table + " FROM STDIN DELIMITERS ',' CSV HEADER";
        var stream = client.copyFrom(copyString);
        stream.on('close', function () {
          done();
        });
        var csvFile = __dirname + '/' + fileName;
        var str = fs.readFileSync(csvFile);
        stream.write(str);
        stream.end();
      } else { // Other queries don't need special treatment
        client.query(query, function(result) {
          done();
        });
      }
    });
  });
}

# 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');
}

select * 
from folder f
  join uploads u ON u.id = f.folderId 
where '8' = ANY (string_to_array(some_column,','))

DECLARE @AnyDate DATETIME
SET @AnyDate = GETDATE()

SELECT @AnyDate AS 'Input Date',
  DATEADD(q, DATEDIFF(q, 0, @AnyDate), 0) 
                        AS 'Quarter Start Date',       
  DATEADD(d, -1, DATEADD(q, DATEDIFF(q, 0, @AnyDate) + 1, 0)) 
                        AS 'Quarter End Date'

@echo off
title Activate Windows 10 (ALL versions) for FREE - MSGuides.com&cls&echo =====================================================================================&echo #Project: Activating Microsoft software products for FREE without additional software&echo =====================================================================================&echo.&echo #Supported products:&echo - Windows 10 Home&echo - Windows 10 Professional&echo - Windows 10 Education&echo - Windows 10 Enterprise&echo.&echo.&echo ============================================================================&echo Activating your Windows...&cscript //nologo slmgr.vbs /ckms >nul&cscript //nologo slmgr.vbs /upk >nul&cscript //nologo slmgr.vbs /cpky >nul&set i=1&wmic os | findstr /I "enterprise" >nul
if %errorlevel% EQU 0 (cscript //nologo slmgr.vbs /ipk NPPR9-FWDCX-D2C8J-H872K-2YT43 >nul||cscript //nologo slmgr.vbs /ipk DPH2V-TTNVB-4X9Q3-TJR4H-KHJW4 >nul||cscript //nologo slmgr.vbs /ipk YYVX9-NTFWV-6MDM3-9PT4T-4M68B >nul||cscript //nologo slmgr.vbs /ipk 44RPN-FTY23-9VTTB-MP9BX-T84FV >nul||cscript //nologo slmgr.vbs /ipk WNMTR-4C88C-JK8YV-HQ7T2-76DF9 >nul||cscript //nologo slmgr.vbs /ipk 2F77B-TNFGY-69QQF-B8YKP-D69TJ >nul||cscript //nologo slmgr.vbs /ipk DCPHK-NFMTC-H88MJ-PFHPY-QJ4BJ >nul||cscript //nologo slmgr.vbs /ipk QFFDN-GRT3P-VKWWX-X7T3R-8B639 >nul||cscript //nologo slmgr.vbs /ipk M7XTQ-FN8P6-TTKYV-9D4CC-J462D >nul||cscript //nologo slmgr.vbs /ipk 92NFX-8DJQP-P6BBQ-THF9C-7CG2H >nul&goto skms) else wmic os | findstr /I "home" >nul
if %errorlevel% EQU 0 (cscript //nologo slmgr.vbs /ipk TX9XD-98N7V-6WMQ6-BX7FG-H8Q99 >nul||cscript //nologo slmgr.vbs /ipk 3KHY7-WNT83-DGQKR-F7HPR-844BM >nul||cscript //nologo slmgr.vbs /ipk 7HNRX-D7KGG-3K4RQ-4WPJ4-YTDFH >nul||cscript //nologo slmgr.vbs /ipk PVMJN-6DFY6-9CCP6-7BKTT-D3WVR >nul&goto skms) else wmic os | findstr /I "education" >nul
if %errorlevel% EQU 0 (cscript //nologo slmgr.vbs /ipk NW6C2-QMPVW-D7KKK-3GKT6-VCFB2 >nul||cscript //nologo slmgr.vbs /ipk 2WH4N-8QGBV-H22JP-CT43Q-MDWWJ >nul&goto skms) else wmic os | findstr /I "10 pro" >nul
if %errorlevel% EQU 0 (cscript //nologo slmgr.vbs /ipk W269N-WFGWX-YVC9B-4J6C9-T83GX >nul||cscript //nologo slmgr.vbs /ipk MH37W-N47XK-V7XM9-C7227-GCQG9 >nul||cscript //nologo slmgr.vbs /ipk NRG8B-VKK3Q-CXVCJ-9G2XF-6Q84J >nul||cscript //nologo slmgr.vbs /ipk 9FNHH-K3HBT-3W4TD-6383H-6XYWF >nul||cscript //nologo slmgr.vbs /ipk 6TP4R-GNPTD-KYYHQ-7B7DP-J447Y >nul||cscript //nologo slmgr.vbs /ipk YVWGF-BXNMC-HTQYQ-CPQ99-66QFC >nul&goto skms) else (goto notsupported)
:skms
if %i% GTR 10 goto busy
if %i% EQU 1 set KMS=kms7.MSGuides.com
if %i% EQU 2 set KMS=s8.uk.to
if %i% EQU 3 set KMS=s9.us.to
if %i% GTR 3 goto ato
cscript //nologo slmgr.vbs /skms %KMS%:1688 >nul
:ato
echo ============================================================================&echo.&echo.&cscript //nologo slmgr.vbs /ato | find /i "successfully" && (echo.&echo ============================================================================&echo.&echo #My official blog: MSGuides.com&echo.&echo #How it works: bit.ly/kms-server&echo.&echo #Please feel free to contact me at msguides.com@gmail.com if you have any questions or concerns.&echo.&echo #Please consider supporting this project: donate.msguides.com&echo #Your support is helping me keep my servers running 24/7!&echo.&echo ============================================================================&choice /n /c YN /m "Would you like to visit my blog [Y,N]?" & if errorlevel 2 exit) || (echo The connection to my KMS server failed! Trying to connect to another one... & echo Please wait... & echo. & echo. & set /a i+=1 & goto skms)
explorer "http://MSGuides.com"&goto halt
:notsupported
echo ============================================================================&echo.&echo Sorry, your version is not supported.&echo.&goto halt
:busy
echo ============================================================================&echo.&echo Sorry, the server is busy and can't respond to your request. Please try again.&echo.
:halt
pause >nul

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

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()

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

Powercfg -setacvalueindex scheme_current sub_processor PERFBOOSTMODE 0
Powercfg -setactive scheme_current

set WEBAPIURL=http://...

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

[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

$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"

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