All posts by Freakling

Alcohol calculator

Calculate the amount of pure alchohol in litre you consume per year. All forms needs to be filled in.

[void][reflection.assembly]::LoadWithPartialName("System.Windows.Forms")
$Form = New-Object 'System.Windows.Forms.Form'
  
$Calculate = {
    
[double]$Result = 0
[double]$beer = $BeerTextBox.Text
[double]$wwine = $WhiteWineTextBox.Text
[double]$rwine = $RedWineTextBox.Text
[double]$spirit = $SpiritTextBox.Text
$result = ($beer*0.5*0.05)+($wwine*0.7*0.08)+($rwine*0.7*0.13)+($spirit*0.7*0.4)
$ResultBox.Text = $Result
}

$Form.Size = "300,300"

$BeerLabel = New-Object 'System.Windows.Forms.Label'
$BeerLabel.Text = "Beer"
$BeerLabel.Location = "10,10"
$BeerLabel.Size = "70,15"
$Form.Controls.add($BeerLabel)

$BeerTextBox = New-Object 'System.Windows.Forms.TextBox'
$BeerTextBox.Location = "80,10"
$BeerTextBox.Text = "~ Antal 50cl 5%"
$Form.Controls.add($BeerTextBox)

$WhiteWineLabel = New-Object 'System.Windows.Forms.Label'
$WhiteWineLabel.Text = "White Wine"
$WhiteWineLabel.Location = "10,25"
$WhiteWineLabel.Size = "70,15"
$Form.Controls.add($WhiteWineLabel)

$WhiteWineTextBox = New-Object 'System.Windows.Forms.TextBox'
$WhiteWineTextBox.Location = "80,25"
$WhiteWineTextBox.Text = "~ Antal 70cl 8%"
$Form.Controls.add($WhiteWineTextBox)

$RedWineLabel = New-Object 'System.Windows.Forms.Label'
$RedWineLabel.Text = "Red Wine"
$RedWineLabel.Location = "10,40"
$RedWineLabel.Size = "70,15"
$Form.Controls.add($RedWineLabel)

$RedWineTextBox = New-Object 'System.Windows.Forms.TextBox'
$RedWineTextBox.Location = "80,40"
$RedWineTextBox.Text = "~ Antal 70cl 13%"
$Form.Controls.add($RedWineTextBox)

$SpiritLabel = New-Object 'System.Windows.Forms.Label'
$SpiritLabel.Text = "Spirit"
$SpiritLabel.Location = "10,55"
$SpiritLabel.Size = "70,15"
$Form.Controls.add($SpiritLabel)

$SpiritTextBox = New-Object 'System.Windows.Forms.TextBox'
$SpiritTextBox.Location = "80,55"
$SpiritTextBox.Text = "~ Antal 70cl 40%"
$Form.Controls.add($SpiritTextBox)

$CalcButton = New-Object 'System.Windows.Forms.Button'
$CalcButton.Location = "10,90"
$CalcButton.Size = "100,40"
$CalcButton.Text = "Calculate"
$CalcButton.add_Click($calculate)
$Form.Controls.add($CalcButton)

$ResultBox = New-Object 'System.Windows.Forms.Label'
$ResultBox.Location = "10,155"
$ResultBox.Size = "100,25"
$ResultBox.Text = "inget ännu"
$Form.Controls.add($ResultBox)

$AverageLabel = New-Object 'System.Windows.Forms.Label'
$AverageLabel.Location = "10,130"
$AverageLabel.Size = "150,25"
$AverageLabel.Text = "Medel i sverige är 10,3"
$Form.Controls.add($AverageLabel)

$Form.showdialog()

Get startpage

Invent startpage set in IE for XP and Windows7 clients. Needs a list of computers and collects the startpage in a CSV file.

[cmdletbinding()]
Param(
    $Computers,
    $output,
    $Progress,
    [Switch]$Loop
)

$ErrorActionPreference = "SilentlyContinue"

If(Test-Path -Path $Computers -PathType Leaf){
    [Array]$Script:computers = Get-Content $Computers
}

