#WindowsPowerShell and Windows Certificates

So I had a need to get an idea of certificates installed on my Exchange 2010 Servers.  So after a but of “binging” I adapted and created this function.

function Get-Certificate{
        [ValidateSet("AuthRoot","Root","CA","MY","Disallowed", "AddressBook","TrustedPeople","TrustedPublisher")] [string]$MemberName="root")
  IF($verbose){Write-Host $Server"\"$MemberName -Foregroundcolor Yellow}
  $store=new-object System.Security.Cryptography.X509Certificates.X509Store("\\$Server\$MemberName",$lm)


Now this is okay, but I wanted to get it for all my Exchange Servers.  So I bolted this around the function. (NB you have to run it in the Exchange Management Shell). Now this part

$CertStoresCSV =@()
$CertStoresCSV+="AuthRoot,Third-Part Root Certification Authorities"
$CertStoresCSV+="CA,Intermediate Certification Authorities"
$CertStoresCSV+="Root,Trusted Root Certification Authorities"
$CertStores = $CertStoresCSV | ConvertFrom-CSV
$s=@(); Get-ExchangeServer | where {-NOT $_.IsEdgeServer}| ForEach{$s+= $_.Name+"."+$_.Domain}
ForEach($Server in $s){
  ForEach($CertStore in $CertStores){
    $cs = $CertStore.CertStore
    $n  = $CertStore.Name
    Write-Host $server"\"$cs"\"$n -Foregroundcolor Yellow
    $c=Get-Certificate -Server $server -MemberName $cs
    [array]$tmpMatrix = $c | Select @{Expression={$server};Label="Server"}, @{Expression={$cs};Label="Certstore"}, @{Expression={$n};Label="CertStoreName"},FriendlyName, Issuer, Subject, Thumbprint, NotAfter, NotBefore, HasPrivateKey
    $Matrix += $tmpMatrix
  Write-Host "-"

Okay so that is cool.  We now have an array object called $Matrix, how about a bit of output. This creates another array that we can output to html in a bit.

$HtmlMatrix = @()
$byServer = $Matrix | Group Server | Sort Name
$tmpColS  = @();$byServer | ForEach{$tmpColS += $_.Name}
$tmpCol   = @();$tmpCol += "Certstore","CertStoreName", "FriendlyName", "Subject","Thumbprint"; 
$tmpCol  += $tmpColS

[array]$byCert = $Matrix | Group Certstore, CertStoreName, Subject, FriendlyName, Thumbprint | Sort Name
ForEach($tmpCert in $byCert){
  $tmpHtmlMatrix = "" | Select $tmpCol
  [Array]$tmpName = $tmpCert.Name.Split(",")
  $tmpHtmlMatrix.CertStore = $tmpCert.Group[0].CertStore
  $tmpHtmlMatrix.CertStoreName = $tmpCert.Group[0].CertStoreName
  $tmpHtmlMatrix.FriendlyName = $tmpCert.Group[0].FriendlyName
  [Array]$tmpCertGroup = $tmpCert.Group
  ForEach($tmpGroup in $tmpCertGroup){
    $tmpS = $tmpGroup.Server
    $tmpHtmlMatrix.$tmpS = "x"
    $tmpHtmlMatrix.Subject = $tmpGroup.Subject
    $tmpHtmlMatrix.Thumbprint = $tmpGroup.Thumbprint 
  $HtmlMatrix += $tmpHtmlMatrix

Now that is a bit funky, we have taken the array and then flipped it ;-) We now have servers as columns and certificates as rows. What the it then does is against each server / certificate, it puts an x in column. The result is $HtmlMatrix.  Lets format that bad boy:

$today = Get-Date
$OutHtml = @()
$OutHtml += @"
<meta http-equiv='Content-Type' content='text/html; charset=utf-8' />
<TITLE>Certificate Information</TITLE></HEAD></span>
<span style="font-family: Consolas;"><Style>
TABLE{border-width: 1px;padding: 1px;border-style: solid;border-color: black;border-collapse: collapse; width=100%}
TD{border-width: 1px;padding: 1px;border-style: solid;border-color: black;}
TH{font-family:'Arial';font-size:12px;border-width: 1px;padding: 1px;border-style: solid;border-color: black;background-color:peachpuff;}
<B><FONT size='2' face='VERDANA'>Certificate Information</B></font>
<BR><FONT size='1' face='VERDANA'>Last updated: $today</FONT></FONT>

$tmpColH = @(); $tmpColH += "FriendlyName", "Subject","Thumbprint"; $tmpColH += $tmpColS
$htmlMatrix | Group CertStoreName | ForEach{$OutHtml += "<HR Size=6 Color=Green><H2>" + $_.Name + " [" + $_.Count + "]</H2>";$OutHtml += $_.Group | Select $tmpColH | ConvertTo-Html -Fragment}
$i=0; $OutHtml | foreach{
If($x -eq 0){$bgcolor = "style='background-color:#dddddd'";$x=1}ELSE{$bgcolor = ""; $x=0}
Switch -wildcard ($_){
    "*<tr>*"       { $OutHtml[$i] = $OutHtml[$i].Replace("<tr>", "<tr $bgcolor>")}
    "*<td>x</td>*" { $OutHtml[$i] = $OutHtml[$i].Replace("<td>x</td>","<td bgcolor=Yellow align=center><B>x</B></td>")}
    "*<th>*"       { $OutHtml[$i] = $OutHtml[$i].Replace("<th>", "<th class='vertical'>")}
$OutHtml | out-file CertificateInformation.html

The end result should be CertificateInformation.html.

Code to download is here: https://ucinfo.files.wordpress.com/2014/03/get-certificate.zip


#Lenovo Helix

Its been a while since my original post [June 2013], and I wanted to update you on how my Helix and I was getting along.

Let me start by saying I had hoped it would be “the” laptop for me, but it’s not.  Its lacking two key things for me that would make a lot of difference and make it awesome.

The two things it is missing are a microsd slot and an accessible USB port!

Why you say? So I am working on a powerpoint off a USB.  I have to go to a meeting, but its one of those where you know your not going to have to do much, so you just want to take the tablet and leave the dock at you desk.  Have you seen the issue yet?  My USB stick is plugged in to the keyboard dock, so I have save and close powerpoint, eject the USB, remove the tablet from the dock and then reinsert in the USB stick in the bottom of the tablet!  It would be sooooooooooo much better there was a USB port was on the side of the tablet!

Next is the microsd.  I typically was TV on the way to work in the morning.  I currently use a Microsoft Surface RT to do just that.  With the USB ports in the bottom of the Helix tablet it’s a pain in the butt to balance the tablet on your lap with a USB sticking out the bottom.  The Helix has a full size SIM slot in the bottom as well as two stupid label trays.  Why not make the sim slot a mini or micro sim and have a tray that can take a microsd, in similar way to the Nokia tablet?

.. and finally, the Pen.  You can ink with the bad boy, but unlike my two previous tablets, the pen doesn’t have an eraser!  Huh? Aye, on the Tosh you write with one end and then turn it around and erase with the other! Cool, but not with the Helix!

Anyway, rant off.  I use my Helix every day and it does what I need, but could do better! A few simple things could make the Helix 2 even better.

Calculate the number of workdays using Excel

This is a cool function, that I didn’t know about.  I basically wanted to find out the number of working days between two dates.  Found the NETWORKDAYS function! This is extracted from the Help file ;-)

Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.
Tip: To calculate whole workdays between two dates by using parameters to indicate which and how many days are weekend days, use the NETWORKDAYS.INTL function.

NETWORKDAYS(start_date, end_date, [holidays])

The NETWORKDAYS function syntax has the following arguments:
Start_date    Required. A date that represents the start date.
End_date    Required. A date that represents the end date.
Holidays    Optional. An optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contains the dates or an array constant of the serial numbers that represent the dates.

#Powershell WhoAmI

This is a funky function I have in my profile.ps1

Function WhoAmI([switch]$All=$False){
  If($All){$xUser = [System.Security.Principal.WindowsIdentity]::GetCurrent() }ELSE{ $xUser = ([System.Security.Principal.WindowsIdentity]::GetCurrent()).Name}
  Write-Host $xUser -Foregroundcolor Green

You can run:


which give provide your user name -or-

WhoAmI -All

will give you extra goodness

#Powershell Check if Running

So I have a number of scripts that run as scheduled tasks.  The issue is that some scripts take longer to run and at times I have multiple copies of the same script running.  I wanted to stop this.  So I knocked up this.  Essentially it checks to see if the script that has started is already running, and then it kills the old process, and the script continues.

#AppName is the name of the script
$AppName = "Get-PerformanceInfo.ps1"

Write-Host "Checking if script is already running"
#Get the PID of this script
$myPID = ([System.Diagnostics.Process]::GetCurrentProcess()).id
Write-Host $myPID

#Who is the user running the script
$cu = ([System.Security.Principal.WindowsIdentity]::GetCurrent()).Name.split("\")[-1];

#Check to see if the script is already running
$xRunning=get-wmiobject win32_process|where{$_.name -eq "powershell.exe"}|select name,processid,commandline,@{n="owner"; e={$_.getowner().user}} | where {($_.owner -eq $cu) -AND ($_.commandline -like "*$AppName*")  -AND ($_.processid -ne $myPID)};

#If it's running kill it 
IF(-NOT [string]::IsNullOrEmpty($xRunning)){Write-Host "Running! Terminating!";$xRunning | FL; $xRunning | forEach{$xpid = $_.processid; $xpid;Stop-Process -id $xpid -force -confirm:$false}}ELSE{Write-Host "Not Running!"}

Powershell Flip-Array Function

You know when you get some output from say Get-ExchangeServer that has load of values and when you output it to HTML it goes off the page and looks pants?  I do quite a bit of this, and thought, can I flip the array, so around.  Yeah baby you can! So here you go.  This v2 with some bugs ironed out.  Enjoy

function Flip-Array([Array]$Array, [String]$key){
  $matrix =@()
  $KeyValue = @()
  $Array | ForEach{$KeyValue += $_.$key}
  $cols = @(); $cols += "Property" ; $cols += $keyValue
  $rows = @(); $array | Get-Member | Where {($_.MemberType -like "*Property") -AND ($_.Name -ne $key)} | ForEach{$Rows += $_.Name}
  $Rows = $Rows | sort unique
  $rows | ForEach{
    $tmpMatrix = "" | Select $cols
    $tmpMatrix.Property = $_
    $Matrix += $tmpMatrix

  ForEach($item in $Array){
    $ItemKey = $item.$key
    ForEach($Row in $Rows){
      $itemvalue = $Item.$Row
      if($itemvalue.GetType.Name -like "MultiValued*"){$itemvalue = $Item.$Row -Join "!#!"}
      ($Matrix | Where {$_.Property -eq $row}).$ItemKey = $itemvalue 
  $matrix = $matrix | sort property -Unique
Return $Matrix

$e = get-exchangeserver
$h = Flip-Array $e Name | ConvertTo-Html
$h | out-file .\exchange.html

#MsExchange Versions


So if you want to find out the version of Exchange you are running you can always run:

Get-ExchangeServer | Select Name, ServerRole, Edition, AdminDisplayVersion

Now this is cool, but it only shows the major version and not the CU.

You can find exchange build information from two main places:

I have dropped the information into a CSV file [ExchangeBuilds].   I found that updating a CSV file with new builds was easier than editing code all the time.

Now code below to create a summary matrix.  Enjoy

#Import the csv and covert to a hash table
$ExchangeBuilds = import-csv .\ExchangeBuilds.csv
$BuildMatrix = @{}
ForEach($Item in $ExchangeBuilds){
  $BuildMatrix.Add($item.Build, $item.version)

#Get exchange servers
Get-ExchangeServer | Select Name, ServerRole, Edition, AdminDisplayVersion

#Loop the servers to create the matrix
$matrix =@()
$ExchangeServers = Get-ExchangeServer | Sort Name
ForEach($Item in $ExchangeServers){
  $tmpServer = $item.Name + "." + $item.domain
  Write-Host $tmpServer 
  $tmpMatrix = "" | Select Name, Roles, Edition, FileVersion,UpdateRollup
  $tmpMatrix.Name = $tmpServer 
  $tmpMatrix.Roles = $Item.ServerRole
  $tmpMatrix.Edition = $Item.Edition

     6 {$key="SOFTWARE\Microsoft\Exchange\Setup"}
     8 {$key="SOFTWARE\Microsoft\Exchange\Setup"}
    14 {$Key="SOFTWARE\Microsoft\ExchangeServer\v14\Setup"}

  #Find the exchange binary path
  $type = [Microsoft.Win32.RegistryHive]::LocalMachine
  $regKey = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey($type, $tmpServer )
  $regKey = $regKey.OpenSubKey($key)

  #work out the file we need to use
     6      {$tmpPath = $regKey.GetValue("Services");      $tmpFile = "mad.exe";    $tmpPath += "\bin\"}
    Default {$tmpPath = $regKey.GetValue("MsiInstallPath");$tmpFile = "ExSetup.exe";$tmpPath += "bin\"}

  #Get the file verision of mad.exe or exsetup.exe
  $tmpPath = $tmpPath.replace(":", "$")
  $tmpunc  = "\\" + $tmpServer + "\" + $tmpPath + $tmpFile

  IF (test-path $tmpunc){
    $tmpVer = (dir "$tmpunc").VersionInfo.FileVersion
    $tmpMatrix.FileVersion = $tmpVer
    $tmpMatrix.UpdateRollup = $BuildMatrix.$tmpver
  $Matrix += $tmpMatrix

#MsExchange export GAL and Addresslists to CSV


So I was asked to export the GAL so we could give it to another part of the company that has not been assimilated yet.


Get-GlobalAddressList | ForEach{$n=$_.Name;$p=$pwd.path+"\"+$n+".csv";$l=$_.LdapRecipientFilter;$n;Get-Recipient -RecipientPreviewFilter $l -resultsize unlimited| Where-Object {$_.HiddenFromAddressListsEnabled -ne $true} | Select-Object Name,PrimarySmtpAddress | Export-CSV $p -NoTypeInformation}

Address List

Get-AddressList | ForEach{$n=$_.Name;$p=$pwd.path+"\"+$n+".csv";$l=$_.LdapRecipientFilter;$n;Get-Recipient -RecipientPreviewFilter $l -resultsize unlimited| Where-Object {$_.HiddenFromAddressListsEnabled -ne $true} | Select-Object Name,PrimarySmtpAddress | Export-CSV $p -NoTypeInformation}

Both lumps of code export to a csv file in the current path ($p)


#Powershell, SQL and #BlackBerry

So following on from my last post (https://blog.flaphead.com/2014/02/12/powershell-and-sql/) I wanted to add some SQL queries to it.

BlackBerry hosts all it’s information in a SQL database.  Now if you want to get a list of your BlackBerry users you can fire this SQL query at the BlackBerry SQL Database:

$SQLCommand = "
SELECT [ServerConfig]. [ServiceName]
      , [UserConfig]. [DisplayName]
      , [UserConfig]. [UserName]
      , [userconfig]. [MailboxSMTPAddr] as [SMTPAddress]
      , [SyncDeviceMgmtSummary]. [ModelName]
      , [SyncDeviceMgmtSummary]. [PhoneNumber]
      , [SyncDeviceMgmtSummary]. [PlatformVer]
      , [SyncDeviceMgmtSummary]. [IMEI]
      , [SyncDeviceMgmtSummary]. [HomeNetwork]
      , [SyncDeviceMgmtSummary]. [AppsVer]
      , [UserConfig]. [PIN]
      , [ITPolicy2]. [PolicyName]
      , [UserConfig]. [MailboxSMTPAddr]
      , [UserConfig]. [MailboxDN]
      , [UserConfig]. [ServerDN] as [ExchangeServer]
      , [UserConfig]. [AgentId]
      , [UserConfig]. [RoutingInfo]
      , [UserStats]. [MsgsPending]
      , [UserStats]. [LastFwdTime]
      , [UserStats]. [LastSentTime]
      , CASE [UserStats] . [Status]
            WHEN 14 THEN 'Failed to start'
            WHEN 13 THEN 'Stopped'
            WHEN 12 THEN 'Running'
            WHEN 9  THEN 'Redirection disabled'
            WHEN 7  THEN 'No PIN'
            WHEN 0  THEN 'Initializing'
            ELSE 'Unknown [' + CONVERT (varchar , [UserStats]. [Status] ) + ']'
        END AS UserStatus
  FROM [dbo] . [UserConfig]
   LEFT OUTER JOIN [dbo] .[UserStats]
    ON [UserConfig]. [Id] =[UserStats] . [UserConfigId]
   LEFT OUTER JOIN [dbo] .[ITPolicy2]
    ON [UserConfig]. [ITPolicy2Id] =[ITPolicy2] . [Id]
   LEFT OUTER JOIN [dbo] .[ServerConfig]
    ON [UserConfig]. [ServerConfigId] =[ServerConfig] . [Id]
   LEFT OUTER JOIN [dbo] .[SyncDeviceMgmtSummary]
    ON [UserConfig]. [Id] =[SyncDeviceMgmtSummary] . [UserConfigId]

$tmpCSVData = Run-SqlQuery "$sql" "$db" $SQLCommand

So this will give you an array ($tmpCSVData) with all your BlackBerry users in it ;-)

Now for some extra Powershell love, what you can do it look at the LastFwdTime and say if the user device has not forwarded any mail in say the last 60 days, create a CSV file.

$today = Get-Date
$AgeInDays = 60
$removalDate = $today.AddDays(-$AgeInDays)

$ToRemove = $tmpCSVData | where {$_.LastFwdTime -le $removaldate} | Select @{Expression={$_.SMTPAddress};Label="-u"}
$tcsv = $ToRemove | ConvertTo-Csv -NoTypeInformation
$csv= $tcsv | ForEach{$_.replace('"','')}
$csv | Out-File InactiveBlackBerryUsers.csv
$csv | Out-File $RootFolder\InactiveBlackBerryUsers.csv

Yeah and? well the CSV can be pushed in to the BESUserAdminClient resource kit tool, and you can delete the inactive accounts using the csv!

Yeah Baby!

#Powershell and SQL

So I have been “playing” a lot with Exchange, Powershell and SQL.  Essentially on a daily basis, I get an export of all AD Users, mailboxes with stats and then pump it in to SQL, do some magic, and play with Excel Pivot tables and charts to give the powers that be an idea of the mailbox estate.

I wanted to share a simple function that I use run sql queries from powershell, to push or pull data.

Function Run-SqlQuery(
    Write-Host "SQL Server:..." $SQLserver
    Write-Host "SQL Database:." $SQLdb
    Write-Host "SQL User:....." $SQLUserName
    Write-Host "SQL Pswd:....." $SQLUserPassword
    Write-Host "Verbose:......" $Verbose
    Write-Host "SQLcmd:......."
    Write-Host $SQLcmd -foregroundcolor Yellow

  $Connection = New-Object System.Data.SQLClient.SQLConnection
    #Use Trusted Connection
    Write-Host "Using a trusted connection"  -foregroundcolor Green
    $Connection.ConnectionString ="server=$SqlServer;database=$SqlDb;trusted_connection=true;"
    #Use UserName and Password
     Write-Host "Using a specified username & password for connection"  -foregroundcolor Green
    $Connection.ConnectionString ="server=$SqlServer;database=$SqlDb;User ID=$SQLUserName;Password=$SQLUserPassword;"

  $Command = New-Object System.Data.SQLClient.SQLCommand
  $Command.CommandType = [System.Data.CommandType]"Text"
  $Command.Connection = $Connection
  If($Error.Count -gt 0){Exit}
  $Command.CommandText = $SQLCmd 
  $Command.CommandTimeout = 0

  $tmpDT = New-Object "System.Data.DataTable"
  $Reader = $Command.ExecuteReader()

  $i=0;$tmpDT | ForEach{$i++}

  Write-Host $i "Records returned"
  Return $tmpDT

Enjoy, have some working examples coming soon too ;-) but in essence run it like:

$sqlquery = "Select * from sometable"
$sqlserver = "mysqlserver"
$sqldb = "mydb"
run-sqlquery -SQLServer $sqlserver -SQLdb $sqldb -SQLcmd $sqlquery

This assumes the account you are using has trusted credentials, otherwise use the -SQLUserName and -SQLUserPassword switches