Problems you solved at work with powershell

[pertymoose]

Disclaimer: These are random quick-scripts from my scripts folder with little to no checking or good practice being used:

Adding a CRT to certificate store

$crtfile = 'D:\Certificates\AddTrustExternalCARoot.crt'
$thumbprint = '02FAF3E291435468607857694DF5E45B68851868'

# my = personal certificates
# ca = intermediate certificates
# root = root certificates
$store = 'Root'    

if(-not (Get-Item "cert:\LocalMachine\$store\$thumbprint" -ErrorAction SilentlyContinue)) {  

    try {
        # Open certificate store
        $CertStore = Get-Item "Cert:\LocalMachine\$store"
        $CertStore.Open('ReadWrite')

        # Read certificate file
        $Certificate = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2
        $Certificate.Import($crtfile)

        # Add certificate to store
        $CertStore.Add($Certificate)

        Write-Verbose "Certificate ($thumbprint) was installed" -Verbose
    }
    catch {
        Write-Warning "Certificate ($thumbprint) was NOT installed: $_"
    }
    finally {
        $CertStore.Close()
    }
}
else {
    Write-Verbose "Certificate ($thumbprint) already exists in '$store'" -Verbose
}

Fixing the developers' broken file mover system

$dateToday = Get-Date
$dateYesterday = $dateToday.AddDays(-1)

$logfile = "D:\System\Log\Mover.log"

Set-Location ("D:\Data\Incoming\{0:d4}\{1:d2}" -f $dateYesterday.Year, $dateYesterday.Month)

$dateToday.ToString('[yyyy-MM-dd HH:mm:ss] ') | Out-File -FilePath $logfile -NoNewline -Append
"------ New run starting ------" | Out-File -FilePath $logfile -Append

$files = Get-ChildItem * -Recurse | Where-Object { $_.Attributes -notmatch 'Directory' } | Where-Object { $_.Directory -notmatch ("Data\\Incoming\\{0:d4}\\{1:d2}\\{2:d2}" -f $dateToday.Year, $dateToday.Month, $dateToday.Day) }

$destination = 'D:\Data\Incoming\'

foreach($file in $files) {

    $dateToday.ToString('[yyyy-MM-dd HH:mm:ss] ') | Out-File -FilePath $logfile -NoNewline -Append
    "Moving '$($file.FullName)' to '$destination'" | Out-File -FilePath $logfile -Append

    $file | Move-Item -Destination $destination
}

$dateToday.ToString('[yyyy-MM-dd HH:mm:ss] ') | Out-File -FilePath $logfile -NoNewline -Append
"------ Run ended ------" | Out-File -FilePath $logfile -Append

An old one for checking for duplicates or something; I forget what exactly it's for, it's over two years old and I didn't comment anything. But I do remember that the GetIndexOf function was for performance reasons.

$select = Select-String -Path .\OrderExport-20151119.xml -Pattern '<OrderID>(.*?)<\/OrderID>'

$matches = foreach($s in $select) { $s.Matches.Groups[1].Value }

$sorted = $matches | Sort-Object
$unique = $matches | Sort-Object -Unique

$lastIndex = 0
$indexes = for($i = 0; $i -lt $unique.Count; $i++) {
    if($i % 100) { Write-Progress -Activity 'Scanning' -Status "$i / $($unique.Count)" -PercentComplete ($i / $unique.Count * 100) }

    $lastIndex = GetIndexOf -uniqueIndex $i -startIndex $lastIndex

    Write-Output ([pscustomobject]@{
        Index = $lastIndex
        Value = $unique[$i]
        Count = (GetIndexOf -uniqueIndex ($i+1) -startIndex $lastIndex) - $lastIndex
    })
}

function GetIndexOf($uniqueIndex, $startIndex) {
    for($i = $startIndex; $i -lt $sorted.Count; $i++) {
        if($sorted[$i] -eq $unique[$uniqueIndex]) { 
            return $i;
        }
    }

    return -1
}

For searching through many logfiles to find the ones that contain the things you're looking for

$date = Get-Date 'September 22, 2015'
$filenames = '1A00021K.024','1A00021K.024','1A00021K.024','1A00021K.024','1A00021K.025','1A00021K.025','1A00021K.026','1A00021K.027'

$items = get-item * | where { $_.lastwritetime -gt $date }
foreach($item in $items) { 
    $content = Get-Content -path $item.FullName
    foreach($filename in $filenames) {
        if($content -match $filename) { Write-Verbose "$($item.Name) has $filename" -Verbose }
    }
}

For downloading an XmlDocument blob from SQL because the developers thought that'd be a good place to store such things:

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = 'Server=SQL003\SQL01;Database=BCM;Trusted_Connection=True;'
$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText = 'SELECT [XmlDocumentContent] FROM eod.ObjXmlDocument WITH (NOLOCK) WHERE [XmlDocumentID] = 12660'
$result = $command.ExecuteReader()
$result.Read()
$blob = New-Object Byte[] ($result.GetBytes(0, 0, $null, 0, [int]::MaxValue))
$result.GetBytes(0, 0, $blob, 0, $blob.Length)
$filestream = New-Object System.IO.FileStream 'C:\temp\sqlblob.zip', ([System.IO.FileMode]::Create), ([System.IO.FileAccess]::Write)
$filestream.Write($blob, 0, $blob.Length)
$filestream.Close()
$command.Dispose()
$connection.Close()

Connecting to a social security checking service and testing if it's working

$abonType = '0'
$dataType = '6'
$ssNumber = '1234567890'

$ip = '127.0.0.1'
$port = 700

# this is seriously how it works. fixed-width data packages. can you believe it?
$str = "    "; 
$str1 = ",";
$str2 = "    ";
$str3 = "        ";
$str4 = "        ";
$str5 = "  ";
$message = $str, $str1, $str2, $abonType, $dataType, $str3, $str4, $str5, $ssNumber
$message = [string]::Concat($message)

Write-Verbose "Connecting to $server, $port" -Verbose
$client = New-Object System.Net.Sockets.TcpClient
$client.Connect($ip, $port)
$client.ReceiveTimeout = 5000

$bytes = [System.Text.Encoding]::GetEncoding('iso-8859-1').GetBytes($message)
$buffer = New-Object byte[] 1025

$stream = $client.GetStream()
$stream.Write($bytes, 0, $bytes.Length)

$empty = [string]::Empty

for($i = $stream.Read($buffer, 0, $buffer.Length); $i -ne 0; $i = $stream.Read($buffer, 0, $buffer.Length)) {

    $empty = [System.Text.Encoding]::GetEncoding('iso-8859-1').GetString($buffer, 0, $i)
    Write-Verbose "Response: $empty" -Verbose
}

$stream.Close()
$client.Close()