Archive | Powershell scripts RSS for this section

Text to speech alarm clock with powershell

Thanks to a blog by Jefferey Hicks about an alarm clock in powershell using the PC speaker, I thought it would be nice to tweak that script a little bit and use Microsoft’s text to speech engine so you can use the alarm and let a voice wake you through your headphones or speakers.

To enable the engine, I believe you need .NET 4 on your system and you need powershell version 2 or higher. But if you use Windows 8.1 (or higher; thus powershell 4 or higher) the engine is a lot better and will talk with a reasonably natural voice. All you need is 2 lines of code to reference to the text to speech engine and create an object.

Add-Type -AssemblyName System.speech
$Speak = New-Object System.Speech.Synthesis.SpeechSynthesizer

Now you can use that object to say something by using $Speak.Speak(“Something”), to which you can either pass a string or an array.

All the credit for this script goes to Jefferey Hicks (as mentioned above), I only replaced the pc speaker part of his script with the code for the text to speech engine and maybe some of you know the reference to the first part of what’s being said… And I guess that a lot of you will know the the last line.

You can find my version of the script here: Alarm.ps1

Just run it with the -NapTime parameter and the amount of minutes to count down from.

Get current cpu usage for a process

I wanted to get the current CPU usage for certain processes, which appeared to be harder than I thought.

The Get-Process command will give you CPU time and program start time, but will not tell you when the CPU was being used by the process. Thus if you use the previous information you got from the tool to calculate the CPU usage, you’ll get the average CPU usage for that process since it started. (So if it started at 100% CPU usage and has been busy since, it’ll show as 100%, but when it’s software that’s been started days ago and just been using 100% for the past 10 minutes, without doing much the time before, it’ll show as the program using 0,05 % or something like that, since that is its average usage). If you’re interested in this information, explains how to do this. This is not the information I want to know, thus I started searching for another way to do it.

WMI has many possibilities, but most of my scripts that are lagging, are lagging because of WMI usage, thus I try to use this only if I can’t find another way to do so.

I finally got to the Get-Counter command, which gets information from Performance Counters. I’d like to get % processor time information, thus I’ll check this with the following command (add the * to get all instances of the process):

Get-Counter "\Process(Chrome*)\% Processor Time"

This gives me a list of all process instances and their current CPU usage.

If you’d want to take multiple samples, this can also be specified (just like the sample interval)

Get-Counter "\Process(Chrome*)\% Processor Time" -SampleInterval 1 -MaxSamples 5

At the moment I’m only interested in getting 1 quick sample (this script will be integrated in a monitoring tool), thus I’ll skip these switches. But to explain them: The SampleInterval switch will set the interval time between the 2 measuring samples, default is 1 second, the MaxSamples switch will give you an x amount of samples; the default value is 1 sample.

The output is not quite what I want, I only like to get the CounterSamples. This is achieved with this command:

(Get-Counter "\Process(Chrome*)\% Processor Time").CounterSamples

This’ll give me this output (with a lot of open tabs in chrome):


But now, there’s the problem that the name as being used in the Performance Counters isn’t the same as in the process list, neither do I know a process ID. Lets solve this with the Get-Counter command as well. This command will show me the Process ID’s:

(Get-Counter "\Process(Chrome*)\ID Process").CounterSamples

And will give me this list of ID’s:


Now it’s time to start working with the data. I’ll start by putting the data in variables, then I’ll check if the lists are the same size. Once that’s done, I’ll store the data that I want and return the list sorted with the process with the highest CPU usage on top:

