PowerShell – Create a Barcode GUI Form and Print to PDF

April 9, 2019

When you need to create a Barcode you can use this method

Download one of the free TTF (True Type Fonts) barcode fonts.

Example : “CCode39”

image

image

Solution :

CLS

Function PrintPDF{

    $PrintPageHandler = {
        param([object]$sender, [System.Drawing.Printing.PrintPageEventArgs]$ev)

    $linesPerPage = 0
    $yPos = 0
    $count = 0
    $leftMargin = $ev.MarginBounds.Left
    $topMargin = $ev.MarginBounds.Top
    $line = $null

    $printFont = New-Object System.Drawing.Font("CCode39",30)

    # Calculate the number of lines per page.
    $linesPerPage = $ev.MarginBounds.Height / $printFont.GetHeight($ev.Graphics)

    # Print each line of the BarCode.
    $yPos = $topMargin + ($count * $printFont.GetHeight($ev.Graphics))
        write-host $yPos " " $count " " $linesPerPage
    $ev.Graphics.DrawString($oLabel.Text, $printFont, [System.Drawing.Brushes]::Black, $leftMargin, $yPos, (New-Object System.Drawing.StringFormat))

    # If more lines exist, print another page.
    if ($line -ne $null) 
      {
        $ev.HasMorePages = $true
      }
    else
      {
        $ev.HasMorePages = $false
      }
   }

    $pd = New-Object System.Drawing.Printing.PrintDocument
    $pd.PrinterSettings = New-Object System.Drawing.Printing.PrinterSettings
    $pd.PrinterSettings.PrinterName = 'Microsoft Print to PDF'
    $pd.PrinterSettings.PrintToFile = $true

    # https://social.technet.microsoft.com/Forums/scriptcenter/en-US/c7351021-800a-4ce9-bfa3-37b54e1750df/printing-a-windows-form?forum=winserverpowershell
    $pd.add_PrintPage($PrintPageHandler)

    $pd.PrinterSettings.PrintFileName = "C:\Temp\BarCode 39.pdf"
    
    $pd.Print()
}


Function CreateForm {

    Add-Type -AssemblyName System.Windows.Forms
    Add-Type -AssemblyName System.drawing
 
    # Create a new Form Object
    $Form = New-Object System.Windows.Forms.Form
 
    # Create the size of your form 
    $Form.width = 1000
    $Form.height = 500
 
    # Set the name of the form 
    $Form.Text = ”This is a Barcode 39 Form”
 
    # Set the font of the text to be used within the form
    $Font = New-Object System.Drawing.Font("Arial",10)
    $Form.Font = $Font
 
    # Add and set the BarCode text and TTF Font
    $oLabel = new-object System.Windows.Forms.Label
    $oLabel.Location = new-object System.Drawing.Size(300,180) 
    $oLabel.size = new-object System.Drawing.Size(600,80) 
    
    $oLabel.Text = "*1234567*"
    $oLabel.Font = New-Object System.Drawing.Font("CCode39",30)
    
    $Form.Controls.Add($oLabel)
   
    # Add a Print Button
    $oButton = New-Object Windows.Forms.Button
    $oButton.Text = "Print to PDF"
    $oButton.Top = 10
    $oButton.Left = 10
    $oButton.Width = 150
    $oButton.Anchor = [System.Windows.Forms.AnchorStyles]::Bottom -bor [System.Windows.Forms.AnchorStyles]::Right 
    
    $oButton.add_click({PrintPDF})
    $oButton.add_click({$oButton.Text = "PDF Created"})
    
    $Form.controls.add($oButton)
 
 
    $Form.Add_Shown({$Form.Activate()})
    
    #Show the Form
    $Form.ShowDialog()| Out-Null
} 

# call the function 
CreateForm

Enjoy ! Smile


PowerShell / VBScript – Automating FTP / SFTP using WinSCP .Net Assembly COM Server

April 9, 2019

When you want to automate WinSCP using a VBScript it is not so straight forward ? Sad smile

First of all you can find the script here :

https://winscp.net/eng/docs/library_session_listdirectory#vbscript

