Tag Archives: powershell

My script cheat sheet

That’s not a post, at least in the classical sense 😊 Rather it’s a collection of small scripts, that I will keep updating, for me to find easily. No rocket science, just small everyday stuff that I find myself googling again and again.

Powershell: Get the first X bytes of a file.

#
# Source: DotJim blog (http://dandraka.com)
# Jim Andrakakis, September 2022
#

# ===== Change here =====
$path = 'C:\somepath\somehugelogfile.log'
$pathOut = 'C:\temp\sneakpeak.txt'
$numBytes = 10000
# =======================

$ErrorActionPreference='Stop'
Clear-Host

$bytes = Get-Content -Path $path -Encoding byte -TotalCount $numBytes
$str = [System.Text.Encoding]::UTF8.GetString($bytes)
Out-File -FilePath $pathOut -InputObject $str

Powershell: Remove files older than X days recursively.

#
# Source: DotJim blog (http://dandraka.com)
# Jim Andrakakis, September 2022
#

# ===== Change here =====
$path = 'C:\somepath\'
$filter = '*.xml'
$numDays = 30 
# =======================

$ErrorActionPreference='Stop'
Clear-Host

Get-ChildItem -Path $path -Filter $filter -Recurse | Where-Object {($_.LastWriteTime -lt (Get-Date).AddDays($numDays * -1))} | Remove-Item

Powershell: Change information in XML files en masse.

#
# Source: DotJim blog (http://dandraka.com)
# Jim Andrakakis, September 2022
#

# ===== Change here =====
$dir = 'C:\somepath\'
# =======================

$ErrorActionPreference='Stop'
Clear-Host

$list = Get-ChildItem -Path $dir -Filter '*.xml'
foreach($file in $list) {
    [xml]$xml=Get-Content -Path $file.FullName -Encoding UTF8
    # customize the XML paths below
    $customerName = $xml.PrintJob.DocumentHeader.CustomerName 
    if ([string]::IsNullOrWhiteSpace($customerName )) {
        continue
    }
    $xml.PrintJob.DocumentBody.RecipientName = $customerName 
    $xml.Save($file.FullName)
}

Windows command line: Change permissions of all files and directories in a path recursively.

CD C:\somepath
FOR /D %o IN (*.*) DO echo Y| cacls %o /T /G "NT AUTHORITY\Authenticated Users":F

Here “NT AUTHORITY\Authenticated Users” stands for the authenticated users group of the local machine; “F” stands for Full Permissions.

Linux command line: copy file from one PC to another

# scp -r /path/to/file USERNAME@IP_OF_TARGET:/path/to/dir
scp -r /home/dimitris/Downloads/Win11.iso dimitris@192.168.0.5:/home/dimitris/Downloads

Powershell: Get info from multiple XML files and write into CSV

#
# Source: DotJim blog (http://dandraka.com)
# Jim Andrakakis, October 2022
#

# The file filter here is based on date from-to, 
# but obvs you can change Where-Object to match size or name or whatever.

# The CSV separator was chosen to be tab (`t) because this is 
# very Excel-friendly.

# ===== Change here =====
$path = 'E:\somepath'
$outPath = "C:\temp\out-$([guid]::NewGuid().ToString().Split('-')[0]).csv"

$strDateTimeFrom = "2022-09-29 18:00:00"
$strDateTimeTo = "2022-09-29 20:00:00"
# =======================

Clear-Host
$ErrorActionPreference='Stop'

[DateTime]$dateTimeFrom = [DateTime]::Parse($strDateTimeFrom)
[DateTime]$dateTimeTo = [DateTime]::Parse($strDateTimeTo)

$filesList = Get-ChildItem -Path $path -Recurse -Filter '*.xml' | Where-Object { ($_.LastWriteTime -gt $dateTimeFrom) -and ($_.LastWriteTime -lt $dateTimeTo) }

$dataList = New-Object -TypeName 'System.Collections.Generic.List[string]'
# change this to match the XML info below
$dataList.Add("CompanyName`tInvoiceId`tTemplateId`tDocumentId`tFileName")
foreach($file in $filesList) {
    $fileFullName = $file.FullName
    $fileName = $file.Name
    [xml]$xml = Get-Content -Path $fileFullName
    # that's the part where you specify what info you need from the XML
    # my XMLs have multiple Document nodes per file, that's why I need a loop
    foreach($document in $xml.PrintJob.Documents.Document) {
        $documentId = $document.DocumentHeader.DocumentId
        $templateId = $document.DocumentHeader.TemplateId
        $invoiceId = $document.ArchiveAttributes.InvoiceId
        $custName = $document.DocumentHeader.Addresses.Recipient.CompanyName

        $dataList.Add("$custName`t$invoiceId`t$templateId`t$documentId`t$fileName")
    }
}

Out-File -FilePath $outPath -Encoding utf8 -InputObject $dataList
Write-Host "Finished, $($filesList.Count) files processed"

Chocolatey: My dev machine install list

choco install notepadplusplus
choco install winmerge -y 
choco install vscode -y 
choco install vscode-powershell -y 
choco install vscode-csharp -y 
choco install vscode-gitlens -y 
choco install git -y 
choco install tortoisegit -y 
choco install svn -y 
choco install tortoisesvn -y 
choco install postman -y 
choco install soapui -y 
choco install sql-server-management-studio -y

choco install intellijidea-community
choco install openjdk8

choco install visualstudio2019professional --package-parameters " --add Microsoft.VisualStudio.Workload.Azure --add Microsoft.VisualStudio.Workload.ManagedDesktop --add Microsoft.VisualStudio.Workload.NetCoreTools --add Microsoft.VisualStudio.Workload.NetWeb --add Microsoft.VisualStudio.Workload.Universal --includeRecommended --includeOptional --passive --locale en-US" -y
choco install visualstudio2022professional --package-parameters " --add Microsoft.VisualStudio.Workload.Azure --add Microsoft.VisualStudio.Workload.ManagedDesktop --add Microsoft.VisualStudio.Workload.NetCoreTools --add Microsoft.VisualStudio.Workload.NetWeb --add Microsoft.VisualStudio.Workload.Universal --includeRecommended --includeOptional --passive --locale en-US" -y
choco install dotnet-5.0-sdk -y
choco install dotnet-6.0-sdk -y

choco install ServiceBusExplorer -y

Install-Package \\fileserver\share\JamsScheduler\SetupClientx64.msi

Powershell: Archive files (zip + delete)


#
# Source: DotJim blog (http://dandraka.com)
# Jim Andrakakis, November 2022
#

# Prerequisite: 7zip is installed in the system.

# =================================
# This script zips everything found in $archiveName that 
# has a ModifiedDate after $dateFromStr and matches $filter.
# Inside $archivePath, it creates one dir per run and 
# inside this, one 7zip file per month plus one txt file
# that has the 7zip contents.
# E.g.
# C:\OldLogFiles
#     Run-LogFileArchive-20221122-112015
#         Archive-LogFileArchive-20200301-20200401.7z.001
#         Archive-LogFileArchive-20200301-20200401.txt
#         Archive-LogFileArchive-20200401-20200501.7z.001
#         Archive-LogFileArchive-20200401-20200501.txt
#         (etc etc)
# - The names of the run dirs (Run-LogFileArchive-20221122-112015) are
#   always Run-[archiveName]-[current date-time].
# - The names of the archives are Archive-[archiveName]-[From date]-[To date].7z.
# - Obviously the script will only generate 7z files for the months 
#   where it finds files.
# =================================