$ProcessId = (Get-Counter "\Process($Process*)\ID Process").CounterSamples | Sort-Object Path
$ProcessCPU = (Get-Counter "\Process($Process*)\% Processor Time").CounterSamples | Sort-Object Path,CPU
$TotalInfo = @()
If($ProcessId.Count -eq $ProcessCPU.Count)
    For($i = 0; $i -lt $ProcessId.Count; $i++)
        $TempInfo = $ProcessId[$i].Path.Split("\")[3].Substring($ProcessId[$i].Path.Split("\")[3].IndexOf("(") + 1)
        $TempInfo = $TempInfo.Substring(0,$TempInfo.IndexOf(")"))
        $ProcessInfo = New-Object psobject
        Add-Member -InputObject $ProcessInfo -MemberType noteproperty -Name "CounterName" -Value $TempInfo -Force
        Add-Member -InputObject $ProcessInfo -MemberType noteproperty -Name "Id" -Value $ProcessId[$i].CookedValue -Force
        Add-Member -InputObject $ProcessInfo -MemberType noteproperty -Name "Cpu" -Value $ProcessCPU[$i].CookedValue -Force
        $TotalInfo += $ProcessInfo
Return $TotalInfo | Sort-Object Cpu -Descending

And this will give me a nice list like this:


Oh, if you’re interested in getting the total CPU load of a system, this is also pretty easy with the Get-Counter command. also describes this, but can be done with this command:

(Get-Counter '\processor(_total)\% processor time').CounterSamples

The script can be downloaded here.

Get logged on users and sessions

There are several ways to get a list of currently logged on users on a system, but only a few return the things that I like to know. In case of my servers, I’d like to know which users are connected to which session.

One way to do so is by:

Import-Module RemoteDesktop

but this doesn’t give a consistent return (on some of my 2012 R2 servers it doesn’t return anything, even though users are logged on to the system and RDS is setup) and it doesn’t include the 0 session user(s)


WMI can also do the job, but by default will give a lot of unnecessary information

Get-WmiObject Win32_LoggedOnUser | Select Antecedent -Unique | %{"{0}\{1}" -f $_.Antecedent.ToString().Split('"')[1],$_.Antecedent.ToString().Split('"')[3]}

(It’ll return DWM-1, DWM-2, DWM-3, IUSR, SYSTEM and more things I don’t need to know or work with) To filter most unwanted items, you’d get a big command like:

Get-WmiObject Win32_LoggedOnUser | Select Antecedent -Unique | Where-Object { $_.Antecedent.ToString().Split('"')[1] -ne $env:COMPUTERNAME -and $_.Antecedent.ToString().Split('"')[1] -ne "Window Manager" -and $_.Antecedent.ToString().Split('"')[3] -notmatch $env:COMPUTERNAME } | %{"{0}\{1}" -f $_.Antecedent.ToString().Split('"')[1],$_.Antecedent.ToString().Split('"')[3]}

This’ll return a list of all users (including administrator), but no sessions:


So until now the first command would be the best; but since it’s pretty inconsistent wether it indeed returns a list of users, I thought of ‘misusing’ another command for this purpose. The Get-process command. Since every logged in user has several processes in their name, this might be the best approach:

Get-Process -IncludeUserName | Select-Object UserName,SessionId | Where-Object { $_.UserName -ne $null } | Sort-Object UserName -Unique

This will return a list of all users, but users that are connected to multiple sessions, will only show in one this way. To get the list of users based on the session and filter out the accounts like DWM-1 to 3 etc, the following command  The Where-Object { $_.UserName -ne $null } part will make sure the UserName field is filled when walking through it, otherwise you’d get lots of errors on not being able to call a method on a mull-valued expression. Thus to rule these out, this part is added.

Get-Process -IncludeUserName | Select-Object UserName,SessionId | Where-Object { $_.UserName -ne $null -and $_.UserName.StartsWith("DOMAIN OR COMPUTERNAME") } | Sort-Object SessionId -Unique

This will return an object with UserName and SessionID for that user. Once multiple users are connected to the same session (like session 0), the filter for the domain name or computer name is needed to get the list of names you’d be interested in. Here is a screenshot of the result of the last command:


The IncludeUserName switch has been added since Powershell 4.0, thus older versions will not be able to use this switch. Also the switch requires this command to be ran elevated; otherwise you’d get an error.

In the end I will definitely use the Get-Process command to get a consistent list of logged on users on my servers.

Edit: By the way, if you’re about to run this on a remote computer, wrap an Invoke-Command around it. Because if you include the -ComputerName switch, the -IncludeUserName switch can’t be combined. Next to that, the Get-Process command with -ComputerName will return all processes as running on session 0.

Thus if you are to use it on a remote computer, use a command like:

Invoke-Command -ComputerName "COMPUTERNAME" -ScriptBlock { Get-Process -IncludeUserName | Select-Object UserName,SessionId | Where-Object { $_.UserName -ne $null -and $_.UserName.StartsWith("DOMAIN OR COMPUTERNAME") } | Sort-Object SessionId -Unique } | Select-Object UserName,SessionId

Note that in the end there’s an extra Select-Object, because the Invoke-Command will add 2 extra properties to the result; PSComputerName and RunspaceId. You can also add credentials to this command and other parameters, depending on your needs.



Writing to Excel


About a year ago I started writing things to Excel with PowerShell, based on the information I found on an MSDN blog – Create Excel file with PowerShell

I wasn’t quite satisfied, though. I wanted some more formatting (specifically: change the top row behavior). It was somewhat frustrating to not be able to get everything the way I wanted, thus I decided to start finding out how to do this. It started early evening and late night (or early morning) I was finally satisfied with all the things I could do with Excel. I decided to share the script and struggles with you. I couldn’t find a place which had all these customizations together with creating the Excel file, thus this might help you save a lot of time when trying to reach the same goal.


My goal is to freeze the top row, set the text at least to bold (depending on difficulty in setting the font properties) and set an autofilter on the top row. And of course to fill the Excel workbook with data. I also want to refit the column width based on its text.

In real life I use it to document things (who likes documenting and why document things if you can have the computer doing it for you?), in this Example, I’ll use it to write the output of the Get-Process command to get a list of processes, process IDs, UserNames and Sessions (in the example, the CPU object is also documented)

Writing the script

With the Get-Process command including the IncludeUserName parameter in my script, I need to run this as administrator. There are several ways to achieve this, I found these two options on the net: Start script as administrator & Check if user is administrator (nearly last comment from MOW)

Combining those two gets met to these lines of code (check if the user started the script as administrator & check if the user can be an administrator. If the script wasn’t started as admin and the security settings allow it, it’ll start the script as admin, otherwise it’ll give an error that you need to be an admin to be able to run this script):

$IsAdmin = ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator")
$CanBeAdmin = ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]"$env:USERNAME").IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator")

       $Arguments = "& '" + $MyInvocation.MyCommand.Definition + "'"
       Start-Process powershell -Verb runAs -ArgumentList $Arguments
       Write-Error "You have to be an administrator to run this script" -ErrorAction Stop

