SQL Server: How to allow access to a Windows service without a password

Let us start by stating the obvious: password management for programs and services is a huge pain for developers.

It’s one of the things that is always overlooked during development, where you’re just trying to make the thing work. It’s even not given much attention during testing, where people are usually focused on whether it works correctly on normal uses and edge cases, they look for the UI and usability etc etc.

But come deployment time and the admins start complaining. Storing passwords in plain text files is, how to put it mildly, BLOODY HORRIBLE from a security perspective. And storing them in better ways takes a surprising amount of time –just when the devs thought they’re almost finished.

So having less passwords to store and secure is very helpful for everyone. And one thing many applications need is the credentials to a database.

Fortunately, if your application is running as a Windows service and your database is SQL server, you don’t need a password. You can use integrated security. All you need is to allow (grant) access for the service user to read data from SQL server.

Now here’s the thing: if you’re using a domain user to run the server, that’s obvious. You just create the user in SQL and grant access as needed (you can even use the script below and change the user). But what happens when, as is very common, the application is running under the Local System account?

Turns out, fortunately, there’s a solution for that as well. Every computer’s Local System account exists in Active Directory as “hostname$”. E.g. if the hostname of the application server is MYSERVER, the user name will be MYDOMAIN\MYSERVER$.

So you can run the following SQL to grant access:

/*
Source: DotJim blog (http://dandraka.com)
Jim Andrakakis, April 2023
*/

-- suppose you work on domain MYDOMAIN
-- and the server that hosts the Windows
-- service is MYSERVER
-- this is the name given by the hostname command

USE mydatabase;
GO

CREATE LOGIN [MYDOMAIN\MYSERVER$] FROM WINDOWS;
GO
CREATE USER [MYDOMAIN\MYSERVER$] FOR LOGIN [MYDOMAIN\MYSERVER$]
GO
/* db_datareader grants read-only access */
ALTER ROLE [db_datareader] ADD MEMBER [MYDOMAIN\MYSERVER$]
GO
/* if you want to insert, update or delete, add db_datawriter */
ALTER ROLE [db_datawriter] ADD MEMBER [MYDOMAIN\MYSERVER$]
GO

That done, you can use the following connection string to connect to the database:

Server=MYDBSERVER;Database=myDataBase;Trusted_Connection=yes;

or if you’re running a named instance:

Server=MYDBSERVER\MYINSTANCE;Database=myDataBase;Trusted_Connection=yes;

RabbitMQ: list queues and exchanges with Powershell

As I haven’t yet had the time to set up a proper devops deployment pipeline from my development RabbitMQ to UAT and then to production (don’t yell at me, I know, I’ll get to it… eventually), I find myself comparing instances in order not to forget adding a queue or an exchange.

So I wrote this script, that produces a diff-friendly text file that I can use to compare instances and see what’s missing:

#
# Source: DotJim blog (https://dandraka.com)
# Jim Andrakakis, April 2023
#
 
# ===== Parameters =====
 
param(
    [string]$rqServer = 'http://myServer:15672', # better use HTTPS though
    [string]$rqVhostName = 'myVhost',
    [string]$rqUsername = 'myUsername', # this user needs at least 'Management' permissions to post to the REST API
    [string]$rqPassword = 'myPassword',
    [string]$outDir = 'C:\temp'
)
 
# ======================
 
Clear-Host
$ErrorActionPreference = 'Stop'
$WarningPreference = 'Continue'

$plainCredentials = "$($rqUsername):$($rqPassword)"
$encodedCredentials = [Convert]::ToBase64String([System.Text.Encoding]::UTF8.GetBytes($plainCredentials))
$authHeader = "Basic " + $encodedCredentials

[string]$rqUrlQueues = "$rqServer/api/queues/$rqVhostName/"
[string]$rqUrlExchanges = "$rqServer/api/exchanges/$rqVhostName/"

