PowerShell to convert downloaded transaction file from LT Trust 401k to Quicken format

2021-10-14 / Random / No Comment
FYI: This assumes a 100% vesting from the employer provided funds. It lumps all the trade types together and gives a final amount. It helps get it close enough but there will be a few cents$ left over as LT Trusts export rounding is not highly accurate for some reason.
Update the file paths below to match the downloaded file location and output location.
$downloadedTransactionsFile = "C:\Users\<<NAME>>\Downloads\export.csv";
$exportedFile = "C:\Users\<<NAME>>\Desktop\import-lt.csv"


# NO TOUCH
Write-Output "Removing old cleaned file: $exportedFile"
$null = Remove-Item -Path $exportedFile -Force:$true -Confirm:$false -ErrorAction SilentlyContinue
#read downloaded csv file
$transactions = Get-Content -Path $downloadedTransactionsFile;

#convert csv into psobject
$trades = ConvertFrom-Csv $transactions;
Write-Output "Converted: $($trades.Count) items from CSV";

$buys = @();
$sells = @();
$divs = @();

#split all the trades by type
foreach ($trade in $trades) {
    if ($trade.action -eq "Buy") {
        $buys += $trade;
    }
    elseif ($trade.action -eq "Sell") {
        $sells += $trade;
    }
    elseif ($trade.action -eq "REINVDIV") {
        $divs += $trade;
    }
}
#verify counts
Write-Output "Found $($buys.Count) buy trades";
Write-Output "Found $($sells.Count) sell trades";
Write-Output "Found $($divs.Count) div trades";
Write-Output "Total trades found: $($buys.Count + $sells.Count + $divs.Count)";

#Group trades by security
$buys = $buys | Group-Object -Property Security -AsHashTable;
$sells = $sells | Group-Object -Property Security -AsHashTable;
$divs = $divs | Group-Object -Property Security -AsHashTable;

$cleanedBuys = @();
$cleanedSells = @();
$cleanedDivs = @();

$cleanedTrade = [ordered] @{
    Action   = ""
    Security = ""
    Quantity = 0
    Price    = 0
    Amount   = 0
}

Write-Output "Cleaning and preparing to export"
foreach ($buy in $buys.Keys) {
    Write-Debug "Creating: $buy buy object"
    $buyObj = New-Object psobject -Property $cleanedTrade
    $buyObj.Security = $buy
    $buyObj.Action = "Buy"
    foreach ($security in $buys[$buy]) {
        $buyObj.Quantity = ($buyObj.Quantity + ($security.Quantity -replace '[^\d.]'))
        $buyObj.Amount = ($buyObj.Amount + ($security.Amount -replace '[^\d.]'))
        $buyObj.Price = $security.Price -replace '[^\d.]'
    }
    $cleanedBuys += $buyObj
    Export-Csv -InputObject $buyObj -Path $exportedFile -NoClobber -Append -NoTypeInformation
}

foreach ($sell in $sells.Keys) {
    Write-Debug "Creating: $sell sell object"
    $sellObj = New-Object psobject -Property $cleanedTrade
    $sellObj.Security = $sell
    $sellObj.Action = "Sell"
    foreach ($security in $sells[$sell]) {
        $sellObj.Quantity = ($sellObj.Quantity + ($security.Quantity -replace '[^\d.]'))
        $sellObj.Amount = ($sellObj.Amount + ($security.Amount -replace '[^\d.]'))
        $sellObj.Price = $security.Price -replace '[^\d.]'
    }
    $cleanedSells += $sellObj
    Export-Csv -InputObject $sellObj -Path $exportedFile -NoClobber -Append -NoTypeInformation
}

foreach ($div in $divs.Keys) {
    Write-Debug "Creating: $div div object"
    $divObj = New-Object psobject -Property $cleanedTrade
    $divObj.Security = $div
    $divObj.Action = "ReinvDiv"
    foreach ($security in $divs[$div]) {
        $divObj.Quantity = ($divObj.Quantity + ($security.Quantity -replace '[^\d.]'))
        $divObj.Amount = ($divObj.Amount + ($security.Amount -replace '[^\d.]'))
        $divObj.Price = $security.Price -replace '[^\d.]'
    }
    $cleanedDivs += $divObj
    Export-Csv -InputObject $divObj -Path $exportedFile -NoClobber -Append -NoTypeInformation
}