These lines go in the top of the script. The script breaks if the user isn’t an admin.

When you live in a non US country (which are more people that actually live in the US, but ok), your CurrentCulture settings will probably have you run into problems when trying to use the Excel com object. The following Error will probably appear on many of your Excel com object calls:

Exception calling "<name>" with "<int>" argument(s): "Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))"
At line:<int> char:<int>
+ <PSObject>.<call> <<<< ()
   + CategoryInfo         : NotSpecified: (:) [], MethodInvocationException
   + FullyQualifiedErrorId : ComMethodTargetInvocation

Thus we’ll have to add this line of code to set the CurrentCulture to US:

[System.Threading.Thread]::CurrentThread.CurrentCulture = "en-US"

Now we can start with the Excel code. In my example I delete the Excel file if it exists, before I start a new one. This makes the code obsolete to Open & Save instead of Create & SaveAs, but I left it in there as a reference. Then came the struggle of setting the top row defaults.

The bold part was pretty easy and straightforward:

$Range = $sheet.UsedRange
$Range.Font.ColorIndex = 11
$Range.Font.Bold = $True

Locking the top rows was somewhat harder, but I stumbled upon it when searching for setting the auto filter. Thanks, rwskas on Experts-Exchange

$sheet.Application.ActiveWindow.SplitColumn = 0
$sheet.Application.ActiveWindow.SplitRow = 1
$sheet.Application.ActiveWindow.FreezePanes = $true

With the top rows locked all that’s left is setting the autofilter. Which proved to be pretty hard information to find. Microsoft has information about the Range.AutoFilter Method, but it still left me with questions on how to implement this in PowerShell. I had this C# example from Stack Overflow. Then I found a nearly correct way to use it on Thanks, Tim Pringle. I still needed to know how get the ‘missing type’ and the Excel.XlAutoFilterOperator.xlAnd (from the C# example), which was (probably) the 1 in the PowerShell example. When searching for the XLAutoFilterOperators I stumbled upon a TechNet post with an answer by Boe Prox (Thank you!), there’s the missing type, now I can try it. And … It works! The 1 seems to be xlAnd (the default value) as we thought. This gives us the following code:

$Range.AutoFilter(1, $MissingType, 1, $MissingType, $MissingType) | Out-Null

All that’s left now is refitting the column width based on its text (pretty straightforward)

$Range.EntireColumn.AutoFit() | Out-Null

Then I added an extra line of code, so my example file won’t contain my user name.

If($Process.UserName -eq "$env:USERDOMAIN\$env:USERNAME") { $Process.UserName = "PowershellAdministrator" }

In the end, I quit Excel, release its com object and remove the Excel variable as used in the script.

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) | out-null
Remove-Variable -Name Excel -Scope Global -EA 0

And that’s it. Now you’re automatically writing to Excel. In the end the code isn’t that hard to understand, but to get there can be a pain sometimes. So I hope this’ll help someone trying to reach the same goal(s) as I did.

The script and Excel example file

The Excel example file can be found here, the PowerShell script here.

Happy scripting.

Just to let you know: I just got myself a domain name for my blog. From now on, this blog can be found on


The Get-Date cmdlet is pretty nifty and gives you a lot of date/time formats you’d want to use in your script.

Like if you were to save files with a unique stamp, you’d want a date/time format like <year><month><day><hour><minute><second>

Here are some examples to achieve this goal, with outputs

Get-Date -Format s

(Get-Date -Format s).Replace("-","").Replace(":","").Replace("T","")

"{0}{1}{2}{3}{4}{5}" -f (Get-Date).Year,(Get-Date).Month,(Get-Date).Day,(Get-Date).Hour,(Get-Date).Minute,(Get-Date).Second

$Date = Get-Date
"{0}{1}{2}{3}{4}{5}" -f $Date.Year,$Date.Month,$Date.Day,$Date.Hour,$Date.Minute,$Date.Second

Get-Date -Format yyyyMMddHHmmss

