# Sending Email Notifications for Windows Updates

I have this Windows server installed which hosts a couple of websites, and is really not doing much.

Every now and then I need to log in for some server operation, only to find the typical Windows Updates Pending notification. Every time this happens I think to myself, hey, I’d like to get some notification that there are updates available, without having to log in.

So I finally dug around the internets – well, as it turned out, the info was all on StackOverflow:

The result is a tiny PowerShell script called update-mailer.ps1, which queries the Windows Update Service and sends an email accordingly, and can be found on my GitHub PowerShell repository.

To run this script as a Scheduled Task, create a .cmd batch file containing the command

powershell.exe -ExecutionPolicy Bypass -File path\to\update-mailer.ps1

to bypass PowerShell’s restricted execution policy, and call the script from the task.

# Finding unused SpecFlow step implementations with PowerShell

SpecFlow steps are public methods adorned using the [Given], [When], and [Then] attributes inside classes marked with the [Binding] attribute.

To load the assembly containing the compiled SpecFlow steps, I found it necessary to load all other assemblies inside the bin directory. Otherwise an error would occur in the assembly’s GetTypes() method stating the references to other assemblies could not be resolved.

$asm = [System.Reflection.Assembly]::LoadFrom("$basedir\$specdll",  $null, 0)$types =$asm.GetTypes()

Next, the SpecFlow attribute types are retrieved

$binding = [TechTalk.SpecFlow.BindingAttribute]$given = [TechTalk.SpecFlow.GivenAttribute]$when = [TechTalk.SpecFlow.WhenAttribute]$then = [TechTalk.SpecFlow.ThenAttribute]

along with all [Binding] classes and there methods with the attributes listed above.

The attributes are collected in an array, as their Regex property contains the regular expression that will be used to search the .feature files using PowerShell’s select-string cmdlet.

