Using PowerShell to find IIS Sites without https enabled

March 21, 2017

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

October 14, 2016

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

October 13, 2016

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

April 18, 2014

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

January 4, 2014

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  ("site`tstate`tapp`tvirtdir`tphys.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”

October 15, 2013

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”.


Listing Fonts used in an SSRS Report (.rdl)

June 18, 2013

When deploying SSRS reports, one needs to check whether the fonts used in a .rdl report are actually installed on the SSRS server, as a developer may select fonts installed by software that is not available on the server (I’m looking at YOU, Arial Narrow).

A font declaration inside an .rdl file looks like this

<Style>
    <FontFamily>Arial Narrow</FontFamily>
    <FontSize>8pt</FontSize>
</Style>

and those elements reside in the

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition">

namespace.

First, we load the .rdl as XML and declare the namespace

[xml] $rdl = get-content "c:\path\to\my.rdl"
$ns = @{ r ="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" };

As we need to select all Style elements containing the FontFamily AND the FontSize child elements, we query the XML

$rdl.Report.Body | select-xml "//r:Style[r:FontFamily and r:FontSize]" -namespace $ns

and then select the text values

    | foreach-object { "$($_.Node.FontFamily) $($_.Node.FontSize)" }

This returns a list of font specifications like this

Arial Narrow 8pt
Arial Narrow 8pt
Arial Narrow 8pt
Arial Black 8pt
Arial Black 8pt
Arial Black 8pt
Arial Black 8pt
Arial Black 8pt

Grouping the list and counting the items

    | Group-Object | select-object name, count

returns

Name                                  Count
----                                  -----
Arial Narrow 8pt                         20
Arial Black 8pt                           6

So this is the script:

[xml] $rdl = get-content "c:\path\to\my.rdl"
$ns = @{ r ="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" };
$rdl.Report.Body 
    | select-xml "//r:Style[r:FontFamily and r:FontSize]" -namespace $ns
    | foreach-object { "$($_.Node.FontFamily) $($_.Node.FontSize)" }
    | Group-Object | select-object name, count