Get-Date -UFormat %Y%m%d%H%M%S

As you can see, all the given examples have the same output.

The first example doesn’t completely live up to the task as I described in my format, thus I need to remove several characters (by replacing them with a blank), which is the second example.

The third and fourth example will do a format-string and get all different get-date attributes. The fourth example is the same as the third, but in this case I fist put the output of the Get-Date cmdlet into a variable called $Date, this already makes the code a little bit less, but still longer than the Second example.

The fifth and sixth example use the formatting parameters Format and Uformat. Format is the .Net format and UFormat is the Unix format. Those are the shortest ones to use.

I myself like the switches for formatting a lot, for cases where you need to get the date the way you want it, without changing the localization settings of a server/computer.

Like for example in the EU, most (or maybe all) countries work with dates in the <day>-<month>-<year> pattern and time in the 24H format; while in the US dates are worked with in the <month>/<day>/<year> pattern and time in 12h format with AM and PM. But once working with files and sorting dates, the format as used in the example in usually used.

The PowerShell help tells us all the options for Uformat (each is preceded by a % sign):

  • c Date and time – abbreviated (Fri Jun 16 10:31:27 2006)


  • D Date in mm/dd/yy format (06/14/06)
  • x Date in standard format for locale (09/12/07 for English-US)


  • C Century (20 for 2006)
  • Y Year in 4-digit format (2006)
  • y Year in 2-digit format (06)
  • G Same as ‘Y’
  • g Same as ‘y’


  • b Month name – abbreviated (Jan)
  • B Month name – full (January)
  • h Same as ‘b’
  • m Month number (06)


  • W Week of the year (00-52)
  • V Week of the year (01-53)
  • U Same as ‘W’


  • a Day of the week – abbreviated name (Mon)
  • A Day of the week – full name (Monday)
  • u Day of the week – number (Monday = 1)
  • d Day of the month – 2 digits (05)
  • e Day of the month – digit preceded by a space ( 5)
  • j Day of the year – (1-366)
  • w Same as ‘u’


  • p AM or PM
  • r Time in 12-hour format (09:15:36 AM)
  • R Time in 24-hour format – no seconds (17:45)
  • T Time in 24 hour format (17:45:52)
  • X Same as ‘T’
  • Z Time zone offset from Universal Time Coordinate (UTC) (-07)


  • H Hour in 24-hour format (17)
  • I Hour in 12 hour format (05)
  • k Same as ‘H’
  • l Same as ‘I’ (Upper-case I = Lower-case L)

Minutes & Seconds:

  • M Minutes (35)
  • S Seconds (05)
  • s Seconds elapsed since January 1, 1970 00:00:00 (1150451174.95705)

Special Characters:

  • n newline character (\n)
  • t Tab character (\t)

The help tells us that the settings for the Format switch can be found here. This is the information which is currently (when writing this blog) available on the site:

Format pattern Associated Property/Description
d ShortDatePattern
D LongDatePattern
f Full date and time (long date and short time)
F FullDateTimePattern (long date and long time)
g General (short date and short time)
G General (short date and long time)
m, M MonthDayPattern
o, O Round-trip date/time pattern; with this format pattern, the formatting or parsing operation always uses the invariant culture
r, R RFC1123Pattern; with this format pattern, the formatting or parsing operation always uses the invariant culture
s SortableDateTimePattern (based on ISO 8601) using local time; with this format pattern, the formatting or parsing operation always uses the invariant culture
t ShortTimePattern
T LongTimePattern
u UniversalSortableDateTimePattern using the format for universal time display; with this format pattern, the formatting or parsing operation always uses the invariant culture
U Full date and time (long date and long time) using universal time
y, Y YearMonthPattern

The following table lists the custom DateTime format patterns and their behavior. For more information, see Custom DateTime Format Strings.