Clear-Host
$ErrorActionPreference = 'Stop'

try
{
    # ===== Parameters - Change here =====

    # A short description for the archive. This is added to the filenames.
    $archiveName = "LogFileArchive"

    # Directory to create the archive in
    $archivePath = "C:\OldLogFiles"

    # Directory to archive files from
    $path = "C:\logs"
    
    # Filter for files to archive, for example *.*, *.log or *.pdf
    $filter = "*.log"

    # How many months of files to keep (i.e. not archive), for example 12 (1 year).
    $monthsToKeep = 1

    # From-date to archive, e.g. '2020-12-31'
    # If $deleteFiles = $true you don't need to change this ever.
    $dateFromStr = "1900-01-01"

    # Delete files and empty folders after archiving?
    $deleteFiles = $true

	# Path of 7zip command line
	$zip = "C:\Program Files\7-Zip\7z.exe"

    # ===== Parameters =====

    if ([string]::IsNullOrWhitespace($filter)) 
    { 
    	$filter = "*.*"
    }   
            
    if ($monthsToKeep -le 0)
    {
    	throw "Months to keep cannot be 0 or negative"
    }

    if ([string]::IsNullOrWhitespace($dateFromStr))
    { 
    	throw "Date From cannot be empty"
    }     
        
    $dateToStr = [datetime]::Today.AddMonths($monthsToKeep * -1).ToString("yyyy-MM-01")

	Write-Host "Delete files set to $deleteFiles"	

    # ===== Sanity checks =====
    if ([string]::IsNullOrWhitespace($archiveName)) { throw "Parameter archiveName cannot be empty" }
    if ([string]::IsNullOrWhitespace($archivePath)) { throw "Parameter archivePath cannot be empty" }
    if ([string]::IsNullOrWhitespace($path)) { throw "Parameter path cannot be empty" }
    if ([string]::IsNullOrWhitespace($zip)) { throw "Parameter sevenZipPath cannot be empty" }
    
    if (-not(Test-Path -Path $archivePath)) { throw "Archive path $archivePath does not exist" }
    if (-not(Test-Path -Path $path)) { throw "Root path $path does not exist" }
    if (-not(Test-Path -Path $zip)) { throw "7zip not found in $zip" }
    
    $archivePath = [System.IO.Path]::Combine($archivePath, "Run-$archiveName-$([datetime]::Now.ToString("yyyyMMdd-HHmmss"))")

    # Loop through months
    $dateFrom = [datetime]::Parse($dateFromStr)
    $dateTo = [datetime]::Parse($dateToStr)

    $dateFromLoop = $dateFrom
    $loop = $true

    $haveArchivedFiles = $false

    if (Test-Path -Path $archivePath)
    {
        throw "Directory $archivePath already exists, stopping out of precaution"
    }
    New-Item -ItemType Directory -Path $archivePath | Out-Null
    
    $fullList = Get-ChildItem -Path $path -Filter $filter -File -Recurse `
    	| Where-Object { ($_.LastWriteTime -ge $dateFrom) -and ($_.LastWriteTime -lt $dateTo) }    

    while($loop)
    {    
        $dateToLoop = $dateFromLoop.AddMonths(1)
        if ($dateToLoop -gt $dateTo)
        {
            $dateToLoop = $dateTo
            $loop = $false
        }

        $archiveFile = [System.IO.Path]::Combine($archivePath, "Archive-$archiveName-$($dateFromLoop.ToString("yyyyMMdd"))-$($dateToLoop.ToString("yyyyMMdd")).7z")
        #Write-Host $archiveFile
        $archiveList = $archiveFile.Replace(".7z", ".txt")
    
        $list = $fullList | Where-Object { ($_.LastWriteTime -ge $dateFromLoop) -and ($_.LastWriteTime -lt $dateToLoop) }

        if ($list.Count -gt 0)
        {
            $haveArchivedFiles = $true
            $list | % { Out-File -FilePath $archiveList -Encoding utf8 -Append -InputObject "$($_.FullName)" }

            $cmd = $zip
            Write-Host "================ Archiving files from $path to $archiveFile ================"
            $params = "a $archiveFile -spf -ssw -stl -v2g -mx2 @$archiveList"
            & "$cmd" $params.Split(" ")
            
            # 7z parameter -sdel instructs 7zip to delete files after archiving
            # $params = "a $archiveFile -sdel -spf -ssw -stl -v2g -mx2 @$archiveList"
            # BUUUUUUUUT there's an open 7z bug which is that -sdel doesn't work
            # with file lists (which we need here)
            # that's why we need to delete the files with powershell after 7z
            if ($deleteFiles) {
				$list | % { Remove-Item -Force -ErrorAction Continue -Path "$($_.FullName)" }
                Write-Host "Deleted $($list.Count) files"
            }         
        }

        $dateFromLoop = $dateToLoop
    }

    if (-not $haveArchivedFiles)
    {
        Write-Host "================ No files found to archive ================"
    }
}
catch
{
    Write-Host "================ An error occured ================"
    Write-Error $_
}

Git: how to avoid checking in secrets (using a Powershell pre-commit hook)

Who among us hasn’t found him- or herself in this very awkward position: committing a config or code file with secrets (such as passwords or API keys) and then semi-panicked googling how to delete it from source control.

Been there and let me tell you the easiest way to delete it: copy all the code on disk, delete the repository completely and then re-create it.

(if this is not an option, well, there’s still a way but with much more work and risk, so do keep that code backup around!)

But you know what’s even better? That’s right, avoid this in the first place! That’s why Git hooks are so useful: they work without you neededing to remember to check your config files every time.

So here’s my solution to this:

  1. In the repository, go to .git/hooks and rename pre-commit.sample to pre-commit (i.e. remove the extension)
  2. Open pre-commit with a text editor and replace its contents with the following:
#!/bin/sh
C:/Windows/System32/WindowsPowerShell/v1.0/powershell.exe -ExecutionPolicy Bypass -Command '.\hooks\pre-commit.ps1'
  1. Add a new directory on the root of the repository named hooks.
  2. Inside this, add a text file named pre-commit.ps1 with the following code:
#
# Source: DotJim blog (http://dandraka.com)
# Jim Andrakakis, July 2022
#
Clear-Host
$ErrorActionPreference='Stop'

# ===== Change here =====
$listOfExtensions=@('*.xml','*.config')
$listOfSecretNodes=@('username','password','clientid','secret','connectionstring')
$acceptableString='lalala'
# ===== Change here =====

$codePath = (Get-Item -Path $PSScriptRoot).Parent.Parent.FullName

$errorList=New-Object -TypeName 'System.Collections.ArrayList'

foreach($ext in $listOfExtensions) {
    $list = Get-ChildItem -Path $codePath -Recurse -Filter $ext

    foreach($file in $list) {
        $fileName = $file.FullName
        if ($fileName.Contains('\bin\')) {
            continue
        }
        Write-Host "Checking $fileName for secrets"
        [xml]$xml=[xml]((Get-Content -Path $fileName).ToLowerInvariant())
        foreach($secretName in $listOfSecretNodes) {
            $nodes = $xml.SelectNodes("//*[contains(local-name(), '$secretName')]")
            foreach($node in $nodes) {
                if ($node.InnerText.ToLowerInvariant() -ne $acceptableString) {
                    $str = "[$fileName] $($node.Name) contains text other than '$acceptableString', please replace this with $acceptableString before commiting."
                    $errorList.Add($str) | Out-Null
                    Write-Warning $str
                }
            }
        }
    }
}

if ($errorList.Count -gt 0) {
    Write-Error 'Commit cancelled, please correct before commiting.'
}

So there you have it. I’m getting automatically stopped every time I tried to commit any .xml or .config file that contains a node with a name that contains username, password, clientid, secret or connectionstring, whenever the value of it is not ‘lalala’.

Obviously the extensions, node names and acceptable string can be changed at the top of the script. You can also change this quite easily to check JSON files as well.

Also note that this works on Windows (because of the Powershell path in the pre-commit hook) but with a minor change in the pre-commit bash script, you should be able to make it work cross-platform with Powershell core. I haven’t tested it but it should be:

#!/usr/bin/env pwsh -File '.\hooks\pre-commit.ps1'

Have fun coding!

RabbitMQ: How to move configuration, data and log directories on Windows

A good part of my job has to do with enterprise messaging. When a piece of data -a message- needs to be sent from, say, an invoicing system to an accounting system and then to a customer relationship system and then to the customer portal… it has to navigate treacherous waters.

Avast ye bilge-sucking scurvy dogs! A JSON message from accounting says they hornswaggled 1000 doubloons! Aarrr!!!

So we need to make sure that whatever happens, say if a system is overloaded while receiving the message, the message will not be lost.

A key component in this is message queues (MQ), like RabbitMQ. An MQ plays the middleman; it receives a message from a system and stores it reliably until the next system has confirmed that it picked it up.

My daily duties includes setting up, configuring and maintaining a few RabbitMQ instances. It works great! Honestly, so far -for loads up to a couple of 100s of messages per second- I haven’t even had the need to do any serious tuning.

But one thing that annoys me on Windows is that, after installation, the location of everything except the binaries -configuration, data, logs- is under the profile dir of the user (C:\Users\USERNAME\AppData\Roaming\RabbitMQ) that did the installation, even if the service runs as LocalSystem. Not very good, is it?

Therefore I’ve created this script to help me. The easiest way to use it is run it before you install RabbitMQ. Change the directories in this part and run it from an admin powershell:

# ========== Customize here ==========
$BaseLocation = "C:\mqroot\conf"
$DbLocation = "C:\mqroot\db"
$LogLocation = "C:\mqroot\log"
# ====================================

Then just reboot and run the installation normally; when it starts, RabbitMQ will use the directories you specified.

You can also do it after installation, if you have a running instance and want to move it. In this case do the following (you can find these steps also in the script):

  1. Stop the RabbitMQ service.
  2. From Task Manager, kill the epmd.exe process if present.
  3. Go to the existing base dir (usually C:\Users\USERNAME\AppData\Roaming\RabbitMQ)
    and move it somewhere else (say, C:\temp).
  4. Run this script (don’t forget to change the paths).
  5. Reboot the machine
  6. Run the “RabbitMQ Service (re)install” (from Start Menu).
  7. Copy the contents of the old log dir to $LogLocation.
  8. Copy the contents of the old db dir to $DbLocation.
  9. Copy the files on the root of the old base dir (e.g. advanced.config, enabled_plugins) to $BaseLocation.
  10. Start the RabbitMQ service.

Here’s the script. Have fun 🙂

#
# Source: DotJim blog (http://dandraka.com)
# Jim Andrakakis, March 2021
#

# What this script does is:
#   1. Creates the directories where the configuration, queue data and logs will be stored.
#   2. Downloads a sample configuration file (it's necessary to have one).
#   3. Sets the necessary environment variables.

# If you're doing this before installation: 
# Just run it, reboot and then install RabbitMQ.

# If you're doing this after installation, i.e. if you have a 
# running service and want to move its files:
#   1. Stop the RabbitMQ service
#   2. From Task Manager, kill the epmd.exe process if present
#   3. Go to the existing base dir (usually C:\Users\USERNAME\AppData\Roaming\RabbitMQ)
#      and move it somewhere else (say, C:\temp).
#   4. Run this script.
#   5. Reboot the machine
#   6. Run the "RabbitMQ Service (re)install" (from Start Menu)
#   7. Copy the contents of the old log dir to $LogLocation.
#   8. Copy the contents of the old db dir to $DbLocation.
#   9. Copy the files on the root of the old base dir (e.g. advanced.config, enabled_plugins) 
#      to $BaseLocation.
#   10. Start the RabbitMQ service.

# ========== Customize here ==========

$BaseLocation = "C:\mqroot\conf"
$DbLocation = "C:\mqroot\db"
$LogLocation = "C:\mqroot\log"

# ====================================

$exampleConfUrl = "https://raw.githubusercontent.com/rabbitmq/rabbitmq-server/master/deps/rabbit/docs/rabbitmq.conf.example"

Clear-Host
$ErrorActionPreference = "Stop"

$dirList = @($BaseLocation, $DbLocation, $LogLocation)
foreach($dir in $dirList) {
    if (-not (Test-Path -Path $dir)) {
        New-Item -ItemType Directory -Path $dir
    }
}

# If this fails (e.g. because there's a firewall) you have to download the file 
# from $exampleConfUrl manually and copy it to $BaseLocation\rabbitmq.conf
try {
    Invoke-WebRequest -Uri $exampleConfUrl -OutFile ([System.IO.Path]::Combine($BaseLocation, "rabbitmq.conf"))
}
catch {
    Write-Host "(!) Download of conf file failed. Please download the file manually and copy it to $BaseLocation\rabbitmq.conf"
    Write-Host "(!) Url: $exampleConfUrl"
}

&setx /M RABBITMQ_BASE $BaseLocation
&setx /M RABBITMQ_CONFIG_FILE "$BaseLocation\rabbitmq"
&setx /M RABBITMQ_MNESIA_BASE $DbLocation
&setx /M RABBITMQ_LOG_BASE $LogLocation

Write-Host "Finished. Now you can install RabbitMQ."

Powershell & Microsoft Dynamics CRM: get results and update records with paging

I’ve written before an example on how to use Powershell and FetchXml to get records from a Dynamics CRM instance. But there’s a limit, by default 5000 records, on how many records CRM returns in a single batch -and for good reason. There are many blog posts out there on how to increase the limit or even turn it off completely but this is missing the point: you really really really don’t want tens or hundreds of thousand -or, god forbid, millions- of records being returned in a single operation. That would probably fail for a number of reasons, not to mention it would slow the whole system to a crawl for a very long time!

So we really should do it the right way, which is to use paging. It’s not even hard! It’s basically almost the same thing, you just need to add a loop.

That’s the code I wrote to update all active records (the filter is in the FetchXml, so you can just create yours and the code doesn’t change). I added a progress indicator so that I get a sense of performance.

#
# Source: DotJim blog (http://dandraka.com)
# Jim Andrakakis, June 2020
#
# Prerequisites:
# 1. Install PS modules
#    Run the following in a powershell with admin permissions:
#       Install-Module -Name Microsoft.Xrm.Tooling.CrmConnector.PowerShell
#       Install-Module -Name Microsoft.Xrm.Data.PowerShell -AllowClobber
#
# 2. Write password file
#    Run the following and enter your user's password when prompted:
#      Read-Host -assecurestring | convertfrom-securestring | out-file C:\usr\crm\crmcred.pwd
#
# ============ Constants to change ============
$pwdFile = "C:\usr\crm\crmcred.pwd"
$username = "myuser@mycompany.com"
$serverurl = "https://myinstance.crm4.dynamics.com"
$fetchxml = "C:\usr\crm\all_active.xml"
# =============================================

Clear-Host
$ErrorActionPreference = "Stop"

# ============ Login to MS CRM ============
$password = get-content $pwdFile | convertto-securestring
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $username,$password
try
{
    $connection = Connect-CRMOnline -Credential $cred -ServerUrl $serverurl
}
catch
{
    Write-Host $_.Exception.Message 
    exit
}
if($connection.IsReady -ne $True)
{
    $errorDescr = $connection.LastCrmError
    Write-Host "Connection not established: $errorDescr"
    exit
}
else
{
    Write-Host "Connection to $($connection.ConnectedOrgFriendlyName) successful"
}

# ============ Fetch data ============
[string]$fetchXmlStr = Get-Content -Path $fetchxml

$list = New-Object System.Collections.ArrayList
# Be careful, NOT zero!
$pageNumber = 1
$pageCookie = ''
$nextPage = $true

$StartDate1=Get-Date

while($nextPage)
{    
    if ($pageNumber -eq 1) {
        $result = Get-CrmRecordsByFetch -conn $connection -Fetch $fetchXmlStr 
    }
    else {
        $result = Get-CrmRecordsByFetch -conn $connection -Fetch $fetchXmlStr -PageNumber $pageNumber -PageCookie $pageCookie
    }

    $EndDate1=Get-Date
    $ts1 = New-TimeSpan –Start $StartDate1 –End $EndDate1

    $list.AddRange($result.CrmRecords)

    Write-Host "Fetched $($list.Count) records in $($ts1.TotalSeconds) sec"    

    $pageNumber = $pageNumber + 1
    $pageCookie = $result.PagingCookie
    $nextPage = $result.NextPage
}


# ============ Update records ============
$StartDate2=Get-Date

$i = 0
foreach($rec in $list) {
    $crmId = $rec.accountid
    $entity = New-Object Microsoft.Xrm.Sdk.Entity("account")
    $entity.Id = [Guid]::Parse($crmId)
    $entity.Attributes["somestringfieldname"] = "somevalue"
    $entity.Attributes["somedatefieldname"] = [datetime]([DateTime]::Now.ToString("u"))
    $connection.Update($entity)
    $i = $i+1
    # this shows progress and time every 1000 records
    if (($i % 1000) -eq 0) {
        $EndDate2=Get-Date
        $ts2 = New-TimeSpan –Start $StartDate2 –End $EndDate2
        Write-Host "Updating $i / $($list.Count) in $($ts2.TotalSeconds) sec"
    }
}

$EndDate2=Get-Date
$ts2 = New-TimeSpan –Start $StartDate2 –End $EndDate2

Write-Host "Updated $($list.Count) records in $($ts2.TotalSeconds) sec"

For my purposes I used the following FetchXml. You can customize it or use CRM’s advanced filter to create yours:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="account">
    <attribute name="accountid" />
    <order attribute="accountid" descending="false" />
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
  </entity>
</fetch>

Something to keep in mind here is to minimize the amount of data being queried from CRM’s database and then downloaded. Since we’re talking about a lot of records, it’s wise to check your FetchXml and eliminate all fields that are not needed.

Enjoy!

Powershell: how to overcome MaxCharactersInDocument limit when reading XML

When working with XML files, which I usually process with Powershell scripts, sometimes I stumble on the following error message:

Select-Xml : The file 'C:\data\lalala.xml' cannot be read: The input document has exceeded a limit set by MaxCharactersInDocument.

The problem here is that Powershell has a hard-coded limit to stop you from reading huge files. This limit is 536870912 bytes (0.5 Gb).

So what can you do if you need to process such a large file? C# to the rescue! You can add inline C# code that uses XmlTextReader, which is very fast.

The specific problem I had to solve is to get all the IDs from several XML files and create SQL out of them. Then I used this SQL to create a temp table in my SQL server, like this:

SELECT t.* INTO #documentids FROM ( 
  SELECT 'e29e16f9-f77e-4dce-92e0-99fef71c868d' AS id UNION ALL
  SELECT '0cd0fef5-0037-492e-861f-593f7322c2a3' AS id UNION ALL
  SELECT 'e963a4da-2a60-43f1-b4d4-9b07b9d07d57' AS id UNION ALL
  -- <all the stuff created by powershell> 
  SELECT 'ca70d7b7-40c8-418d-8612-42fe2fd5bf28' AS id 
) t

But of course you can easily customize the code to meet your needs.

I tested with an 1.76 Gb XML on my dev laptop. It took 19.6 seconds. Not bad 🙂

Here’s the code I used:

#
# Source: DotJim blog (http://dandraka.com)
# Jim Andrakakis, January 2020
#
Clear-Host
$ErrorActionPreference='Stop'

$referencingassemblies = ("System.XML.dll")
Add-Type -ReferencedAssemblies $referencingassemblies -Language CSharp @"
using System;
using System.IO;
using System.Xml;
using System.Collections.Generic;
namespace DotJim.Powershell 
{
    public static class Xml
    {
        public static List<string> GetAttributes(string fileName, string localName, string template)
        {
            var list = new List<string>();
            XmlTextReader txtreaderObj = new XmlTextReader(fileName);
            txtreaderObj.WhitespaceHandling = WhitespaceHandling.None;
            while (txtreaderObj.Read())
            {
                if (txtreaderObj.LocalName.Equals(localName) &&
                txtreaderObj.IsStartElement())
                {
                     txtreaderObj.Read();
                     //Console.WriteLine(txtreaderObj.Value);
                     string s = txtreaderObj.Value;
                     string line = string.Format(template, s);
                     list.Add(line);
                }
            } 
            return list;
        }
    }
}
"@;

# Here I'm trying to create a big SELECT sql statement out of all the IDs in the xml
$template = "SELECT '{0}' AS id UNION ALL"
$fileList = @("C:\data\printdata1.xml", "C:\data\printdata1.xml")

$StartDate=(Get-Date)

foreach($fileName in $fileList)
{
    $list = [DotJim.Powershell.Xml]::GetAttributes($fileName, "DocumentId", $template)
    $list[$list.Count-1] = $list[$list.Count-1].Replace("UNION ALL", "")
    Out-File -FilePath $fileName.Replace(".xml", ".sql") -InputObject $list
}

$EndDate=(Get-Date)

$timeDiff = New-TimeSpan –Start $StartDate –End $EndDate
Write-Host "Finished processing $($fileList.Count) files in $($timeDiff.TotalSeconds) seconds"

Have fun coding!

Find and kill processes that lock a file or directory

It’s an old problem when doing installations: some directory or file are locked by some process, and the installation fails.

As I’ve had this exact problem in the context of a web application, I’ve written this powershell script to take care of it. The idea was taken from the Octopus Deploy Blog.

So what the script does is:
downloads the Microsoft Handle utility (in %temp%)
– uses it to scan the $PathToCheck (can be a file or directory; if it’s a dir, it scans subdirs and all files as well)
parses the output
– if needed, kills all processes mentioned

#
# Source: DotJim blog (http://dandraka.com)
# Jim Andrakakis, August 2019
#
param([string]$PathToCheck = "c:\temp")

Clear-Host
$ErrorActionPreference = "Stop"

$url = "https://download.sysinternals.com/files/Handle.zip"

# === download handle.exe from microsoft ===
$tempDir = [System.IO.Path]::GetTempPath()
$handlePath = [System.IO.Path]::Combine($tempDir, "handle64.exe")
if (-not (Test-Path $handlePath)) {    
    $output = [System.IO.Path]::Combine($tempDir, "handle.zip")
    Invoke-WebRequest -Uri $url -OutFile $output
    Expand-Archive -LiteralPath $output -OutputPath $tempDir
}

# === run handle.exe ===
# see https://octopus.com/blog/how-to-handle-locked-files to see why the reg entry is needed
& reg.exe ADD "HKCU\Software\Sysinternals\Handle" /v EulaAccepted /t REG_DWORD /d 1 /f | Out-Null
$handleOutput = & $handlePath -a $PathToCheck

# === do we have to kill anything? ===
if ($handleOutput -match "no matching handles found") {
    Write-Host "Nothing to kill, exiting"
    exit
}

# === get process ids from handle.exe output ===
$pidList = New-Object System.Collections.ArrayList
$lines = $handleOutput | Split-String -RemoveEmptyStrings -separator "`n" 
foreach($line in $lines) {
  # sample line: 
  # chrome.exe         pid: 11392  type: File           5BC: C:\Windows\Fonts\timesbd.ttf
  # regex to get pid and process name: (.*)\b(?:.*)(?:pid: )(\d*)
  $matches = $null
  $line -match "(.*)\b(?:.*)(?:pid: )(\d*)" | Out-Null
  if (-not $matches) { continue }
  if ($matches.Count -eq 0) { continue }
  $pidName = $matches[1]
  $pidStr = $matches[2]
  if ($pidList -notcontains $pidStr) { 
    Write-Host "Will kill process $pidStr $pidName"
    $pidList.Add($pidStr) | Out-Null
  }
}

# === DIE PROCESS DIE ===
foreach($pidStr in $pidList) {
    $pidInt = [int]::Parse($pidStr)    
    Stop-Process -Id $pidInt -Force
    Write-Host "Killed process $pidInt"
}

Write-Host "Finished"

Hope that helps!

Powershell – file system operations within a transaction

Anyone who’s ever developed anything connected to a database knows about transactions. Using a transaction we can group data operations so that they happen “all or nothing”, i.e. either all of them succeed or no one does. One example is a transfer from one bank account to another: the complete transaction requires subtracting the amount to be transferred from one account and adding that same amount to the other. We wouldn’t want one to happen without the other, would we?

(yes, I know that’s not a complete description of what transactions are or do; that’s not my purpose here)

But what happens when we need the same from our filesystem?

Let’s take this scenario (which is a simplified version of a true story): we are receiving CSV files from solar panels (via SFTP) and we want to do some preprocessing and then store the data to a database. When processing them we have to generate a lot of intermediate files. After that, we want to move them to a different dir. But if something happens, say if the database is down, we want the whole thing to be cancelled so that, when we retry, we can start over.

Obviously a simple solution would be as follows:

try {
  # do a lot of hard work
  # store the data in the db
  # clean up intermediate files
  # move the CSV file to an "archive" dir
}
catch {
  # clean up intermediate files, potentially clean up any db records etc
}

That’s… well it can work but it’s not great. For example, the cleanup process itself -inside the catch block- might fail.

A much better process would be like that:

try {   
  # start a transaction
    # do a lot of hard work   
    # store the data in the db   
    # clean up intermediate files   
    # move the CSV file to an "archive" dir 
  # commit the transaction
} 
catch {   
  # rollback everything: files, db changes, the whole thing
}

That’s much cleaner! But is it possible to group db changes and filesystem changes (file create, move, delete & append, dir create & delete etc) all in one transaction?

Unix geeks are allowed to feel smug here: some flavors like HP-UX (though not Linux as far as I know) have this baked in from the get go. Your code doesn’t have to do anything special; the OS takes care of this on user request.

But as a matter of fact yes, it is also available on Windows, and it has been for some time now. The requirement is that you’re working on a file system that supports this, like NTFS.

But there’s a problem for the average .NET/Powershell coder: the standard methods, the ones inside System.IO, do not support any of this. So you have to go on a lower level, using the Windows API. Which for .NET coders, there’s no other way to put this, sucks. That’s also the reason why the Powershell implementation of file transactions (e.g. New-Item -ItemType File -UseTransaction) doesn’t work -it relies on System.IO.

I’m pretty sure that this is what crossed the minds of the developers that wrote AlphaFS which is just wonderful. It’s exactly what you’d expect but never got from Microsoft: a .NET implementation of most of System.IO classes that support NTFS’s advanced features that are not available in, say, ye olde FAT32. Chief among them is support for file system transactions.

So the example below shows how to do exactly that. I tried to keep it as simple as possible to highlight the interesting bits, but of course a real world example would be much more complicated, e.g. there would be a combined file system and database transaction, which would commit (or rollback) everything at the same time.

Note that there’s no need for an explicit rollback. As soon as the transaction scope object is disposed without calling Complete(), all changes are rolled back.

#
# Source: DotJim blog (http://dandraka.com)
# Jim Andrakakis, July 2019
#
# Prerequisite: 
#   1) internet connectivity
#   2) nuget command line must be installed 
#      (see https://www.nuget.org/downloads).
# If nuget is not in %path%, you need to change 
#   the installation (see below) to call it with 
#   its full path.
 
# Stop on error
$ErrorActionPreference = "Stop"

if ($psISE)
{
    $binPath = Split-Path -Path $PSISE.CurrentFile.FullPath        
}
else
{
    $binPath = $PSScriptRoot
}
$alphaFSver = "2.2.6"
$libPath = "$binPath\AlphaFS.$alphaFSver\lib\net40\AlphaFS.dll"
$basePath = "$binPath\..\alphatest"

# ====== installation ======
if (-not [System.IO.File]::Exists($libPath)) {
    Out-File -FilePath "$binPath\packages.config" `
        -Force `
        -Encoding utf8 `
        -InputObject ("<?xml version=`"1.0`" encoding=`"utf-8`"?><packages>" + `
          "<package id=`"AlphaFS`" version=`"$alphaFSver`" targetFramework=`"net46`" />" + `
          "</packages>")
    cd $binPath
    & nuget.exe restore -PackagesDirectory "$binPath"
}
# ==========================
 