[string]$filename = [System.IO.Path]::Combine($outDir, [guid]::NewGuid().ToString().Split('-')[0] + ".txt")
Out-File -FilePath $filename -Encoding utf8 -Append -InputObject "Server : $rqServer"
Out-File -FilePath $filename -Encoding utf8 -Append -InputObject "VHost : $rqVhostName"
 
$respQueues = Invoke-WebRequest -Method Get -Uri $rqUrlQueues -Headers @{'Authorization'= $authHeader} 
$respExchanges = Invoke-WebRequest -Method Get -Uri $rqUrlExchanges -Headers @{'Authorization'= $authHeader} 

$queuesJson = ConvertFrom-Json $respQueues.Content
$queuesJson | Sort-Object -Property name | % { Out-File -FilePath $filename -Encoding utf8 -Append -InputObject  "Queue : $($_.name)" }

$exchangesJson = ConvertFrom-Json $respExchanges.Content
$exchangesJson | Sort-Object -Property name | % { Out-File -FilePath $filename -Encoding utf8 -Append -InputObject  "Exchange : $($_.name)" }

Write-Host "Finished, output written to $filename"

Jams Scheduler: Get info about the currently running job in Powershell

When writing jobs in JAMS Scheduler it’s very common to need info about the job that’s currently running. Logging is an obvious need but it’s not the only one.

You’d think the way to do this should be prominently displayed in their otherwise very good documentation, but for whatever reason it’s not. I couldn’t find this anywhere.

Fortunately their support is beyond excellent -it’s hands down the best support I’ve ever worked with- and they gave me the answer as soon as I asked:

Import-Module JAMS
$currentJobEntry = Get-JAMSEntry <<JAMS.JAMSEntry>>

That’s it! It’s really as simple as that. That’s a sample that you can use to see what properties you get:

Import-Module JAMS
$currentJobEntry = Get-JAMSEntry <<JAMS.JAMSEntry>>

# to print all info
$currentJobEntry | select *

# or to get individual info
$jobId = $currentJobEntry.JAMSEntry
$jobName = $currentJobEntry.Name
$jobError = $currentJobEntry.Error

Write-Host "Job $jobId [$jobName]"
Write-Host "Error (if any): $jobError"