Format pattern Description
d, %d The day of the month. Single-digit days do not have a leading zero. The application specifies “%d” if the format pattern is not combined with other format patterns.
dd The day of the month. Single-digit days have a leading zero.
ddd The abbreviated name of the day of the week, as defined in AbbreviatedDayNames.
dddd The full name of the day of the week, as defined in DayNames.
f, %f The fraction of a second in single-digit precision. The remaining digits are truncated. The application specifies “%f” if the format pattern is not combined with other format patterns.
ff The fraction of a second in double-digit precision. The remaining digits are truncated.
fff The fraction of a second in three-digit precision. The remaining digits are truncated.
ffff The fraction of a second in four-digit precision. The remaining digits are truncated.
fffff The fraction of a second in five-digit precision. The remaining digits are truncated.
ffffff The fraction of a second in six-digit precision. The remaining digits are truncated.
fffffff The fraction of a second in seven-digit precision. The remaining digits are truncated.
F, %F Displays the most significant digit of the seconds fraction. Nothing is displayed if the digit is zero. The application specifies “%F” if the format pattern is not combined with other format patterns.
FF Displays the two most significant digits of the seconds fraction. However, trailing zeros, or two zero digits, are not displayed.
FFF Displays the three most significant digits of the seconds fraction. However, trailing zeros, or three zero digits, are not displayed.
FFFF Displays the four most significant digits of the seconds fraction. However, trailing zeros, or four zero digits, are not displayed.
FFFFF Displays the five most significant digits of the seconds fraction. However, trailing zeros, or five zero digits, are not displayed.
FFFFFF Displays the six most significant digits of the seconds fraction. However, trailing zeros, or six zero digits, are not displayed.
FFFFFFF Displays the seven most significant digits of the seconds fraction. However, trailing zeros, or seven zero digits, are not displayed.
gg The period or era. This pattern is ignored if the date to be formatted does not have an associated period or era string.
h, %h The hour in a 12-hour clock. Single-digit hours do not have a leading zero. The application specifies “%h” if the format pattern is not combined with other format patterns.
hh The hour in a 12-hour clock. Single-digit hours have a leading zero.
H, %H The hour in a 24-hour clock. Single-digit hours do not have a leading zero. The application specifies “%H” if the format pattern is not combined with other format patterns.
HH The hour in a 24-hour clock. Single-digit hours have a leading zero.
K Different values of the Kind property, that is, Local, Utc, or Unspecified.
m, %m The minute. Single-digit minutes do not have a leading zero. The application specifies “%m” if the format pattern is not combined with other format patterns.
mm The minute. Single-digit minutes have a leading zero.
M, %M The numeric month. Single-digit months do not have a leading zero. The application specifies “%M” if the format pattern is not combined with other format patterns.
MM The numeric month. Single-digit months have a leading zero.
MMM The abbreviated name of the month, as defined in AbbreviatedMonthNames.
MMMM The full name of the month, as defined in MonthNames.
s, %s The second. Single-digit seconds do not have a leading zero. The application specifies “%s” if the format pattern is not combined with other format patterns.
ss The second. Single-digit seconds have a leading zero.
t, %t The first character in the AM/PM designator defined in AMDesignator or PMDesignator, if any. The application specifies “%t” if the format pattern is not combined with other format patterns.
tt The AM/PM designator defined in AMDesignator or PMDesignator, if any. Your application should use this format pattern for languages for which it is necessary to maintain the distinction between AM and PM. An example is Japanese, for which the AM and PM designators differ in the second character instead of the first character.
y, %y The year without the century. If the year without the century is less than 10, the year is displayed with no leading zero. The application specifies “%y” if the format pattern is not combined with other format patterns.
yy The year without the century. If the year without the century is less than 10, the year is displayed with a leading zero.
yyy The year in three digits. If the year is less than 100, the year is displayed with a leading zero.
yyyy The year in four or five digits (depending on the calendar used), including the century. Pads with leading zeros to get four digits. Thai Buddhist and Korean calendars have five-digit years. Users selecting the “yyyy” pattern see all five digits without leading zeros for calendars that have five digits. Exception: the Japanese and Taiwan calendars always behave as if “yy” is selected.
yyyyy The year in five digits. Pads with leading zeros to get five digits. Exception: the Japanese and Taiwan calendars always behave as if “yy” is selected.
yyyyyy The year in six digits. Pads with leading zeros to get six digits. Exception: the Japanese and Taiwan calendars always behave as if “yy” is selected. The pattern can be continued with a longer string of “y”s padding with more leading zeros.
z, %z The time zone offset (“+” or “-” followed by the hour only). Single-digit hours do not have a leading zero. For example, Pacific Standard Time is “-8”. The application specifies “%z” if the format pattern is not combined with other format patterns.
zz The time zone offset (“+” or “-” followed by the hour only). Single-digit hours have a leading zero. For example, Pacific Standard Time is “-08”.
zzz The full time zone offset (“+” or “-” followed by the hour and minutes). Single-digit hours and minutes have leading zeros. For example, Pacific Standard Time is “-08:00”.
: The default time separator defined in TimeSeparator.
/ The default date separator defined in DateSeparator.
% c Where c is a format pattern if used alone. To use format pattern “d”, “f”, “F”, “h”, “m”, “s”, “t”, “y”, “z”, “H”, or “M” by itself, the application specifies “%d”, “%f”, “%F”, “%h”, “%m”, “%s”, “%t”, “%y”, “%z”, “%H”, or “%M”.

The “%” character can be omitted if the format pattern is combined with literal characters or other format patterns.

\ c Where c is any character. Displays the character literally. To display the backslash character, the application should use “\\”.

Have fun with getting the date in your format.

Create Windows scheduled task (for the NppUpdater script)

Create Windows scheduled task (for the NppUpdater script)

The previous NppUpdater script doesn’t do anything without it being scheduled in the task scheduler.