Write-Output "Exported: $($cleanedBuys.Count) buys"
Write-Output "Exported: $($cleanedSells.Count) sells"
Write-Output "Exported: $($cleanedDivs.Count) divs"
Write-Output "Total exported: $($cleanedBuys.Count + $cleanedSells.Count + $cleanedDivs.Count) items."
Write-Output "We are now complete, file exported: $exportedFile" 
$null = Remove-Item -Path $downloadedTransactionsFile -Force:$true -Confirm:$false -ErrorAction SilentlyContinue
Tags: , , ,
Read More

Move A records from subdomain to a new Primary Zone non Active Directory Integrated

2016-11-02 / Random / No Comment
$dnsServer = "baldns1"
$oldDomain = "mytest.baldanza.org"
$newDomain = "mytest.baldanza.org"
$dynamicUpdate = "None"
$responsiblePerson = "hostmaster.baldanza.org."
$notifySettings = "NotifyServers"
$transferType = "TransferToSecureServers"
$secondaryServers = "10.23.0.4","10.23.0.5","192.20.0.4","192.0.5"


#DONT TOUCH BELOW
$records = Get-WmiObject -ComputerName $dnsServer -Namespace root\microsoftDNS -Query ("select * from MicrosoftDNS_AType where DomainName = ""$oldDomain""")
$foundZone = Get-DnsServerZone -ComputerName $dnsServer -Name $newDomain -ErrorAction SilentlyContinue
if ($foundZone -eq $null) {
    Write-Output "Creating zone $newDomain..."
    Add-DnsServerPrimaryZone -ComputerName $dnsServer -Name "$newDomain" -ZoneFile "$newDomain.dns" -ResponsiblePerson $responsiblePerson -DynamicUpdate $dynamicUpdate
    Set-DnsServerPrimaryZone -ComputerName $dnsServer -Name $newDomain -Notify $notifySettings -NotifyServers $secondaryServers -SecureSecondaries $transferType -SecondaryServers $secondaryServers -PassThru
    $foundZone = Get-DnsServerZone -ComputerName $dnsServer -Name $newDomain -ErrorAction SilentlyContinue
} else {
    Write-Output "Zone: $newDomain already created"
}
if ($foundZone -ne $null) {
    Write-Output "Copying records from $oldDomain to $newDomain"
    foreach ($record in $records) {
        $hostname = $record.OwnerName.Replace("."+$record.DomainName, "").ToString();
        $ipAddr = $($record.IPAddress.ToString());
        $ipTTL = $($record.TTL.ToString());
        $ipTTL = [timespan]::fromseconds($ipTTL)
        try {
            Write-Output "Adding..."
            Write-Output "Hostname: $hostname"
            Write-Output "IP: $ipAddr"
            Write-Output "TTL: $ipTTL"
            Add-DnsServerResourceRecordA -ComputerName $dnsServer -Name "$hostname" -ZoneName "$newDomain" -AllowUpdateAny -IPv4Address "$ipAddr" -TimeToLive $ipTTL
        } catch {
            Write-Output "Unable to add: $hostname - $ipAddr"
        }
    }
} else {
    Write-Host "Unable to copying records because $newDomain does not exist"
}
Read More

Remotely or locally recycle IIS application pool via PowerShell with AppPool Menu

2016-05-12 / PowerShell / 3 Comments

Save the PowerShell as Recycle-AppPool.ps1 and you can call with or without options.

Recycle-AppPool.ps1 -ServerName REMOTESERVER —– (Will run on a server remotely with current logged in creds)
Recycle-AppPool.ps1 -ServerName REMOTESERVER -WithCredentials —– (Will run on a server remotely with different credentials specified)
Recycle-AppPool.ps1 —– (Will run on locally, defaults to localhost with current logged in user)

<#
.SYNOPSIS
Recycles a selected IIS application pool 

.DESCRIPTION
Recycle-AppPools.ps1 uses a PS session to connect to a local or remote computer. It pulls down the currently configured application pools and builds a menu. 
The end user gets to select from the menu which application pool to restart. The Recycle-AppPools.ps1 then connects back to the PS Session and recycles the selected
application pool.