# === all properties with example values ===
#Entry               : 21760
#ExtensionData       : System.Runtime.Serialization.ExtensionDataObject
#JAMSEntry           : 21760
#RON                 : 3565757
#AvgElapsedTime      : 
#TodaysDate          : 2/17/2023 12:00:00 AM
#ScheduledTime       : 2/17/2023 10:35:40 AM
#ScheduledTimeUTC    : 2/17/2023 9:35:40 AM
#OriginalHoldTime    : 2/17/2023 10:35:39 AM
#OriginalHoldTimeUTC : 2/17/2023 9:35:39 AM
#HoldTime            : 2/17/2023 10:35:39 AM
#HoldTimeUTC         : 2/17/2023 9:35:39 AM
#StartTime           : 2/17/2023 10:35:40 AM
#StartTimeUTC        : 2/17/2023 9:35:40 AM
#CompletionTime      : 1/1/0001 12:00:00 AM
#CompletionTimeUTC   : 1/1/0001 12:00:00 AM
#MethodId            : 16
#MethodName          : PowerShell
#ParentFolderID      : 96
#ParentFolderName    : \el\MyFolder\Test
#JobID               : 1747
#ExecutingAgentID    : 1
#ExecutingAgentName  : Agent JAMS App-Server
#InitiatorType       : ManualSubmit
#InitiatorID         : 0
#InitiatorUid        : 00000000-0000-0000-0000-000000000000
#ProcessID           : 5408
#SchedulingPriority  : 0
#ExecutionPriority   : 0
#FinalStatusCode     : 0
#FinalSeverity       : Success
#RetainOption        : Default
#RetainTime          : 
#RestartCount        : 0
#CurrentState        : Executing
#Tags                : 
#Debug               : False
#Held                : False
#Icon                : Default
#IconPermanent       : False
#IconMessage         : 
#LogFilename         : D:\JAMS\Logs\Logs\test-jobinfo_003668BD.log
#TempFilename        : D:\JAMS\Temp\test-jobinfo_003668BD.ps1
#SubmittedBy         : MYDOMAIN\myuser
#Name                : test-jobinfo
#JobName             : test-jobinfo
#DisplayName         : test-jobinfo
#FinalStatus         : 
#Note                : 
#JobStatus           : 
#ReconnectAgentName  : MYSERVER
#Source              : 
#JAMSId              : 939fb4c8-ce31-4e1a-8704-10258e85c003
#WFNextTimer         : 1/1/0001 12:00:00 AM
#WFState             : 0
#WFInstance          : 00000000-0000-0000-0000-000000000000
#AuditTrail          : {}
#WFTracking          : {}
#Parameters          : {[JAMSTraceLevel, MVPSI.JAMS.EntryParam], [PSExecutionPolicyPreference, MVPSI.JAMS.EntryParam], 
#                      [ErrorActionPreference, MVPSI.JAMS.EntryParam]}
#Elements            : {MaintenanceWindow, SendEMail, SendEMail}
#SourceElements      : {}
#Properties          : {ExecuteAs: JAMS, HomeDirectory: C:\JamsWorkingFolder, SingleInstanceAction: AllowMultiple, 
#                      NotifyEMail: ...}
#ExecuteAsName       : JAMS
#ExecuteAsID         : 1
#LoadedFrom          : Server: MYSERVER.mycompany.local/Default
#BatchQueue          : Queue: 
#BatchQueueName      : 
#BatchQueueID        : 0
#Calendar            : Calendar: 
#CalendarName        : 
#CalendarID          : 0
#Agent               : Agent: Agent JAMS App-Server
#AgentID             : 1
#AgentName           : Agent JAMS App-Server
#MinimumSeverity     : Warning
#Job                 : Job: test-jobinfo
#LogFile             : System.ServiceModel.Dispatcher.StreamFormatter+MessageBodyStream
#Modified            : False
#NewObject           : False
#Validated           : False
#InEdit              : False
#HasErrors           : False
#Error               : 

SQL Server: How to backup all databases with a timestamp

I hate, hate, hate clicking and clicking again, especially for tasks that can be automated. And one of these tasks is doing DB backups, which I have to do every time before starting a deployment.

So here’s what I’ve come up with. This generates a backup for every database except the system ones (see line 20) as databasename_yyyymmdd.bak, e.g. ERP-UAT-DB_20230321.bak.

/*
Source: DotJim blog (http://dandraka.com)
Jim Andrakakis, March 2023
*/

DECLARE @name NVARCHAR(256) -- database name  
DECLARE @path NVARCHAR(512) -- path for backup files  
DECLARE @fileName NVARCHAR(512) -- filename for backup  
DECLARE @fileDate NVARCHAR(40) -- used for file name

/* specify database backup directory */
SET @path = 'D:\myBackups\'  
 
/* get date as yyyyMMdd */
SELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) 
 
DECLARE db_cursor CURSOR READ_ONLY FOR  
SELECT name 
FROM master.sys.databases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   
 
WHILE @@FETCH_STATUS = 0   
BEGIN   
   SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
   BACKUP DATABASE @name TO DISK = @fileName  
   FETCH NEXT FROM db_cursor INTO @name   
END   
 
CLOSE db_cursor   
DEALLOCATE db_cursor

Change the path, obviously, and you can also change the WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’). E.g. you can do something like WHERE name LIKE ‘%_PRODUCTION’ to suit your scenario.

RabbitMQ: How to publish (upload) a file to a queue via Powershell using REST