So I thought I also release a script to you, which does just that. If you add the code in this script to the NppUpdater script, it’ll also create the scheduled task if it doesn’t exist. Of course the script can also be used for scheduling other things.

The task is created with the following parameters:

  • The task’s created in a subfolder called “SysAdmins”
  • The task’s name is “NppUpdater”.
  • The task will start the NppUpdater.cmd file.
  • The task runs daily at 6 am, with a random start delay of 15 minutes
  • The task its start-in path is set to the path the script started from when it rain
  • The task will run with highest privileges as the account that ran it (which needs to be a member of the administrators group)
  • The task requires a logged on user (if you don’t prefer this: just change the script)

If you don’t want the text in your log file about the task already existing, change the line in the bottom from




The script can be found here: Create-ScheduledTask.ps1

Notepad++ downloader and updater

I love to use Notepad++ (npp), but I also have npp installed on servers on which users can log on, but don’t have administrative privileges. They also love to use npp, but with the updater enabled, they get popup messages for the updates, but cannot install them. I don’t want to have to check all servers that I manage if they need a new version of npp, but if there’s a new version I do want to like that one to be rolled out on all servers.

There are many ways to do this job, but next to wsus, we don’t use other tooling, so I decided to create two little tools.

  1. NppDownloader
  2. NppUpdater

The first tool will check if the npp website if there’s a newer version available then the one that’s already on the disk in a certain folder or on a certain share (this one I schedule on my file server). The second tool is one that I schedule on each server with npp, it’ll check a certain folder or share for npp installers. The latest one found, will be installed on the system, if it’s newer than the current version that’s installed (I assume the default installtion location is used).

Both scripts will create a log file which is overwritten each time the script is ran. These scripts were made before I did my loop and fire write speed tests (as you can read in my previous blog post), thus still contains the [io.file]::WriteAllLines commands instead of the [io.file]::WriteLine command. It only writes a couple of lines in the file, so should be about as quick I guess.

I’ve zipped them both, and added cmd files with some options so it can be ran as administrator with the script starting in the correct path (normally a cmd that’s ran as administrator will start in C:\windows\system32). There is an option to enable the localextentions and go to the path the cmd file is started from. This is achieved by these lines in the cmd file:

setlocal enableextensions
cd /d "%~dp0"

The zipped powershell scripts and cmd files can be found here:


Edit: If you also want to automatically schedule the NppDownloader file, you might be interested in my follow-up post: Create windows scheduled task for the nppupdater script

Speed of loops and different ways of writing to files – Which is the quickest?


A while ago, I stumbled upon two blogs about speed and PowerShell. First Guillaume Bordier’s blog about PowerShell and writing files. And second there was IT Idea’s Blog on PowerShell Foreach vs ForEach-Object. While researching the matter, I also came across an article on about speeding up loops (which wasn’t of use in my tests, but still interesting and something to think about when creating a for loop).

For the first blog: I saw a flaw in the export-csv he used, as the output of the file was not the string “some text” and I myself use .net File class WriteAllLines a lot, which was not in his list.

Thus I decided to create my own test and try out the information found in both of the above blogs. Things got out of hand a little bit, which made me end up with the tool I have today and which I share with you. This way you can test them yourself or use my script as something to learn from. By creating this script I myself learnt a lot of things as well and used techniques which I hadn’t used before. Let me take you on the journey (if you’re not interested in the story and results, scroll down to the end of the post for the script):

Test Goals:

  1. I want the output to be the same and least interrupted by having the need of using variables next to writing and looping; but if they are needed, their time will be measured as well. (thus: file contents needs to be the same, for as far possible)
  2. I want to test the speed of each file write type
  3. I want to test the speed of each each loop
  4. In case of export-csv, I want the output to be the same text as in each other file, but it’s allowed to have quotes around it, because that’s part of the csv format.
  5. I will use the default setting for each way to write to a file, I will just deliver a string/array (depending on the way to write) and a file name and let the command do its work.

The different ways to write to a file, which are used in the tests (minor number in the test files):

  1. export-csv -append
  2. >> output
  3. out-file
  4. out-file -append
  5. io file WriteAllLines (.net file class)
  6. .net streamwriter WriteLine (.net streamwriter class)
  7. io file WriteLine (.net file class)

The different loops, which are used in the tests (major number in the test files):

  1. .5000 | ForEach-Object
  2. Foreach 1..5000
  3. For x = 1 – 5000
  4. While x -ne 5000
  5. Do .. While x -ne 5000
  6. ForEach(x in [arr5000])

(I know there’s also a Do Until, but I figured there’s already a While and a Do.. While in here, so it probably wouldn’t matter that much; maybe I will add it later. If I missed some more, please tell me in the comments and I will try to add it.)

Like I said before, once I started testing things, it got a little bit out of hand and I added more and more, also because the results were pretty interesting, but primarily because I enjoyed testing and learning about all the differences and new ways of doing things in my code.