.PARAMETER ServerNAme 
a single computer name or leave off for localhost

.PARAMETER WithCredentials
Use this switch to specifiy credentials other than the current logged in user.

.EXAMPLE
Recycle-AppPool.ps1 -ServerName REMOTESERVER
Will run on a remote server with current logged in creds

.EXAMPLE 
Recycle-AppPool.ps1 -ServerName REMOTESERVER -WithCredentials
Will run on a server remotely with different credentials specified during launch

.EXAMPLE
Recycle-AppPool.ps1
Will run on locally. Defaults to localhost with current logged in user

.NOTES
WithCredentials is a switch, it does not accept any credentials. A prompt will appear later.
#>

Param(
   [string]$ServerName = 'localhost',
   [switch]$WithCredentials
 
) #end param
 
if (!(Test-Connection -ComputerName $ServerName -Quiet -Count 1))
{
    Write-Output "Unable to connect to $serverName"
    break
}
 
$scriptBlock = {
    Clear-Host
    Write-Output "Connected to: $env:computerName"
    if (Get-Module -ListAvailable -Name WebAdministration)
    {
        Write-Host "Loading PowerShell Web Administration..."
        Import-Module WebAdministration -ErrorAction SilentlyContinue
    }
    else
    {
        Write-Host "WebAdministration module does not exist..."
        break
    }
 
    $poolTable = @{}
    $allAppPools = Get-ChildItem IIS:\apppools
    $poolNumber = 0 
    Write-Output "Select Application Pool to Restart on server: $env:computerName"
    Write-Output ""
    foreach ($appPool in $allAppPools )
    {
        $pool = $appPool.Name
        $poolNumber += 1
        $poolTable.Add($poolNumber, $pool)
        Write-Output "$poolNumber > To restart app pool : $pool "
    }
    Write-Output ""
    $selectedPool = Read-Host "Enter # of Application Pool to Restart "
    if ($selectedPool)
    {    
        foreach ($hashValue in $poolTable.GetEnumerator())
        {  
            $key = $hashValue.Name
            $val = $hashValue.Value 
            if ($key -eq $selectedPool)
            {
                Write-Output "Restarting : $val"          
                Restart-WebAppPool $val       
            } 
        }
    }
    Write-Output "Completed reset on: $env:computerName"
    Write-Output ""
}
 
try {
     
    if ($WithCredentials)
    {
        $credentials = Get-Credential -Message "Enter credentials with administrative privilege on server"
        $session = New-PSSession -ComputerName $serverName -Credential $credentials -ErrorAction SilentlyContinue
    }
    else
    {
        $session = New-PSSession -ComputerName $serverName -ErrorAction SilentlyContinue
    }
 
    if ($session)
    {
        Invoke-Command -Session $session -ScriptBlock $scriptBlock -ErrorAction Continue
        Remove-PSSession -Session $session | Out-Null
    }
    else
    {
        Write-Output "An error has occured setting up PowerShell session (check username and password)"
    }
}
catch
{
    if ($session)
    {
        Remove-PSSession -Session $session | Out-Null
    }
    Write-Output "An error has occured setting up PowerShell session on $serverName "
}
Tags: , ,
Read More

nginx reverse proxy for ADFS 3.0 server

2016-03-22 / Linux, Microsoft / 2 Comments

Problem:
After setting up a nginx reverse proxy for a ADFS 3.0, instead of MS WAP, it was coming back connection reset by peer. When browsing to the site via IP or anything other than the original hostname it would return this error.

Nginx for some reason was not passing the host header in the reverse proxy request. When connecting to the backend server it was only using the IP of the upstream server causing ADFS to not accept connections.

Solution:

1. Run the below command get get the certhash and the appid from the ADFS server
netsh http show sslcert

2. Run the command to enable http.sys to listen on all IPs with this certificate.
netsh http add sslcert ipport=0.0.0.0:443 certhash=5117dffde15446cf1cfd8bd855cfef25202c6c17 appid='{5d89a20c-beab-4389-9447-324788eb944a}'

Keep in mind if you have other things running on this server it could cause conflict. However, if its setup correctly, nothing else should be on the server.

The nginx config looked like this

upstream adfs_backend {
    server 1x2.xx8.xx.xx:443;
    keepalive 100;
}