$ComputerCount = $Computers.Count
$Script:Database = @{}
Function Get-Startpage{
    Param(
        $output,
        $Progress
    )
    if(Test-Path -Path $Progress -PathType Leaf){
        [Array]$ProgressContent = Get-Content $Progress
    }
    Else{
        Try{"DATE,Computer,Status" | Out-File $Progress
            [Array]$ProgressContent = Get-Content $Progress
        }
        Catch{Write-Error $_;Break}
    }
    If(!(Test-Path -Path $output -PathType Leaf)){
        Try{'SID,Computername,USER,Start Page' | Out-File $output}
        Catch{Write-Error $_;Break}
    }
    Foreach($Client in $Script:Computers){
        
        Try{
            [Array]$status = $ProgressContent | Where-Object{ $_ -match "$Client"}
            $Status = $status[-1].Split(",")[-1]
        }
        Catch{$status -eq $null}
        
        Write-Host $Client
        
        $Ping = new-object Net.NetworkInformation.Ping
        
        If(($Ping.Send($Client).Status -eq "Success") -and (($status -eq "Failed") -or ($status -eq $null))){
            
            $OperatingSystem  = (([adsisearcher]"cn=$client").FindOne()).Properties.operatingsystem
            
            Switch($OperatingSystem){
                "Windows 7 Enterprise"{
                    Try{$Userprofile = Get-WmiObject -ComputerName $client win32_userprofile}
                    Catch{
                        Write-Host $Client
                        Write-Error $_
        				$Userprofile = $null
        			}
                    If($Userprofile){
                        $Userprofile | Where-Object {($_.localpath -match 'c*users') -and ($_.Loaded)} | Select SID | ForEach-Object{
                            $SID = $_.SID    
                            $objSID = New-Object System.Security.Principal.SecurityIdentifier($SID)
                            $objUser = $objSID.Translate([System.Security.Principal.NTAccount])
                            $Username = $objUser.Value.Split("\")[1]
                            $table = @{"USER" = $Username;"SID" = $SID; "Start Page" = "n/a"; "Computername" = "$Client"}
                            $currentUser = New-Object psobject -property $table
                            $Script:Database.$client += @{"$username" = $currentUser}
                        }
                    }
                }
                
                "Windows XP Professional"{
                    Try{$Userprofile = (Get-WmiObject -computername $Client -Query "Select UserName from Win32_ComputerSystem").UserName}
                    Catch{
                        Write-Host $Client
                        Write-Error $_
        			    $Userprofile = $null
        			}
                    If($Userprofile){
                        $Domain = $Userprofile.Split("\")[0]
                        $Username = $Userprofile.Split("\")[1]
                        $objUser = New-Object System.Security.Principal.NTAccount($Domain, $Username)
                        $SID = ($objUser.Translate([System.Security.Principal.SecurityIdentifier])).Value

                        $table = @{"USER" = $Username;"SID" = $SID; "Start Page" = "n/a"; "Computername" = "$Client"}
                        $currentUser = New-Object psobject -property $table
                        $Script:Database.$client += @{"$username" = $currentUser}
                    }
                    
                }
                
                "Default"{Break}
            
            }
            $Userdata = ($Script:Database.$Client | ForEach-Object {$_.Values})
            Foreach($User in $Userdata){
                Try{
                    $SID = $User.SID
                    $objSID = New-Object System.Security.Principal.SecurityIdentifier($SID)
                    $objUser = $objSID.Translate( [System.Security.Principal.NTAccount])  
                    $Username = $objUser.Value.Split("\")[1]
                    $reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('USERS', $Client)
                    $regkey = $reg.OpenSubkey("$SID\\Software\\Microsoft\\Internet Explorer\\Main")
                    $StartPage = $regkey.GetValue("Start Page")
                        
                    If($StartPage.GetType().Name -ne "String"){Throw "Value not String"}

                    $Script:Database.$Client.$Username.'Start Page' = $StartPage

                }Catch{
                    Write-Host $Client
                    Write-Error $_
                    $Script:Database.$Client.$Username.'Start Page' = "Unknown"
                }
            }
			Try{
				$Script:Database | ForEach-Object{$_.$Client.Values} | ForEach-Object{
					$SID = $_.SID
					$Computername = $_.Computername
					$User = $_.USER
					$StartPage = $_.'Start Page'
					"$SID,$Computername,$User,$StartPage"
				} | Out-File $output -Append
			}
			Catch{Write-Host $Client;Write-Error $_;Break}
				
			Try{
				$TimeStamp = Get-Date -Format "yyyyMMdd:HH.mm.ss"
				"$TimeStamp,$Client,Successful" | Out-File $Progress -Append
				$Script:Database.Status += @{"$Client" = $True}
			}
			Catch{Write-Host $Client;Write-Error $_;Break}
        }
        ElseIf(($Status -eq "Successful") -or ($Status -eq "Skipped")){
            #Do nothing. Already Successful.
            $Script:Database.Status += @{"$Client" = $True}
        }
        Else{
            Try{
                $Script:Database.Status += @{"$Client" = $false}
                $TimeStamp = Get-Date -Format "yyyyMMdd:HH.mm.ss"
                "$TimeStamp,$Client,Failed" | Out-File $Progress -Append
            }
            Catch{Write-Host $Client;Write-Error $_}
        }
        
        $Script:Percentage = 100/$ComputerCount
        $Script:Completed = ($Script:Database.Status.Values | Where-Object{$_ -Match $true}).Count
        If(!$Script:Completed){$Script:Completed = 0}
        $Script:currentpercentage = $Script:Percentage*$Script:Completed
        
        write-progress -Activity "Collecting Startpage information." -ID 1 -Status "Processing $Client. Completed count: $Script:Completed/$Script:ComputerCount" -PercentComplete $Script:currentpercentage
    }
}


[Void](Get-Startpage -output $Output -Progress $Progress)
If($Loop){
    While(($Database.status.Values  | Where-Object{$_ -Match $false}) -eq $false){
        [Void](Get-Startpage -output $Output -Progress $Progress)
    }
}

Access CSC

this is used to quickly gain access to a users client side cache without ruining sync.
Used for backup issues and other stuff.

$comp = 'computer1' # Replace with computer
$FQDN = 'your.domain.com' # Replace with FQDN
$UserID = 'user1' # Replace with the useraccount to gain access

Function ConvertTo‐Base64($string) {
  $bytes  = [System.Text.Encoding]::Unicode.GetBytes($string);
  $encoded = [System.Convert]::ToBase64String($bytes); 
  Return $encoded;
}

$cmd = @'
if (schtasks.exe /Query | select-string "RUNCMD1") {
    schtasks.exe /delete /tn "RUNCMD1" /F
}
     
mkdir c:\temp\ -ea 0

"running"|out-file c:\temp\csctemp
schtasks.exe /ru "SYSTEM" /Create /TN "RUNCMD1" /TR "cmd /C icacls c:\windows\csc\ /c /grant {FQDN}\{UserID}`:`(OI`)`(CI`)F /T > c:\temp\cmd1.log && del c:\temp\csctemp" /sc "once" /ST "23:00"
schtasks.exe /run /tn "RUNCMD1"
'@.Replace('{FQDN}',$FQDN).Replace('{UserID}',$UserID)

$EncodedCmd = ConvertTo‐Base64($cmd)
$EncodedArgs = 'c:\windows\System32\WindowsPowerShell\v1.0\powershell.exe -ExecutionPolicy ByPass -EncodedCommand "' + $EncodedCmd + '"'
$result = Invoke-WmiMethod -ComputerName $comp -class win32_process -name create -ea 0 -ArgumentList $EncodedArgs

Add users as local admin on select computers

This is used to add users as local admin on a select group of clients.
Needs a input file specified or default to input.txt in same folder.
It converts Administrator SID to friendlyname to account for things such as foreign characters.

It uses WMI to invoke a client side script to be run under admin credentials in order to bypass PSremoting policies.

Content in input should be formatted as below.

Computername,Username
computer1,User1
computer2,User1
computer2,User2
This is used to add users as local admin on a select group of clients.
Needs a input file specified or default to input.txt in same folder.
It converts Administrator SID to friendlyname to account for things such as foreign characters.

It uses WMI to invoke a client side script to be run under admin credentials in order to bypass PSremoting policies.

Content in input should be formatted as below.
Computername,Username
computer1,User1
computer2,User1
computer2,User2

Script below:

[Cmdletbinding()]
Param(
    [String]$InputFile=".\input.txt"
)
$MyPath = Split-Path $MyInvocation.MyCommand.path -Parent
$Logpath = "$MyPath\Status.log"
$Ping = new-object Net.NetworkInformation.Ping
If(!(Test-path -Path $InputFile -PathType Leaf)){Throw{"Could not find $InputFile"}}
$Data = Import-Csv $InputFile -Delimiter ','

$Logpath = "$MyPath\Status.log"
If(!(Test-Path -Path $Logpath -PathType Leaf)){
    New-Item -Path $Logpath -ItemType file
    "Computername,Username,Status" | Out-File $Logpath
}
$LogData = Import-Csv $Logpath -Delimiter ','

Function ConvertTo-Base64($String){
    $Bytes = [System.Text.Encoding]::Unicode.GetBytes($String)
    $Encoded = [System.Convert]::ToBase64String($Bytes)
    Return $Encoded
}
$cmdtemplate = @'
$SID = "S-1-5-32-544" # built-in administrator group sid
# Find Administrator group based on SID and gets the friendlyname of account
$admins = New-object System.Security.Principal.SecurityIdentifier($SID)
[string]$adminNTaccount = $admins.Translate([System.Security.Principal.NTAccount]).Value.Split('\')[1]

# Gets a list of members and collects friendlyname of each member
$computer = [ADSI]('WinNT://.,computer')
$Group = $computer.psbase.children.find($adminNTaccount)
$Group.Add("WinNT://{replacewithuser},user") # Must change/replace user string here with real user account
'@

$data | Foreach-object{
    $Computer = $_.Computername
    $User = $_.Username
    $Cmd = $cmdtemplate -replace '{replacewithuser}',$User
    $EncodedCmd = ConvertTo-Base64($Cmd)
    $EncodedArgs = 'c:\windows\System32\WindowsPowerShell\v1.0\powershell.exe -ExecutionPolicy ByPass -EncodedCommand "' + $EncodedCmd + '"'
    Remove-Variable added -Force -ErrorAction Ignore
    $added = $LogData | Where-Object {($_.Computername -match $computer) -and ($_.Username -match $user) -and ($_.Status -match "Success")}
    $Status = ($Ping.Send($Computer)).Status
    If($added){
        Write-host "Already added $User to $Computer" -ForegroundColor Blue
    }
    ElseIf($Status -eq "Success"){
        $Result = Invoke-WmiMethod -ComputerName $Computer -class win32_process -name create -ErrorAction Ignore -ArgumentList $EncodedArgs
        Write-Verbose $Result
        If($Result.ReturnValue -eq 0){
            Write-host "Added $User to $Computer" -ForegroundColor Green
            $Returnvalue = "Success" 
        }
        Else{
            Write-Host "Failed to add $User to $Computer" -ForegroundColor Red
            $Returnvalue = "Error $($result.ReturnValue)"
        }        
        
    }
    Else{
        Write-Host "Could not contact $Computer" -ForegroundColor Red
        $Returnvalue = "Host Unreachable"
    }

    "$computer,$User,$Returnvalue" | Out-File -Append $Logpath


}
Write-host "Done!" -BackgroundColor Green -ForegroundColor Black
Write-host "Bash the keyboard to quit application!" -BackgroundColor Green -ForegroundColor Black
[void]$Host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")

Custom progress bar

Not that anyone wants or needs it, but here is a custom progress bar!

31

54

It has two modes. Loop and step. And is called like below.

PS> 0..99 | ForEach-Object{"Processing, current step $_";Start-Sleep -Milliseconds 200;.\epicship.ps1 -step $_};"Installation complete!"
 .\epicship.ps1 -loop

The Loop has more functions, such as a sun shining and getting obscured by the ship (notice the reflections dissapearing as the sunrays are blocked by the ship).

Loop
Capture

Param(
    [int]$step,
    [Switch]$Loop,
    [string]$FlagMsg,
    [int]$speed = 500,
    $condition = $False
)

$ErrorActionPreference = "SilentlyContinue"

if($step -gt 100){Write-Error "Step cannot exceed 100";Break}
[int]$step = $step/100*77
if($step -le 0){$step++}
#[console]::CursorVisible = $False

Function Animatrix{
    Param(
        [switch]$Loop,
        [int]$step
    )

    if($Loop){$action = "Loop"}
    Elseif($step){$action = "Step"}

    $ship = "                                                                                                                    "
    if(!($FlagMsg)) { $FlagMsg = "LOADING.-" } else { 

    $FlagMsg = "$FlagMsg-." 

    if($flagmsg.length -gt 22) { 
      write-error "Flag text is too long .. max 20 characters!!"
      Break
    }

    }

    # Add padding to left
    while($FlagMsg.length -lt 22) {
      $FlagMsg = " $FlagMsg"
    }

    # Add padding to right
    while($FlagMsg.length -lt 116) {
      $FlagMsg += ' '
    }  
    $ship += $FlagMsg
$ship += @'

                 |                                                                                                  
         Ya...___|__..ab.     .   .                                                                                 
          Y88b##\88b##\88b   (     )                                                                                
           Y88b##:88b##:88b   `.oo'                                                                                 
           :888##|888##|888  ( (`-'                                                                                 
  .-.      d88P##;88P##;88P   `.`.                                                                                  
 //._)    d8P-"""|"""'-Y8P      `.`.                                                                                
(o(`    .-.  .-. |.-.  .-.  .-.  )oo)                                                                               
 \o`---(-O-)(-O-)(-O)(-O-)(-O-)-'//                                                                                 
~~`.~~~~`-'~~`-'~~`-'~~`-'~~`-'~~.'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   `----------------------------'                                                                                   
'@ 

    $arrship = $ship.Split("`n")
    $saveY = [console]::CursorTop
    $saveX = [console]::CursorLeft
    Switch($action){
        "Loop"{

            0..109 | ForEach-Object{
                $i = 0
                $arrship | ForEach-Object{
                    If($i -eq $arrship.count-2){
                        $arrship[$i] = "~" + $arrship[$i].Substring(0,$arrship[$i].Length-1)
                    }
                    else{$arrship[$i] = " " + $arrship[$i].Substring(0,$arrship[$i].Length-1)}
                    $i++
                }
                $n = 0
                $chance = 0
                $sunpos = 60,75
                $sunMultiplier = 0.28
                $arrship | ForEach-Object{
                    $nChar = 0
                    If($n -eq $arrship.Count-5){
                    [char[]]$arrship[$n] | ForEach-Object{
                        If(($nChar -ge $sunpos[0]+5) -and ($nChar -le $sunpos[1]-5)){
                            If($_ -eq " "){Write-Host $_ -BackgroundColor Yellow -NoNewline;$chance += $sunMultiplier}
                            Else{Write-Host $_ -ForegroundColor Yellow -NoNewline}
                        }
                        Elseif($nChar -lt 116){Write-Host $_ -ForegroundColor DarkGray -NoNewline}
                        Else{Write-Host $_ -ForegroundColor DarkGray}
                        $nChar++
                        }
                    }
                    ElseIf($n -eq $arrship.Count-4){
                    [char[]]$arrship[$n] | ForEach-Object{
                        If(($nChar -ge $sunpos[0]+2) -and ($nChar -le $sunpos[1]-2)){
                            If($_ -eq " "){Write-Host $_ -BackgroundColor Yellow -NoNewline;$chance += $sunMultiplier}
                            Else{Write-Host $_ -ForegroundColor Yellow -NoNewline}
                        }
                        Elseif($nChar -lt 116){Write-Host $_ -ForegroundColor DarkGray -NoNewline}
                        Else{Write-Host $_ -ForegroundColor DarkGray}
                        $nChar++
                        }
                    }
                    ElseIf($n -eq $arrship.Count-3){
                    [char[]]$arrship[$n] | ForEach-Object{
                        If(($nChar -ge $sunpos[0]) -and ($nChar -le $sunpos[1]) -and ($_ -eq " ")){Write-Host $_ -BackgroundColor Yellow -NoNewline;$chance += $sunMultiplier}
                        Elseif($nChar -lt 116){Write-Host $_ -ForegroundColor DarkGray -NoNewline}
                        Else{Write-Host $_ -ForegroundColor DarkGray}
                        $nChar++
                        }
                    }
                    ElseIf($n -eq $arrship.count-2){Write-Host $arrship[$n] -ForegroundColor Blue}
                    Elseif($n -eq $arrship.count-1){Write-Host $arrship[$n] -ForegroundColor Cyan}
                    Else{Write-host $arrship[$n] -ForegroundColor DarkGray}
                    $n++
                }

                wavegen -modulus 3 -SunXY $sunpos -chance $chance
                wavegen -modulus 10 -SunXY $sunpos -chance $chance
                wavegen -modulus 30 -SunXY $sunpos -chance $chance
                wavegen -modulus 200 -SunXY $sunpos -chance $chance
                $refchance = $chance*10
                "chance of reflection is: $refchance%"

                Start-Sleep -Milliseconds $speed

                [console]::setcursorposition($saveX,$saveY)
            }
        }
        "Step"{
            Clear-Host
            $i = 0
            $arrship | ForEach-Object{
                If($i -eq $arrship.count-2){$arrship[$i] = "~"*$step + $arrship[$i].Substring(0,$arrship[$i].Length-1*$step)}
                else{$arrship[$i] = " "*$step + $arrship[$i].Substring(0,$arrship[$i].Length-1*$step)}
                    $i++
            }
            $n = 0
            $arrship | ForEach-Object{
                If($n -eq $arrship.count-2){Write-Host $arrship[$n] -ForegroundColor Blue}
                elseif($n -eq $arrship.count-1){Write-Host $arrship[$n] -ForegroundColor Cyan}
                Else{Write-host $arrship[$n] -ForegroundColor Yellow}
                $n++
            }
            $chance = 7
            $sun = 30,45

            wavegen -modulus 3 -SunXY $sun -chance $chance
            wavegen -modulus 10 -SunXY $sun -chance $chance
            wavegen -modulus 30 -SunXY $sun -chance $chance
            wavegen -modulus 200 -SunXY $sun -chance $chance
        }
    }
}

function wavegen{
    Param(
        [int]$modulus,
        [array]$SunXY,
        [int]$chance
    )
    $test = 0
    0..115 | ForEach-Object{
        $test++
        $rand = get-random -Maximum 1000  
        $rand%$modulus
    } | ForEach-Object{

            $i = Get-random -maximum 5  
            If(($test -ge $SunXY[0]) -and ($test -le $SunXY[1]) -and ((Get-Random -Maximum 10) -lt $chance)){
                $color = "Yellow"
            }Else{
                If($i%2 -eq 0){$color = "DarkBlue"} 
                else{$color = "blue"}  
            }
            if($_ -eq 1){

                write-host -foregroundcolor $color "~" -NoNewline        
            }
            elseif($_ -eq 2){
                Write-Host -ForegroundColor $color "-" -NoNewline
            }
            elseif($_ -eq 3){
                Write-Host -ForegroundColor $color "_" -NoNewline
            }
            else{Write-host " " -NoNewline}  
    }
    Write-host " "
}

If($Loop){
    Animatrix
    While($condition -eq $False){
        Animatrix -Loop
    }
}
Elseif($step){
    Animatrix -step $step
    [console]::setcursorposition($saveX,$saveY)
}
Else{
    Write-Warning "You're doing it wrong!"
}

FileDialogs

Function SaveFileDialog{
    Param(
        [Parameter(Mandatory=$True)]
        $Filetype
    )
    [Void][Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
    $SaveFileDialog = New-Object System.Windows.Forms.SaveFileDialog
    $SaveFileDialog.Filter = "$Filetype files (*.$Filetype)|*.$Filetype|All files (*.*)|*.*"
    $status = $SaveFileDialog.ShowDialog()

    If($status -eq "Cancel"){$Return = $status}
    Else{$Return = $SaveFileDialog.FileName}

    $SaveFileDialog.Dispose()
    Return $Return
}
Function SelectFileDialog{
    Param(
        [Parameter(Mandatory=$True)]
        $Filetype
    )
    [Void][Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
    $OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog

    $OpenFileDialog.Filter = "$Filetype files (*.$Filetype)|*.$Filetype|All files (*.*)|*.*"

    $status = $OpenFileDialog.ShowDialog()

    If($status -eq "Cancel"){$Return = $status}
    Else{$Return = $OpenFileDialog.FileName}

    $OpenFileDialog.Dispose()
    Return $Return
}
Function SelectFolderDialog{
    [Void][Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")

    $SelectFileDialog = New-Object System.Windows.Forms.FolderBrowserDialog
    $status = $SelectFileDialog.ShowDialog()

    If($status -eq "Cancel"){$Return = $status}
    Else{$Return = $SelectFileDialog.FileName}

    $SelectFileDialog.Dispose()
    Return $Return
}

The one-liner maker!

I give you all, probably the most unused function on the planet. The “Make one-liner”!

It’s probably not even close to “Complete” but it is easy to add exceptions on where to format.

Function Main{
    $ScriptString = Get-Content [enter powershell script path here]
    Make-Oneliner $ScriptString
}

Function Make-Oneliner{
    Param([Array]$array)
    [string]$OneLiner = @()
    Foreach($Line1 in $array){
    
    $Line1 = $Line1.Split("#")[0]
    $Line1 = $Line1.Trim()
    Foreach($Line in $Line1){
        #Parse and add ;
            If(
                ($line.EndsWith("`{")) -or
                ($line.EndsWith("`;")) -or
                ($line.EndsWith("`(")) -or
                ($line.EndsWith("`|")) -or
                ($line.EndsWith("`]"))
            ){
                    $OneLiner += $Line
            }
            Elseif($line.EndsWith("`'")){
                if(($Line.Split("`'").Count % 1) -eq 0){
                    $Line = $Line + "`;"
                    $OneLiner += $Line
                    }
            }
            Elseif($line.EndsWith("`"")){
                if(($Line.Split("`"").Count % 1) -eq 0){
                    $Line = $Line + "`;"
                    $OneLiner += $Line
                    }
            }
            Elseif($Line.StartsWith("#")){
                # do nothing
            }
            Else{
                $Line = $Line + "`;"
                $OneLiner += $Line
            }
        }
    }
    # Cleanup inappropriate ";".
    $OneLiner = $OneLiner.Replace(';)',')').Replace(',;',',').Replace(';;',';').Replace(';;',';')

    # Cleanup inappropriate ";" on words using insensitive replace methods.
    $OneLiner = $OneLiner -ireplace '};else','}else'
    $OneLiner = $OneLiner -ireplace '};catch','}catch'
    $OneLiner = $OneLiner -ireplace '};finally','}finally'

    Return $OneLiner
}
Main

SQL queries in powershell

I was trying to query the SCCM database and sort the results with Powershell to get a full report on all the connections to a collection. To do this i needed to inject a SELECT query and retrieve an object from the database which proved to be a bit difficult. Below is how i resolved it.

To connect to a SQL you need a client, in powershell and .NET we have the System.Data.SQLClient namespace

$connection = New-Object System.Data.SqlClient.SqlConnection

Then i needed a connection string to open the database.

$connection.ConnectionString = "$ConnectionString"
$connection.Open()

Now we need to inject the command we want. Fairly simple, we create a SQLCommand and connect it to the already established connection.

$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection

Then we insert our command

$Command.CommandText = $SQLQuery

What we do after is execute the command and create a counter of the columns in the result.

$Reader = $Command.ExecuteReader()
$Counter = $Reader.FieldCount

Now we create the DataTable and use the counter to populate the columns. We name them after each column in the query.

$DataTable = New-Object system.Data.DataTable
for($i = 0; $i -lt $counter; $i++){
    $Column = New-Object system.Data.DataColumn $Reader.GetName($i),([string])
    $DataTable.Columns.Add($Column)
}

While the Reader is active it processes a loop for the length of counter and ejects the results into new rows in the DataTable.
To get the correct formatting, we place everything into a string and format accordingly. Name and value.
We then use the Invoke-Expression cmdlet to execute the formatted string and add values to the rows

while ($Reader.Read()) {
    $Data = @()
    for ($i = 0; $i -lt $Counter; $i++) {
        $Data += [String]$Reader.GetValue($i)
    }
    $DataTable.Rows.Add($Data)
}

Last but not least, we close the reader and the connection to the database and output the finished table

$Reader.Close()
$Connection.Close()
   
Return $DataTable

And we’re done!

The finished function will now look like this:

Function Get-SQLQuery{
    Param(
        [Parameter(Mandatory=$true)]
        [String]$SQLQuery,
        [Parameter(Mandatory=$true)]
        [String]$ConnectionString
    )
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = "$ConnectionString"
    $connection.Open()
    $Command = New-Object System.Data.SQLClient.SQLCommand
    $Command.Connection = $Connection
    $Command.CommandText = $SQLQuery
    $Reader = $Command.ExecuteReader()
    $Counter = $Reader.FieldCount
    
    $DataTable = New-Object system.Data.DataTable
    for($i = 0; $i -lt $counter; $i++){
        $Column = New-Object system.Data.DataColumn $Reader.GetName($i),([string])
        $DataTable.Columns.Add($Column)
    }
    
    while ($Reader.Read()) {
        $Data = @()
        for ($i = 0; $i -lt $Counter; $i++) {
            $Data += [String]$Reader.GetValue($i)
        }
        $DataTable.Rows.Add($Data)
    }
    $Reader.Close()
    $Connection.Close()
    
    Return $DataTable
}

First set your connectionstring and Select query then invoke the command like this:

Get-SQLQuery -ConnectionString $connectionstring -SQLQuery $SelectQuery

Examples of connectionstrings can be found at Connectionstrings.com
The usual string is the trusted which uses your credentials

If you are using the function a lot, concider adding it to your Powershell profile. For more information check out my previous post on WGET and profiles