But when you look carefully you will notice that this is not really a VBS Script but a WSH Script ?

What is the difference ? See here

WSH  is a very COM scripting technique, that has been replaced over time by .NET and PowerShell.

But is still available even on Windows 10  Winking smile

 

Prerequisites :

Download the WinSCPnet.dll Assembly from here

And register the .Net Assembly to expose the COM component.

‘ 32 Bit => %WINDIR%\Microsoft.NET\Framework\v2.0.50727\RegAsm.exe WinSCPnet.dll /codebase /tlb:WinSCPnet32.tlb
‘ 64 bit => %WINDIR%\Microsoft.NET\Framework64\v4.0.30319\RegAsm.exe WinSCPnet.dll /codebase /tlb:WinSCPnet64.tlb

PS :

Keep in mind that you also need the WinSCP.exe (from the same version as the WinSCPnet.dll !

So make sure you download both of them to guarantee the compatibility between the 2

 

Solution :

This is the original WSH Code

<job>                                                               
<reference object="WinSCP.Session"/>
<script language="VBScript">
Option Explicit
 
' Setup session options
Dim sessionOptions
Set sessionOptions = CreateObject("WinSCP.SessionOptions")
With sessionOptions
    .Protocol = Protocol_Sftp
    .HostName = "example.com"
    .UserName = "user"
    .Password = "mypassword"
    .SshHostKeyFingerprint = "ssh-rsa 1024 xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"
End With
 
Dim session
Set session = WScript.CreateObject("WinSCP.Session")
 
' Connect
session.Open sessionOptions
 
' Upload files
Dim transferOptions
Set transferOptions = WScript.CreateObject("WinSCP.TransferOptions")
transferOptions.TransferMode = TransferMode_Binary
 
Dim transferResult
Set transferResult = session.PutFiles("d:\toupload\*", "/home/user/", False, transferOptions)
 
' Throw on any error
transferResult.Check
 
' Print results
Dim transfer
For Each transfer In transferResult.Transfers
    WScript.Echo "Upload of " & transfer.FileName & " succeeded"
Next
 
' Disconnect, clean up
session.Dispose
 
</script>
</job>

 

Convert WHS into a real VBS Script like this :

Option Explicit
 
' Setup session options
Const Protocol_Ftp = 2 ' 0 = SFTP

Dim sessionOptions

Set sessionOptions = CreateObject("WinSCP.SessionOptions")

With sessionOptions
    .Protocol = Protocol_Ftp
    .HostName = "xxx.xx.xxx.xx"
    .UserName = "xxxxxxx"
    .Password = "xxxxx"
    '.SshHostKeyFingerprint = "ssh-rsa 2048 xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"
End With
 
Dim session
Set session = CreateObject("WinSCP.Session")
 
' Connect
session.Open sessionOptions
 
Dim directoryInfo
Set directoryInfo = session.ListDirectory("/")
 
Dim fileInfo
For Each fileInfo In directoryInfo.Files

' To avoid Variable uses an Automation type not supported in VBScript 800A01CA Error
' https://support.microsoft.com/en-us/help/306916/prb-vbscript-type-mismatch-error-when-field-type-is-adnumeric

    WScript.Echo fileInfo.Name  & " with size " & CDbl(fileInfo.Length) & _
        ", permissions " & fileInfo.FilePermissions & _
        " and last modification at " & fileInfo.LastWriteTime
Next
 
' Disconnect, clean up
session.Dispose

 

If you want to use FTP using TLS encryption you need to use these Session Options

Option Explicit
 
' Setup session options
Const Protocol_Ftp = 2
Const Protocol_SFtp = 0

Const Implicit = 0
Const ExplicitTls = 3
Const ExplicitSsl = 2

Dim sessionOptions

Set sessionOptions = CreateObject("WinSCP.SessionOptions")

With sessionOptions
    .HostName = "xxxxxxxxxx"
    .UserName = "xxxxxxxxxx"
    .Password = "xxxxxx"

    .Protocol = Protocol_Ftp
    .FTPSecure =  ExplicitSsl 
    .TlsHostCertificateFingerprint = "xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"

    ' .Protocol = Protocol_SFtp 
    ' .FTPSecure =  ExplicitTls 
    '.SshHostKeyFingerprint = "xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"

End With
Option Explicit
 
' Setup session options
Const Protocol_Ftp = 2
Const Protocol_SFtp = 0

Const Implicit = 0
Const ExplicitTls = 3
Const ExplicitSsl = 2

Dim sessionOptions

Set sessionOptions = CreateObject("WinSCP.SessionOptions")

With sessionOptions
    .HostName = "xxxxxxxxxx"
    .UserName = "xxxxxxxxxx"
    .Password = "xxxxxx"

    .Protocol = Protocol_Ftp
    .FTPSecure =  ExplicitSsl 
    .TlsHostCertificateFingerprint = "xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"

    ' .Protocol = Protocol_SFtp 
    ' .FTPSecure =  ExplicitTls 
    '.SshHostKeyFingerprint = "c4:15:73:e2:3a:c9:7b:cd:9e:a4:f1:ed:00:d2:ff:d7:56:fb:58:58"

End With

To get access to the Protocol and KeyFingerPrint in WinSCP

Go to Sessions – Server/Protocol Information

image

Copy the FingerPrint Key to the keyboard to use in your script

image

Bonus :

The beauty of the WinSCPnet.dll Assembly is that it has 2 interfaces being a DotNet Assembly that is COM Compatible.

As shown above you can use it in any COM Compatible Client (like VBS Script or AutoIT) Smile

1. .Net Assembly – PowerShell

But as well in PowerShell or any other DotNet Compatible Client (like C# or VB .Net or AutoIT !)

Here is how to generate the script from within WinSCP GUI.

Go to Generate Session URL/Code

image

Select .Net Assembly Code / PowerShell

image

Copy to clipboard and you’re off …

2. Scripting : Batch – Commandline and more …

image

For the Batch you need to download the commandline version WinSCP.com

Or you can run the the /console commandline option using the WinSCP.exe

image

ISSUES :

1. File Encoding Windows to UNIX

I had issues sending files from a Windows server to a UNIX server.

All looked OK when inspecting the source files… But the receiving party alarmed me that the files where corrupt Confused smile

There are 3 strange characters preceding the data on the other end ?

image

After investigation these 3 character are an indication that file is a UTF-8 encoding including a BOM

clip_image001

While UNIX don’t these encoded files, but wants ANSI or ASCIIF encoded files.

You can fix it 2 ways :

A. GUI

image

Select Preference of the DESITINATION Site and SET the “remove BOM and EOF marksoption

B. Scripting

The scripting engine of WinSCP has not means of setting this option automatically Sad smile

PowerShell script to save the files before sending as ASCII encoded files

Use this script to inspect the encoding of the files

CLS

Get-Content -Encoding Byte -TotalCount 100 "YourFile.txt" |% {Write-Host ("{0:x2} " -f $_) -NoNewline}; Write-Host

If you see these 3 Bytes : ef bb bf appearing at the beginning of the file  you have a UTF-8 encoded file

image

Save the file using ASCII encoding fixes the issue :

Get-Content "YourFile.txt" | out-file -encoding ASCII "NewFile.txt

 

AutoIT script to save the file as ANSI encoding:

If you open the file in Binary Mode – 16

You can see the same 3 characters appearing

<!– HTML generated using hilite.me –>

$file = FileOpenDialog("Select test file", @DesktopDir, "CSV files (*.csv*)")

 

$hFileOpen = FileOpen ($file,16)
$out = FileRead ($hFileOpen)

 

image

 

To fix it save the file as ANSI Mode – 512

<!– HTML generated using hilite.me –>

$file = FileOpenDialog("Select test file", @DesktopDir, "CSV files (*.csv*)")

 

$hFileOpen = FileOpen ($file,512) ; ANSI Encoding
$out = FileRead ($hFileOpen)

 

2. Event ID Error 8 / 11 Crypt32

When running the GUI I did not have any issues but when running the cmdline options I could not get connected ?

It took me a long time to figger out what the problem was. In the event viewer I saw a lot of Crypt32 Errors ?

image

The reason was that the server did not have Internet Access …

image

Once I fixed it I could log connect … this means that the GUI behaves differently from the cmdline option.

The GUI could connect using port 22 or 21 while the cmdline option needed to have regular internet access to download so Certificate information

Once this was done I could disable the regular internet access again.

 

Conclusion :

Basically the WinSCP options are unlimited on a windows platform.

You just have to choose the right scripting environment, GUI / Command Line / COM / .Net

Test it against your FTP server or SFTP Server

 

Happy Scripting Smile

Enjoy


PowerShell – Limit OneDrive4Business Synchronization to certain AD Domains

March 29, 2019

In the Onedrive4Business Admin Portal you can define which user that are part of a certain AD Domain are allowed to sync.

image

Solution :

You need to get the AD GUID’s first using the PowerShell Get-ADDomain Cmdlet.

See here on how to.

image

image

Enter the GUID’s and save it.

And choose to Block MAC devices if needed.

Enjoy !


PowerShell – Using PowerShell Script in Excel VBA

March 11, 2019

There are numerous real life scenarios where you would like to hand out an Excel to an End User to retrieve data straight in Excel.

Without him or her have to use PowerShell or any other obscure tool that does not look familiar to them.

So we can establish this by using the old time favorite tool from SAPIEN Technologies.

image

To download ActiveXPosh you need to register first …

This Free Tool is doing the magic between the PowerShell scripting and any COM compatible client, like Excel or VBScript or AutoIT Smile

So here we are going to create an Excel VBA macro to demonstrate the use.

Solution :

The flow being used is to export a PS script to CSV. And next open the data in the Excel file that runs the macro.

1. Make sure that you are using the correct architecture version x86 or x64, depending of your Excel architecture …

So if you use an Excel x86 version you need to install the x86 ActiveXPosh version.

2. Create a Excel + Macro using this script :

The PowerShell Script is :

Get-WmiObject -class Win32_Service | Export-Csv -Path c:\temp\temp.csv -NoTypeInformation

The complete VBA macro is :

 

Sub ActiveXPoSH_ExportPS_ImportCSV()

Const OUTPUT_CONSOLE = 0
Const OUTPUT_WINDOW = 1
Const OUTPUT_BUFFER = 2

Dim ActiveXPosh As Object
Dim success As Variant
Dim cmd As String
Dim file As String
Dim sheet As String

' see output location here
file = "C:\temp\temp.csv"
sheet = "Sheet1"

' create the PowerShell object
Set ActiveXPosh = CreateObject("SAPIEN.ActiveXPoSHV3")

success = ActiveXPosh.Init(vbFalse) 'Do not load profiles

If success <> 0 Then
    Debug.Print "Init failed"
End If

If ActiveXPosh.IsPowerShellInstalled Then
    Debug.Print "Ready to run PowerShell commands"
    'MsgBox ("Ready to run PowerShell commands")
Else
    Debug.Print "PowerShell not installed"
End If

'Set the output mode
ActiveXPosh.OutputMode = OUTPUT_CONSOLE

' set command string
cmd = "Get-WmiObject -class Win32_Service " _
      & " | Export-Csv -Path " & file & " -NoTypeInformation"

' clear all data
Sheets(sheet).Cells.Delete

Dim outtext, Str
' set the output mode to buffer
ActiveXPosh.OutputMode = OUTPUT_BUFFER

' run cmd
ActiveXPosh.Execute (cmd)

' get the output line by line and add it to a variable
For Each Str In ActiveXPosh.Output
    outtext = outtext & Str
    outtext = outtext & vbCrLf
Next

' import the data to the Sheet1
Set ws = ActiveWorkbook.Sheets(sheet)
With ws.QueryTables.Add(Connection:="TEXT;" & file, Destination:=ws.Range("A1"))
    .TextFileParseType = xlDelimited
    .TextFileCommaDelimiter = True
    .Refresh
End With

' delete the querytable if there is one
On Error GoTo nothingtodelete
    Sheets(sheet).QueryTables(1).SaveData = False
    Sheets(sheet).QueryTables.Item(1).Delete
nothingtodelete:

' Delete the temp CSV File
If Len(Dir$(file)) > 0 Then Kill file

' Alternatively you can get the output as a single String
' outtext = ActiveXPosh.OutputString
Debug.Print outtext

' Clear Immediate
Application.Wait (Now + TimeValue("0:00:02"))
Debug.Print Now
Application.SendKeys "^g ^a {DEL}"

End Sub

The result is :

image

I chose to work using an interim CSV file, because this is the fastest and most compatible with Excel. Smile

You can of course write cell per cell or an PowerShell array to Excel. But it will take you forever to make it happen. Sad smile

As you can imagine I can now get O365 and Azure data straight in Excel …

If you put a password on the macro you can safely hand it out in your organization !

 

Enjoy!


PowerShell–Azure ‘Az’ Module

January 1, 2019

A few weeks ago there was a new Module released for Azure called, Azure PowerShell ‘Az’ Module version 1.0

Az runs on Windows PowerShell 5.1 and PowerShell Core (cross-platform)

It can be used side-by-side the existing AzureRM Module.

But new features will only be released in the Az module.

To install the new module run this command using Admin priviliges.

Install -Module -Name Az

image

To see if the installation was successful run this command

Get-Module -ListAvailable "Az*"

image

To list all the commands run this command

Get-Command -Name *Az*

image

The connection method has been changed compared to what you are used to.

The Get-Credential cmdlet is replaced with Connect-AzAccount

Once executed you will see this reply

image

Go to the URL https://microsoft.com/devicelogin and fill in the code you got using this command

Connect-AzAccount

image

For more info see here

How to migrate scripts from AzureRM to Az see here

Enjoy !!


Office 365 – Retrieve User Password Expiration Date

December 4, 2018

Sometimes it will be handy to scan your O365 User base on next Password Expiration Date.

This is done using PowerShell, like this.

First connect to your tenant and execute this script :

CLS

$cred = Get-Credential "your.credentials@company.com"

Connect-MsolService -Credential $cred

$domain = Get-MsolDomain | where {$_.IsDefault -eq $true}

$PasswordPolicy = Get-MsolPasswordPolicy -DomainName $domain.Name

$Account = "Account.ToCheck@company.com" # Change HERE !!

$UserPrincipal  = Get-MsolUser -UserPrincipalName $Account

$UserPrincipal | fl PasswordNeverExpires

$PasswordExpirationDate = $UserPrincipal.LastPasswordChangeTimestamp.AddDays($PasswordPolicy.ValidityPeriod)

Write-host "Password will Expire on : $PasswordExpirationDate"

$StartDate = (GET-DATE)
$DaysLeft = NEW-TIMESPAN -Start $StartDate -End $PasswordExpirationDate
$DaysLeft = [math]::Floor($DaysLeft.TotalDays)

Write-host "Password will Expire in # Days : $DaysLeft"

$UserPrincipal | select DisplayName, LastPasswordChangeTimeStamp,@{Name=”PasswordAge”;Expression={((Get-Date).ToUniversalTime())-$_.LastPasswordChangeTimeStamp}} | sort-object PasswordAge -desc

image

If you want to change the Password Policy to NEVER EXPIRE use this codereset

Set-MsolUser -UserPrincipalName $Account -PasswordNeverExpires $true

If you want to RESET the Password, use this code

Set-MsolUserPassword -UserPrincipalName $Account -NewPassword "PassWord"

Enjoy !


Windows Server 2016 – Hyper-V CheckPoints Disk Space

December 4, 2018

Once you create VM checkpoints in Hyper-V it might start consuming a lot a disk space afterwards.

image

Because it will write data to the AVHDX files.

You can use PowerShell to investigate the current disk size.

Get-VM YourVM | Select-Object VMId | Get-VHD | Select-Object –Property path,computername,vhdtype,
@{label=’Size(GB)’;expression={$_.filesize/1gb –as [int]}}

image

As you can see the VHD Type is saying DIFFERENCING.

That is because you have a checkpoint pending. And this is NOT recommended in a production environment.

There are some tricky scenario’s where deleting checkpoints can corrupt your VM.

See here for more info

Enjoy !