As part of my job, this is something I use a lot. And the thing is, it’s quite easy, it’s just an Invoke-WebRequest. Here’s how I do it:

#
# Source: DotJim blog (https://dandraka.com)
# Jim Andrakakis, January 2023
#

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

param(
    [string]$fileName = 'C:\temp\uploadinfo.json',
    [string]$rqServer = 'http://myServer:15672', # better use HTTPS though
    [string]$rqVhostName = 'myVhost',
    [string]$rqQueueName = 'myQueue',
    [string]$rqExchangeName = 'amq.default', # or your exchange name
    [string]$rqUsername = 'myUser', # this user needs at least 'Management' permissions to post to the REST API
    [string]$rqPassword = 'myPass',
	# RabbitMQ has a recommended message size limit of 128 MB
    # See https://www.cloudamqp.com/blog/what-is-the-message-size-limit-in-rabbitmq.html
    # But of course depending on your app you might want to set it lower
	[int]$rqMessageLimitMB = 128		
)

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

Clear-Host
$ErrorActionPreference = 'Stop'
$WarningPreference = 'Continue'

[string]$rqUrl = "$rqServer/api/exchanges/$rqVhostName/$rqExchangeName/publish"

# Sanity check
if (-not (Test-Path $fileName)) {
    Write-Error "File $fileName was not found"
}

# Check RabbitMQ size limit
[int]$rqMessageLimit = $rqMessageLimitMB * 1024 * 1024 
[long]$fileSize = (Get-Item -Path $fileName).Length
if ($fileSize -gt $rqMessageLimit) {
    Write-Error "File $fileName is bigger that the maximum size allowed by RabbitMQ ($rqMessageLimitMB MB)"
}

$plainCredentials = "$($rqUsername):$($rqPassword)"
$encodedCredentials = [Convert]::ToBase64String([System.Text.Encoding]::UTF8.GetBytes($plainCredentials))
$authHeader = "Basic " + $encodedCredentials