The test results

The first thing to notice when the script is complete is that the different write types give different file sizes. With the csv export is easily explainable, because of the quotes around each text line. (64kb instead of 54kb)


When you open them with my favorite text editor (Notepad++), it’ll tell you the different types of encoding that was used.

UTF-8 is used by Export-CSV, the .net file WriteAllLines & WriteLine and .net streamwriter WriteLine

The others use UCS-2 LE BOM; I did not research the difference, but they probably support double byte characters, thus explaining the double file size.

(* Note – in most or all cases the file format can be specified, but I was interested in the default setting, as this would be the one used the most when I’ll use these commands)

After I saw these differences, I also noticed the time differences, which I color coded so it’s easier to find the best and worst ones. In the end of the tool it’ll show 2 views on the results. The results based on the types of loop and on the types of writing to the files.

In the end I limited the file to create test files with a maximum 50 million lines (which is a 524MB UTF-8 file), but you can easily override that by commenting it out.

The slow writing types and loops will in most cases be exponentially slower when using larger files, but testing with a minimum of 5000 lines will give a decent result. It is possible to go as low as 50 lines though, but the quick writing types will be done witting 0 milliseconds. Even with only 50 lines to write, some of the ways will result in taking nearly 1 second, thus already lagging your script if you use them.

All test results show that the ForEach-Object is one of the slowest loops. The quickest seems to be the ForEach loop, but Do.. While, While and For follow with just several milliseconds difference. It would be quicker to create a list of objects which you’d otherwise ran through ForEach-Object and create a ForEach loop to work with that list of objects. This would probably be about 30 seconds quicker with a loop that runs 5000 times. One little interesting thing is that the Do .. While loop is a little bit quicker than the While loop (maybe because the Do already initiates the start of the loop, without knowing the conditions?), that started me wondering about the Do .. Until loop and will probably add that to the types of loop to test. But first I wanted to share my findings and script with you.

On the writing part there’s a lot of differences to see.

The .net File class WriteAllLines  (which I thought was using streamwriter, but it is filestream) is performing well with small files, but once they get bigger, it is easily outperformed by the .net File class WriteLine and .net Streamwriter. Those two are the quickest, with the io file WriteLine leading on top on all tests and loops.

The test results In screenshots:

TestResults5000Test-LoopsAndWriteFile PS4Test-LoopsAndWriteFile PS4 524MB file WriteStartTest6

(The first one is a screenshot of test results as put into an Excel sheet and comparing 3 test runs; the second one is a screenshot of the results of the script with its default parameters, thus all tests and 5000 lines per file; the third one is a screenshot of the results of the script with 50 million lines (524MB UTF-8 file) written and only the quickest 2 writing methods. The red line (which is marked worst) is actually very good, but since it’s the slowest in the list, it gets this marking)

I’m pretty impressed by the results as well; writing 524MB, line by line in 10.4 seconds.

My conclusion:

Only use the Foreach-Object if you really really really (yes, I just did repeat the word really 3 times) need to; because it’s very bad for the performance of your script. If it’s at all possible, just create a ForEach loop and you will spend a lot less time waiting on your scripts to complete. (Which makes you and others around you that use your scripts happy). There are some valid reasons to do so, as described on the IT Idea blog (mentioned in the top of the article), to which Jeffery Hicks also replied with a valid reason to use foreach, though his example may be lacking a bit.

If you were considering of using export-csv , >>, out-file append or out-file; they all take 1 second up to 40 seconds for a 5000 lines file (54kb in UTF-8); so I would recommend no to use those. In case of getting data to a CSV file, you can always use ConvertTo-Csv and then use any of the other writing methods to save your data. Or if you’re into quick and dirty solutions, just put quotes around the strings you save and then use any of the quick writing methods to save this to a file.

If you were considering and/or using .net File class WriteAllLines (like I am/was): you’re already on the right track, and it is a quick way to save your data, but move on to one of the two quickest ones on the block:

  1. .net File WriteLine
  2. .net StreamWriter WriteLine

Which one of those to use? I’d say get my test script and test it yourself. On my dekstop and laptop computer I got different time results, but the winner (by a hair) was the .net File class with WriteLine.

So I know that from now on I’ll be using that one a lot more, in combination with a ForEach or For loop; which both perform the best.

What I learned by creating this

On the learning part for me (when creating this script), I started learning about using dynamic script block names, putting a console cursor on a certain place (doesn’t work on PowerShell 2.0 by the way), formatting strings, dynamic variables, how to properly write my text with export-csv (which I’ll never use again) and probably more.

I was confused on the .net file class, hence it uses filestream I had assumed it is the same type of steam as streamwriter; but the test results showed me something different in timing, so I also learned that there’s a difference between filestream and streamwriter.

Nice piece of code

I believe this to be a very nice line in the script (as it calls all the different tests and loops) :