# Make sure the path matches the version from step 2
Import-Module -Name $libPath
 
if (-not (Test-Path $basePath)) {
    New-Item -ItemType Directory -Path $basePath
}
 
# Check if the filesystem we're writing to supports transactions.
# On a FAT32 drive you're out of luck.
$driveRoot = [System.IO.Path]::GetPathRoot($basePath)
$driveInfo = [Alphaleonis.Win32.Filesystem.DriveInfo]($driveRoot)
if (-not $driveInfo.VolumeInfo.SupportsTransactions) {
    Write-Error ("Your $driveRoot volume $($driveInfo.DosDeviceName) " + `
      "[$($driveInfo.VolumeLabel)] does not support transactions, exiting")
}
 
# That's some example data to play with.
# In reality you'll probably get data from a DB, a REST service etc.
$list = @{1="Jim"; 2="Stef"; 3="Elena"; 4="Eva"}
 
try {
    # Transaction starts here
    $transactionScope = [System.Transactions.TransactionScope]::new([System.Transactions.TransactionScopeOption]::RequiresNew)
    $fileTransaction = [Alphaleonis.Win32.Filesystem.KernelTransaction]::new([System.Transactions.Transaction]::Current)
 
    # Here we're doing random stuff with our files and dirs, 
    #   just to show how this works.
    # The important thing to remember is that for the transaction 
    #   to work correctly, ALL methods you use have to be -transacted.
    # I.e. you must not use AppendText() but AppendTextTransacted(), 
    #   not CreateDirectory() but CreateDirectoryTransacted() etc etc.
    $logfileStream = [Alphaleonis.Win32.Filesystem.File]::AppendTextTransacted($fileTransaction, "$basePath\list.txt")
    foreach($key in $list.Keys) {
        $value = $list.$key
        $filename = "$([guid]::NewGuid()).txt"
        $dir = "$basePath\$key"
 
        Write-Host "Processing item $key $value"
 
        if (-not [Alphaleonis.Win32.Filesystem.Directory]::ExistsTransacted($fileTransaction, $dir)) {
            [Alphaleonis.Win32.Filesystem.Directory]::CreateDirectoryTransacted($fileTransaction, $dir)
        }
        [Alphaleonis.Win32.Filesystem.File]::WriteAllTextTransacted($fileTransaction, "$basePath\$key\$filename", $value)        
        $logfileStream.WriteLine("$filename;$key;$value")
    }
    $logfileStream.Close()
     
    # to simulate an error and subsequent rollback:
    # Write-Error "Something not great, not terrible happened"
     
    # Commit transaction
    $transactionScope.Complete()
    Write-Host "Transaction committed, all modifications written to disk"
}
catch {
    Write-Host "An error occured and the transaction was rolled back: '$($_.Exception.Message)'"
    throw $_.Exception
}
finally {
    if ($null -ne $logfileStream -and $logfileStream -is [System.IDisposable]) {
        $logfileStream.Dispose()
        $logfileStream = $null
    }    
    if ($null -ne $transactionScope -and $transactionScope -is [System.IDisposable]) {
        $transactionScope.Dispose()
        $transactionScope = $null
    }    
}

Have fun coding!

Bulk modify jobs in JAMS Scheduler

As I’ve mentioned before, at work we’re migrating all our scheduled tasks to JAMS. Now JAMS has a lot of flexibility to notify, sends emails etc but… you have to tell it to 🙂

And you can imagine that having to click-click-type-click in order to change, say, the email address in a few tens of jobs is not the creative work a developer craves for. Writing a powershell script to do that, though, is!

So here’s the script I wrote to change the email address for Warnings and Critical conditions, in bulk. Of course you can easily modify it to do whatever change you want (enable/disable a lot of jobs at once is a good example).

param(
    [string]$jamsServer = "myJamsServer", 
    [string]$jamsPath = "\somePath\someOtherPath"
)

# This script loops through all enabled JAMS jobs under a certain folder
# recursively, and changes the email address except for successes.

Import-Module Jams
$ErrorActionPreference = "Stop"
cls

try
{
    if ($null -eq (Get-PSDrive JD))
    {
        New-PSDrive JD JAMS $jamsServer -scope Local
    }
}
catch
{
    New-PSDrive JD JAMS $jamsServer -scope Local
}

$folders = New-Object System.Collections.ArrayList
$rootFolder = (Get-Item "JAMS::$($jamsServer)$($jamsPath)").Name
$folders.Add($rootFolder) | Out-Null
$childFolders = Get-ChildItem "JAMS::$($jamsServer)$($jamsPath)\*" -objecttype Folder -IgnorePredefined 
$childFolders | foreach { $folders.Add($_.Name) | Out-Null }

$rootJobs = New-Object System.Collections.ArrayList

foreach($f in $folders)
{
    Write-Host "Folder: $f"
    if ($f -eq $rootFolder)
    {
        $jobs = Get-ChildItem "JAMS::$($jamsServer)$($jamsPath)\*" -objecttype Job -IgnorePredefined -FullObject 
        $jobs | foreach { $rootJobs.Add($_.Name) | Out-Null }
    }
    else
    {
        $jobs = Get-ChildItem "JAMS::$($jamsServer)$($jamsPath)\$f\*" -objecttype Job -IgnorePredefined -FullObject 
    }

    # for test
    #$jobs | Format-Table -AutoSize

    foreach($job in $jobs)
    {
        #Write-Host "$($job.Name) : $($job.Properties["Enabled"])"
        #if you need a name filter as well, you can do:
        #if (($job.Name -notlike "*SomeString*") -or ($job.Properties["Enabled"].Value -eq $false))
        if ($job.Properties["Enabled"].Value -eq $false)
        {
            continue
        }

        $jobElements = $job.Elements
        $doUpdate = $false

        foreach($jobElement in $jobElements)
        {
            #Write-Host "$($job.Name) / $($jobElement.ElementTypeName) / $($jobElement.Description) / $($jobElement.ToString())"
            if (($jobElement.ElementTypeName -eq "SendEMail") -and ($jobElement.EntrySuccess -eq $false))
            {
                #Write-Host "$($job.Name) / $($jobElement.ElementTypeName) / $($jobElement.Description) / $($jobElement.FromAddress) / $($jobElement.ToAddress)"
                if ([string]::IsNullOrWhiteSpace($jobElement.ToAddress))
                {
                    $jobElement.FromAddress = "admin@superduperincrediblesoftware.com"
                    $jobElement.ToAddress = "someone@superduperincrediblesoftware.com;andhisdog@superduperincrediblesoftware.com"
                    $jobElement.MessageBody = "Uh, Houston, we've had a problem"      
                    $doUpdate = $true              
                }
            }
        }

        if ($doUpdate -eq $true)
        {
            $job.Update()
            Write-Host "Job $($job.Name) is updated"
        }
    }    
}

Have fun coding 🙂

Running Groovy scripts in JAMS Scheduler

Here at work, we’re working on a migration project, from Jenkins (which we’ve been using as a scheduler) to JAMS Scheduler. In Jenkins we have a lot of Groovy scripts, and we have them in source control. So, to make the migration as effortless as possible, we wanted to use them “as-is”, right out of source control.

The solution I found was:

  1. On the JAMS agent, install the subversion command line client
  2. Also on the JAMS agent, install groovy
  3. Create a job that gets (“checks out”) the latest scripts every evening from source control in a specific directory; let’s call it c:\jobs
  4. Create a JAMS Execution Method called Groovy (see below)
  5. Create the Jenkins jobs in JAMS, one by one. In the source box, only write the full path of the groovy script, e.g. c:\jobs\TransferOrders.groovy

#4 is where the magic happens. The execution method is defined as a Powershell method. In the template, there’s code that (suprise) calls groovy. The powershell code is the following (see if you can spot a couple of tricks):

#
# Source: DotJim blog (https://dandraka.com)
# Jim Andrakakis, December 2018
#
Import-Module JAMS

# the job's source is supposed to contain ONLY 
# the full path to the groovy script, without quotes
$groovy = "C:\app\groovy-2.5.4\bin\groovy.bat"
$groovyScript="<<JAMS.Current.Source>>"

Write-Host "[JAMS-GROOVY] Running script $groovyScript via $groovy"
if ((Test-Path -Path $groovy) -ne $true)
{
	Write-Error "[JAMS-GROOVY] Groovy executable $groovy not found, is Groovy installed?"
}
if ((Test-Path -Path $groovyScript) -ne $true)
{
	Write-Error "[JAMS-GROOVY] Source file $groovyScript not found"
}

$currentJob = Get-JAMSEntry {JAMS.JAMSEntry} 
$currentJobParams = $currentJob.Parameters
$currentJobParamNames = $currentJobParams.Keys

foreach($n in $currentJobParamNames)
{
	[string]$v = $currentJobParams[$n].Value
	
	# look for replacement tokens
	# in the form of <<ParamName>>
	foreach($r in $currentJobParamNames)
	{
		if ($v.Contains("<<$r>>"))
        {
            [string]$replVal = $currentJobParams[$r].Value
            $v = $v.Replace("<<$r>>", $replVal)
        }
	}
	
	Write-Host "[JAMS-GROOVY] Setting parameter $n = $v"
	[Environment]::SetEnvironmentVariable($n, $v, "Process")
}

# execute the script in groovy
& $groovy $groovyScript

Write-Host "[JAMS-GROOVY] script finished"

Two tricks to note here:

  • Almost all our groovy scripts have parameters; Jenkins inserts the parameters as environment variables so the scripts can do:
myVar = System.getenv()['myVar']

The first powershell loop does exactly that; it maps all the job’s parameters, defined or inherited, as environment variables, so the scripts can continue to work happily, no change needed.

  • The second trick is actually an enhancement. As the scripts get promoted though our environments (development > test > integration test > production) some parts of the parameters change –but not all of them.

For example, let’s say there’s a parameter for an inputDirectory.
In the development server, it has the value c:\documents\dev\input. In test, it’s c:\documents\test\input, in integration test it’s c:\documents\intg\input and in production c:\documents\prod\input.

What we can do now is have a folder-level parameter, defined on the JAMS folder where our job definitions are –which is not transferred from
environment to environment. And we can have job-defined parameters that, using the familiar JAMS <<param>> notation, get their values substituted.

So, for example, let’s say I define a folder parameter named “SERVERLEVEL”, which will have the value of “dev” in development, “test” in test etc. In the job, I define another parameter called inputDirectory. This will have the value c:\documents\<<SERVERLEVEL>>\input.

Et voilà! Now we can promote the jobs from environment to environment, completely unchanged. In Jenkins we couldn’t do that; we had to define different values for parameters in dev, in test etc.

Here’s the export xml of the execution method:

<?xml version="1.0" encoding="utf-8"?>
<JAMSObjects>
  <method
    name="Groovy"
    type="Routine">
    <description><![CDATA[Run a pre-fetched groovy script. The job's source should contain the full path to the groovy script.

Note: in the "Bad regex pattern", the execution methon looks for "Caught:" to try to undertand whether 
groovy encountered an exception or not. Here's an example of the groovy output of a script where
an unhandled exception occured:

Hello, world!
Caught: java.lang.NullPointerException: Cannot invoke method test() on null object
java.lang.NullPointerException: Cannot invoke method test() on null object
        at test1.run(test1.groovy:4)]]></description>
    <template><![CDATA[Import-Module JAMS

# the job's source is supposed to contain ONLY 
# the full path to the groovy script, without quotes
$groovy = "C:\app\groovy-2.5.4\bin\groovy.bat"
$groovyScript="<<JAMS.Current.Source>>"

Write-Host "[JAMS-GROOVY] Running script $groovyScript via $groovy"
if ((Test-Path -Path $groovy) -ne $true)
{
	Write-Error "[JAMS-GROOVY] Groovy executable $groovy not found, is Groovy installed?"
}
if ((Test-Path -Path $groovyScript) -ne $true)
{
	Write-Error "[JAMS-GROOVY] Source file $groovyScript not found"
}

$currentJob = Get-JAMSEntry {JAMS.JAMSEntry} 
$currentJobParams = $currentJob.Parameters
$currentJobParamNames = $currentJobParams.Keys

foreach($n in $currentJobParamNames)
{
	[string]$v = $currentJobParams[$n].Value
	
	# look for replacement tokens
	# in the form of <<ParamName>>
	foreach($r in $currentJobParamNames)
	{
		if ($v.Contains("<<$r>>"))
        {
            [string]$replVal = $currentJobParams[$r].Value
            $v = $v.Replace("<<$r>>", $replVal)
        }
	}
	
	Write-Host "[JAMS-GROOVY] Setting parameter $n = $v"
	[Environment]::SetEnvironmentVariable($n, $v, "Process")
}

# execute the script in groovy
& $groovy $groovyScript

Write-Host "[JAMS-GROOVY] script finished"]]></template>
    <properties>
      <property
        name="HostAssemblyName"
        typename="System.String"
        value="JAMSPSHost" />
      <property
        name="HostClassName"
        typename="System.String"
        value="MVPSI.JAMS.Host.PowerShell.JAMSPSHost" />
      <property
        name="StartAssemblyName"
        typename="System.String"
        value="" />
      <property
        name="StartClassName"
        typename="System.String"
        value="" />
      <property
        name="EditAssemblyName"
        typename="System.String"
        value="" />
      <property
        name="EditClassName"
        typename="System.String"
        value="" />
      <property
        name="ViewAssemblyName"
        typename="System.String"
        value="" />
      <property
        name="ViewClassName"
        typename="System.String"
        value="" />
      <property
        name="BadPattern"
        typename="System.String"
        value="^Caught\:" />
      <property
        name="ExitCodeHandling"
        typename="MVPSI.JAMS.ExitCodeHandling"
        value="ZeroIsGood" />
      <property
        name="GoodPattern"
        typename="System.String"
        value="" />
      <property
        name="SpecificInformational"
        typename="System.String"
        value="" />
      <property
        name="SpecificValues"
        typename="System.String"
        value="" />
      <property
        name="SpecificWarning"
        typename="System.String"
        value="" />
      <property
        name="Force32Bit"
        typename="System.Boolean"
        value="false" />
      <property
        name="ForceV2"
        typename="System.Boolean"
        value="false" />
      <property
        name="HostLocally"
        typename="System.Boolean"
        value="false" />
      <property
        name="Interactive"
        typename="System.Boolean"
        value="false" />
      <property
        name="NoBOM"
        typename="System.Boolean"
        value="false" />
      <property
        name="SourceFormat"
        typename="MVPSI.JAMS.SourceFormat"
        value="Text" />
      <property
        name="EditAfterStart"
        typename="System.Boolean"
        value="false" />
      <property
        name="EditSource"
        typename="System.Boolean"
        value="false" />
      <property
        name="Extension"
        typename="System.String"
        value="ps1" />
      <property
        name="JobModule"
        typename="System.String"
        value="" />
      <property
        name="SnapshotSource"
        typename="System.Boolean"
        value="false" />
      <property
        name="Redirect"
        typename="MVPSI.JAMS.Redirect"
        value="All" />
      <property
        name="HostSubDirectory"
        typename="System.String"
        value="" />
      <property
        name="HostExecutable"
        typename="System.String"
        value="JAMSHost.exe" />
    </properties>
  </method>
</JAMSObjects>

Powershell: How do you add inline C#?

Powershell is great for admin tasks. Stuff like iterating through files and folders, copying and transforming files are very, very easily done. But inevitably there will always be stuff that are easier to do via a “normal” language such as C#.

Trying to solve a problem I had at work, I needed to transform a CSV file by changing the fields -which is easily done via powershell- and, at the same time, do a “get only the highest record of every group”. This is done with LINQ, which you can use in powershell but it’s cumbersome and will result in many, many lines of code.

So I wanted to do this in a more clean way, in C#. The general template to include C# inside a powershell script is the following:

#
# Source: DotJim blog (http://dandraka.com)
# Jim Andrakakis, November 2018
#
# Here goes the C# code:
Add-Type -Language CSharp @"
using System; 
namespace DotJim.Powershell 
{
    public static class Magician 
    {
        private static string spell = ""; 
        public static void DoMagic(string magicSpell) 
        {
            spell = magicSpell; 
        }
        public static string GetMagicSpells() 
        {
            return "Wingardium Leviosa\r\n" + spell; 
        }
    }
}
"@;

# And here's how to call it:
[DotJim.Powershell.Magician]::DoMagic("Expelliarmus")
$spell = [DotJim.Powershell.Magician]::GetMagicSpells()

Write-Host $spell

Note here that the C# classes don’t have to be static; but if they are, they’re easier to call (no instantiation needed). Of course this only works if all you need to do is provide an input and get a manipulated output. If you need more complex stuff then yes, you can use non-static classes or whatever C# functionality solves your problems. Here’s the previous example, but with a non-static class:

#
# Source: DotJim blog (https://dandraka.com)
# Jim Andrakakis, November 2018
#
# Here goes the C# code:
Add-Type -Language CSharp @"
using System; 
namespace DotJim.Powershell 
{
    public class Magician 
    {
        private string spell = ""; 
        public void DoMagic(string magicSpell) 
        {
            spell = magicSpell; 
        }
        public string GetMagicSpells() 
        {
            return "Wingardium Leviosa\r\n" + spell; 
        }
    }
}
"@;

# Here's how to create an instance:
$houdini = New-Object -TypeName DotJim.Powershell.Magician
# And here's how to call it:
$houdini.DoMagic("Expelliarmus")
$spell = $houdini.GetMagicSpells()

Write-Host $spell

UPDATE: What if you need to use some assembly in the C# code, either a system one or a dll that your have anywhere on disk?
In this case, you add -ReferencedAssemblies to the Add-Type line, like this:
Add-Type -Language CSharp -ReferencedAssemblies @('System.Xml', 'System.Runtime.Serialization', "C:\app\ServiceBusExplorer\Microsoft.ServiceBus.dll") @"
using System;
using System.Runtime.Serialization;
using System.Xml;
using Microsoft.ServiceBus;
using Microsoft.ServiceBus.Messaging;
namespace DotJim.Powershell
{
// etc etc
}
"@;

The main advantage of having C# inside the powershell script (and not in a separate dll file) is that it can be deployed very easily with various Devops tools. Otherwise you need to deploy the dll alongside which can, sometimes, be the source of trouble.

So here’s my complete working code, which worked quite nicely:

#
# Source: DotJim blog (http://dandraka.com)
# Jim Andrakakis, November 2018
#
# The purpose of this script is to read a CSV file with bank data
# and transform it into a different CSV.
#
# 1. The Bank class is a POCO to hold the data which I need
#    from every line of the CSV file.
# 2. The Add() method of the BankAggregator class adds the
#    record to the list after checking the data for correctness.
# 3. The Get() methof of the BankAggregator class does a
#    LINQ query to get the 1st (max BankNr) bank record
#    from every record with the same Country/BIC.
#    It then returns a list of strings, formatted the way
#    I want for the new (transformed) CSV file.
#
# Here is where I inline the C# code:
Add-Type -Language CSharp @"
using System;
using System.Collections.Generic;
using System.Linq;
namespace DotJim.Powershell {
 public class Bank {
  public int BankNr;
  public string Country;
  public string BIC;
 }
 public static class BankAggregator {
  private static List list = new List();
  public static void Add(string country, string bic, string bankNr) {
   //For debugging
   //Console.WriteLine(string.Format("{0}{3}{1}{3}{3}{2}", country, bic, bankNr, ";"));
   int mBankNr;
   // Check data for correctness, discard if not ok
   if (string.IsNullOrWhiteSpace(country) ||
    country.Length != 2 ||
    string.IsNullOrWhiteSpace(bic) ||
    string.IsNullOrWhiteSpace(bankNr) ||
    !int.TryParse(bankNr, out mBankNr) ||
    mBankNr & gt; = 0) {
    return;
   }
   list.Add(new Bank() {
    BankNr = mBankNr, Country = country, BIC = bic
   });
  }
  public static List Get(string delimiter) {
   // For every record with the same Country & BIC, keep only
   // the record with the highest BankNr
   var bankList = from b in list
   group b by new {
    b.Country, b.BIC
   }
   into bankGrp
   let maxBankNr = bankGrp.Max(x = & gt; x.BankNr)
   select new Bank {
    Country = bankGrp.Key.Country,
     BIC = bankGrp.Key.BIC,
     BankNr = maxBankNr
   };
   // Format the list the way I want the new CSV file to look
   return bankList.Select(x = & amp; amp; amp; amp; amp; amp; amp; amp; amp; amp; amp; amp; amp; gt; string.Format("{0}{3}{1}{3}{3}{2}",
    x.Country, x.BIC, x.BankNr, delimiter)).ToList();
  }
 }
}
"@;

# Read one or more files with bank data from the same dir
# where the script is located ($PSScriptRoot)
$srcSearchStr = "source_bankdata*.csv"
$SourcePath = $PSScriptRoot
$destPath = $SourcePath

$fields = @("Country","BIC","EmptyField","BankId")

$filesList = Get-ChildItem -Path $SourcePath -Filter $srcSearchStr

foreach ($file in $filesList)
{
Write-Host "Processing" $file.FullName

# Fields in the source CSV:
# BANKNUMMER  = BankNr
# BANKLAND    = Country
# BANKSWIFT   = BIC
$data = Import-Csv -Path $file.FullName -Delimiter ";"

foreach ($item in $data)
{
# Call the C# code to add the CSV lines to the list
[DotJim.Powershell.BankAggregator]::Add($item.BANKLAND,$item.BANKSWIFT,$item.BANKNUMMER)
}

# Call the C# code to get the transformed data
$list = [DotJim.Powershell.BankAggregator]::Get(";")

Write-Host "Found" $list.Count "valid rows"

# Now that we have the list, write it in the new CSV
Out-File -FilePath "$destPath\transformed_bankdata_$(New-Guid).csv" -Encoding UTF8 -InputObject $list
}

Have fun coding!