[string]$content = Get-Content -Path $fileName -Encoding UTF8
$msgBase64 = [Convert]::ToBase64String([System.Text.Encoding]::UTF8.GetBytes($content))
$json = "{`"properties`":{`"content_type`":`"application/json`",`"delivery_mode`":2},`"routing_key`":`"$rqQueueName`",`"payload`":`"$msgBase64`",`"payload_encoding`":`"base64`"}"
$resp = Invoke-WebRequest -Method Post -Uri $rqUrl -Headers @{'Authorization'= $authHeader} -Body $json
if([math]::Floor($resp.StatusCode/100) -ne 2) {
    Write-Error "File $fileName could not be posted, error $($resp.BaseResponse)"
}

Write-Host "File $fileName was posted to $rqUrl"

Signs that you need coffee, #8

You wake up on a rainy Saturday morning and desperately need a cappuccino.

You take the milk out of the fridge.

You start the milk frother without actually pouring milk into it .

You put the coffee cup under the espresso machine and press the coffee button without having turned it on.

You see the milk out of the fridge, wonder who forgot it there, and put it back in.

You stare at the coffee cup trying to understand why it’s empty.

You peek at the milk frother and are perplexed to discover it too is empty.

In this state, it will probably take a miracle for me to get the coffee I so obviously need.

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: Basic CATCH block

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

$ErrorActionPreference='Stop'
Clear-Host

$errorList = New-Object -TypeName System.Collections.ArrayList
try {
    # whatever
}
catch {
    $errorMsg = "Error processing file '$($file.FullName)', exception in line $($_.InvocationInfo.ScriptLineNumber): $_.Exception.Message $_"
    $errorList.Add($errorMsg) | Out-Null
    Write-Warning $errorMsg      
}

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"

Powershell: Copy XML files depending on their data plus data from a database

Clear-Host
$ErrorActionPreference='Stop'

# === Parameters ===
$path = 'D:\dataFilesPath'
$copyPath = 'D:\copyFilesPath'
$dbServer = 'mySqlServer'
$dbName = 'Customers'
$sql = 'SELECT CustomerName FROM Customers WHERE Active=1'
# === Parameters ===

$res = Invoke-Sqlcmd -ServerInstance $dbServer -Database $dbName -Query $sql

$exclusionList = New-Object -TypeName 'System.Collections.Generic.List[string]'

# if a different field of the query is needed, change [0]
$res | % { $exclusionList.Add($_[0].ToLowerInvariant().Trim()) }

if (-not (Test-Path -Path $copyPath)) { New-Item -ItemType Directory -Path $copyPath} $list = Get-ChildItem -Path $path -Filter '*.xml' -Recurse

$cnt=0
foreach($file in $list) {
    [xml]$contents = Get-Content -Path $file.FullName -Encoding UTF8
    # obvs you'll need to change the XPATH 
    # to match your XML structure
    $customerName = $contents.Data.CustomerData.Customer_Name.'#cdata-section'.ToLowerInvariant().Trim()

    if ($exclList.Contains($customerName)) {
        continue
    }
    Write-Host "Found $customerName"

    Copy-Item -Path $file.FullName -Destination $copyPath
    $cnt++
}

Write-Host "Finished, $cnt files copied to $copyPath"

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: Keep the lights on

Clear-Host
Add-Type -AssemblyName System.Windows.Forms
Write-Host 'Starting...'
$WShell = New-Object -com "Wscript.Shell"
while ($true)
{
  $WShell.sendkeys("{F16}")
  Start-Sleep -Seconds 180
}

Powershell: Quickly format (“pretty print”) XML files

cd C:\mydata
gci *.xml | % { [xml]$x=get-content $_ ; $x.Save($_.FullName) }

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: Add existing code to repo

# See also https://docs.github.com/en/migrations/importing-source-code/using-the-command-line-to-import-source-code/adding-locally-hosted-code-to-github
cd C:\mysourcecode
git init -b main
git add . 
git commit -m "initial commit"
git remote add origin https://mycodeprovider/myrepo
git push -u origin --all

Powershell: 1-liner to create azure devops project and repos from zip files

Prerequisite: git config and az login have been completed.

cls; $proj=(Split-Path -Path $pwd -Leaf); az devops project create --name $proj; gci *.zip | % { Expand-Archive $_.FullName }; gci -Directory | % { cd $_.FullName; $dir=(Split-Path -Path $pwd -Leaf); az repos create --project $proj --name $dir; git init -b main ; git add . ; git commit -m "initial commit" ; git remote add origin https://MYUSERNAME@dev.azure.com/MYUSERNAME/$proj/_git/$dir ; git push -u origin --all }

Powershell: Zip directory and generate hash

#
# Source: DotJim blog (http://dandraka.com)
# Jim Andrakakis, April 2023
#

Clear-Host
$ErrorActionPreference = 'Stop'

# customize here
$releaseId = [guid]::NewGuid().ToString().Split('-')[0]
$SourceDir = "$PSScriptRoot\source\" 
$OutputFile = 'mypackage.zip'
$DestinationPath = "$PSScriptRoot\deploy\$($releaseId)"
$tempPath = "$($env:TEMP)\$releaseId"

New-Item -ItemType Directory -Path $DestinationPath
New-Item -ItemType Directory -Path $tempPath

# to avoid file lock issues
Copy-Item -Recurse -Path $SourceDir -Destination $tempPath

$compressParams = @{
    Path = "$tempPath\source\*"
    CompressionLevel = 'Optimal'
    DestinationPath = Join-Path $DestinationPath $OutputFile
    Force = $true
}

Compress-Archive @compressParams
(Get-FileHash $compressParams.DestinationPath).Hash | Out-File $(Join-Path $DestinationPath $OutputFile.Replace('.zip','_hash.txt'))

Write-Host "Finished"
Start-Sleep -Seconds 30