Invoke-Command -ScriptBlock (Get-Variable -Name "$TestName" -ValueOnly) -ArgumentList $s,$TestName,$TestNumber,$TotalTestText

If you believe there’s anything missing in my tests, or things can be done on a better and/or different way, let me know below in the comments.

Optional parameters:

  • LoopStartTest (default value = 1; run all tests). If you want only the quick loops, start from 2
  • TestLines (default value = 5000; max = 50000000; 50 million). The amount of lines to write to each test file. Going below 1000 will result in writing methods completing within 0ms
  • WriteStartTest (default value = 1; run all tests). If you want only the quickest 2 writers, set the value to 6, if you want to include WriteAllLines set it to 5 (which can also be pretty quick, depending on amount of lines and file size)

Once you set the testlines value above 1 million, it is advised to only start from writing method 5; and loop test 2. Though I am interested in your results. Do they compare to mine? If you do have the time and patience to wait for the slow ones on files above 100k lines, please let me know the results below in the comments. I don’t thing the color coding will still be any good, though I tried to tie a formula to it to try to keep it within acceptable ranges. If you’ve got the patience to run this script to create larger files than 50 million lines (524MB in UTF-8 encoding)

The test script can be found here: Test-LoopsAndWriteFile.ps1

Note for PowerShell 2.0 users: The Append parameter is not implemented for Export-Csv, thus this test is skipped if PowerShell 2.0 is detected.

Download SolarEdge solar production data and save to csv

I’ve got a nice solar panel setup on my roof, which uploads its data to the SolarEdge monitoring portal (this is the brand of inverter that I got). It appeared that this monitoring portal also has an API to automate getting the energy data. With me working in a company that works on energy savings, monitoring, consultancy etc, it’s a logical step for me to automate downloading the production of my panels, so my collueges at work can import this data in my energy monitoring portal at work and then they can do their magic calculations on them; which results in me getting nice graphs of my solar enrgy production.

The purpose of the tool:

I wrote this tool to be somewhat smart in what to download and what not. I only want to download the information if it’s up-to-date. Next to that, I’d want daily values, but also values with a 15 minute interval. In the end I’d want all the info to be exported to CSV files containing 1 month of data and/or 1 year of data. All files that have already been generated don’t need their data to be downloaded again and overwritten, thus it skips this data once it’s downloaded. In the end I can either mail these files or put them on a file share, so they can be imported by our energy monitoring system. This last step I’ve removed from the script that I share with you.

Several nice ‘techniques’ used in this script to get to the goal:

Since I’m talking with an API, the main important command is the Invoke-WebRequest with a pipe to ConvertFrom-Json

To get the last day of the month, the following line is used: $LastDayOfTheMonth = ((Get-Date -Date “01-$Month-$Year” -Hour 0 -Minute 0 -Second 0).AddMonths(1).AddSeconds(-1)).Day

(which will add a month to the date/time 01-<month>-<year> : 0:00 and then remove 1 second to get to the last second of the previous month, thus resulting in returning the last day of that month (<last day>-<month>-<year> 23:59:59))

In the end, the downloaded data is converted to CSV with this command: | ConvertTo-Csv -Delimiter “;” -NoTypeInformation (in Excel with localization the Netherlands, the semicolon is a better seperator than a comma, since Excel expects a semicolon. This saves me time on opening the csv files and converting the information to an excel readable file. Depending on your localization settings, you’d want to change this accordingly) I also add the switch -NoTypeInformation, since I’m not interested in getting information about the type of variable PS used, I only need the data.

Script parameters and bounds:

The script will check the solaredge portal for the date/time on which the installation received its latest data. If this date is not the same as today, something might be wrong. It’ll warn you about it, but will still download. If it’s out of date for more than 1 day, it’ll give an error about this, but the script will still continue. When no data has been downloaded, the tool will download all data (starting on the installation date of the inverter) per month and save these to csv files. It will also generate files with yearly data. The data that’s downloaded is from the previous month (unless this is a later date than the date the last data was received on the solaredge monitoring portal, then the last data date will be used). In case of the daily values (monthly and yearly csv’s), the tool will only store the value’s if they’re not $null (which is: no data received on portal or the solar panels with its optimizers as a whole aren’t yet initialized by the inverter). In case of the 15 minute interval values, all $null values will be replaced by 0,0 (in that case the inverter is turned off because of its night mode); thus giving a nice list of 96 readings each day, and resulting in about 2688 – 2976 values in each file, depending on the amount of days in the month (which can be checked for, if needed; in my case I don’t, since my work already has many comprehensive tools to check for gaps or strange behaviour in data).

The script can be found and downloaded here: DownloadEnergyData.ps1


Edit: I got myself an undocumented API call parameter from a SolarEdge developer, which gives me 15 minute values in WH 🙂 The script has been updated. (&TimeUnit=QUARTER_OF_AN_HOUR)