:Outer foreach($a in$attributes) {     foreach($ff in$featurefiles) {        $found =$featurefilecontents[$ff] | select-string -Pattern$a.Regex -CaseSensitive        if ($found.Matches.Length -gt 0) { #write-host "found$($a.Regex) in file$ff"            $foundattributes +=$a            continue Outer        }    }    write-host "did not found reference for $($a.GetType().Name) $($a.Regex)"    $notfoundattributes +=$a}

The script has been developed to analyze a project using SpecFlow 2.4.0, and is available on my PowerShell Snippets repository.

# Retrieving SP and CU of installed SQL Server instances

Microsoft provides a list of Latest updates for Microsoft SQL Server which gives you the latest Cumulative Update (CU) number for each Service Pack (SP) of each version of SQL Server since 2000.

But how do you check which version of SQL Server you have installed on your machines?

Sure, there internets are full of SQL scripts which retrieve this information (MS, SO, TN, etc), but those scripts require you to connect to every SQL Server instance and query it individually.

If you have direct access to the machine running SQL Server (such as your development machine), wouldn’t it be nice if you saw the list of installed SQL Server versions, whether the instances are running or stopped?

So I typed along in the PowerShell ISE, retrieving all executables named sqlservr.exe (the executable hosting SQL Server), retrieving their VersionInfo, and outputting the relevant information:

$basedir = "C:\Program Files\Microsoft SQL Server"$exes = Get-ChildItem -Path $basedir -Filter sqlservr.exe -Recurse -ErrorAction SilentlyContinue -Force $exes | foreach-object -Process { $ip = Get-ItemProperty -Path$_.FullName $vi =$ip.VersionInfo [pscustomobject]@{ ProductVersion = $vi.ProductVersion;  FileVersion =$vi.FileVersion;
DirectoryName = $_.DirectoryName;  LastWriteTime =$_.LastWriteTime; }} | format-table

So, as a first step, I have the ProductVersion numbers.

As I found while researching this script, Microsoft also provides a list of product versions (“build versions”) indicating SQL Server version, Service Pack and Cumulative Update. The list can be downloaded as .xlsx from aka.ms/SQLServerbuilds.

So I have the installed product versions as list of PS objects, and the build numbers in .xlsx format, let’s combine both.

Fortunately, there is a PS library to read .xlsx files called ImportExcel (GitHub), and you install it by simply running

Install-Module ImportExcel -Scope CurrentUser

(you probably need to update PowerShell’s nuget, which is done semi-automatically in the process)

As it turned out, ImportExcel is PS code wrapping OfficeOpenXml.ExcelPackage, which I have dealt with in previous C# projects, so you do not have to have Excel installed when parsing the downloaded .xlsx.

The script uses Get-ExcelSheetInfo to query all the worksheets of the Excel file, and for each worksheet runs Import-Excel to retrieve the worksheet’s data into a hashtable indexed by the build number.

Finally, the original procedure is extended to lookup the ProductVersion number in the hashtable, and merge the Excel data with the original result:

Both versions of the script are available on my GitHub PowerShell repository.

# Retrieving the Umbraco Version

There are several ways to figure out which version of Umbraco a given Umbraco site is running.

### Umbraco.Core.dll

For Umbraco 7 and higher, the File Properties dialog of Umbraco.Core.dll lists the Umbraco version:

However, older versions of Umbraco only show the Build version:

To retrieve the actual Umbraco version of older Umbraco installations, you need to resort to Powershell:

[System.Reflection.Assembly]::LoadWithPartialName("Umbraco.Core.dll")
[Umbraco.Core.Configuration.UmbracoVersion]::Current
[Umbraco.Core.Configuration.UmbracoVersion]::Current.ToString()

Compare the source code of the UmbracoVersion class of Umbraco 6, Umbraco 7, and Umbraco 8.

### Database

Umbraco 7 keeps track of the installed or upgraded versions in the table umbracoMigration:

id name createDate version
1 Umbraco 2017-05-11 15:45:57.410 7.6.1
2 Umbraco 2019-08-26 10:49:14.110 7.7.0
3 Umbraco 2019-08-26 13:56:57.727 7.15.2

### web.config

Umbraco’s upgrade mechanism makes use of the <appSettings> to figure out whether a database upgrade is necessary. Depending on the version, the keys  umbracoConfigurationStatus (up to V7), or Umbraco.Core.ConfigurationStatus (V8) are used:

<add key="umbracoConfigurationStatus" value="7.7.0" />

<add key="Umbraco.Core.ConfigurationStatus" value="8.1.4" />



# Using PowerShell to find IIS Sites without https enabled

To retrieve all certificates registered in IIS, run this command in PowerShell Administrator mode (source)

import-module WebAdministration
get-childitem iis:SslBindings

To retrieve the bindings (i.e. http and https properties) of IIS sites, run (source)

import-module WebAdministration
get-website | select name,id,state, physicalpath,
@{n="Bindings"; e= { ($_.bindings | select -expa collection) -join ';' }} I used the last code snippet to find the IIS sites without https / SSL certificate: import-module WebAdministration get-website | where {$_.State -eq "Started"} |
select name,
@{n="Bindings"; e= { ($_.bindings | select -expa collection) -join ';' }} | where {$_.Bindings -notmatch "ssl" } 

There is also a nice script finding IIS certificates and whether they are used in an IIS site or not.

# Retrieving the List of Installed Programs in Windows 7

The Programs and Features view in the Windows Control Panel does not provide a means to export the list of installed programs as a text file.

A quick web search brought up the information that the information about installed software resides under the registry key

HKLM:\Software\Microsoft\Windows\CurrentVersion\Uninstall

or, on 64-bit systems, under

HKLM:\Software\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall

(Source: social technet, Scripting Guy, HowToGeek, SuperUser)

PowerShell kann enumerate the information under these keys using the Get-ItemProperty commandlet. You can retrieve the PS properties of this data using the Get-Member commandlet.

So the first PS command to retrieve this list is

Get-ItemProperty HKLM:\Software\Microsoft\Windows\CurrentVersion\Uninstall\*

However, Control Panel does not display the registry entry if

• its DisplayName is empty or not set
• its UninstallString is empty or not set

which results in the command

Get-ItemProperty HKLM:\Software\Microsoft\Windows\CurrentVersion\Uninstall\* |
where-object {$_.DisplayName -and$_.UninstallString }

To retrieve the programs directly in PowerShell ISE, we can simply use the Format-Table commandlet

Get-ItemProperty HKLM:\Software\Microsoft\Windows\CurrentVersion\Uninstall\* |
Where-Object {$_.DisplayName -and$_.UninstallString } |
Select-Object Publisher, DisplayName, DisplayVersion, InstallLocation, InstallDate, URLInfoAbout, UninstallString |
Sort-Object Publisher,DisplayName |
Format-Table

To output to a file for further processing in Excel, export to a .csv file like this

Get-ItemProperty HKLM:\Software\Microsoft\Windows\CurrentVersion\Uninstall\* |
Where-Object {$_.DisplayName -and$_.UninstallString } |
Select-Object Publisher, DisplayName, DisplayVersion, InstallLocation, InstallDate, URLInfoAbout, UninstallString |
Sort-Object Publisher,DisplayName |
Export-Ssv -delimiter "t" -encoding "unicode" c:\temp\installed.csv

# Finding Deleted IIS Application Directories

If you develop web applications for IIS, or administrate IIS and file systems, you sooner or later end up with orphaned IIS application which point to non-existing directories.

To get an overview of IIS applications, start up PowerShell in administrator mode (I prefer PowerShell ISE) and run

import-module webadministration
dir "IIS:\\sites\Default Web Site"

To exclude any files that reside in IIS root, filter out the “file” entries:

import-module webadministration
dir "IIS:\\sites\Default Web Site" | where { $_.NodeType.Trim() -ne "file" } Finally, we test the Physical Path property of the resulting entries import-module webadministration dir "IIS:\\sites\Default Web Site" | where {$_.NodeType.Trim() -ne "file"
-and ![System.IO.Directory]::Exists( $_.PhysicalPath ) } This command lists all IIS web application and virtual root directories which point to non-existing directories. The result looks like this: Type Name Physical Path ---- ---- ------------- application application1 D:\app\app1 application web2 D:\app\web2 # Querying Facebook Like Count and Public Page Properties There are various way to query the Like count of Facebook pages. If you just want to want the live stats of a given Facebook page, Quintly provides a live statistics page. Upon entering the FB page name (www.facebook.com/[page-name] becomeswww.quintly.com/facebook-live-statistics/[page-name]), the page displays a live chart updating every couple of seconds. But you can also query Facebook directly using the FQL API or the Graph API. Here’s a sample URL for the FQL API https://api.facebook.com/method/fql.query?query=select %20url,share_count,like_count,comment_count,total_count %20from%20link_stat %20where%20url=%27[my-fb-url]%27 (enter this into the navigation bar. [my-fb-url] is the complete URL starting with “https:// The result is an XML document in the form <fql_query_response list="true"> <link_stat> <url>https://www.facebook.com/[page-name]</url> <share_count>[value]</share_count> <like_count>[value]</like_count> <comment_count>[value]</comment_count> <total_count>[value]</total_count> </link_stat> </fql_query_response> and like_count or total_count will be the values you are looking for. The Graph has a simpler approach, using simply http://graph.facebook.com/?id=https://www.facebook.com/%5Bpage-name%5D Here the result is given as JSON value, and the relevant JSON property is “likes”: { "id": "74865038590", ..."likes": 194804, ... } If you want to query repeatedly, here’s a simple PowerShell solution using FQL: $wc = New-Object system.Net.WebClient;
$response =$wc.downloadString("https://api.facebook.com/method/fql.query?query=
select%20%20url,share_count,like_count,comment_count,total_count
%20from%20link_stat%20where%20url=%27https://www.facebook.com/[page-name]%27")
$xml = [xml]$response
$xml.fql_query_response.link_stat # Retrieving all IIS Directories using PowerShell To tidy up the applications on the IIS of my development PC, I wanted to find out which IIS application (and their virtual directories) point to which physical paths. Starting out with this SO answer and some help from MSDN I quickly assembled this PS solution for the task: $dummy = [System.Reflection.Assembly]::LoadFrom(
"c:\windows\System32\InetSrv\Microsoft.Web.Administration.dll")

$srvmgr = new-object Microsoft.Web.Administration.ServerManager write-Output ("sitetstatetapptvirtdirtphys.path") foreach($site in $srvmgr.Sites | Sort-Object Name ) { foreach($app in $site.Applications | Sort-Object Path ) { foreach($virt in $app.VirtualDirectories | Sort-Object Path ) { write-Output ($site.Name + "t" + $site.State + "t" +$app.Path + "t" + $virt.Path + "t" +$virt.PhysicalPath)

}
}
}

This piece of code generates a tab-separated list of physical paths referenced by IIS sites, applications, and virtual directories. The application’s physical path is contained in a virtual directory with Path=”/”.

Note that the script must be “run as administrator” from the PS prompt or the PowerShell ISE.

# Working around SSRS error “is ambiguous in the namespace”

I am developing a couple of SSRS reports which need to translate UTC dates into timezone-based date values based on a timezone parameter (tzid).

After I found this answer on SO I added System.Core 3.5 to the reports’ References, and added custom Code:

Public Function FromUTC(ByVal d As Date, ByVal tz As String) As Date
Return (TimeZoneInfo.ConvertTimeBySystemTimeZoneId(d, tz))
End Function

and changed the Expression of each datetime field to

= Code.FromUTC(Fields!MyDateTimeField.Value, Parameters!tzid.Value)`

Worked fine.

Then a production system was upgraded to SSRS 2012 (and thus VS SSDT 2010), and report deployment did not work anymore, as VS displayed the error messages when hitting Deploy

The definition of the report ‘/myreport’ is invalid

and opening the report

[rsCompilerErrorInCode] There in an error on line 1 of custom code: [BC30560] “TimeZoneInfo” is ambiguous in the namespace “System”

or, in German

“TimeZoneInfo” ist im Namespace “System” nicht eindeutig.

So there’s no deployment for you, sorryyyyyyy.

A bit of google-fu brought up this question on Social and this issue on Connect, which means that MS is sitting on this bug for half a year now, and the “workaround” seems to be to create your own SSRS assembly, and/or to use Reflection to access the TimeZoneInfo methods, as sketched here.

I did not want to give in so easily, so I tried and verified that the procedure described in Deploying SSRS Reports with PowerShell still works for SSRS 2012.

And a colleague found that you can still deploy to SSRS 2012 using BIDS 2008 if you set the project’s TargetServerVersion to “SQL Server 2008 R2 or later”.