server {
        listen   443;
        server_name xxsecurity.customer.com;
        ssl    on;
        ssl_certificate    /etc/ssl/private/adfs.cer;
        ssl_certificate_key    /etc/ssl/private/adfs.key;


     location / {
        proxy_pass https://adfs_backend;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
        proxy_http_version 1.1;
    }
}
Read More

Allow KVM/Qemu to use Hyper-V enlightenment settings

2016-03-16 / iPhone, Server, Windows / No Comment

Problem:
Slow performance for Windows Servers in KVM/QEMU environments

Solution:

Enable KVM to emulate the Hyper-V settings and allow the guest VMs to take advantage.

sudo virt-xml $VMNAME --edit --features hyperv_relaxed=on,hyperv_vapic=on,hyperv_spinlocks=on,hyperv_spinlocks_retries=8191
sudo virt-xml $VMNAME --edit --clock hypervclock_present=yes
Read More

PowerShell script to logoff user remotely on a list of servers

2015-11-25 / PowerShell, Server, Windows / No Comment

Problem:

I needed a PowerShell script to remotely logoff a domain user on multiple servers.

 

Solution:

Using my script with the PowerShell module PSTerminalServices from https://psterminalservices.codeplex.com/
I was able to create a method to disconnect the users remotely

$username = 'jbaldanza'
$filepath = 'c:\temp\servers.txt'

If (!(Get-module PSTerminalServices )) 
{
    try 
    {
        Import-Module PSTerminalServices
        Write-Host "PSTerminalServices Module Loaded" -ForegroundColor Green
    } 
    catch 
    {
        Write-Host "PSTerminalServices module does not exist, please install" -ForegroundColor Red
    }
}

foreach ($server in (Get-Content -Path $filepath)) 
{
    Write-Host "Checking for $username on $server" -ForegroundColor Cyan   
    try 
    {
        $loggedInUsers = get-tssession -ComputerName $server
        if ($loggedInUsers.UserName -contains $username) 
        {

            Write-Host "Found $username connected to $server" -ForegroundColor Green
            $userNameSessionID = $loggedInUsers | Where-Object -Property UserName -eq $username | Select-Object -Property SessionID
            foreach ($sessionID in $userNameSessionID) 
            {
                $currentSessionID = $sessionID.SessionId       
                Write-Host "$username's SessionID is $currentSessionID" -ForegroundColor Cyan
                try 
                {
                    Start-Sleep 2
                    Stop-TSSession -ComputerName $server -Id $currentSessionID -Confirm:$false -Force
                    Write-Host "Disconnected $username's session $currentSessionID from $server" -ForegroundColor Green
                }
                catch 
                {
                    Write-Host "Unable to disconnect $username's session $currentSesionID from $server" -ForegroundColor Red
                }
            }
        }
        else 
        {
            Write-Host "User $username not connected to $server" -ForegroundColor Yellow
        }
    }
    catch 
    {
        Write-Host "Unable to connect to $server" -ForegroundColor Red
    }
}
Read More

PowerShell script to change permission of a certificate’s private key

2015-11-01 / PowerShell, Server / 2 Comments

Problem:
I needed to change the permissions of a certificate’s private key in the windows local computer store on multiple servers. I use the certificate’s thumbprint to find the certificate and then apply the permissions to the user listed.

Solution:

$serviceAccount = 'NETWORK SERVICE'
$certThumbprint = 'x xx xx x xx xx xx dd dd ee ee ff ff gg hh 5e 20 3f 53 52'
$permissionType = 'Read'
    try
    {
        #Clear Existing Variables
        $cert = ''
        $keyFullPath = ''
        Write-Host "--------------------------"
        Write-Host "Server: $env:ComputerName" -ForegroundColor Cyan
        Write-Host "Finding Certificate..." -ForegroundColor Green
        #Get Certificate
        $cert = Get-ChildItem -Path cert:\LocalMachine\My | Where-Object {$_.Thumbprint -eq ($certThumbprint -replace '\s','')}
        If ($cert -ne $null -and $cert.PrivateKey.CspKeyContainerInfo.UniqueKeyContainerName -ne $null) 
        {
            # Get Location of the machine related keys
            $keyPath = $env:ProgramData + "\Microsoft\Crypto\RSA\MachineKeys"; 
            $keyName = $cert.PrivateKey.CspKeyContainerInfo.UniqueKeyContainerName;
            $keyFullPath = $keyPath + $keyName;
            Write-Host "Found Certificate..." -ForegroundColor Green
            Write-Host "Granting access to $serviceAccount..." -ForegroundColor Green
            #Grant Full Control to account listed in $serviceAccount
            $acl = (Get-Item $keyFullPath).GetAccessControl('Access') #Get Current Access
            $buildAcl = New-Object  System.Security.AccessControl.FileSystemAccessRule($serviceAccount,$permissionType,"Allow") #Build Access Rule
            $acl.SetAccessRule($buildAcl) #Add Access Rule
            Set-Acl $keyFullPath $acl #Save Access Rules
            Write-Host "Access granted to $serviceAccount..." -ForegroundColor Green
            Write-Host "--------------------------"
        }
        Else {
            Write-Host "Unable to find Certificate that matches thumbprint $certThumbprint or the private key is missing..." -ForegroundColor Red
            Write-Host "--------------------------"
        }
    }
    catch
    {
        Write-Host "Unable to grant access to $serviceAccount..." -ForegroundColor Yellow
        Write-Host "--------------------------"
        throw $_;
    }
Read More

PowerShell script to modify local account password on multiple remote computers

I had a need to do this for a client.

Problem: Need to change a single user’s account password and flags across multiple systems

Solution:
You can use this script to modify a local user’s password across multiple machines. It will check that the machine is reachable (by ping) and it will set the account to never expire and enabled (userflag 66048)

Modify the $user and $password fields with their desired strings and update the $computers path to the computers.txt file with a list of computers
Run the PowerShell script as someone who is administrator on the machines in the computers.txt file

$computers = Get-Content -path C:\temp\computers.txt
$user = "USER"
$password = "PASSWORD"
Foreach ($computer in $computers)
{
    #Start-Sleep 1
    $ErrorMessage = ""
    $FailedItem = ""
    $localuser = ""
     
    if (Test-Connection $computer -ErrorAction stop -count 3 -Quiet)
    {
        try
        {
            $localuser = [adsi]"WinNT://$computer/$user,user"
             
            if ($localuser.Path -ne $null)
            {
                $localuser.SetPassword($Password)
                $localuser.userflags = 66048
                $localuser.SetInfo()
                Write-Host "Password changed on: $computer" -ForegroundColor Green
            }
            else
            {
                Write-Host "$user not found on: $computer" -ForegroundColor Red
            }
        }
        catch
        {
            Write-Host "Error changing password on: $computer" -ForegroundColor Red
            #$ErrorMessage = $_.Exception.Message
            #$FailedItem = $_.Exception.ItemName
            #Write-Host $ErrorMessage -ForegroundColor Gray
            #Write-Host $FailedItem -ForegroundColor Gray
        }
    }
    else
    {
        Write-Host "Unable to connect, not changing password on: $computer" -ForegroundColor Red
    }
}
Read More

in place windows server 2008 r2 standard to enterprise upgrade

2015-05-18 / Random / No Comment

You can find information on how to upgrade your license here

http://richardstk.com/2012/04/10/in-place-upgrade-of-windows-server-2008-r2-standard-to-enterprise-or-datacenter-edition/

Read More

Windows NFS server and issues with php rename() and chown

Problem: Windows NFS has various mount points exported. on a UNIX system you use php and a method to move/rename files using php rename() .

while normally chown is restricted to root access, you can still chown to yourself in linux.

the rename() method in php also does a chown() which changes the owner to itself, not sure why, but it does. This caused problems when connected to our NFS server hosted in windows and we were unable to reproduce it on a local linux storage or a linux NFS server. The problem was due to the nature of Windows NFS server.

“Users accessing NFS shares from a Windows Server 2008 R2 based NFS server may get the error “Permission Denied” while trying to run the ‘chown’ command from UNIX NFS clients. This can happen even though the user is the owner of the files. The UNIX “root” user does not exhibit these issues.”

Solution: Apply recommended registry setting for Windows Server 2008 R2, although we were running Server 2012 this is still relevant
http://support.microsoft.com/kb/2708985

Read More